Cascading Measure Values in a User Hierarchy in SSAS

February 12, 2012

one comment

I had a new measure to add to my cube a new measure. My fact table up till now was with a monthly granularity. I assumed that I would have to define a scope in MDX for the intermediate period and the monthly period. I connected the fact to the year level in the Period dimension and got this message:

DescendingValues4



If you select a non – key granularity attribute, the server will not be able to aggregate data properly unless you make sure that all other attributes are directly or indirectly related to it by specifying them as related attributes


Now, I knew I had connected the attributes in the Period level to each other when I defined the user hierarchy (best practices and a little squiggly line in SSAS 2008 onwards recommend it :) ):



DescendingValues5


(The squiggly line I still have is for not defining a default member for the year)


So I processed the cube with the new fact table connected to the year level. After the process finished, I looked straight away at what I got:
The year level gave me the desired number:



DescendingValues1


Opening to the intermediate level gave me the sum for the year level:



DescendingValues2


And so did the month level:



DescendingValues3


So actually I didn’t need to define any scope on the lower levels as Analysis services already knew to give the children in the user hierarchy the value of their parent. Analysis Services is one smart cookie – which is why I love her so much :)


Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

one comment

  1. mikypuffFebruary 14, 2012 ב 0:18

    Good post, I didn’t think that SSAS will anticipate this.

    It’s funny that you decided that SSAS is a female… :-)

    Reply