Use Powershell to Get SSAS Partition Details

The Powershell script below returns information on all the partitions within the Measure Groups of a given cube. I prefer to use Powershell to get this data as there’s no way to get he info out of the UI this quickly. If, like me, you have lots of partitions within a Measure Group and want to exit the script you can press “X” when the list reaches the bottom of the screen. The script will pause at the end of the screen so that you can review the partitions, and to give you the option to exit or to carry on through the partitions.

[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $ssasInstance,
[Parameter(Position=1,mandatory=$true)]
[string] $ssasDatabase,
[Parameter(Position=2,mandatory=$true)]
[string] $ssascube)
[System.Reflection.Assembly]::LoadWithPartialName("microsoft.AnalysisServices")
$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect($ssasInstance)
$database=$svr.databases
$currWS = $host.UI.RawUI.Get_WindowSize()
$WindowHeight=$currWS.height
$i=0
$db=$database[$ssasDatabase]
Write-Host "Database:"$db.Name $db.State $db.LastProcessed
Write-Host "Press any key to continue ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
$Cub=New-object Microsoft.AnalysisServices.Cube
$cub = $db.Cubes.FindByName($ssasCube)
foreach ($mg in $cub.MeasureGroups)
{
Write-Host "Measure Group:" $mg.Name $mg.State $mg.LastProcessed
Write-Host "Press any key to continue ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
foreach ($p in $mg.Partitions)
{
if ($p.state -ne 'Processed')
{
Write-Host "Partition:"$p.name $p.State $p.LastProcessed $p.AggregationDesignId -foregroundcolor red -backgroundcolor black
$i++
write-host
$i++
}
else
{
Write-Host "Partition:"$p.name $p.State $p.LastProcessed $p.AggregationDesignId -foregroundcolor darkgreen -backgroundcolor white
$i++
write-host
$i++
}
if ($i -gt $Windowheight)
{
Write-Host "Press X to cancel or any other key to continue"
$x = Read-Host
if ($x -eq "X")
{exit}
else
{
$i=0
$currWS = $host.UI.RawUI.Get_WindowSize()
$WindowHeight=$currWS.height
}
}
}
}
$svr.disconnect

 

And if you want to batch it multiple times then you can just do so by creating a powershell script that calls this script and passes in the parameters;


PS C:\Users\richard.lee> C:\Users\richard.lee\Documents\Get-AllPartInfo.ps1 -ssasinstance "sqla\olap01" -ssasDatabase "ABC" -ssascube "Sales"

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s