Role-Playing Dimensions
Sometimes, a single physical dimension table is joined to a fact table multiple times, with each join representing a different logical view of the dimension. These separate views (with unique attribute column names) are called "roles".
Example (Dates in an Order Fact Table):
Instead of creating three separate date tables for ordering, shipping and delivering, you use SQL aliases to join the exact same dim_date table three times.
| order_id | order_date_key | ship_date_key | delivery_date_key |
|---|---|---|---|
| 9001 | 20240101 | 20240103 | 20240105 |
| 9002 | 20240102 | 20240102 | 20240106 |
Each foreign key represents a different role and references the appropriate row in the shared Conformed Date Dimension.
Overlapping Dimension Classifications
Dimension classifications are not necessarily mutually exclusive. A dimension can have multiple characteristics at the same time. For example, dim_date can be both a conformed dimension and a role-playing dimension.