If you have ever manually edited aggregation designs, you may have wondered what aggregation currently exist for a particular attribute. Well the following powerSSAS script will give you just that. (you could do this with straight PowerShell, but powerSSAS just makes it slightly easier to address a given measure group)
Technically this script is just one single statement, with a couple of parameters to make it more re-usable. I have broken it up in order to make more readable by using the PowerShell line continuation character which is the back tick (`). I also pretty much developed it, starting off at the top and slowly drilling down adding one "line" at a time.
Before I (try to) explain what is going on here I thought I should explain some of the PowerShell notations that this script uses.
| | | pipes the objects output from the previous command into the next command |
| % | shorthand for "for each" |
| { } | this is a script block, when it comes after % the script block is executed once for each object in the collection that was passed in. |
| ; | command separator |
| $_ | denotes the current object |
| -eq | does an equality comparison, in powershell the = sign is only used for assigning values |
So here is the script:
| 1
2 3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 | add-PSSnapin powerssas
new-PSDrive ssas powerssas localhost function getAggsForAttrib ([String]$MeasureGroupPath, [String]$Attribute)
{ (get-Item $MeasureGroupPath).AggregationDesigns `
| % { $_.Aggregations } `
| % { $agg = $_ ; $agg} `
| % { $_.Dimensions} `
| % {$_.Attributes} `
| % {$_.Attribute} `
| Where-Object {$_.Name -eq $Attribute} `
| %{$agg} `
| % { $_.Dimensions} `
| % { $curDim = $_ ; $curDim} `
| % {$_.Attributes} `
| % {$_.Attribute} `
| add-Member ScriptProperty AggregationName {$agg.Name } -passthru `
| add-Member scriptProperty CubeDimension {$curDim.CubeDimension} -passthru `
| Format-Table AggregationName, CubeDimension, name
} |
Now, I'm sort of conscious that most of the people that are reading this will more more into Analysis Services than PowerShell. So for those of you that are interested, below is a rough outline of what is going on in this function. If you are just interested in the output, you can skip ahead to see what the output looks like.
lines 1 & 2 - these load powerSSAS and setup a connection to the localhost, if you are working with a remote instance you would just need to replace localhost with the name of the instance.
lines 3,4 & 20 - this is the function declaration
line 5 - This is where we get the Aggregation design for the specified Measure Group
line 6 - this can be translated into English as "for each AggregationDesign object that is piped in, echo out the Aggregations collection"
line 7 - for each aggregation in the Aggregations collection that was passed in, store the value in a variable called $agg (created on the fly) and echo out the contents of that variable.
lines 8-10 - for each Dimension in the Aggregation that was passed in get the Attributes collection and the individual Attributes
line 11 - filter all the Attribute objects so that we only return those where the name is the same as the $Attribute parameter
line 12 - echo out the $agg variable that was associated with the attributes that were filtered out in line 11.
lines 13-14 - grab the dimensions from the filtered aggregations and stored them in the $curDim variable and echo the contents of that variable to the next step in the pipeline.
lines 15-16 - output the set of other Attributes that are part of the aggregation design
lines 17-18 - to the Attributes which we are outputting from the previous step add a couple of dynamic member properties, one for the name of the aggregation and another for the name of the dimension.
line 19 - format the output as a table, only displaying the selected properties.
And here is what happens when you ask the function for aggregations for the Internet Sales measure group in the Adventure Works DW database that include the Sales Territory Country attribute.
PS> getAggsForAttrib "ssas:\databases\Adventure works dw\cubes\adventure works\measuregroups\internet sales" "Sales Territory Country"
AggregationName CubeDimension Name
--------------- ------------- ----
Aggregation 8 Promotion Promotion Category
Aggregation 8 Sales Territory Sales Territory Country
Aggregation d Sales Territory Sales Territory Country
Aggregation d Product Days to Manufacture
Aggregation e Sales Territory Sales Territory Country
Aggregation e Product Product Line
Aggregation f Sales Territory Sales Territory Country
Aggregation f Date Fiscal Year
Aggregation 10 Sales Territory Sales Territory Country
Aggregation 10 Date Calendar Year
Aggregation 11 Sales Territory Sales Territory Country
Aggregation 11 Ship Date Fiscal Year
Aggregation 12 Sales Territory Sales Territory Country
Aggregation 12 Ship Date Calendar Year
Aggregation 13 Sales Territory Sales Territory Country
Aggregation 13 Delivery Date Fiscal Year
Aggregation 14 Sales Territory Sales Territory Country
Aggregation 14 Delivery Date Calendar Year
Aggregation 15 Sales Territory Sales Territory Country
Aggregation 15 Product Category
And here is what happens when you ask the function for aggregations for the Internet Sales measure group in the Adventure Works DW database that include the Calendar Semester attribute. Note that the Date dimension is used a couple of times in the Adventure Works cube as a role playing dimension (Date, Ship Date, Delivery Date)
PS> getAggsForAttrib "ssas:\databases\Adventure works dw\cubes\adventure works\measuregroups\internet sales" "Calendar Semester"
AggregationName CubeDimension Name
--------------- ------------- ----
Aggregation 2 Promotion Promotion Category
Aggregation 2 Delivery Date Calendar Semester
Aggregation 4 Promotion Promotion Category
Aggregation 4 Ship Date Calendar Semester
Aggregation 7 Promotion Promotion Category
Aggregation 7 Date Calendar Semester