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