Role-playing dimensions

A table with multiple relationships between itself and another table is known as a role-playing dimension.

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

Create a table for each role that you want the Time table to play. If your role-playing dimension requires only a subset of columns from the original table, you can remove unneeded columns for a cleaner presentation, and provide appropriate names, such as Date in Time, Ship Date in Ship Day, and Close Date in Close Day. You can name the keys appropriately as well. For example, rename Day Key to Ship Day Key in Ship Day and to Close Day Key in Close Day. Ensure that a single, appropriate relationship exists between each role-playing table and the fact table on the appropriate key. In the following example, the Time table is used to represent the Order Day for a sale. The other two role-playing dimensions, Ship Day and Close Day, are self-explanatory.

The role-playing dimensions that you create might or might not be applicable to other fact tables. For example, Product forecast fact might be applicable only to the Time dimension and would, therefore, be joined only to this dimension.

Now, Time can be used to compare Product forecasts and Sales.