Data modeling · Extra High-Value

What is a role-playing dimension?

Mediumrole-playing dimensiondim_datealiases

Interview question

What is a role-playing dimension?

How to answer

The short answer

A role-playing dimension is one physical dimension reused in multiple roles via different on the fact.

The classic is dim_date:

Output
fct_orders
  order_date_key   --> dim_date  (role: order date)
  ship_date_key    --> dim_date  (role: ship date)
  delivery_date_key--> dim_date  (role: delivery date)

In SQL / BI you alias the same table 3 times:

SQL
FROM fct_orders f
JOIN dim_date order_dt ON f.order_date_key = order_dt.date_key
JOIN dim_date ship_dt  ON f.ship_date_key  = ship_dt.date_key

Other examples

  • dim_airport as origin vs destination
  • dim_employee as seller vs support agent
  • dim_geography as billing vs shipping address

Why it helps

One maintained calendar (fiscal flags, holidays) serves every date role. You do not copy dim_date 3 times.

Interview tip

> "Same dimension, multiple roles, multiple FK columns, aliases in queries."