• Home
  • Blog
  • SSAS partitions mover – PowerShell
21.08.2017

SSAS partitions mover – PowerShell

Technical Value
Operation & Maintenance Business Intelligence

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

New Query Editor Window
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.

  1. <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  2.         <Object>
  3.                 <DatabaseID>AdventureWorksDW2014Multidimensional-EE</DatabaseID>
  4.                 <CubeID>Adventure Works</CubeID>
  5.                 <MeasureGroupID>Fact Currency Rate</MeasureGroupID>
  6.                 <PartitionID>Currency_Rates</PartitionID>
  7.         </Object>
  8.         <ObjectDefinition>
  9.                 <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">
  10.                         <ID>Currency_Rates</ID>
  11.                         <Name>Currency_Rates</Name>
  12.                         <Source xsi:type="DsvTableBinding">
  13.                                 <DataSourceViewID>Adventure Works DW</DataSourceViewID>
  14.                                 <TableID>dbo_FactCurrencyRate</TableID>
  15.                         </Source>
  16.                         <StorageMode>Molap</StorageMode>
  17.                         <ProcessingMode>Regular</ProcessingMode>
  18.                         <StorageLocation>D:\DATA\</StorageLocation>
  19.                         <ProactiveCaching>
  20.                                 <SilenceInterval>-PT1S</SilenceInterval>
  21.                                 <Latency>-PT1S</Latency>
  22.                                 <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
  23.                                 <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
  24.                                 <Source xsi:type="ProactiveCachingInheritedBinding" />
  25.                         </ProactiveCaching>
  26.                         <EstimatedRows>14264</EstimatedRows>
  27.                         <AggregationDesignID>Exchange Rates</AggregationDesignID>
  28.                 </Partition>
  29.         </ObjectDefinition>
  30.  </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:

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.

  1. <ObjectDefinition>
  2.   <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">
  3.     <ID>Currency_Rates_New</ID>
  4.     <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”.

  1. <ID>Currency_Rates</ID>
  2. <Name>Currency_Rates</Name>
  3. <Source xsi:type="QueryBinding">
  4.   <DataSourceID>Adventure Works DW</DataSourceID>
  5.   <QueryDefinition>SELECT top 0 CurrencyKey, DateKey, AverageRate, EndOfDayRate FROM dbo.FactCurrencyRate</QueryDefinition>
  6. </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:

  1. .\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:

  1. _PARTION_MOVER.PS1
  2. #
  3. # <Description= "Partition mover without reprocess">
  4. #
  5. # <File="SSAS_PARTION_MOVER.PS1" company="ORAYLIS GmbH" url="http://www.oraylis.de">
  6. #
  7. # <author>Frank Karls</author>
  8. # <date>07/01/2017 11:39:58 AM </date>
  9. # <example>
  10. # SSAS_PARTION_MOVER.PS1
  11. #</example>
  12. Param(
  13. [Parameter(Mandatory=$true)] [String]$ServerName, # "my_dev_server"
  14. [Parameter(Mandatory=$true)] [String]$my_DB, # "AdventureWorksDW2014Multidimensional-EE"
  15. [Parameter(Mandatory=$true)] [String]$my_Cube, # "myCube"
  16. [Parameter(Mandatory=$true)] [String]$my_MG, # "Events"
  17. [Parameter(Mandatory=$true)] [String]$my_part_contains, # "*_2016*"
  18. [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
  19. )
  20. #$ServerName = "my_dev_server"
  21. #$my_DB = "AdventureWorksDW2014Multidimensional-EE"
  22. #$my_Cube = "Adventure Works"
  23. #$my_MG = "Reseller Orders"
  24. #$my_part_contains = "*2011*"
  25. #$temp_part_StorageLocation = "D:\as_data"
  26. $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
  27. $server = New-Object Microsoft.AnalysisServices.Server
  28. $server.connect($ServerName)
  29. if ($server.name -eq $null) {
  30. #Write-Output ("Server '{0}' not found" -f $ServerName)
  31. break
  32. }
  33. #Iteration databases
  34. foreach ($d in $server.Databases )
  35. {
  36. #check for database name like filter
  37. if ($d.Name -eq $my_DB) {
  38. #Write-Output ( "Filter DB: {0}" -f $d.Name )
  39. #Iteration cubes
  40. foreach ($cube in $d.Cubes) {
  41. #check for cube name like filter
  42. if ($Cube.Name -eq $my_Cube) {
  43. #Write-Output ( "Filter Cube: {0}" -f $Cube.Name )
  44. #Iteration measure groups
  45. foreach ($mg in $cube.MeasureGroups) {
  46. #check for measure group name like filter
  47. if ($mg.Name -eq $my_MG) {
  48. #Write-Output ( "FILTER MG: {0}" -f $mg.Name )
  49. #Iteration partitions
  50. foreach ($part in $($mg.Partitions)) {
  51. #check for partition name like filter
  52. if ($part.Name -like $my_part_contains) {
  53. #check for partition processed
  54. if ($part.State.ToString() -like "Processed") {
  55. # Clone original partition to temp partition
  56. $temp_part = $part.Clone()
  57. #check for partition querydefinition
  58. if ($temp_part.Source.QueryDefinition) {
  59. try{
  60. #Write-Output ( "Filter Part {0}; {1}; {2}; {3}; {4}" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
  61. #set temp ID and name
  62. $temp_part.ID = $part.Name + "_COPY"
  63. $temp_part.Name = $part.Name + "_COPY"
  64. #set new partion storage location
  65. $temp_part.StorageLocation = $temp_part_StorageLocation + "\" + $my_DB + "\" + $my_Cube + "\" + $my_MG + "\"
  66. #set partitionquery to zero resultset
  67. $temp_part.Source.QueryDefinition = $part.Source.QueryDefinition -Replace("\b*SELECT\b*","SELECT TOP 0")
  68. #add temp partition
  69. $mg.Partitions.Add($temp_part)
  70. $temp_part.Update()
  71. #Write-Output ( "Added temp partition {0}" -f $temp_part.Name)
  72. #process temp parttion
  73. $temp_part.Process()
  74. # Clone original partition to final partition
  75. $final_part = $part.Clone()
  76. #set final partion storage location
  77. $final_part.StorageLocation = $temp_part_StorageLocation + "\" + $my_DB + "\" + $my_Cube + "\" + $my_MG + "\"
  78. #merge original partition to temp partition
  79. $my_partitionlist = New-Object System.Collections.Generic.List[System.Object]
  80. $my_partitionlist.add($part)
  81. $temp_part.Merge($my_partitionlist)
  82. #Write-Output ( "Merged {0} data to temp partition" -f $part.Name)
  83. #Remove objects
  84. $my_partitionlist.dispose()
  85. #refresh Partition data
  86. $mg.refresh()
  87. #Write-Output ("Refresh")
  88. #add final partition
  89. $mg.Partitions.Add($final_part)
  90. $final_part.Update()
  91. #Write-Output ( "Added final partition {0}" -f $final_part.Name)
  92. #process temp parttion
  93. $final_part.Process()
  94. #merge temp partition to final partition
  95. $my_partitionlist = New-Object System.Collections.Generic.List[System.Object]
  96. $my_partitionlist.add($temp_part)
  97. $final_part.Merge($my_partitionlist)
  98. #Write-Output ( "Merged {0} temp data to final partition" -f $temp_part.Name)
  99. $my_partitionlist.dispose()
  100. }
  101. catch{
  102. #Write-Output(($Error[0]).Exception)
  103. }
  104. } #check for partition querydefinition
  105. else {
  106. #Write-Output ( "{0} {1} {2} {3} {4} is no query partition" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
  107. }
  108. }#check for partition processed
  109. else {
  110. #Write-Output ( "{0} {1} {2} {3} {4} is unprocessed" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
  111. }
  112. } #check for partition name like filter
  113. } #Iteration partitions
  114. } #check for measure group name like filter
  115. } #Iteration measure groups
  116. } #check for cube name like filter
  117. } #Iteration cubes
  118. } #check for database name like filter
  119. } #iteration databases
  120. $server.Disconnect()
  121. #
  122. # <Description= "Partition mover without reprocess">
  123. #
  124. # <File="SSAS_PARTION_MOVER.PS1" company="ORAYLIS GmbH" url="http://www.oraylis.de">
  125. #
  126. # <author>Frank Karls</author>
  127. # <date>07/01/2017 11:39:58 AM </date>
  128. # <example>
  129. # SSAS_PARTION_MOVER.PS1
  130. #</example>
  131. Param(
  132. [Parameter(Mandatory=$true)] [String]$ServerName, # "my_dev_server"
  133. [Parameter(Mandatory=$true)] [String]$my_DB, # "AdventureWorksDW2014Multidimensional-EE"
  134. [Parameter(Mandatory=$true)] [String]$my_Cube, # "myCube"
  135. [Parameter(Mandatory=$true)] [String]$my_MG, # "Events"
  136. [Parameter(Mandatory=$true)] [String]$my_part_contains, # "*_2016*"
  137. [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
  138. )
  139. #$ServerName = "my_dev_server"
  140. #$my_DB = "AdventureWorksDW2014Multidimensional-EE"
  141. #$my_Cube = "Adventure Works"
  142. #$my_MG = "Reseller Orders"
  143. #$my_part_contains = "*2011*"
  144. #$temp_part_StorageLocation = "D:\as_data"
  145. $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
  146. $server = New-Object Microsoft.AnalysisServices.Server
  147. $server.connect($ServerName)
  148. if ($server.name -eq $null) {
  149. #Write-Output ("Server '{0}' not found" -f $ServerName)
  150. break
  151. }
  152. #Iteration databases
  153. foreach ($d in $server.Databases )
  154. {
  155. #check for database name like filter
  156. if ($d.Name -eq $my_DB) {
  157. #Write-Output ( "Filter DB: {0}" -f $d.Name )
  158. #Iteration cubes
  159. foreach ($cube in $d.Cubes) {
  160. #check for cube name like filter
  161. if ($Cube.Name -eq $my_Cube) {
  162. #Write-Output ( "Filter Cube: {0}" -f $Cube.Name )
  163. #Iteration measure groups
  164. foreach ($mg in $cube.MeasureGroups) {
  165. #check for measure group name like filter
  166. if ($mg.Name -eq $my_MG) {
  167. #Write-Output ( "FILTER MG: {0}" -f $mg.Name )
  168. #Iteration partitions
  169. foreach ($part in $($mg.Partitions)) {
  170. #check for partition name like filter
  171. if ($part.Name -like $my_part_contains) {
  172. #check for partition processed
  173. if ($part.State.ToString() -like "Processed") {
  174. # Clone original partition to temp partition
  175. $temp_part = $part.Clone()
  176. #check for partition querydefinition
  177. if ($temp_part.Source.QueryDefinition) {
  178. try{
  179. #Write-Output ( "Filter Part {0}; {1}; {2}; {3}; {4}" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
  180. #set temp ID and name
  181. $temp_part.ID = $part.Name + "_COPY"
  182. $temp_part.Name = $part.Name + "_COPY"
  183. #set new partion storage location
  184. $temp_part.StorageLocation = $temp_part_StorageLocation + "\" + $my_DB + "\" + $my_Cube + "\" + $my_MG + "\"
  185. #set partitionquery to zero resultset
  186. $temp_part.Source.QueryDefinition = $part.Source.QueryDefinition -Replace("\b*SELECT\b*","SELECT TOP 0")
  187. #add temp partition
  188. $mg.Partitions.Add($temp_part)
  189. $temp_part.Update()
  190. #Write-Output ( "Added temp partition {0}" -f $temp_part.Name)
  191. #process temp parttion
  192. $temp_part.Process()
  193. # Clone original partition to final partition
  194. $final_part = $part.Clone()
  195. #set final partion storage location
  196. $final_part.StorageLocation = $temp_part_StorageLocation + "\" + $my_DB + "\" + $my_Cube + "\" + $my_MG + "\"
  197. #merge original partition to temp partition
  198. $my_partitionlist = New-Object System.Collections.Generic.List[System.Object]
  199. $my_partitionlist.add($part)
  200. $temp_part.Merge($my_partitionlist)
  201. #Write-Output ( "Merged {0} data to temp partition" -f $part.Name)
  202. #Remove objects
  203. $my_partitionlist.dispose()
  204. #refresh Partition data
  205. $mg.refresh()
  206. #Write-Output ("Refresh")
  207. #add final partition
  208. $mg.Partitions.Add($final_part)
  209. $final_part.Update()
  210. #Write-Output ( "Added final partition {0}" -f $final_part.Name)
  211. #process temp parttion
  212. $final_part.Process()
  213. #merge temp partition to final partition
  214. $my_partitionlist = New-Object System.Collections.Generic.List[System.Object]
  215. $my_partitionlist.add($temp_part)
  216. $final_part.Merge($my_partitionlist)
  217. #Write-Output ( "Merged {0} temp data to final partition" -f $temp_part.Name)
  218. $my_partitionlist.dispose()
  219. }
  220. catch{
  221. #Write-Output(($Error[0]).Exception)
  222. }
  223. } #check for partition querydefinition
  224. else {
  225. #Write-Output ( "{0} {1} {2} {3} {4} is no query partition" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
  226. }
  227. }#check for partition processed
  228. else {
  229. #Write-Output ( "{0} {1} {2} {3} {4} is unprocessed" -f $ServerName, $d.Name, $mg.Name, $mg.State, $part.Name)
  230. }
  231. } #check for partition name like filter
  232. } #Iteration partitions
  233. } #check for measure group name like filter
  234. } #Iteration measure groups
  235. } #check for cube name like filter
  236. } #Iteration cubes
  237. } #check for database name like filter
  238. } #iteration databases
  239. $server.Disconnect()

 

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.
Hilfe zum Textformat

Klartext

  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden