AMO – Delete All Partitions From a Database

From time to time I’ll need to recreate the dev/beta environment and I have to do that from production. I want an exact copy of production minus all the partitions that have been created with the exception of the template partitions.


At first I would go in and change the XMLA script manually to remove those partitions. However, if there were a lot of measuregroups (50-100), this could take upwards of an hour. Being that it was such a pain I probably didn’t keep dev and beta as up-to-date as I should have.


An Analysis Services Stored Procedure is perfect to handle this task. Works beautifully and what took me an hour to do before now takes 30 seconds.


I decided to use arrays within the procedure even though you could write the proc without them.




*objDatabase is a database object I’ve set in another function



  Public Sub DeleteAllPartitions()


      Dim oCube As Cube

      Dim oMeasureGroup As MeasureGroup

      Dim oPartition As Partition

      Dim i As Integer

      Dim j As Integer

      Dim k As Integer

      Dim AryCubes As String()

      Dim AryCubesSize As Integer

      Dim AryMeasureGroups As String()

      Dim AryMeasureGroupsSize As Integer

      Dim AryPartitions As String()

      Dim AryPartitionsSize As Integer

‘Create the cube array (AryCubes)

      AryCubesSize = (objDatabase.Cubes.Count – 1)

      ReDim AryCubes(AryCubesSize)


      ‘Loop through each of the cubes in the database and throw the cube names in an array

      For Each oCube In objDatabase.Cubes

          AryCubes(i) = oCube.Name

          i = i + 1

      Next oCube


      ‘Loop through the cube array

      For i = 0 To AryCubesSize


          ‘Set the cube object to the current item in AryCubes

          oCube = objDatabase.Cubes.GetByName(AryCubes(i))


          ‘Create the measuregroup array (AryMeasureGroups)

          AryMeasureGroupsSize = (oCube.MeasureGroups.Count – 1)

          ReDim AryMeasureGroups(AryMeasureGroupsSize)


          ‘Set j back to zero for the current item

          j = 0


          ‘Loop through each of the measure groups in the cube and throw the measure group names in an array

          For Each oMeasureGroup In oCube.MeasureGroups

              AryMeasureGroups(j) = oMeasureGroup.Name

              j = j + 1

          Next oMeasureGroup


          ‘Loop through the measure group array

          For j = 0 To AryMeasureGroupsSize


              ‘Set the measure group object to the current item in AryMeasureGroups

              oMeasureGroup = oCube.MeasureGroups.GetByName(AryMeasureGroups(j))


              ‘If the measuregroup is linked then don’t delete partitions

              If oMeasureGroup.IsLinked = False Then


                  ‘Create the partition array (AryPartitions)

                  AryPartitionsSize = (oMeasureGroup.Partitions.Count – 1)

                  ReDim AryPartitions(AryPartitionsSize)


                  ‘Set k back to zero for current item

                  k = 0


                  ‘Loop through each of the partitions in the measure group and throw the name in an array

                  For Each oPartition In oMeasureGroup.Partitions

                      AryPartitions(k) = oPartition.Name

                      k = k + 1

                  Next oPartition


                  ‘Loop through the partition array and drop the partition if its not the template partition

                  For k = 0 To AryPartitionsSize

                      If AryPartitions(k) like "template" Then         

                          oPartition = oMeasureGroup.Partitions.GetByName(AryPartitions(k))


                      End If

                  Next k


              End If


          Next j


      Next i


  End Sub

This entry was posted in Uncategorized. Bookmark the permalink.

1 Response to AMO – Delete All Partitions From a Database

  1. Vidas says:

    Link to this post was added to our website in the [Analysis Services Articles]/[AMO] section:

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s