Frank Karls

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

 

Script and recreate SSAS Partitions

Script and recreate SSAS Partitions

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.

 

 

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:

testFotolia, 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.

 

 

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”.

 

 

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:

 

Code: