http://www.sqlservercentral.com/blogs/briankmcdonald/2010/12/07/automating-the-processing-of-a-measure-groups-index-using-ssis/

Printed 2014/08/01 10:02AM

Automating the Processing of a Measure Groups Index Using SSIS

By Brian K. McDonald, 2010/12/07

In a recent post (Processing a Measure Groups Index Using SSMS), I showed you how to utilize SQL Server Management Studio to process just the index of a measure group. In this post, I am going to show you how you can script the processing index out and then run it from within an SSIS package. If you followed along in my last post, I will pick up on step number 4 where I updated the Processing Option to “Process Index”.

 

Step 4 - Continued: Change Processing Options to “Process Index”

Update the Process Options by selecting the down arrow and then choosing “Process Index” as depicted in my screenshot below with a red square and yellow highlight.

Change Processing Option - Brian K McDonald 

Now script it out by clicking on the down arrow next to the script button and select “Script Action to New Query Window” like shown below.

Script it out - Brian K McDonald 

 

After the script is generated, click Cancel on the Process Measure Group window to see the results of scripting out to a new query window. Results may look something like the XMLA script shown below.

Resulting XMLA - Brian K McDonald 

You could just hit F5 or the “Execute” button right here within SSMS, but that’s not what I want to show you. Now we get to the fun part! I am going to use this script here in an SSIS package. I am going to fire up BIDS (Business Intelligence Development Studio) and create a new package. Then I’m going to drag an Analysis Services Execute DDL Task onto the Control Flow and then double click it to open the editor. Since I have my cube on my local box, I set up a connection to my localhost. The SourceType property is DirectInput by default, but I could choose to load my XMLA script via a file connection or a variable. I chose to just paste my XMLA script above into the SourceDirect property (shown below) and clicked OK to save my settings.

Execute DDL Editor - Brian K McDonald 

 

If all goes well, I can execute my package and it will return green. J In this case, it did…So now, I could deploy this package and schedule it to be executed as part of a maintenance plan for the cube.

 

I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me at any of the below methods.

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com

Blogs: SQLBIGeek | SQLServerCentral | BIDN | SQLServerPedia

Twitter: @briankmcdonald

LinkedIn: http://tinyurl.com/BrianKMcDonald

 

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.