A role-playing dimension is a single physical dimension joined to one fact several times, each join standing in for a different business role. The textbook case is the date dimension: an order fact has an order date, a ship date, and a due date - all three are calendar dates described by the same dim_date table, but each plays a distinct role in the fact. Rather than build three separate date dimensions, you build one and reference it three times.

The fact carries one key per role

The fact table holds a separate foreign key for each role, all pointing at the same dimension's primary key.

fact_order (
  order_id,
  order_date_key   INT,   -- role 1  -> dim_date.date_key
  ship_date_key    INT,   -- role 2  -> dim_date.date_key
  due_date_key     INT,   -- role 3  -> dim_date.date_key
  customer_key     INT,
  amount           DECIMAL
)
dim_date (date_key, full_date, fiscal_period, day_of_week, ...)

Implementing the roles: views or aliases

The problem is that you can't join dim_date to the same fact three times in one query without disambiguating it - and most BI tools won't let three measures all resolve to one physical table cleanly. There are two standard implementations. The first is a view per role: create dim_order_date, dim_ship_date, and dim_due_date as views over the one physical dim_date, each with role-prefixed column names. The single table stays the source of truth; the views give the BI layer three distinct, self-describing objects to model.

CREATE VIEW dim_ship_date AS
SELECT
  date_key      AS ship_date_key,
  full_date     AS ship_full_date,
  fiscal_period AS ship_fiscal_period,
  day_of_week   AS ship_day_of_week
FROM dim_date;

The second is table aliases in the query - join dim_date three times with different aliases (od, sd, dd), one per role key. Views are the better choice for a self-service BI environment because the roles become first-class, discoverable objects; aliasing is fine for hand-written SQL where the analyst controls the join. Either way the dimension is stored once and maintained once.

Frequently asked questions

What is a role-playing dimension? One physical dimension joined to a single fact multiple times, each join representing a different business role - order date, ship date, and due date all drawn from one date dimension.

Why not just build separate dimensions? Because the underlying data is identical - they're all calendar dates with the same attributes. Maintaining one table and reusing it avoids duplicating the dimension and keeps the calendar logic in one place.

How do you implement the roles? Either a view per role over the single physical table (with role-prefixed column names) or table aliases in the query. Views suit self-service BI because each role becomes a discoverable object; aliases suit hand-written SQL.

Is it only used for dates? Date is the classic and most common case, but any dimension playing multiple roles in one fact qualifies - for example an airport dimension serving as both origin and destination on a flight fact.