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.

Advertisements
This entry was posted in SSAS, Syndication and tagged . Bookmark the permalink.

One Response to SSAS Joining Facts at Different Granularities

  1. vijayakumar says:

    when i am trying to set granularity attribute as Category Name then automatically relation attribute is changed from category code to Category Name but as per ur demo its showing as Category Name as granularity attribute and made relation between fact budget and dimaccount with category code can you please explain how to set

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s