Role Playing Dimensions

The same measure can be explained by a single dimension in different ways. For instance, when goods are shipped between countries, "units sold" can by explained by both "Country From" and "Country To". The total would always match, but the values at a Country level would differ because two separate countries are related to each transaction. The two main methods to handle this scenario require multiple instances of foreign keys in a single table, they are:

1. Duplicate Dimensions: Multiple instance of the same dimension

2. Role Playing Dimensions: Multiple relationships to a single dimension

This article examines the above scenarios as well as considerations on when to use one over the other.

Duplicate Dimensions

Duplicate Dimensions requires the duplication of a dimension for each associated foreign key. For instance, in the below Fact table their are 3 instances of "Date" with relationships between the surrogate key and foreign key: TradingDateID, ShippingDateID & DelieveryDateID:

Because there are 3 instances of the Date dimension, the users can slice and dice the measures in the fact by the required dimensions. For instance, to understand Quantity by:

• Trading Date - select "Date" from the "Trading Date" dimension

• Shipping Date - select "Date" from the "Shipping Date" dimension

• Delivery Date - select "Date" from the "Delivery Date" dimension

Role Playing Dimensions

Role Playing Dimensions requires the duplication of relationships to a single table for each associated foreign key. For instance, in the below Fact table their are 3 instances of "Date" with 3 different relationships to the same Date dimension:

To avoid ambiguity, only a single relationship can be active at any one time; for instance, you can only slice and dice the measure by Trading Date, Shipping Date or Delivery Date. In Power BI, we account for this by creating different measures using the DAX language for each of the measures. For instance, to understand Quantity there would be a measure for:

• Trading Date Quantity - sum of Quantity where the Date to TradingDateID relationship is activated

• Shipping Date - sum of Quantity where the Date to ShippingDateID relationship is activated

• Delivery Date - sum of Quantity where the Date to DeliveryDateID relationship is activated

This method can require significantly more DAX. For instance, there might by YTD, MTD and QTD calculations required for all of the above. There are, however, ways of producing this quickly such as C# scripting the measures and calculation groups.

---

Considerations for each method

1) Role Playing dimension will not work

When a single measure is explained by 2 or more instances of the same dimensions simultaneously, role playing dimensions will not work. For instance, if you had a scatter chart with Date on both the X and Y axis and a measure. There is a workaround with duplicate records of your Surrogate Key in the dimension and complex DAX, but this is not a practical solution in most case.

2) Understandability

Generally, the greater the amount of dimensions and attributes in those dimensions the less understandable a model is. It, therefore, is usually much simpler for a user to select the measure they want, i.e., "Shipping Quantity" than having to remember which date table they're filtering from.

3. Performance

There is a performance cost at loading time for multiple versions of same table. Similarly, there is a performance cost at load time in building out the relationships and at query time when using Role Playing Dimensions. However, the cost of both of these is very minimal and it would require an extreme case for performance to influence your decision.

---

Conclusion

The general advice is to use Role Playing Dimensions whenever you can over creating duplicate instances of the same dimension. This is despite the extra work it causes from a DAX perspective. This is because, as mentioned in the Non-Functional Requirements, the generic principle is to:

Design your models in a way which they are as understandable as possible, whilst meeting NFRs.

Having separate measures generally makes it easier for your end users to know exactly what they're selecting.