How to run MDX/Create local cubes from SSIS

Just drag an Execute SQL Task and double click to open SQL Task Editor. Under the SQL Statement section, you will find the slot for Connection.

Click on New connection and you will see the Configure OleDb connection Manager window. Click on the New button.

Now , you will find the window called Connection Manager. Click the provider called Native OleDb/SQL Server Native Client and choose Microsoft OLEDB provider for Analysis Services 10.0.

Now, click ok and go back to the SQL Task Editor window and write your MDX statement (I used a Create Global Cube statement for creating a local cube). Now, you can exeucte the task and see the result.

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in MDX, SSAS (Analysis Service), Cube, SSIS (SQL Server Integration Service). Bookmark the permalink.

3 Responses to How to run MDX/Create local cubes from SSIS

  1. Lisa Olivieri says:

    This was extremely helpful. I’m hoping to use this for an ALTER CUBE statement to set a default member on a role playing dimension.

  2. Lisa Olivieri says:

    Hi, I just learned this is not the right way to do an ALTER CUBE statement. It needs to go into the cube calculations tab. Live and learn.

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