Extracting Data from SSAS in SSIS

Scenario: I have a requirement to design a SSRS report merging both data from a SSAS cube and from a relational datamart.

Instead of talking about the step by step solution for the above scenario, I just write about the challenges I faced. (Applicable only to SQL Server 2005)

1) Setting up the cube OLAP Server as a linked server in relational database posed lots of problems.
Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB provider “MSOLAP” for linked server.
Recommended solution:
   1. Start –> Run –> Dcomcnfg
   2. Component services –> My computer –> DCOM config –> MSDAINITIALIZE
   3. Right click on MSDAINITIALIZE –> properties –>security
   4. Add the SQL Start-up account ( you may have to go to services in administrative tools under control panel to check log on account) under   “launch and activation permission”, “Access permission” and “Configuration permission”.
   5. Give full rights.

2) When designing a SSIS package to extract the data from the cube, the DataFlowTask with OLE  DB -Analysis Services Provider, always failed. Further investigation (spent lots of time) revealed the following fix from Microsoft:
Change the Provider to Native OLE DB\Microsoft OLE DB Provider for Analysis Services 9.0, – Click the Connection Properties tab:
You need to tell the Connection Manager to return the query in tabular format, so in the Extended Properties text box enter Format=Tabular

 3) Then, the SSAS datatype and the SSIS datatype didnot match – failed to convert from unicode to non-unicode. The following article addresses the problem.

4) Finally the MDX query had to finetuned to retrived only the relevant result (Caption, Name, All Members) – Good Understanding of MDX will be very beneficial when trying to use ssas data inside a SSIS package.

Four ways to generate a report off of SSAS:


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: