Welcome to EMC Consulting Blogs

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

Public Class ScriptMain

    Const subComponentName As String = "Partition Creator"

    Public Sub Main()

        Dim server As New Server
        Dim databaseName As String
        Dim cubeName As String
        Dim measureGroupID As String
        Dim partitionID As String
        Dim mg As MeasureGroup
        Dim cube As Cube
        Dim vars As Variables



        Try
            'Lock variables
            Dts.VariableDispenser.LockForRead("User::OLAPDatabaseName")
            Dts.VariableDispenser.LockForRead("User::OLAPMeasureGroupID")
            Dts.VariableDispenser.LockForRead("User::OLAPPartitionID")
            Dts.VariableDispenser.LockForRead("User::OLAPCubeName")
            Dts.VariableDispenser.GetVariables(vars)

            databaseName = vars("User::OLAPDatabaseName").Value.ToString()
            cubeName = vars("User::OLAPCubeName").Value.ToString()
            measureGroupID = vars("User::OLAPMeasureGroupID").Value.ToString()
            partitionID = vars("User::OLAPPartitionID").Value.ToString()

            server.Connect(Dts.Connections("olap").ConnectionString)
            cube = server.Databases(databaseName).Cubes(cubeName)
            mg = cube.MeasureGroups(measureGroupID)

            Dts.Events.FireInformation(1, subComponentName, "Server=" + server.Name + " Database=" + databaseName + " Cube=" + cubeName + " MeasureGroup=" + measureGroupID, "", 0, True)

            'The real work goes on here. It checks to see if the given measure group has a partition
            '   named for the given week. If it doesn't then it creates it.
            If (Not PartitionExists(mg, partitionID)) Then
                If CreatePartition(mg, partitionID) Then
                    Dts.Events.FireInformation(1, subComponentName, "Creating partition '" + partitionID + "' on measure group '" + mg.ID + "'", "", 0, True)
                    'Commit changes to the cube
                    cube.Update(UpdateOptions.ExpandFull)
                Else
                    Dts.Events.FireInformation(1, subComponentName, "Creation of partition '" + partitionID + "' on measure group '" + mg.ID + "' failed!", "", 0, True)
                End If
            Else
                Dts.Events.FireInformation(1, subComponentName, "Partition '" + partitionID + "' on measure group '" + mg.ID + "' already exists!", "", 0, True)
            End If



        Catch ex As Exception
            Dts.Events.FireError(-1, subComponentName, ex.Message, "", 0)
        Finally
            vars.Unlock()
        End Try

        Dts.TaskResult = Dts.Results.Success
    End Sub

    Function CreatePartition(ByRef mg As MeasureGroup, ByVal partitionID As String) As Boolean
        Dim partition As Partition
        Dim queryString As String

        Try
            'Clone the default partition and then change its name and ID
            ' Assumption made here that the measure group has a default partition with the same name
            partition = mg.Partitions(mg.ID).Clone()
            partition.ID = partitionID
            partition.Name = partition.ID

            queryString = "---" 'Set query string here as appropriate
            CType(partition.Source, QueryBinding).QueryDefinition = queryString
            Dts.Events.FireInformation(1, "", "Changing query string of Partition '" + partitionID + "' to '" + queryString + "'", "", 0, True)

            'Add the new partition to the measure group
            mg.Partitions.Add(partition)

            'Commit the changes
            mg.Update(UpdateOptions.ExpandFull)

            Return True
        Catch ex As Exception
            Dts.Events.FireError(-1, subComponentName, ex.Message, "", 0)
            Return False
        End Try
    End Function

    Function PartitionExists(ByVal mg As MeasureGroup, ByVal partitionID As String) As Boolean
        Dim returnVal As Boolean
        Dim partition As Partition

        returnVal = False
        'Loop over partitions. If it finds the one its after, flip a boolean flag
        For Each partition In mg.Partitions
            If partition.ID = partitionID Then
                returnVal = True
            End If
        Next

        Return returnVal
    End Function
End Class