Cisco Information Server (CIS) and MS OLAP

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

PROC FORMAT Error with CNTLIN

What I was doing:

Using a SQL Server table to feed a PROC FORMAT using CNTLIN.

Error message seen:

ERROR: Cannot mix missing and nonmissing values in the same range

Cause:

START column was defined as character and END column was defined as a numeric (decimal)