In this post we are going to cover the role-playing dimension: one dimension table that a fact table uses several times, each time with a different meaning.
The classic example is the date dimension. An order fulfillment fact has an order date, a ship date and a delivery date. We don't build three date tables. All three keys point to the same dim_date, and the table simply plays three roles. One calendar, one set of holidays and fiscal years, reused for every date on the fact.
We will build a small date dimension and a fulfillment fact with three date keys, join the date dimension three times with aliases, and then create a role-specific view that makes the query read like the business question.
Question 27 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A role-playing dimension is one physical dimension that a fact table references several times, in different roles. For example, dim_date plays order date, ship date and delivery date. Each role is joined with its own alias, or exposed as a role-specific view.
Let's create some sample data
We need two tables: a small dim_date with eight days in January 2026, and fact_order_fulfillment with three date keys that all reference it.
DROP VIEW IF EXISTS dim_ship_date;
DROP TABLE IF EXISTS fact_order_fulfillment;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
full_date DATE,
day_name VARCHAR(10),
day_type VARCHAR(10)
);
INSERT INTO dim_date VALUES
(20260105, '2026-01-05', 'Mon', 'Weekday'), (20260106, '2026-01-06', 'Tue', 'Weekday'),
(20260107, '2026-01-07', 'Wed', 'Weekday'), (20260108, '2026-01-08', 'Thu', 'Weekday'),
(20260109, '2026-01-09', 'Fri', 'Weekday'), (20260110, '2026-01-10', 'Sat', 'Weekend'),
(20260111, '2026-01-11', 'Sun', 'Weekend'), (20260112, '2026-01-12', 'Mon', 'Weekday');
-- one fact, THREE references to the same date dimension
CREATE TABLE fact_order_fulfillment (
order_id INT PRIMARY KEY,
order_date_key INT REFERENCES dim_date(date_key),
ship_date_key INT REFERENCES dim_date(date_key),
delivery_date_key INT REFERENCES dim_date(date_key)
);
INSERT INTO fact_order_fulfillment VALUES
(1001, 20260105, 20260107, 20260109),
(1002, 20260108, 20260110, 20260112),
(1003, 20260109, 20260112, 20260112);Let's look at the date dimension.
SELECT * FROM dim_date ORDER BY 1;| date_key | full_date | day_name | day_type |
|---|---|---|---|
| 20260105 | 2026-01-05 | Mon | Weekday |
| 20260106 | 2026-01-06 | Tue | Weekday |
| 20260107 | 2026-01-07 | Wed | Weekday |
| 20260108 | 2026-01-08 | Thu | Weekday |
| 20260109 | 2026-01-09 | Fri | Weekday |
| 20260110 | 2026-01-10 | Sat | Weekend |
| 20260111 | 2026-01-11 | Sun | Weekend |
| 20260112 | 2026-01-12 | Mon | Weekday |
Eight days from January 5 to January 12. January 10 and 11 are a Saturday and a Sunday, so they are marked Weekend.
SELECT * FROM fact_order_fulfillment ORDER BY 1;| order_id | order_date_key | ship_date_key | delivery_date_key |
|---|---|---|---|
| 1001 | 20260105 | 20260107 | 20260109 |
| 1002 | 20260108 | 20260110 | 20260112 |
| 1003 | 20260109 | 20260112 | 20260112 |
One row per order, with three date keys: when the order was placed, when it shipped and when it was delivered. All three are foreign keys to the same dim_date.
Step 1: Join the same dimension three times
So how do we query it? We join dim_date three times, with a different alias for each role: od for the order date, sd for the ship date and dd for the delivery date. Same table, different keys from the fact.
SELECT f.order_id,
od.day_name AS order_day,
sd.day_name AS ship_day,
dd.day_name AS delivery_day
FROM fact_order_fulfillment f
JOIN dim_date od ON od.date_key = f.order_date_key
JOIN dim_date sd ON sd.date_key = f.ship_date_key
JOIN dim_date dd ON dd.date_key = f.delivery_date_key
ORDER BY f.order_id;| order_id | order_day | ship_day | delivery_day |
|---|---|---|---|
| 1001 | Mon | Wed | Fri |
| 1002 | Thu | Sat | Mon |
| 1003 | Fri | Mon | Mon |
Now each order shows the day it was placed, shipped and delivered, side by side. Order 1002 was placed on a Thursday, shipped on a Saturday and delivered on a Monday. Order 1003 shipped and was delivered on the same Monday.
The aliases are what make this work. Without them, the database would not know which join of dim_date we mean when we ask for day_name. With them, each copy of the join answers for exactly one role, and every role still reads from the same calendar.
Step 2: Create a role-specific view
Aliases work, but business users and BI tools prefer clear names. Many teams create a small view per role. Let's create dim_ship_date: it is just the date dimension with every column renamed for the ship role.
CREATE VIEW dim_ship_date AS
SELECT date_key AS ship_date_key,
full_date AS ship_date,
day_name AS ship_day,
day_type AS ship_day_type
FROM dim_date;The view adds no data and copies nothing. It only renames columns: ship_date_key, ship_date, ship_day and ship_day_type.
Step 3: Which orders shipped on a weekend?
Now let's ask a business question using the view.
SELECT f.order_id, s.ship_date, s.ship_day
FROM fact_order_fulfillment f
JOIN dim_ship_date s
ON s.ship_date_key = f.ship_date_key
WHERE s.ship_day_type = 'Weekend';| order_id | ship_date | ship_day |
|---|---|---|
| 1002 | 2026-01-10 | Sat |
Order 1002 shipped on 2026-01-10, a Saturday. The query reads exactly like the question: join to the ship date and filter on the ship day type.
Only one relationship between two tables can be active, so role-specific views, or the USERELATIONSHIP function, are the usual ways to handle role-playing dimensions.
Not just dates
Dates are the classic example, but not the only one. Whenever the same kind of thing appears more than once on a fact row, it is a role-playing dimension.
| Dimension | Roles on one fact row |
|---|---|
| dim_date | order date, ship date, delivery date |
| dim_customer | bill-to customer, ship-to customer |
| dim_employee | sales rep, approving manager |
| dim_airport | origin, destination |
- A customer dimension can play bill-to and ship-to customer on the same order.
- An employee dimension can play sales rep and approving manager.
- An airport dimension can play origin and destination on a flight.
A conformed dimension is shared across several fact tables. A role-playing dimension is used several times in one fact table.
Follow-up questions interviewers ask
Why not copy the date table per role?
Multiple copies drift apart; one table keeps one calendar, one set of holidays and fiscal periods.
How do BI tools handle role-playing dimensions?
Role-specific views, or (Power BI) inactive relationships activated with USERELATIONSHIP.
Role-playing vs conformed dimension?
Conformed: shared by several fact tables. Role-playing: used several times by one fact table.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #27 - What is a role-playing dimension?
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q27; SET search_path TO dwh_q27;
-- ========================================================================
-- Step 0: create and load the sample data
DROP VIEW IF EXISTS dim_ship_date;
DROP TABLE IF EXISTS fact_order_fulfillment;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date (
date_key INT PRIMARY KEY,
full_date DATE,
day_name VARCHAR(10),
day_type VARCHAR(10)
);
INSERT INTO dim_date VALUES
(20260105, '2026-01-05', 'Mon', 'Weekday'), (20260106, '2026-01-06', 'Tue', 'Weekday'),
(20260107, '2026-01-07', 'Wed', 'Weekday'), (20260108, '2026-01-08', 'Thu', 'Weekday'),
(20260109, '2026-01-09', 'Fri', 'Weekday'), (20260110, '2026-01-10', 'Sat', 'Weekend'),
(20260111, '2026-01-11', 'Sun', 'Weekend'), (20260112, '2026-01-12', 'Mon', 'Weekday');
-- one fact, THREE references to the same date dimension
CREATE TABLE fact_order_fulfillment (
order_id INT PRIMARY KEY,
order_date_key INT REFERENCES dim_date(date_key),
ship_date_key INT REFERENCES dim_date(date_key),
delivery_date_key INT REFERENCES dim_date(date_key)
);
INSERT INTO fact_order_fulfillment VALUES
(1001, 20260105, 20260107, 20260109),
(1002, 20260108, 20260110, 20260112),
(1003, 20260109, 20260112, 20260112);
-- Step 1: Join the SAME date dimension three times, with three aliases
SELECT f.order_id,
od.day_name AS order_day,
sd.day_name AS ship_day,
dd.day_name AS delivery_day
FROM fact_order_fulfillment f
JOIN dim_date od ON od.date_key = f.order_date_key
JOIN dim_date sd ON sd.date_key = f.ship_date_key
JOIN dim_date dd ON dd.date_key = f.delivery_date_key
ORDER BY f.order_id;
-- Step 2: A role-specific view makes the role obvious for BI users
CREATE VIEW dim_ship_date AS
SELECT date_key AS ship_date_key,
full_date AS ship_date,
day_name AS ship_day,
day_type AS ship_day_type
FROM dim_date;
-- Step 3: Which orders shipped on a weekend?
SELECT f.order_id, s.ship_date, s.ship_day
FROM fact_order_fulfillment f
JOIN dim_ship_date s
ON s.ship_date_key = f.ship_date_key
WHERE s.ship_day_type = 'Weekend';
Key points
- One physical dimension, referenced several times by a fact
- dim_date as order, ship and delivery date
- Join it once per role, with aliases
- Role views rename columns for BI users
- Role-playing: roles in one fact. Conformed: shared across facts
Question 28 — Mini-dimensions and outriggers What are mini-dimensions and outriggers?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.