Role-Playing Dimensions

A table with multiple valid relationships between itself and another table is known as a role-playing dimension. This is most commonly seen in dimensions such as Time and Customer.

For example, the Sales fact has multiple relationships to the Time query subject on the keys Order Day, Ship Day, and Close Day.

the multiple relationships between the sales fact and the time query subject. each relationship represents a role.

Remove the relationships for the imported objects, fact query subjects, and role-playing dimensional query subjects. Create a model query subject for each role. Consider excluding unneeded query items to reduce the length of the metadata tree displayed to your users. Ensure that a single appropriate relationship exists between each model query subject and the fact query subject. Note: This will override the Minimized SQL setting but given a single table representation of the Time dimension, it is not considered to be problematic in this case.

the sales fact and the order day, ship day and close day query subjects. The multiple relationships shown previously have been converted to model query subjects.

Decide how to use these roles with other facts that do not share the same concepts. For example, Product forecast fact has only one time key. You need to know your data and business to determine if all or any of the roles created for Time are applicable to Product forecast fact.

In this example, you can do one of the following:

  • Create an additional query subject to be the conformed time dimension and name it clearly as a conformed dimension.

    Pick the most common role that you will use. You can then ensure that this version is joined to all facts requiring it. In this example, Close Day has been chosen.

    the close day query subject is the conformed time dimension. it is joined to the sales fact and the product forecast fact
  • You can treat Ship Day, Order Day, and Close Day as interchangeable time query subjects with Product forecast fact.

    In this case, you must create joins between each of the role-playing dimensions and Product forecast fact. You can use only one time dimension at a time when querying the Product forecast fact or your report may contain no data. For example, Month_key=Ship Month Key (200401) and Month key=Close Month Key (200312).

    the ship day, order day and close day query subjects are linked to the product forecast fact

If modeling dimensionally, use each model query subject as the source for a regular dimension, and name the dimension and hierarchies appropriately. Ensure that there is a corresponding scope relationship specific to each role.