When a cube fails to process, you don’t want to hear it from your customer. As the last step in our datawarehouse load, I was processing a cube, using the script generated from the SSMS dialog. That was working fine most of the time, but sometimes we’d have a development failure after some changes had been made during the day.

Here is what worked. Notes on this – don’t use $( anywhere in your PS code or SQL will parse it as a token and tell you to escape it. My first thought was to send an email to DBAs when the process failed – or rather hadn’t happened within the last 12 hours, but opted for the throw instead. Throw raises a nice error in the job and brings it to the attention of our central monitoring system.


## Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

## Connect and get the edition of the local server
$connection = “SERVER01”
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($connection)

$databases = $server.get_Databases()
$WF_DB = $databases.GetByName("SSASDB1")

$d = Get-Date #did this because SQL job step doesn't like dollar sign(get-date).AddHours...
$date = $d.AddHours(-12)

if ( $WF_DB.LastProcessed -lt $date)
{# Cube not updated since our threshold date

$dateprocessed = $WF_DB.LastProcessed
$lastupdate = $WF_DB.LastUpdate
$parent = $WF_DB.Parent

$message = "SSASDB1 on $parent has not processed since $date. The last process was on $dateprocessed. Manually reprocess the cube to see error message or view the previous job step for additonal information. Thanks!"
throw $message
}
else
{
echo "Cube updated after $date"

}