Recently I posted a blog entry on how to process all dimensions in a single Analysis Services 2005 database using SQL Server Integration Services (SSIS) package. Here I’ll add code to this package that will allow you to process whole database, cubes, measure groups or partitions.

Please follow steps from previous post on how to create new package and add script component. Then for the script component use code from bellow:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Public Sub Main()
        Dim ExecutionSuccessfull As Boolean = True ‘ If true, package executed without errors
        Dim sProcessType As String = “ProcessFull”

        Dim oConnection As ConnectionManager
        oConnection = Dts.Connections(”DM”)
        Dim sServer As String = CStr(oConnection.Properties(”ServerName”).GetValue(oConnection))
        Dim sDatabaseID As String = CStr(oConnection.Properties(”InitialCatalog”).GetValue(oConnection))
        Dim oServer As New Microsoft.AnalysisServices.Server
        oServer.Connect(sServer) ‘ connect to the server and start scanning down the object hierarchy
        Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabaseID)

        If oDB Is Nothing Then
            ExecutionSuccessfull = False
            GoTo Done
        Else

            Dim oDim As Microsoft.AnalysisServices.Dimension
            Dim oCube As Microsoft.AnalysisServices.Cube
            Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup
            Dim oPartition As Microsoft.AnalysisServices.Partition

            oServer.CaptureXml() = True ‘ Start capturing XML.
            ‘ oDB.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) ‘ Use this to process whole DB at once
            ‘ Process all dimensions
            ‘For Each oDim In oDB.Dimensions
            ’    ‘ This will generate XMLA, but because CaptureXML is True, will not execute it!
            ’    oDim.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) ‘ Process all dimensions
            ‘ Next

            For Each oCube In oDB.Cubes
                ‘ oCube.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) ‘ Process all cubes

                For Each oMeasureGroup In oCube.MeasureGroups
                    ‘ oMeasureGroup.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) ‘ Process all measure groups

                    For Each oPartition In oMeasureGroup.Partitions
                        oPartition.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)  ‘ Process all partitions
                    Next
                Next
            Next
            oServer.CaptureXml() = False ‘ Stop capturing XML
            ‘ Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
            ‘ These are very important parameters!

            Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection
            Dim oResult As Microsoft.AnalysisServices.XmlaResult
            Dim dataBytes(0) As Byte

           
            oResults = oServer.ExecuteCaptureLog(True, True)

            Dim oMessage As Microsoft.AnalysisServices.XmlaMessage

            ‘Log the errors and warnings
            For Each oResult In oResults
                For Each oMessage In oResult.Messages
                    If oMessage.GetType.Name = “XmlaError” Then
                        ‘The processing failed
                        Dts.Log(oMessage.Description, 0, dataBytes) ‘ You need to setup proper package loging to see this!
                        ExecutionSuccessfull = False
                    Else
                        ‘It’s just a warning.
                        ExecutionSuccessfull = True ‘ if you want to fail on warning, change this to False
                    End If
                Next oMessage
            Next oResult

        End If
Done:
        oServer.Disconnect() ‘ disconnect from the server — we are done

        If ExecutionSuccessfull Then
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If
    End Sub

End Class

By default this package will build code to process each partition. This is just an example. There is commented out code there that can process all measure groups or cubes or whole database at once.  Use this code as guidance to build your own SSIS package that does what you want.

In my data warehouse design I decided to maintain the list of partitions in SQL Server table. I have a “partition base” table that contains base information about each measure group and partitioning field (I have just one, usually date field).  Then I have a stored procedure, that during each DW load calculates if new partitions have to be created or old ones have to be deleted and loads this information into extended partition information table. It also marks which partitions are affected by latest load and has to be reprocessed. Although this might seem like a complicated task, it is not. And it is well worth the effort. My SSIS package then compares this extended partition  table with actual SSAS database and then drops/creates new partitions and process partitions that have to be processed.

I would be interested to hear how other people are managing processing of partitions?