SSAS: Manipulating Cube Partitions with PowerShell

Jesse Orosz posted some interesting sample code recently on how to remove partitions with a certain prefix using AMO. I thought it would be an interesting comparison to see what similar code would look like in PowerShell. There is definitely not anything wrong with Jesse's code, it's obviously part of a much larger framework. It's more that I think PowerShell is a really good tool for doing these sort of admin tasks and it's an interesting exercise to take someone else's code from .net and re-implement it in Powershell. I like using scripts for these things as they provide a degree of transparency that you don't get with a compiled utility.

Powershell is quite a concise language, excluding the 3 lines of variable initialization at the top, I was able to reduce what was approximately 36 lines of code down to about 6.

$svrName = "localhost\sql05"
$sourceDB = "Adventure Works DW"
$sourceCube = "Adventure Works"

# load the AMO library (redirect to null to 'eat' the output from assembly loading process)
[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices")> $null
# connect to the AS Server
write-Host "Connecting to $svrName"
$svr = New-Object Microsoft.AnalysisServices.Server
$svr.Connect($svrName
)
# get a reference to the database

$cub = $svr.Databases.Item($sourceDB).Cubes.FindByName($sourceCube)
$parts = $cub.MeasureGroups[0].Partitions|Where-Object {$_.name.endswith("2003"
)}
foreach ($p in $parts
)
$p
.drop() }

Now I thought this was a fairly concise sample, but with a bit of work we can get it down to 4 lines! Not that I think I would go this far in a production environment as it tends to make the code write-only.

[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices") > $null
$svr = New-Object Microsoft.AnalysisServices.Server
$svr.Connect($svrName)
$svr.Databases.Item($sourceDB).Cubes.FindByName($sourceCube).MeasureGroups[0].Partitions 
|Where-Object {$_.name.endswith("2003")} | % {$_.drop()}
Technorati Tags: , ,

Print | posted on Sunday, August 26, 2007 10:13 PM

Comments on this post

# re: SSAS: Manipulating Cube Partitions with PowerShell

Requesting Gravatar...
Darren,

Can you confirm that you wrote your own Powershell provider for Analysis Services? I cannot find anyone else using Powershell with Analysis Services. I have unconfirmed news that Microsoft will provide Powershell provider for SSAS 2008, but I cannot find anything about SSAS 2005 and Powershell usage.

Thanks
Left by Vidas Matelis on Sep 26, 2007 10:10 AM

# re: SSAS: Manipulating Cube Partitions with PowerShell

Requesting Gravatar...
Yes, I do have a powershell provider www.codeplex.com/powerSSAS. As far as I am aware there will not be a Microsoft one for SSAS 2008.
Left by Darren Gosbell on Feb 28, 2008 5:22 PM

Your comment:

 (will show your gravatar)