Extracting Olap Data from SSAS using SSIS

There was a question on the newsgroup a few months ago asking how to extract data from an Analysis Services cube and store it in a SQL table using SSIS.

I responded saying that in theory you should be able to setup a connection using the OLE DB provider for MSOLAP 9.0 and then put an MDX query in place of the SQL text. And I even when as far as setting up a package and previewing the data to make sure this was a viable approach. To this point everything looked fine and I assumed that the next step of mapping the results into a SQL table was a “done deal”. Well, as you can probably guess, when you assume you make a donkey out of everyone :)

No matter what I tried I kept getting an OLE DB error 80004005. This seems to be a general sort of error as google turned up lots of hits, even a few that related to the msolap provider, but no one had any answers.

One work around I found was to define a linked server in SQL Server using the following script.

USE master
GO

/* Add new linked server */
EXEC sp_addlinkedserver
@server='LINKED_OLAP', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='localhost', -- analysis server name (machine name)
@catalog='Adventure Works DW' -- default catalog/database

And then I wrapped my MDX statement in an openquery call.

Eg. 

SELECT * 
FROM OpenQuery(linked_olap,'SELECT --measures.members 
  {Measures.[Internet Sales Amount]} ON COLUMNS, 
  [Date].[Month].members ON ROWS 
FROM [Adventure Works]')

One of the advantages of this approach is that instead of doing a "SELECT *" you can alias the verbose names that come out of Analysis Services.

There is a bug in the openquery implementation which I believe should now be fixed in SP1, but I have not had time to confirm this yet. (see details on the issue here: http://geekswithblogs.net/darrengosbell/archive/2006/01/14/65848.aspx , but assuming that this is fixed in SP1, everything should be sweet.

Print | posted on Wednesday, April 26, 2006 8:17 PM