SSAS partitions mover – PowerShell

Technical Value

Sometimes you have to move a certain amount of SSAS Partitions to another physical drive if you are running out of space, or do some rearrangement for historization reasons.
Unfortunately the classic way had you to do that by the mostly very time-consuming task of recreating and reprocessing all regarding partitions.

1. Recreate Partition

2. Locate Storage Location in source code and point it towards the new location, or even add the tag manually if the partition resides within the default Storage Location.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>AdventureWorksDW2014Multidimensional-EE</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Currency Rate</MeasureGroupID>
<PartitionID>Currency_Rates</PartitionID>
</Object>
<ObjectDefinition>
<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
<ID>Currency_Rates</ID>
<Name>Currency_Rates</Name>
<Source xsi:type="DsvTableBinding">
<DataSourceViewID>Adventure Works DW</DataSourceViewID>
<TableID>dbo_FactCurrencyRate</TableID>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<StorageLocation>D:\DATA\</StorageLocation>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
<EstimatedRows>14264</EstimatedRows>
<AggregationDesignID>Exchange Rates</AggregationDesignID>
</Partition>
</ObjectDefinition>
</Alter>

3. Delete the current partition along with temporary losing all data.

4. Reprocess the new partition with new Storage Location.

5. And worst of all, rinse and repeat with lots of more partitions.

It’s a kind of “Towers of Hanoi” game, but without the fun:

Fotolia, Urheber: Scott Leman

There should be a better way. And indeed, there is another one. Without reprocessing the partitions and without temporarily losing the partition data. As a fact that’s an open request on Microsoft connect portal since 2010 (Need to move an AS cube partition without unprocessing it).

Bill Anton suggested a very neat method of accomplishing that task with the following steps. I’ll just give a wrap up without going into much detail:

1. Similar to the first method you have to script out the partitions and locate, or add the Storage Location tag.

2. Next thing you must change is the PartionID along with the Partition Name tag.

<ObjectDefinition>
<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
<ID>Currency_Rates_New</ID>
<Name>Currency_Rates_New</Name>

3. After that it’s up to you if you prefer to recreate the partition select statements with “where 1=0” or “select top 0”.

<ID>Currency_Rates</ID>
<Name>Currency_Rates</Name>
<Source xsi:type="QueryBinding">
<DataSourceID>Adventure Works DW</DataSourceID>
<QueryDefinition>SELECT top 0 CurrencyKey, DateKey, AverageRate, EndOfDayRate FROM dbo.FactCurrencyRate</QueryDefinition>
</Source>

4. Next add or modify the Storage Location tag for your needs and create the new partition.

5. Before you can merge the old partition into the new target partition you have to process the new partition. Here comes in handy that you modified the partition query, as a result the partition will be completely “empty” and the processing will take just a few seconds.

6. Finally merge the old partition into the new one and you are done. No Downtime, no reprocessing, no excessive CPU usage, but most importantly no empty partitions or duplicate data.

7. What if you need to rebuild the partitions with their original name and id? You just have to repeat the partition recreation and merge from step 1.

That wasn’t too much hassle, or was it?

For a more convenient approach I implemented the steps into a Powershell script for open access and easy modification for your project needs with the following features:

– Start-up with Parameterization, or complete parameters on the fly.

– Move many partitions at once.

– Filter through databases, cubes, measure groups and partitions on server level.

– Use wildcards for filtering through partitions and only move those with a certain date for example.

– If you prefer to use regular expressions instead of wildcards for even more complex situations, just replace “-like” with “-match” in line number 101.

– Maintain a reasonable folder structure in the target directory without flooding it with indistinguishable guid-folders

Usage example with parameters:

.\SSAS_PARTION_MOVER.PS1 -ServerName "my_dev_server" -my_DB "AdventureWorksDW2014Multidimensional-EE" -my_Cube "Adventure Works" -my_MG "Reseller Orders" -my_part_contains "*2011*" -temp_part_StorageLocation "D:\as_data"

Code:

