removePartition.ps1

#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()

}