Cisco Information Server (CIS) and MS OLAP

October 12, 2017
By

This post was kindly contributed by Computing from Out in Left Field - go there to comment and to read the full post.

Ok, so my recent issue was integrating CIS with Microsoft OLAP cubes. The normal way to do this is to set up MS OLAP (SSAS) with a website and use XML/A to connect. I started down that path and decided to try a different tack based some StackOverflow articles. Here is my approach that worked great:

The trick here is to use a Linked Server in the middle of the connection. For this, you need a SQL Server
instance.


Steps


Open SSMS. Create a new query


In a SQL Server instance, set up a Linked Server. Here is an example:

EXEC sp_dropserver ‘TEST_OLAP’
EXEC master.dbo.sp_addlinkedserver
@server=’TEST_OLAP’,
@srvproduct=’MSOLAP’,
@provider=’MSOLAP’,
@datasrc=’APSEPXXXX’,
@catalog=’Commitments’


EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=’TEST_OLAP’,
@useself=’False’,
@locallogin=NULL,
@rmtuser=’MS\XXXXX’,
@rmtpassword=’***********’
GO

• Create a query to go against the Linked Server and pass the MDX query:
select
“[Policy].[Master Policy].[Master Policy].[MEMBER_CAPTION]” AS MasterPolicy,
“[Due Date].[Calendar].[Year].[MEMBER_CAPTION]” AS Year,
“[Due Date].[Calendar].[Quarter Name].[MEMBER_CAPTION]” AS Quarter,
“[Due Date].[Calendar].[Month Name].[MEMBER_CAPTION]” AS Month,
“[Policy].[Policy Number].[Policy Number].[MEMBER_CAPTION]” AS Policy,
“[Measures].[Commitments Closed Within TAT]” As CommitmentsClosedWithinTAT
from openquery
(
‘TEST_OLAP,
‘SELECT NON EMPTY { [Measures].[Commitments Closed Within TAT] } ON COLUMNS, NON EMPTY
{ ([Policy].[Master Policy].[Master Policy].ALLMEMBERS *
[Due Date].[Calendar].[Month Name].ALLMEMBERS *
[Policy].[Policy Number].[Policy Number].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Due Date].[Calendar].[Year].&[2015] } ) ON COLUMNS FROM [Commitments
Advocate4Me])’
)


• NOTE: If you need to determine the odd MDX variables coming back, run this query:
select * INTO #TEMP_A4M from openquery
(
‘TEST_OLAP,
‘SELECT NON EMPTY { [Measures].[Commitments Closed Within TAT] } ON COLUMNS, NON EMPTY
{ ([Policy].[Master Policy].[Master Policy].ALLMEMBERS *
[Due Date].[Calendar].[Month Name].ALLMEMBERS *
[Policy].[Policy Number].[Policy Number].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Due Date].[Calendar].[Year].&[2015] } ) ON COLUMNS FROM [Commitments
Advocate4Me])’
)


select * from tempdb.sys.columns where object_id =object_id(‘tempdb..##TEMP_A4M’);


  1. Set up your SQL Server Data Source

• Put it into Packaged Query and do Design by Exampl

This post was kindly contributed by Computing from Out in Left Field - go there to comment and to read the full post.

Welcome!

SAS-X.com offers news and tutorials about the various SAS® software packages, contributed by bloggers. You are welcome to subscribe to e-mail updates, or add your SAS-blog to the site.

Sponsors





Dear readers, proc-x is looking for sponsors who would be willing to support the site in exchange for banner ads in the right sidebar of the site. If you are interested, please e-mail me at: tal.galili@gmail.com
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.