Extracting Olap Data from SSAS using SSIS

<< Documentation Tools | Home | SSIS collapses in a screaming heap >>

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.

Share This Post:
Short Url: http://wblo.gs/JsY

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

Comments on this post

# re: pink Extracting Olap Data from SSAS using SSIS

why this is so hard :(

Left by pink cameras on Dec 26, 2007 4:11 PM

# re: Extracting Olap Data from SSAS using SSIS

Where in SQL Server do I implement this code?

How can I implement the script to actually move the data from the cube to the table? Can I make a package?

Left by ACil on Aug 22, 2008 9:51 AM

# re: Extracting Olap Data from SSAS using SSIS

These are both T-SQL commands. You would run the first one against your relational server in Management Studio to configure your linked server.

Then you could use the second as a query in your datasource against your relational server to get it to pass the MDX query through to SSAS and return the flattened resultset.

Left by Darren Gosbell on Aug 23, 2008 8:48 AM

# re: Extracting Olap Data from SSAS using SSIS

IS THERE ANY OTHER WAY OTHER THAN OPENQUERY IN MY REQUIREMENT I NEED TO RUN EVERY 1HR THIS QUERY. IF I SELECT TO INSERT THE DATA USING LINKED SERVER HOW TO SCHEDULE IT.

PLEASE LET ME KNOW THAKS IN ADVANCE

Left by jim on Oct 30, 2008 6:30 AM

# re: Extracting Olap Data from SSAS using SSIS

@jim - Using OPENQUERY is probably the simplest way. Otherwise you would have to write some custom code. You could code a standard INSERT statement and either put it directly into a SQL Agent job or wrap it in a stored procedure and call that from an Agent job

eg.

INSERT INTO MyTable (...)
SELECT ...
FROM OPENQUERY(....)

Left by Darren Gosbell on Oct 30, 2008 7:05 AM

# re: Extracting Olap Data from SSAS using SSIS

Thanks for the post. I have struggled with this as well.

If you can't use Linked Servers, there is another way. Check out this post to help get around SSIS's errors when you try to execute an MDX query in an OLE DB Command Task:

http://www.sqlservercentral.com/articles/MDX/64697/

This works (at least, it does for me). The nice thing about the example is it's a complex example. So I ignored the Data Conversion stuff (didn't need it, but am grateful for the example if I need it someday).

The other metric I have for you is that, in a comparison that is apples-to-apples for my ETL process, the package that used an MDX query in an OLE DB Command finished in 43% of the time it took to run the same method with MDX via linked servers and openquery.

Still, it's nice to know other people are running into these issues. We should probably petition MSFT to make this easier via Connect and at least keep blog posting our solutions for others to find and improve on.

Left by CGomez on Dec 02, 2008 8:15 AM

# re: Extracting Olap Data from SSAS using SSIS

Yes, you would think that the integration between two MS products should be better than this.

I think my problem was the missing "format=tabular", but the error message was not really helpful.

I'm not surprised that it would be a lot faster than the linked server approach as I don't think it is a driect pass through. I think SQL will query SSAS and load the resultset into memory before passing it on to the client. I think it is this second "hop" that takes the extra time.

I think having a source adaptor for SSAS would be the cleanest way to go.

Left by Darren Gosbell on Dec 02, 2008 11:30 AM

# re: Extracting Olap Data from SSAS using SSIS

May you help me to do the openrowset sentence in sql 2008. I have the error: Cannot fetch a row from OLE DB provider "MSOLAP.4" for linked server "linked_olap4".

I'm using the following:

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

SELECT *
FROM OpenQuery(linked_olap4,'select {[Measures].[Order Count]} on columns, [Product].[Product Categories].members on rows from [Adventure Works]')

thanks for your help

Left by Alejandro on Jan 15, 2010 9:37 AM

# re: Extracting Olap Data from SSAS using SSIS

Have you tried removing the .4 from the end of the provider? The sample code fro this blog post was written for SSAS 2005, but 2008 does not seem to like trying to specify a specific version of the provider. (the MSOLAP provider also needs to have it's in-process property set to true in SSMS for this to work)

eg

EXEC sp_addlinkedserver
@server='LINKED_OLAP4', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP', -- OLE DB provider (no version number means the latest one is used)
@datasrc='localhost', -- analysis server name (machine name)
@catalog='Adventure Works DW 2008' -- default catalog/database
GO

SELECT *
FROM OpenQuery(linked_olap4,'select {[Measures].[Order Count]} on columns, [Product].[Product Categories].members on rows from [Adventure Works]')

Left by Darren Gosbell on Jan 15, 2010 11:10 AM

# re: Extracting Olap Data from SSAS using SSIS

Thanks for the nice article! It definitely help me to get around the problem..

Left by Pyay Nyein on Jun 03, 2010 8:13 PM

# re: Extracting Olap Data from SSAS using SSIS

Darren,
Forgive my ignorance here; you talk about connecting to OLAP (via SSIS) not working as a bug; fixed with SP1. How are you creating the connection? I simply can't figure out how to setup SSAS as a data source in SSIS? Both are SQL 2005. What type of data source / query is it?

Left by Vinny on Nov 13, 2010 7:48 AM

# getting the data currency from the OLAP

Hi Darren,

I wanted to know the max date for each measure in the cube which as got not null value and dump all the measures data currency into a sql table.

Could you let me know how this can be achieved?

Left by Preethi Khatore on Jan 04, 2011 2:47 PM

Darren Gosbell [MVP] - Random Procrastination