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