PowerShell script to process all dimensions and cubes in one DB limiting workload
User Rating: / 10
PoorBest 
Written by Vidas Matelis   
Wednesday, 26 November 2008 01:27

This PowerShell script process all dimensions and cubes in the specified Analysis Services database. This script is similar to the previously posted script that processes all dimensions. But this script allows you to specify how many processing commands you want to run per batch (parameter MaxCmdPerBatch) and how many processing threads (parameter MaxParallel) you want to use.

When you submit multiple processing commands in a single batch and if at least one processing statement fails, then all batch data is rolled back. When you limit batch size, then in case of the failure just processing in that batch will not be re-processed.

You will want to use this script when you need to limit servers workload during processing.

This script uses CaptureXML method to get processing commands into CaptureLog property. But instead of executing these commands using ExecuteCaptureLog method, this script breaks commands into smaller batches and also changes MaxParallel property to limit processing in parallel thread count.

Copy and save code from bellow into the file ProcessSSASDB.ps1. 

# ############################################################
# Author: Vidas Matelis (http://www.ssas-info.com)
# This script processes all dimensions and cubes in one specified database. You can limit how many processing commands are executed per batch and parallel.
# Save this script to the file ProcessSSASDB.ps1. To execute script:
# PowerShell.exe c:\scripts\ProcessSSASDB.ps1 -ServerName 'Vidas-Lenovo' -DBName 'Adventure Works DW' -ProcessTypeDim ProcessFull -ProcessTypeMG ProcessFull -Transactional Y -Parallel Y -MaxParallel 2 -MaxCmdPerBatch 5 -PrintCmd N
# -ProcessTypeDim   : ProcessFull | ProcessUpdate. Default ProcessFull
# -ProcessTypeMG   : ProcessFull | ProcessUpdate. Default ProcessFull
# -Transactional : Y | N; If value Y, then will do all dimension re-processing in one single transaction. Default value Y
# -Parallel      : Y | N; If value Y, then will do dimension re-processing in parallel. Default value Y
# -MaxParallel  : maximum number of threads on which to run commands in parallel. Default 5
# -MaxCmdPerBatch: maximum number of commands per batch. Default 2
# -PrintCmd      : Y | N; Default N; If value is Y prints commands before execution. Note errors will be printed out after each printed command and not at the end!
# Expected values for ProcessType: 'ProcessUpdate','ProcessFull'
# This script was written and tested on SSAS 2008, but it should also work with SSAS 2005
# ############################################################
param($ServerName="localhost", $DBName="Adventure Works DW", $ProcessTypeDim="ProcessFull",$ProcessTypeMG="ProcessFull", $Transactional="Y", $Parallel="Y",$MaxParallel=2,$MaxCmdPerBatch=5, $PrintCmd="N")

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

if ($Transactional -eq "Y") {$TransactionalB=$true} else {$TransactionalB=$false}
if ($Parallel -eq "Y") {$ParallelB=$true} else {$ParallelB=$false}

$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}

$DB = $server.Databases.FindByName($DBName)
if ($DB -eq $null) {
 Write-Output ("Database '{0}' not found" -f $DBName)
 break
}
Write-Output("Load start time {0}" -f (Get-Date -uformat "%H:%M:%S") )
Write-Output("----------------------------------------------------------------")
Write-Output("Server  : {0}" -f $Server.Name)
Write-Output("Database: {0}" -f $DB.Name)
Write-Output("DB State: {0}" -f $DB.State)
Write-Output("DB Size : {0}MB" -f ($DB.EstimatedSize/1024/1024).ToString("#,##0"))
Write-Output("----------------------------------------------------------------")
Write-Output("DB processing started.   Time: {0}" -f (Get-Date -uformat "%H:%M:%S"))

$server.CaptureXml=$TRUE # Just capture server statements, dont execute them
#Process dimensions

foreach ($dim in $DB.Dimensions) {
  $dim.Process($ProcessTypeDim)
} # Dimensions

#Process cubes
foreach ($cube in $DB.Cubes) {
 foreach ($mg in $cube.MeasureGroups) {
  foreach ($part in $mg.Partitions) {
   $part.Process($ProcessTypeMG)
  }
 }
}

# Separate step to process all linked measure groups. Linke MG does not have partitions
foreach ($cube in $DB.Cubes) {
 foreach ($mg in $cube.MeasureGroups) {
  if ($mg.IsLinked) {
   $mg.Process($ProcessTypeMG)
  }
 }
}
 
