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)

2014-04-24 08_36_24-PRE2SQL01.PROD.INFRA.INT - Remote Desktop Connection Manager v2.2

2014-04-24 08_38_45-PRE2SQL01.PROD.INFRA.INT - Remote Desktop Connection Manager v2.2

 

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"

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.

2 thoughts on “Setting Aggregation Designs on SSAS Partitions”

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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