SSAS Different Dimension Granularity

In an SSAS cube you sometimes need to connect a dimension at a different granularity than other dimensions. For example, if you have budget measures and actual numbers. If you are trying to budget your expenses you budget at a higher level than the actual amounts. You budget money for groceries, not for Eggs, Milk, and bread. But your actual amounts will be down to the individual items.

In the cube browser image below you can see this in use. Notice the budget numbers only show on the category level and not on the individual items.

Here are the tables being used in this cube.
FactActuals

FactBudget

DimAccount

To set up this you will need to open the dimension usage tab in the cube editor. Click on the connection between dim account and fact budget and set up the category code as the key between the tables. The category code will be the relationship between the dimension and the fact table. A foreign key between the tables does not need to exist.


One other change is to set the budget measure group to ignore unrelated dimensions. This is in the Cube Structure tab, click on the budget measure group and change this property. It will need to be false, I know this seems backwards.

That is all you need. Let me know if you have any questions.

Ref link: http://www.bidn.com/blogs/MikeDavis/ssis/706/ssas-different-dimension-granularity