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

Tuesday, February 5, 2013

Slightly Intelligent SSAS Processing with Powershell

Recently, I had a story to do some partitioning in one of our cubes to help performance on some data that was loaded once per month and had years of history.  The process is pretty straightforward and part of the convention was to use the word "Current" at the end of the partition name that we would want to process after the ETL was complete.  Once you get into partition specific processing, the XMLA goes from simple to a whole bunch of commands, not complicated but cumbersome.  The very next sprint yielded adding another measure group and even more dimensions, the setup of the XMLA was not hard, but manual and prone to error if you are anything like me.

Quick interruption for a public service announcement: Maple syrup season will hopefully be starting in about one month.  Its a great time to be out in the woods, and the product is pretty good too.

Ok, back to it.  Having worked on some SSAS with Powershell this ended up being a straightforward way to process the dimensions and any partition with the word "Current" in it.  This really is a template for me as I want to utilize a naming convention to make this easier across the board, and if a measure group contains no "Current partitions, I want to process the partitions in that measure group.  Its a nice start to what will likely be many more "management" type scripts in working with SSAS to automate some of the mundane parts.

Here is the base script, free of fluff and error handling.  Remember SQL Agent is really specific about scripts throwing errors....

 $srcSSASName = "MySSASServer"  
  $ssasDb = "Brewery"  
  $partitionSpecificTerm = "Current";  
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null  
  $source = New-Object Microsoft.AnalysisServices.Server  
  #Grab the SSAS Database handle
  $Db = $source.Databases.GetByName($ssasDb)  

  #For the DB, Process any Dimensions that need it  
  foreach($dimension in $Db.Dimensions )  
    Write-Host "Processing dimension " $dimension.Name  
  #Loop through each cube in the SSAS database  
  foreach($cube in $Db.Cubes)  
   foreach($measuregroup in $cube.MeasureGroups)  
   foreach($partition in $measuregroup.Partitions)  
    if($partition.Name -like "*$partitionSpecificTerm*")  
     Write-Host "Processing specific partition " $partition.Name  
    if($partition.State -ne "Processed")  
     Write-Host "Processing unprocessed partition " $partition.Name  
     # $partition.Process("ProcessFull");  
   if($measuregroup.State -ne "Processed")  
    Write-Host "Problem with measuregroup " $measuregroup.Name  

No comments:

Post a Comment