Sql-server – SSAS Date Dimension Relationship In Data Source View

sql serverssas

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?

Best 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.

Related Question