Role-Playing Dimension

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.

Multiple Time Zones

To capture both universal standard time (UTC) as well as local times in multi-time zone applications, dual foreign keys should be placed in the affected fact tables. These keys join to two role-playing date (and potentially time-of-day) dimension tables, allowing users to slice facts either by the absolute global time or by the local time where the event occurred.

fact_id	utc_date_key	local_date_key	sales_amount
100	20260101	20251231	$50.00