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”.
  4. That’s it………no need to process the full cube now………

 

 

     Public Sub Main()

        Try

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

 

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

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

 

            For i = 0 To strSplitMeasureGroup.Length - 1

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

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

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

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

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

                strCubeCommand = strCubeCommand & "<MeasureGroupID>" & strSplitMeasureGroup(i) & _

                "</MeasureGroupID>" & Chr(13)

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

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

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

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

            Next

            strCubeCommand = strCubeCommand & "</Batch>"

 

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

            Dts.Events.FireInformation(1, "", Dts.Variables("User::varCubeCommand").Value.ToString, "", 0, False)

 

            Dts.TaskResult = Dts.Results.Success

 

        Catch ex As Exception

 

            Dts.Events.FireInformation(1, "", ex.Message, "", 0, False)

            Dts.TaskResult = Dts.Results.Failure

 

        End Try

    End Sub

 

 

 

 - Mohit Nayyar

 

Attachment: figure 1.JPG
Published 22 October 2007 20:35 by mohitnayyar
Filed under: ,

Comments

No Comments