WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Wednesday, October 1, 2014

Checking a SSAS cube's last data update via Powershell.

Today my search engine skills failed me when looking for how to get the last cube processed date ("LAST_DATA_UPDATE") from a SSAS database when using Invoke-ASCmd.  I was going to break down and just submit MDX with SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes but decided to use XMLA given I had some time to invest.

The actual problem I was trying to solve was to simply send an alert if a specific cube had not been refreshed in the past 12 hours.   The cube is built in the "cloud" and then backed up and shipped "onprem" for a restore for users; so the processes are disconnected and we wanted something to send out a notification should things be delayed.

I simply saved this as a Powershell script, and then created an SQL Agent job to run it notifying myself on failure.  

      Param (  
           [parameter(Mandatory=$true)]  
           [String]$SSASServer="localhost\staging",  
           [parameter(Mandatory=$true)]  
           [String]$CubeName="",  
           [parameter(Mandatory=$false)]  
           [String]$SSASDB="",  
           [parameter(Mandatory=$false)]  
           [Int]$HoursForAlertRange="-12"  
      )  
      Try {  
     [bool]$Found = $false;  
           [DateTime]$StartDate = (Get-Date).AddHours($hours);  
     [DateTime]$EndDate = (Get-Date);  
     if($SSASDB -eq $null -or $SSASDB.Length -eq 0) {$SSASDB = $CubeName}  
     $q = @"  
     <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">  
      <RequestType>MDSCHEMA_CUBES</RequestType>  
      <Restrictions />  
      <Properties>  
       <PropertyList>  
        <Catalog>$SSASDB</Catalog>  
       </PropertyList>  
      </Properties>  
     </Discover>  
 "@  
     [xml] $res = Invoke-ASCmd -s $SSASServer -Database $SSASDB -Query $q  
     Foreach($itm in $res.GetElementsByTagName("row"))  
     {  
       if($itm.CUBE_NAME -eq $CubeName -and $itm.CATALOG_NAME -eq $SSASDB)  
       {  
        $Found = $true;  
        #Write-Output "Catalog: $itm.CATALOG_NAME $itm.DATE_MODIFIED $itm.LAST_DATA_UPDATE  
        $ts = New-TimeSpan -Start $StartDate $itm.LAST_DATA_UPDATE   
        if($ts.Days -eq 0 -and $hours -ge 0)  
        {  
         Write-Output "$CubeName is up to date"  
        }  
        else  
        {  
         Write-Output "$CubeName has not been updated since: "$itm.LAST_DATA_UPDATE " on " $SSASServer  
         Exit 1;  
        }  
       }  
     }  
   if($Found -eq $false){  
     Write-Output "Did not find $CubeName in $SSASServer"  
     Exit 1;  
   }            
      } Catch {  
     Write-Output "Failed to check $CubeName - $Error[0]"  
           exit 1  
      }  


No comments:

Post a Comment