powerSSAS: Getting Aggregations for a particular Attribute

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
 


Technorati Tags: ,

Print | posted on Sunday, April 27, 2008 10:46 PM

Comments on this post

# re: powerSSAS: Getting Aggregations for a particular Attribute

Requesting Gravatar...
Interesting post.
Can you show us somnething similar to view dimension data allowed or denied sets in database roles?

Thanks.
Left by miltruenos on Apr 29, 2008 9:50 PM

Your comment:

 (will show your gravatar)