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

param(
   [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
$server.Connect($ServerName)

$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)
$newPartition.Update()

$server.Disconnect()

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.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*************************************************************************************
***
*** 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)
)
AS 

SET NOCOUNT ON
DECLARE @cmd VARCHAR(8000)
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.