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.