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))
oPartition.Drop()
End If
Next k
End If
Next j
Next i
End Sub
Jesse Orosz: SQL Server Analysis Services Blog 