SSIS/SSAS: Process SSAS dimensions and measure groups individually
On my current project I am implementing SQL Server Analysis Services (SSAS) and recently I was having a problem doing a full process of my cube. I've got many many measure groups in the cube each containing twelve partitions and the cube process was hanging due to lack of resource issues on the Analysis Server.
Those issues needed sorting out but in the meantime my cube was sitting there unprocessed and I needed to process it. Quick. I decided I wanted something that would process all of my dimensions and measure groups individually and hence I built a SSIS package that would do it.
Here's a screenshot of my package. You can see that I treat the dimensions and measure groups seperately.
So what does this do? Well its pretty simple really:
- A "Get Dimensions" uses a source script component to connect to an Analysis Server (the server name is stored in a variable) and get a list of all the dimensions in all databases on that server. There is a conditional split transform in there which currently doesn't do anything but can be edited to filter out the dimensions/databases that you don't want to process
- I construct the XMLA processing command inside a variable using an expression. I output that XMLA command using a script task as an OnInformation event which means it will get captured by whatever log provider you are using. Also, it will get output to the Visual Studio Output window, and that is very useful indeed.
- Fires the XMLA command using the Analysis Services DDL Task.
It then does the same for all the measure groups on that server.
Feel free to download the package from here. I've already used this many times over the past 3 days - I prefer it to just issuing a blanket "process everything" XMLA command because this way you can see what is happening under the covers and you can also filter which objects get processed. It also limits the load on your server because it isn't doing anything in parallel (which is what caused me to build it in the first place).
Have fun!!!