_PARTION_MOVER.PS1
#
# <Description= "Partition mover without reprocess">
#
# <File="SSAS_PARTION_MOVER.PS1" company="ORAYLIS GmbH" url="http://www.oraylis.de">
#
# <author>Frank Karls</author>
# <date>07/01/2017 11:39:58 AM </date>
# <example>
# SSAS_PARTION_MOVER.PS1
#</example>
Param(
[Parameter(Mandatory=$true)] [String]$ServerName, # "my_dev_server"
[Parameter(Mandatory=$true)] [String]$my_DB, # "AdventureWorksDW2014Multidimensional-EE"
[Parameter(Mandatory=$true)] [String]$my_Cube, # "myCube"
[Parameter(Mandatory=$true)] [String]$my_MG, # "Events"
[Parameter(Mandatory=$true)] [String]$my_part_contains, # "*_2016*"
[Parameter(Mandatory=$true)] [String]$temp_part_StorageLocation # "d:\as_data" Make sure that the full local target path exists on the server, like "D:\Data\$my_DB\$my_Cube\$my_MG" the SSAS server is not capable of creating missing subfolders on its own
)
#$ServerName = "my_dev_server"
#$my_DB = "AdventureWorksDW2014Multidimensional-EE"
#$my_Cube = "Adventure Works"
#$my_MG = "Reseller Orders"
#$my_part_contains = "*2011*"
#$temp_part_StorageLocation = "D:\as_data"
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
#Write-Output ("Server '{0}' not found" -f $ServerName)
break
}
#Iteration databases
foreach ($d in $server.Databases )
{
#check for database name like filter
if ($d.Name -eq $my_DB) {
#Write-Output ( "Filter DB: {0}" -f $d.Name )
#Iteration cubes
foreach ($cube in $d.Cubes) {
#check for cube name like filter
if ($Cube.Name -eq $my_Cube) {
#Write-Output ( "Filter Cube: {0}" -f $Cube.Name )
#Iteration measure groups
foreach ($mg in $cube.MeasureGroups) {
#check for measure group name like filter
if ($mg.Name -eq $my_MG) {
#Write-Output ( "FILTER MG: {0}" -f $mg.Name )
#Iteration partitions
foreach ($part in $($mg.Partitions)) {
#check for partition name like filter
if ($part.Name -like $my_part_contains) {
#check for partition processed
if ($part.State.ToString() -like "Processed") {
# Clone original partition to temp partition
$temp_part = $part.Clone()
#check for partition querydefinition
if ($temp_part.Source.QueryDefinition) {
try{
#Write-Output ( "Filter Part {0}; {1}; {2}; {3}; {4}" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
#set temp ID and name
$temp_part.ID = $part.Name + "_COPY"
$temp_part.Name = $part.Name + "_COPY"
#set new partion storage location
$temp_part.StorageLocation = $temp_part_StorageLocation + "\" + $my_DB + "\" + $my_Cube + "\" + $my_MG + "\"
#set partitionquery to zero resultset
$temp_part.Source.QueryDefinition = $part.Source.QueryDefinition -Replace("\b*SELECT\b*","SELECT TOP 0")
#add temp partition
$mg.Partitions.Add($temp_part)
$temp_part.Update()
#Write-Output ( "Added temp partition {0}" -f $temp_part.Name)
#process temp parttion
$temp_part.Process()
# Clone original partition to final partition
$final_part = $part.Clone()
#set final partion storage location
$final_part.StorageLocation = $temp_part_StorageLocation + "\" + $my_DB + "\" + $my_Cube + "\" + $my_MG + "\"
#merge original partition to temp partition
$my_partitionlist = New-Object System.Collections.Generic.List[System.Object]
$my_partitionlist.add($part)
$temp_part.Merge($my_partitionlist)
#Write-Output ( "Merged {0} data to temp partition" -f $part.Name)
#Remove objects
$my_partitionlist.dispose()
#refresh Partition data
$mg.refresh()
#Write-Output ("Refresh")
#add final partition
$mg.Partitions.Add($final_part)
$final_part.Update()
#Write-Output ( "Added final partition {0}" -f $final_part.Name)
#process temp parttion
$final_part.Process()
#merge temp partition to final partition
$my_partitionlist = New-Object System.Collections.Generic.List[System.Object]
$my_partitionlist.add($temp_part)
$final_part.Merge($my_partitionlist)
#Write-Output ( "Merged {0} temp data to final partition" -f $temp_part.Name)
$my_partitionlist.dispose()
}
catch{
#Write-Output(($Error[0]).Exception)
}
} #check for partition querydefinition
else {
#Write-Output ( "{0} {1} {2} {3} {4} is no query partition" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
}
}#check for partition processed
else {
#Write-Output ( "{0} {1} {2} {3} {4} is unprocessed" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
}
} #check for partition name like filter
} #Iteration partitions
} #check for measure group name like filter
} #Iteration measure groups
} #check for cube name like filter
} #Iteration cubes
} #check for database name like filter
} #iteration databases
$server.Disconnect()
#
# <Description= "Partition mover without reprocess">
#
# <File="SSAS_PARTION_MOVER.PS1" company="ORAYLIS GmbH" url="http://www.oraylis.de">
#
# <author>Frank Karls</author>
# <date>07/01/2017 11:39:58 AM </date>
# <example>
# SSAS_PARTION_MOVER.PS1
#</example>
Param(
[Parameter(Mandatory=$true)] [String]$ServerName, # "my_dev_server"
[Parameter(Mandatory=$true)] [String]$my_DB, # "AdventureWorksDW2014Multidimensional-EE"
[Parameter(Mandatory=$true)] [String]$my_Cube, # "myCube"
[Parameter(Mandatory=$true)] [String]$my_MG, # "Events"
[Parameter(Mandatory=$true)] [String]$my_part_contains, # "*_2016*"
[Parameter(Mandatory=$true)] [String]$temp_part_StorageLocation # "d:\as_data" Make sure that the full local target path exists on the server, like "D:\Data\$my_DB\$my_Cube\$my_MG" the SSAS server is not capable of creating missing subfolders on its own
)
#$ServerName = "my_dev_server"
#$my_DB = "AdventureWorksDW2014Multidimensional-EE"
#$my_Cube = "Adventure Works"
#$my_MG = "Reseller Orders"
#$my_part_contains = "*2011*"
#$temp_part_StorageLocation = "D:\as_data"
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
#Write-Output ("Server '{0}' not found" -f $ServerName)
break
}
#Iteration databases
foreach ($d in $server.Databases )
{
#check for database name like filter
if ($d.Name -eq $my_DB) {
#Write-Output ( "Filter DB: {0}" -f $d.Name )
#Iteration cubes
foreach ($cube in $d.Cubes) {
#check for cube name like filter
if ($Cube.Name -eq $my_Cube) {
#Write-Output ( "Filter Cube: {0}" -f $Cube.Name )
#Iteration measure groups
foreach ($mg in $cube.MeasureGroups) {
#check for measure group name like filter
if ($mg.Name -eq $my_MG) {
#Write-Output ( "FILTER MG: {0}" -f $mg.Name )
#Iteration partitions
foreach ($part in $($mg.Partitions)) {
#check for partition name like filter
if ($part.Name -like $my_part_contains) {
#check for partition processed
if ($part.State.ToString() -like "Processed") {
# Clone original partition to temp partition
$temp_part = $part.Clone()
#check for partition querydefinition
if ($temp_part.Source.QueryDefinition) {
try{
#Write-Output ( "Filter Part {0}; {1}; {2}; {3}; {4}" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
#set temp ID and name
$temp_part.ID = $part.Name + "_COPY"
$temp_part.Name = $part.Name + "_COPY"
#set new partion storage location
$temp_part.StorageLocation = $temp_part_StorageLocation + "\" + $my_DB + "\" + $my_Cube + "\" + $my_MG + "\"
#set partitionquery to zero resultset
$temp_part.Source.QueryDefinition = $part.Source.QueryDefinition -Replace("\b*SELECT\b*","SELECT TOP 0")
#add temp partition
$mg.Partitions.Add($temp_part)
$temp_part.Update()
#Write-Output ( "Added temp partition {0}" -f $temp_part.Name)
#process temp parttion
$temp_part.Process()
# Clone original partition to final partition
$final_part = $part.Clone()
#set final partion storage location
$final_part.StorageLocation = $temp_part_StorageLocation + "\" + $my_DB + "\" + $my_Cube + "\" + $my_MG + "\"
#merge original partition to temp partition
$my_partitionlist = New-Object System.Collections.Generic.List[System.Object]
$my_partitionlist.add($part)
$temp_part.Merge($my_partitionlist)
#Write-Output ( "Merged {0} data to temp partition" -f $part.Name)
#Remove objects
$my_partitionlist.dispose()
#refresh Partition data
$mg.refresh()
#Write-Output ("Refresh")
#add final partition
$mg.Partitions.Add($final_part)
$final_part.Update()
#Write-Output ( "Added final partition {0}" -f $final_part.Name)
#process temp parttion
$final_part.Process()
#merge temp partition to final partition
$my_partitionlist = New-Object System.Collections.Generic.List[System.Object]
$my_partitionlist.add($temp_part)
$final_part.Merge($my_partitionlist)
#Write-Output ( "Merged {0} temp data to final partition" -f $temp_part.Name)
$my_partitionlist.dispose()
}
catch{
#Write-Output(($Error[0]).Exception)
}
} #check for partition querydefinition
else {
#Write-Output ( "{0} {1} {2} {3} {4} is no query partition" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
}
}#check for partition processed
else {
#Write-Output ( "{0} {1} {2} {3} {4} is unprocessed" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
}
} #check for partition name like filter
} #Iteration partitions
} #check for measure group name like filter
} #Iteration measure groups
} #check for cube name like filter
} #Iteration cubes
} #check for database name like filter
} #iteration databases
$server.Disconnect()