Home > SSIS Best Practices > SSIS #113 – Querying SSAS Cube with MDX from SSIS

SSIS #113 – Querying SSAS Cube with MDX from SSIS

I worked on a SSIS package a few months back to retrieve data from a SSAS cube with MDX queries. A co-worker recently is developing a SSIS package to perform a similar task, but couldn’t get the SSIS package to work. The data flow was lost somewhere and the package could never finish the data loading.

This is what it will look like in the Data Flow task. The OLE DB Source will stay yellow forever.

image

I couldn’t remember right away how my SSIS package was configured to make the data flow work. I had to find my old SSIS package and open it to refresh my memory.

Need to set Format=Tabular in the Data Link Extended Properties

After choosing the OLE DB Provider for Analysis Services 10.0 as the provider for the connection manager, we need to go to the All tab to set Format=Tabular in the Data Link Extended Properties.

image

There are many blogs on this little “secret” developers discovered. Some claimed that this is a fix for a bug in SSIS 2005 SP1. I had to do this in both SSIS 2005 and 2008 to make it work. So I am not sure if it is caused by a bug or a designed “feature” in SSIS.

Neither can I find more information about what this property value means. You are welcome to share if you have more insight on this.

The happy green color is what you will see once you set Format=Tabular in the Data Link Extended Properties.

  image

A couple of notes:

1. About the data type. Whenever you try to access the column information, you will get a “nice” warning from SSIS that data type DT_WSTR (nvarchar) will be used instead. Again if you have more insight on this, you are welcome to share.

image

2. I used a Data Conversion transformation to convert the DT_WSTR to DT_STR or DT_NUMERIC accordingly.

image

  1. jvcampbell
    August 29, 2012 at 7:11 am

    Did you find a way to deal with empty results?

    The query doesn’t return metadata and so the package raises an error – have you found an elegant way to deal with this?

    Jason

    Like

  2. David Simon
    September 14, 2012 at 3:44 pm

    Hi,
    regarding datatypes, you can add “ReturnCellProperties=true” in Extended Properties, according to this post:
    http://social.msdn.microsoft.com/Forums/eu/sqlintegrationservices/thread/8aee6769-5177-47d3-91b7-c637b51b49f9

    but it seems it works only with measures

    Like

  3. January 9, 2013 at 10:33 pm

    Awesome thanks,

    Like

  4. helveticious@gmail.com
    February 1, 2013 at 10:52 pm

    THANK YOU! The Extended Properties made my new package work, although strangely it works fine with my old package without that property changed.

    Like

  5. Luis Diaz
    February 10, 2014 at 7:44 pm

    great, thanks so much for your help, the screenshots are very helpful
    Luis

    Like

  6. murtaza
    January 7, 2015 at 9:20 am

    worked for me

    Like

  1. No trackbacks yet.

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: