SSAS partitions mover – PowerShell
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()
Neuen Kommentar schreiben