Deleting SSAS Cube Partitions Using PowerShell and AMO

I’ve really developed a jones for using PowerShell to make painful, repetitive, time consuming tasks much quicker. PowerShell’s great for automation, but it’s also great for running tasks on an adhoc basis.

Recently I had to delete about 600 partitions from a measure group that had thousands of partitions. Doing this manually would be ridiculous, so I created a sql script that used some dynamic -tsql to create the delete commands in xmla. I shared this as a post, and mused that if I had to run this more than once I’d create a Powershell script. Funnily enough, that is exactly what I had to do, and a weakness in using t-sql/xmla became apparent; if one of the partitions did not exist in the measure group then the xmla script would fail. So I needed to make a robust script, and using xmla was no longer an option as there is no “if exists” option.

Enter PowerShell

I knew I would be able to create a Powershell script that used AMO to check if a partition exists and drop it if it did. I also wanted the script to take into account any other partitions in other measure groups that may also need to be dropped. So I made sure the script uses PowerShell switches that can be included when calling the function, and if they are included then the pertaining partitions in that measure group will be deleted. So you can run the script for one, some or all of the measure groups in a cube.

You may have noticed that I said “function”, and that is because within this script there are three functions; the function that serves as the entry point into the script (remove-partitions), another function (set-partitions) that is called within the primary function to build up the name of the partitions, and finally the function that drops the partition (clear-partitions). So you will need to dot-source the script. Details of this are provided in the example below, and also the comments in the script.

Because the script requires customising to a particular cube, I’ve created a demo version that uses the Adventure Works 2012 Multidimensional Model Enterprise Edition, which has several measure groups that are partitioned by year. All that needs to be altered for any other cube is the name of the ssas database, the cube, measure group names and partition prefixes.

If extra measure groups need to be added then only the “remove-partitions” function needs to be updated: the other two functions will work irrespective of how many measure groups are added. A good option here might be to create an -all switch which includes all measure groups, because while 4 switches for all the measure groups seems manageable, any more might make it a chore to add the switches for all measure groups.

Below the script is an example of the script in action against the Adventure Works cube. I’ve also put comments in the script. The example is proper noddy example, so, as ever, if something is not clear feel free to add a comment to this post.


#function is called by the primary function 'remove-partitions' below
# 'set partition' builds the partition names up and returns the name as a script persisting variable
Function set-partitions
{
[CmdletBinding()]
    param(
    [Parameter(Position=0,mandatory=$true)]
    [Microsoft.AnalysisServices.Cube]
    $cube,
    [Parameter(Position=1,mandatory=$true)]
    [Microsoft.AnalysisServices.MeasureGroup]
    $measureGroupName,
    [Parameter(Position=2,mandatory=$true)]
    [string]
    $row,
    [Parameter(Position=2,mandatory=$true)]
    [string]
    $partitionPrefix)
        $MeasureGroup = $Cube.MeasureGroups.FindByName($measureGroupName)
        $PartitionName=$partitionPrefix + $row
        $script:Partition = $MeasureGroup.Partitions.FindByName($partitionName)
}
# primary function that the script enters into
# to access this function you need to dot-source the script
# When you dot source a script, all variables and functions defined in the script will persist in the shell when the script ends.
# dot-source the script like this ". C:\Users\richard.lee\Desktop\DeletePartitions.ps1"
# then run the function as so: "remove-partitions -ssasInstance "comppc01" -SQLDBInstance "comppc01" -ssasdb "AdventureWorksDW2012Multidimensional-EE" -startYear "2006" -endYear "2007" -ResellerOrders -ResellerSales"
# add/remove the measure group switches as required
# notice that sas cube and sqldbname have defaults and so are not included in example, but can be included and default values will be overwritten
# eg "remove-partitions -ssasInstance "comppc01" -SQLDBInstance "comppc01" -ssasdb "AdventureWorksDW2012Multidimensional-EE" -ssascube "adventure works dw 2014" -startYear "2006" -endYear "2007" -ResellerOrders -ResellerSales"

function remove-partitions
{
[CmdletBinding()]
    param(
    [Parameter(Position=0,mandatory=$true)]
    [string] $ssasInstance,
    [Parameter(Position=1,mandatory=$true)]
    [string] $SQLDBInstance,
    [Parameter(Position=2,mandatory=$true)]
    [string] $ssasdb,
    [Parameter(Position=3)]
    [string] $ssascube = "Adventure Works",
    [Parameter(Position=4)]
    [string] $SQLDBName = "AdventureWorksDW2012",
    [Parameter(Position=5,mandatory=$true)]
    [string] $startYear,
    [Parameter(Position=6,mandatory=$true)]
    [string] $endYear,
    [Parameter(Position=7)]
    [switch] $InternetOrders,
    [Parameter(Position=8)]
    [switch] $InternetSales,
    [Parameter(Position=9)]
    [switch] $ResellerOrders,
    [Parameter(Position=10)]
    [switch] $ResellerSales)    

[void].[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$script:Server = New-Object Microsoft.AnalysisServices.Server
$script:Server.connect($ssasInstance)

$dbase = $script:Server.Databases.FindByName($ssasdb)
#check that instance is up and running by returning name
$dbase|select name 

$Cube=New-object Microsoft.AnalysisServices.Cube
$Cube=$dbase.Cubes.FindByName($ssascube)
#check that cube exists but getting name state, and last processed date
# if not found it throws exception
$Cube|select name,state,lastprocessed 

# because partitions on measure groups are by year, we need to get years from database
# in your environment, this sql query is replaced by what your cubes are partitioned by.
$SqlQuery =
"SET NOCOUNT OFF
SELECT CalendarYear
  FROM [AdventureWorksDW2012].[dbo].[DimDate]
WHERE CalendarYear BETWEEN $startYear and $endYear
GROUP by CalendarYear"

#return sqlquery to sanity check what was run
$sqlQuery

# connect and execute
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLDBInstance; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

# create data table and insert into array
$Table = New-Object System.Data.DataTable
$SqlAdapter.Fill($Table)|out-null
$Array = @($table)

# count used for write-progress
# write-progress displays a progress bar
# helps gives us estimate on how long it takes

$count=$Array.Count
"There are $count items to be deleted"
if($count -gt 0)
{
# beginning of progress bar values
# we get first item from array and that becomes the year we want to delete
    for ($i = 0; $i -lt $count; $i++)
    {
    $row = $Array[$i]
# now switches come into affect; if they were included when calling the function then the 'set-partitions' function is called
# we pass values that pertain to that measure group'; it's name, the prefix of the partition and the year
# 'set partition' builds the partition names up and returns the name as a script persisting variable
# script persisting variable is then passed to the measure gorups partition name variable
# we have to check that the name is not null otherwise get-member fails
# out-null is used so that console is not filled up with tons of info we don't care about
# if the partition name is not null then we will drop the partition. if it is null then clearly it doesn't exist and it won't bother trying to delete something that is not there!
    #Internet Orders
    if ($InternetOrders.IsPresent)
        {
        set-partitions -cube $cube -measureGroupName "Internet Orders" -row $row.CalendarYear -partitionprefix "Internet_Orders_"
        if ($Script:Partition.Name -ne $null)     
            {
            $Script:Partition | Get-Member | Out-Null
            clear-partitions -instance $script:Server -db $ssasdb -cube $ssascube -partition $Script:partition -row $row -i $i -count $count
            }
        }
    #Internet Sales
    if ($InternetSales.IsPresent)
        {
        set-partitions -cube $cube -measureGroupName "Internet Sales" -row $row.CalendarYear -partitionprefix "Internet_Sales_"
        if ($Script:Partition.Name -ne $null)     
            {
            $Script:Partition | Get-Member | Out-Null
            clear-partitions -instance $script:Server -db $ssasdb -cube $ssascube -partition $Script:partition -row $row -i $i -count $count
            }
        }
    #Reseller Orders
    if ($ResellerOrders.IsPresent)
        {
        set-partitions -cube $cube -measureGroupName "Reseller Orders" -row $row.CalendarYear -partitionprefix "Reseller_Orders_"
        if ($Script:Partition.Name -ne $null)     
            {
            $Script:Partition | Get-Member | Out-Null
            clear-partitions -instance $script:Server -dbase $ssasdb -cube $ssascube -partition $Script:partition -row $row -i $i -count $count
            }
        }
    #Reseller Sales
    if ($ResellerSales.IsPresent)
        {
        set-partitions -cube $cube -measureGroupName "Reseller Sales" -row $row.CalendarYear -partitionPrefix "Reseller_Sales_"
        if ($Script:Partition.Name -ne $null)     
            {
            $Script:Partition | Get-Member | Out-Null
            clear-partitions -instance $script:Server -db $ssasdb -cube $ssascube -partition $Script:partition -row $row -i $i -count $count
            }
        }
    }
}
#always tidy up
$script:Server.disconnect
$script:Server.Dispose()
}

function clear-partitions
{
[CmdletBinding()]
    param(
    [Parameter(Position=0,mandatory=$true)]
    [Microsoft.AnalysisServices.Server] $Instance,
    [Parameter(Position=1,mandatory=$true)]
    [Microsoft.AnalysisServices.Database] $dbase,
    [Parameter(Position=2,mandatory=$true)]
    [Microsoft.AnalysisServices.Cube] $cube,
    [Parameter(Position=3,mandatory=$true)]
    [Microsoft.AnalysisServices.Partition] $partition,
    [Parameter(Position=4,mandatory=$true)]
    [string] $row,
    [Parameter(Position=5,mandatory=$true)]
    [string] $i,
    [Parameter(Position=6,mandatory=$true)]
    [string] $count)    

# now we are getting to the delete section
# write to console a bit of info
# progress bar goes up per partition dropped
                    $Year = $row.CalendarYear
                    $percentcomplete = (($i/$count)*100)
                    Write-Progress -id 1 -Activity "deleting cube partitions" -CurrentOperation "deleting Year $Year" -Status "Progress:" -PercentComplete $percentcomplete
                        "Dropping $partition on MeasureGroup "+$partition.Parent.Name +" from cube $cube on $dbase on instance $instance."
                        $partition.Drop()

}

 

Here’s the partitions in the cube as they are before anything is run:

AdventureWorksDW2012Multidimensional_EE

Ok, let’s dot-source the script. So copy and save the script somewhere as “dropPartitions.ps1”. Then right click and copy as path the file. Open a Powershell window and type a dot then a space, and paste the location of the file into the console.

2014-11-07 17_20_57-Administrator_ Windows PowerShell ISE

Now the primary function “remove-partitions” will be available to you (the other functions will also be available, but outside of the context of this script there’s no value in trying to use them.) I’m running locally so instances are “.”, but you can run remotely. 2014-11-07 17_23_19-Administrator_ Windows PowerShell ISE

The function runs, finds two partitions per measure group to delete and drops them.

2014-11-07 17_24_15-Administrator_ Windows PowerShell ISE

Refresh the view in the object explorer; the partitions for just Reseller Orders and Reseller Sales will be gone because those were the switches included, and neither of the “Internet*” switches were included.

2014-11-07 17_33_AdventureWorksDW2012Multidimensional-EE

If we run again then the partitions will not be found and the script does nothing.

2014-11-07 17_25_54-Administrator_ Windows PowerShell ISE

So we can be confident that if we had to change the “BETWEEN” values that included partitions already dropped, the script will just ignore them.

lastOne

..and this is the progress bar.

2014-11-07 17_36_56AdventureWorksDW2012Multidimensional

Edit: I have just realised that this is my 200th post: how appropriate that it is a PowerShell script using AMO, the very subject I originally intended for this blog to cover pretty much exclusively, until I realised that I had so much more to talk about!

here’s to the next 200 posts…

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

1 thought on “Deleting SSAS Cube Partitions Using PowerShell and AMO”

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