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"