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 :)):
(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:
Opening to the intermediate level gave me the sum for the year level:
And so did the month level:
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 :)