About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

Search

Report Portal
blank

SSIS Package to generate SSAS 2005 aggregates

March 24th, 2007 by Vidas Matelis

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

Posted in SSAS, SSIS | 8 Comments »

8 Responses

  1. Ravindran Says:

    Hi,
    I am very new to this BI. I am having some basic doubts. Where is the connection area?

    Some other doubts.
    We can able to prepare the cube in SSIS or we will create cube in SSAS then we use it in SSIS.

    Please help me.

  2. Ravindran Says:

    For example,
    I am having a database in sql server 2005, in this DB i having some datas, some other datas will be in MS-Access and some other stores a values in Excel. I want to use all the datas and create a Report for it. If possible pls tell me by Step by step.

    Thanks in advance.

  3. Kevin Says:

    Vidas, I tried the script and followed directions to create it and I am getting a Error –

    ‘Error in the aggregation designer. The ‘_Internet Orders’ aggregation design object has zero estimated rows; no design aggregations can be designed.’

    I am running against the AdventureWorks OLAP db in 2005.

    Any idea what may be causing this?

  4. Vidas Matelis Says:

    Kevin,
    Aggregation designer requires that each partitiona and dimensions has record count defined. YOu’ll not be able to generate aggregations without number even manually. I believe BIDS Helper utility has an option to get all counts from relationship database.

    Vidas

  5. Bohdan Hotskyy Says:

    Kevin, Vidas, it seems to me that error above is not connected with “zero estimated rows”. I think that a main reason of that error is that you try to design aggregations for LINKED measure group. To skip aggregation design for linked measure groups, try to add something like:

    If oMeasureGroup.IsLinked Then
    Continue For
    End If

    at the beginnig of loop by measuregroups and error will go away.

  6. Vidas Matelis Says:

    Bohdan,

    Adventure Works database does not have linked measure groups, so most likely his problem was related to record count.
    But you have a valid point here – I did not filter our linked measure groups, so I updated my script with your code. I just don’t have time to test it now.

    Thanks for posting this fix!

  7. Bohdan Hotskyy Says:

    Vidas, take a look into Adventure works database, cube “Mined Customers”. There are 5 linked measure groups here. So, I insist that exact this is an issue in Kevin’s case :-). Beside, record counts exists by default in AW database.

  8. Bharathi Says:

    Hi Ravindran,

    It has been a long time since you posted this question, Sure you might have got an answer for it, If not,

    “Some other doubts.
    We can able to prepare the cube in SSIS or we will create cube in SSAS then we use it in SSIS.”

    SSAS is used to build the cube (Build aggregations) ans SSIS can be used to refresh the data in the cube.

    Hope this helps, If not, you can send me a mail.