SSAS Date Dimension Relationship In Data Source View

Posted on

Question :

When a fact table is at a higher granularity to the date dimension (e.g. monthly) how should this be represented as a relationship in the data source view?

With a date dimension containing columns DateKey and MonthDateKey and fact table containing MonthDateKey should the relationship be:

Fact.MonthDateKey -> Date.DateKey or Fact.MonthDateKey -> Date.MonthDateKey

What are the implications if this is set incorrectly in the data source view?

Answer :

This relation is not necessarily expressed in the data source view, but in the dimension usage tab, but you can define it in the dsv if you want.

The implications of setting it incorrectly in the DSV are that the dimension usage tab will propose the wrong default and you have to change it manually, but whatever you put in the dimension usage tab on your measure group is what is actually defining the relation.

Without knowing your exact data model I would think your relation has to be Fact.MonthDateKey -> Date.MonthDateKey in your case.

There is an example of how this is done in the AdventureWorks Analysis services project where the date dimension is linked to the Sales Target measure group on Calendar Quarter:

enter image description here

You basically just pick another Granularity Attribute from your dimension and link it to the correct column in your fact table like this:

enter image description here

The dsv relation for this setup looks like this (which would be monthdatekey in your scenario)

enter image description here

The caveat is displayed as a warning when you don’t select the key attribute as your granularity attribute:

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

Which is basically saying you need to define attribute relationships.

This is also explained in more detail in this walkthrough Defining Dimension Granularity within a Measure Group

To specify a grain for a cube dimension other than the default grain,
you modify the granularity attribute for a cube dimension as used
within a particular measure group on the Dimension Usage tab of Cube
Designer. When you change the grain of a dimension within a specific
measure group to an attribute other than the key attribute for that
dimension, you must guarantee that all other attributes in the measure
group are directly or indirectly related to new granularity attribute.
You do this by specifying attribute relationships between all other
attributes and the attribute that is specified as the granularity
attribute in the measure group. In this case, you define additional
attribute relationships rather than move attribute relationships. The
attribute that is specified as the granularity attribute effectively
becomes the key attribute within the measure group for the remaining
attributes in the dimension. If you do not specify attribute
relationships appropriately, Analysis Services will not be able to
aggregate values correctly, as you will see in the tasks in this
topic.

Leave a Reply

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