Delete All Partitions With a Certain Prefix in AMO

We partition by several different date periods: Hour, Day, Week, and Month.  If processing a monthly partition, we want to delete all days that have been processed for that month. To do so, we’ll look for all partitions that start with D_200706 (June 2007) and delete it if it exists. Same with creating a day partition: if creating a partition for D_20070812, we’ll look for all partitions starting with H_200708 and drop them.

The AMO code we use to do so:


Public
Sub DeletePartitionPrefix(ByVal sPartitionPrefix)
 

    Dim oDeletePartition As Partition

    Dim i As Integer

    Dim AryPartitions As String()

    Dim AryPartitionsSize As Integer

 

    AryPartitionsSize = (objMeasureGroup.Partitions.Count – 1)

    ReDim AryPartitions(AryPartitionsSize)

 

    For Each oDeletePartition In objMeasureGroup.Partitions

        AryPartitions(i) = oDeletePartition.Name

        i = i + 1

    Next oDeletePartition

 

    For i = 0 To AryPartitionsSize

       If AryPartitions(i) Like sPartitionPrefix & "*" Then

            DeletePartition(AryPartitions(i), "")

        End If

    Next i

End Sub

  

Public Sub DeletePartition(ByVal sPartitionName, ByVal DeleteType)

 

    Dim BeginTime As DateTime

    Dim oPartition As Partition

 

    Try

        If strKeepProcessing = False Then Exit Sub

        objServer.BeginTransaction()

        BeginTime = Now

        oPartition = objMeasureGroup.Partitions.GetByName(sPartitionName)

        oPartition.Drop()

        objServer.CommitTransaction()

        If DeleteType = "Cleanup" Then

            InsertCubeProcessingLog("Delete Partition Cleanup ", 1, 1, BeginTime, …

        Else

            InsertCubeProcessingLog("Delete Partition", 1, 0, BeginTime, …

        End If 

     Catch

        objServer.RollbackTransaction()

        Dim ErrorReplace As String = Err.Description

        ErrorReplace = ErrorReplace.Replace("’", "")

        InsertCubeProcessingLog("Delete Partition Failed: " & Err.Number & "-" & ErrorReplace,…

        strKeepProcessing = False

    End Try

End Sub

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s