Vidas Matelis Analysis Services Blog

I prefer to use usage statistics to generate SSAS 2005 aggregates, but during the initial phases of a project they are not available. So I usually build a set of random aggregates, and after enough usage statistics are accumulated, I rebuild aggregates based on usage. Microsoft provides a nice wizard to generate aggregates on measure groups and partitions. But because I have to do this task way to many times, I decided to build a simple SSIS package that does it for me.

This package will cycle through cubes, then measure groups in specified Analysis Services database. It checks if the measure group already has any aggregation design assigned to it. If yes, then the package will make sure that each partition for this measure group will use this aggregation design. If no aggregation design is found for the specific measure group, then the package creates a new aggregation design and assigns it to all partitions in this measure group. To simplify design, I will assume that just one aggregation design is important for any measure group. If a measure group has more than one aggregation design already defined, I’ll just pick one randomly and try to use it for partitions in that measure group.

Steps to create such package:

• Start SQL Server Business Intelligence Development studio and create new project based on “Integration Services Project” template.Create new package “CreateSSASAggregates.dtsx”
• In “Connection Managers” area right mouse click and then choose “New Analysis Services Connection”. Choose server name and Initial Catalog for this connection. Rename connection to “DM”.
• From the toolbar drop “Script Task” into Control Flow area.
• Double click on “Script task” control, change Name and Description properties to your requirements, then in the Script area click on the button “Design Script”
• As a first step you have to add reference to Analysis Services. On the left side of the script window you should see “Project Explorer” and tree view of components. Right mouse click on the “References” folder and select “Add reference”. Then find component “Analysis Management Objects” and click “Add” then “OK”.
• In the script area replace existing code with the text from the following link. Here is code use, but my blog software replaces single and double quotes with special characters:

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

Public Class ScriptMain
    Public Sub Main()
        Dim optimizationWanted As Double = 30       ‘ 30%
        Dim maxStorageBytes As Double = 102400000   ‘ 100MB

        Dim oNewAD As Microsoft.AnalysisServices.AggregationDesign
        Dim optimization As Double
        Dim storage As Double
        Dim aggCount As Long
        Dim finished As Boolean

        Dim oConnection As ConnectionManager
        oConnection = Dts.Connections(“DM”)
        Dim sServer As String = CStr(oConnection.Properties(“ServerName”).GetValue(oConnection))
        Dim sDatabase 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(sDatabase)
        If oDB Is Nothing Then
            MsgBox(“Did not find expected database: ” & sDatabase, MsgBoxStyle.OkOnly, “Error looking for partition”)
            GoTo Done
        End If
        Dim oCube As Microsoft.AnalysisServices.Cube
        For Each oCube In oDB.Cubes
            Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup

            For Each oMeasureGroup In oCube.MeasureGroups

                If oMeasureGroup.IsLinked Then ‘ Added as per Bohdan Hotskyy suggestion (not tested)
                  Continue For
                End If
                If oMeasureGroup.AggregationDesigns.Count = 0 Then
                    ‘ Create addgregation designs
                    oNewAD = oMeasureGroup.AggregationDesigns.Add(oMeasureGroup.AggregationPrefix + “_” + oMeasureGroup.Name)
                    oNewAD.InitializeDesign()
                    optimization = 0
                    storage = 0
                    aggCount = 0
                    finished = False
                    While Not finished And optimization < optimizationWanted And storage < maxStorageBytes
                        oNewAD.DesignAggregations(optimization, storage, aggCount, finished)
                    End While
                    oNewAD.FinalizeDesign()
                    oNewAD.Update()
                    oMeasureGroup.Update()
                End If

                If oMeasureGroup.AggregationDesigns.Count > 0 Then
                    Dim oAD As Microsoft.AnalysisServices.AggregationDesign
                    oAD = oMeasureGroup.AggregationDesigns(0)
                    Dim oPartition As Microsoft.AnalysisServices.Partition
                    For Each oPartition In oMeasureGroup.Partitions
                        If oPartition.AggregationDesignID Is Nothing Then
                            oPartition.AggregationDesignID = oAD.ID
                            oPartition.Update()
                        End If
                    Next
                End If

            Next
        Next

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

        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

• Close script window and save package.
• Package is ready to be executed.

Enjoy,

Vidas Matelis