Processing SSAS Databases Using PowerShell
Late night maintenance on databases also means I get to write a blog post during the downtime. Tonight’s blog post is more PowerShell/SSAS: following on from calculating the size of dimensions and Finding Unprocessed Objects, here’s a PowerShell script that will process all the databases on a given instance that match the current process state you input: If you’ve deployed all the databases to an instance, entering Unprocessed for $CurrentProcessState and ProcessFull for $TypeOfProcess will process all the objects. This is great for overnight batching of processing. I’ve also used this script to apply changes and then run a ProcessDefault on a database that is PartiallyProcessed and it seems to have worked well.
[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $ssasInstance,
[Parameter(Position=1,mandatory=$true)]
[string] $CurrentProcessState,
[Parameter(Position=2,mandatory=$true)]
[string] $TypeOfProcess)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ssasInstance)
$ProcessType=$TypeOfProcess
$database=$server.databases
$database|select-object name
foreach ($db in $database)
{
if ($db.state -eq $CurrentProcessState)
{
$date1=get-date
write-host "---------------------------------------------"
Write-host "Processing Client: $db " -foregroundcolor darkgreen -backgroundcolor white
$db.Process($ProcessType)
$date2=get-date
write-host "Processing took " ($date2-$date1).Hours " Hours, " ($date2-$date1).Minutes " Mins, " ($date2-$date1).Seconds " Secs " -foregroundcolor darkgreen -backgroundcolor white
$dbwriteout = $db|select name,state,lastprocessed
write-host $dbwriteout -foregroundcolor darkgreen -backgroundcolor white
}
else
{
write-host "No Processing required for database $db" -foregroundcolor yellow -backgroundcolor darkcyan
}
}
I’m actually running this as I type; automation is a great way to give me more time to