Setting Aggregation Designs on SSAS Partitions
Recently I deployed updates to our cubes that resulted in the aggregation designs being removed from our partitions. This was because in the Visual Studio solution we had more than one aggregation design for each measure group, so that re-processing and adding new partitions caused the aggregation designs to be dropped. D’oh!
Fortunately this is a task that you can do in bulk through the “Object Explorer Details” window (hint: press F7 when you have highlighted the measuregroup in the Object Explorer window)
However, I’m a big fan of using Powershell to run these tasks, because it’s still faster than going through the UI and can be resued and automated. Below is a script that you can run to update the aggregation design for a given measure group. You have to know the name of the aggregation design you want ot apply. If the aggregation design is already applied to the partition it skips the partition.
[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $ssasInstance,
[Parameter(Position=1,mandatory=$true)]
[string] $ssasDatabase,
[Parameter(Position=2,mandatory=$true)]
[string] $ssasCube,
[Parameter(Position=3,mandatory=$true)]
[string] $MeasureGroup,
[Parameter(Position=4,mandatory=$true)]
[string] $AggregationDesignId)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ssasInstance)
$database=$server.databases
$dbase=$database[$ssasDatabase]
#creating cube objects
$Cubes=New-object Microsoft.AnalysisServices.Cube
$Cubes=$dbase.cubes
$CubeToProcess = $ssasCube
$cube = $Cubes.FindByName($cubeToProcess)
$Cube|select name,state,lastprocessed
$mg = $Cube.MeasureGroups.FindByName($MeasureGroup)
foreach ($partition in $mg.Partitions)
{
$currentAggDesign = $partition.AggregationDesignId
if($currentAggDesign -ne $AggregationDesignId)
{
$date1=get-date
write-host "------ Aggregation Design needs to be updated. Partition "$partition.name" will be updated to "$AggregationDesignId
$partition.Set_AggregationDesignId($AggregationDesignId)
$partition.Update()
}
}
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" -ssascube "Sales" -MeasureGroup "Member" -AggregationDesignId "AggDesign_v718"