Setting Aggregation Designs on SSAS Partitions Part Two

Earlier this year I posted a Powershell script that will set the set the aggregation designs on your cube partitions. I recently created another script that contains a “Fix” flag to set the aggregation design, so you can just run it without the flag to get the info, or set the flag and fix the aggregation designs. I also added a “process” part that will run a process index on any partition whose aggregations are not fully processed.

The advantages of using this over the older version include the aforementioned process and checks, but also it means you don’t have to specify cube, measuregroup and the aggregation designs name. The disadvantage to this is that it assumes you want the latest aggregation design applied and processed. Somewhere along the way there will probably be a script which combines all of this nicely!

 [string] $ssasInstance,

 [string] $ssasdb,

 [switch] $Fix)

$server = New-Object Microsoft.AnalysisServices.Server

$Cubes=New-object Microsoft.AnalysisServices.Cube

foreach ($cube in $cubes)
 $Cube|select name,state,lastprocessed 
 foreach ($mg in $cube.MeasureGroups)
 foreach ($partition in $mg.Partitions)
 $totalAggCount = $partition.AggregationDesign.Aggregations.Count
 $con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection("datasource=$ssasInstance")
 $MDX="SELECT * FROM SystemRestrictSchema(`$system.discover_partition_stat, DATABASE_NAME = '$dbase',CUBE_NAME = '$cube', MEASURE_GROUP_NAME = '$mg', PARTITION_NAME = 
 $command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($MDX, $con) 
 $dataAdapter = new-object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($command)
 $ds = new-object System.Data.DataSet
 $dataAdapter.Fill($ds) > $null
 $dataExtract = $ds.tables[0].select("AGGREGATION_NAME IS NOT NULL")
 $processedAggCount = $DataExtract.Count
 $totalAggCount = $partition.AggregationDesign.Aggregations.Count
 if($partition.AggregationDesignID.Length -lt 1)
 "$($partition.Name) does not have an aggregation design applied"
 if ($fix.IsPresent)
 if ( $partition.Parent.AggregationDesigns.Count -gt 0)
 "$($partition.Name) assigning aggregation design $($partition.Parent.AggregationDesigns[$partition.Parent.AggregationDesigns.Count-1].ID)"
 $partition.AggregationDesignID = $partition.Parent.AggregationDesigns[$partition.Parent.AggregationDesigns.Count-1].ID
 $totalAggCount = $partition.AggregationDesign.Aggregations.Count
 {"$($partition.Name) does not have a aggregation design created for it. Ignoring..."}
 if ($totalAggCount -ne $processedAggCount)
 "$($partition.Name) aggregation design $($partition.AggregationDesignID) has $processedAggCount of $totalAggCount processed "
 if ( $partition.Parent.AggregationDesigns.Count -gt 0)
 "$($partition.Name) processing..."
 "$($partition.Name) done. Processing took " ($date2-$date1).Hours " Hours, " ($date2-$date1).Minutes " Mins, " ($date2-$date1).Seconds " Secs "

And if you want to batch it multiple times then you can just do so by creating a powershell script that calls this script and passes in the parameters;

PS C:\Users\richard.lee> C:\Users\richard.lee\Documents\set_AggregationDesign.ps1 -ssasinstance "sqla\olap01" -ssasDatabase "ABC" -Fix

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s