Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSAS Joining Facts at Different Granularities

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.

image

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

image

FactBudget

image

DimAccount

image

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.

image

image

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.

image

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


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...