$server.CaptureXML = $FALSE # Finish capturing statements. All statements are in Server.CaptureLog

$cmdBatch = @"
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel MaxParallel="##MaxParallel##">
##ProcessCmd##
  </Parallel>
</Batch>
"@
$cmdBatch = $cmdBatch -replace("##MaxParallel##",$MaxParallel)

#$ErrorActionPreference = "SilentlyContinue"
$currentCmdNo=0; $currentCmdInBatchNo=0;
$processCmd="";$currentBatchNo=0
$TotalCmdCount = $Server.CaptureLog.Count

foreach ($cmdLine in $Server.CaptureLog) {
 $currentCmdNo = $currentCmdNo + 1
 $processCmd = $processCmd + $cmdLine + "`n"
 $currentCmdInBatchNo=$currentCmdInBatchNo + 1
 if ($currentCmdInBatchNo -ge $MaxCmdPerBatch -or $currentCmdNo -eq $TotalCmdCount) { #MaxCmdPerBatch reached, execute commands
  $processCmd = $cmdBatch -replace("##ProcessCmd##", $processCmd)
  if ($PrintCmd -eq "Y") { Write-Output($processCmd) }
  $currentBatchNo = $currentBatchNo + 1;
  Write-Output("=== Startining batch No {0}. Time: {1} ..." -f $currentBatchNo, (Get-Date -uformat "%H:%M:%S"))
  $Result = $Server.Execute($processCmd)
 
  # Report errors and warnings
  foreach ($res in $Result) {
   foreach ($msg in $res.Messages) {
    if ($msg.Description -ne $null) {Write-Output("{0}" -f $msg.Description)}
   }
  }
  # Reset temp values
  $processCmd = ""; $currentCmdInBatchNo=0;
 }
}#foreach

Write-Output("----------------------------------------------------------------")
Write-Output("DB processing completed. Time: {0}" -f (Get-Date -uformat "%H:%M:%S"))


Write-Output("----------------------------------------------------------------")
Write-Output("Listing objects that are not processed")


 

Sample how to run this script:

PowerShell.exe c:\scripts\ProcessSSASDB.ps1 -ServerName 'Vidas-Lenovo' -DBName 'Adventure Works DW' -ProcessTypeDim ProcessFull -ProcessTypeMG ProcessFull -Transactional Y -Parallel Y -MaxParallel 2 -MaxCmdPerBatch 5 -PrintCmd N

This script was tested on SSAS 2008, but should work on SSAS 2005 too.

 

 
Comments (6)
6 Tuesday, 27 August 2013 02:20
Dominic Lee
Hi,
I see your script comment says: Default MaxParallel=5 and default MaxCmdPerBatch=2. Should it be MaxParallel=2 and MaxCmdPerBatch=5 instead? (Which is the value used in parameter declaration below)
5 Tuesday, 04 June 2013 13:12
sandeepiii
Thanks a ton for this script.
4 Sunday, 06 February 2011 21:24
Girish
My AD account gets locked out as soon as i run the script. any idea what might be causing this
3 Friday, 22 October 2010 13:13
Buzz B
Script is great! How would I be able to process a single partition in a measure group that has more than one partition?
2 Saturday, 18 September 2010 19:28
VidasMatelis
Hi,

Thanks for your comments - I am always very happy when somebody finds my scripts useful.

If you can post any pointers on how you did error capturing - I am sure this would help other users. Even this is few lines where you explain idea - still helpful.
My code is also just an starting point, so other users could start their own customization without strugling with first steps. To be honest I do not use this script in real life - I am doing my processing using SSIS scripts, because all my loads are done with SSIS.
1 Thursday, 16 September 2010 13:44
Best Baboon
I love these scripts and would like to use them more, however it is difficult to catch errors when calling from the SQL Agent powershell cmd line.

I've hashed up your process Dims script so to crash if there are errors logged in the powershell output. (This done in a way too embarrassing to post back - you'd freak if you saw your lovely code treated in such a manner!)

If the errors & warnings were detectable in SQL Agent I would also love to see a similar script where one can pass a particular partition. Its a big struggle for me to adapt the code by inference and I am sure I destabilize it when doing so.

Thanks again for the gift-horse whose mouth I've just looked in and best wishes.

 

XL Cubed