Tuesday, July 31, 2012

BI Admin Scripts: Process SSAS database

The following script can be copied and pasted into a .ps1 file for you to use in processing all Analysis Services databases on a particular SSAS instance or a specific database. This script was originally designed to process all scripts on a server rather than a single database, thus the somewhat inefficient manner of looping through all the databases to find the one to be processed. I may fix it in the future as well as add some more parameters to handle things like ignoring key errors rather than hard-coding them.

Param([string]$ServerName="MyTestSSASServer", [string]$DatabaseName="All") ## Add the AMO namespace $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") ## Establish SSAS server connection $server = New-Object Microsoft.AnalysisServices.Server $server.connect($ServerName) if ($server.name -eq $null) { Write-Output ("Server '{0}' not found" -f $ServerName) break } Clear-Host foreach ($db in $server.Databases ) { ## Looping through databases on the server ## Only process the DB specified in $DatabaseName unless set to "All" ## Also ignore Team Foundation Server DBs (starting with Tfs_) ## Other exclusions might also need to be added in specific cases if ((($DatabaseName -eq "All") ` -or ($db.Name -eq $DatabaseName)) 1 -and !$db.Name.StartsWith("Tfs_")) { ## Set the processing log location on the SSAS server $errorLogFilename = "D:\ProcessLogs\" ` + $db.Name.Replace(" ", "_") ` + "_Process_Errors.log" ## Set up the Error Configuration so that Key Errors are ignored $errorConfig = New-Object ` Microsoft.AnalysisServices.ErrorConfiguration($errorLogFilename) $errorConfig.KeyNotFound = "ReportAndContinue" $errorConfig.KeyErrorAction = "ConvertToUnknown" $errorConfig.KeyErrorLimit = -1 Write-Output ` ("{0}:Start Processing {1}, logging errors to {2} in SSAS Log directory" ` -f $(Get-Date -f "yyyyMMdd-HHmm"), $db.Name, $errorLogFilename) try { ## Process the current database $db.Process("ProcessFull", $errorConfig) } catch { ## Write out the error if one occurs $error } Write-Output("{0}:Done Processing {1}" ` -f $(Get-Date -f "yyyyMMdd-HHmm"), $db.Name) Write-Output("") ## The ErrorConfiguration class implements IDisposable ## so we'll be good citizens and call the Dispose method $errorConfig.Dispose() } }

1 comment:

  1. This is excellent! No more hard coding