| 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’);
- 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. |