A role-playing dimension is the solution to a fact which references the same set of data multiple times. For example, a Party dimension, might have roles of Customer and Supplier. A date dimension might have roles of Order Date, Shipment Date, Invoice Date and Payment Date.
Ajilius implements role-playing dimensions as views over a base dimension.
To create a role playing dimension, start by populating a base dimension. We’ll use the dim_date dimension from the demonstration database. Then, select the Create Role option from the dimension context menu:
Enter a role name and description for the new dimension. The role name will be appended to the base dimension name (eg, dim_date will become dim_date_order), and prefixed to the column name (eg, cal_year will become order_cal_year):
When you press the ADD action button, a new role definition will be created, as if it were a standard dimension:
Generate scripts for the dimension, and you will notice that only a VIEW is created. This is because the contents of the dimension rely on the base table, whereas the roles are created as views over this table:
When adding dimensions to fact tables, role playing dimensions will work exactly the same as a physicalised dimension.