Selective Cube Measure Groups processing using “Analysis Services Processing Task” in SSIS

Sometime its not feasible to process the full cube, so it makes sense to only process the selective measure group in the cube.

This is especially true when you have really big cube which takes time to process and sometime we just need to load data for only few business metrics.

Here is the small example to process only selective measure groups using “SSIS Analysis Services Processing Task”.

I am assuming that the measure group names are stored in a variable and after that we need to create “Processing Commands” to process the cube.

Processing Commands: We need to set this property in “Analysis Services Processing Task” to process the measure groups. So I will be using “Script Task” to generate the command.

Just make sure that the “Delay Validation” property of “Analysis Services Processing Task” is set to “True”.

Follow these steps now……..

1. Get comma separated list of all measure groups needs to be processed in local SSIS variable let say “varMeasureGroups”. You can get this list from some sort of configuration, so no hard-coding ;)
   
2. Creates the command using “Script Task” and store the final command in local SSIS variable let say “varCubeCommand”
   
3. In “Analysis Services Processing Task” task set the property “Processing Commands” using expressions to the recently created variable named “varCubeCommand”.

That’s it………no need to process the full cube now………

Dim strSplitMeasureGroup As String(), i As Integer, strCmd As String

strSplitMeasureGroup = Split(Dts.Variables("User::varMeasureGroups").Value.ToString, ", ")

strCmd = "<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">"

For i = 0 To strSplitMeasureGroup.Length – 1

strCmd = strCmd & "<Process xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" "

strCmd = strCmd & "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">" & Chr(13)

strCmd = strCmd & "<Object>" & Chr(13)

strCmd = strCmd & "<DatabaseID>myCube</DatabaseID>" & Chr(13)

strCmd = strCmd & "<CubeID>myCube</CubeID>" & Chr(13)

strCmd = strCmd & "<MeasureGroupID>" & strSplitMeasureGroup(i) & "</MeasureGroupID>"&chr(13)

strCmd = strCmd & "</Object>" & Chr(13)

strCmd = strCmd & "<Type>ProcessFull</Type>" & Chr(13)

strCmd = strCmd & "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" & Chr(13)

strCmd = strCmd & "</Process>" & Chr(13)

Next

strCmd = strCmd & "</Batch>"

Dts.Variables("User::varCubeCommand").Value = strCmd

- Mohit Nayyar