tim laqua dot com Thoughts and Code from Tim Laqua


Clone Analysis Services Partitions with PowerShell

Most of us with large Analysis Services cubes partition our cubes by month or year or some other time-based slice and we have all, at one point or another, developed some way to create partitions for new months on-demand. Often, the solution to this seems to be a C# console application or SSIS package using AMO to create a new partition based off an existing partition. The problem I see with this is that maintaining it requires opening up the project or package, making changes, re-compiling, deploying, testing, deploying to production, verifying, etc. It also requires that whoever is going to maintain it is comfortable with C#.

To simplify the maintenance and get rid of the "black box" factor that utility apps like this tend to have, I put together a PowerShell script to do the same thing and a stored procedure to call the script. Really, it doesn't matter what you use as you're most likely using an almost identical chunk of code to get your new partition created - my argument is that using PowerShell instead of C# or SSIS reduces the cost of maintenance, improves readability, and facilitates better understanding throughout your team.

The PowerShell Script: cloneSSASPartition.ps1

   [string] $ServerName, 		<# The name of the Analysis Services Instance #>
   [string] $DatabaseName, 		<# The name of the Database our Cube is in #>
   [string] $CubeName,     		<# The name of the Cube that our Measure Group is in #>
   [string] $MeasureGroupName, 	<# The name of the Measure Group that our Partition is in #>
   [string] $SourcePartitionName, 	<# The name of the Partition we want to merge in to another partition #>
   [string] $NewPartitionName, 	<# The desired name of the new partition #>
   [string] $NewPartitionQuery 	<# The Query source for the new partition (will use the same datasource as the SourcePartition #>
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
$database = $server.Databases.GetByName($DatabaseName)
$cube = $database.Cubes.GetByName($CubeName)
$measureGroup = $cube.MeasureGroups.FindByName($MeasureGroupName)
$sourcePartition = $measureGroup.Partitions.GetByName($SourcePartitionName)
$sourceQueryBinding = $sourcePartition.Source
$newPartition = $sourcePartition.Clone()
$newPartition.ID = $NewPartitionName
$newPartition.Name = $NewPartitionName
$measureGroup.Partitions.Add($newPartition) > $NULL
$newPartition.Source = New-Object Microsoft.AnalysisServices.QueryBinding($sourceQueryBinding.DataSourceID, $NewPartitionQuery)

The stored procedure to call the script: [Utility_CloneSSASPartition_S01] - assumes you placed cloneSSASPartition.ps1 at the root of the C: drive. If you put it elsewhere, update the proc to reflect the actual location.

*** Procedure:  		[Utility_CloneSSASPartition]
*** Purpose:		Executes PowerShell script to clone a SSAS partition
*** Author:		tl
*** Date Created:		2010-01-15
*** Revision History
*** Date		Author			Description
*** 2010-01-15	tl			Created
CREATE PROCEDURE [dbo].[Utility_CloneSSASPartition_S01]
	@ServerName AS VARCHAR(255),
	@DatabaseName AS VARCHAR(255),
	@CubeName AS VARCHAR(255),
	@MeasureGroupName AS VARCHAR(255),
	@SourcePartitionName AS VARCHAR(255),
	@NewPartitionName AS VARCHAR(255),
	@NewPartitionQuery AS VARCHAR(255)
SET @cmd = 
	'powershell.exe -Command "& {'
	+ ' C:\cloneSSASPartition.ps1'
	+ ' -ServerName:\"' + @ServerName + '\"'
	+ ' -DatabaseName:\"' + @DatabaseName + '\"'
	+ ' -CubeName:\"' + @CubeName + '\"'
	+ ' -MeasureGroupName:\"' + @MeasureGroupName + '\"'
	+ ' -SourcePartitionName:\"' + @SourcePartitionName + '\"'
	+ ' -NewPartitionName:\"' + @NewPartitionName + '\"'
	+ ' -NewPartitionQuery:\"' + @NewPartitionQuery + '\"}"'
PRINT @cmd
EXEC xp_cmdshell @cmd

And, finally, we execute it

EXEC [dbo].[Utility_CloneSSASPartition_S01]
	 @ServerName = 'TESTSERVER'
	,@DatabaseName = 'TestDatabase'
	,@CubeName = 'TestCube'
	,@MeasureGroupName = 'Test Measure Group'
	,@SourcePartitionName = 'Test Partition - 200912'
	,@NewPartitionName = 'Test Partition - 201001'
	,@NewPartitionQuery = 'SELECT * FROM dbo.FactTableMonth(''1/1/2010'', ''2/1/2010'')'

Again, I'm not saying there's anything "wrong" with using C# or SSIS Packages to do this sort of thing, I'm just pointing out that C# isn't always the answer - especially when someone other than you needs to maintain this thing in the future. Keep it simple.

Comments (1) Trackbacks (0)
  1. Excellent article. I am in the process of learning Powershell scripts, and found your article to be wonderful. Also, thank you for your earlier suggestion about MSBI books. I’ve found them to be very helpful + have gotten enough understanding about BI – and have focused on MS Stack.

    By the way, I am looking for OLTP samples, similar to Adventureworks which I can play around with to get more insight about building DW. I’ve seen too many samples of things which can be done with Adventureworks, and am confused on what I know and I don’t. If you do happen to know anything of that kind, would you please post a comment?


Leave a comment

No trackbacks yet.