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.
February 10th, 2010 - 11:45
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?
Thanks,
O