Large Scale EDW Best Practices

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse


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:!CD3E77E793DF6178!332.entry