Role-playing dimensions

В fact-таблице заказов почти всегда несколько дат: дата оформления заказа, дата отгрузки, дата доставки. Все три — это даты, и про каждую хочется задавать одинаковые вопросы: какой это был день недели, месяц, квартал, был ли это выходной. Возникает соблазн завести три таблицы: dim_order_date, dim_ship_date, dim_delivery_date. Это была бы ошибка — три копии одного и того же справочника, которые надо синхронно обновлять и которые занимают место втрое.

Role-playing dimension — это правильное решение. Одна физическая dimension-таблица используется в одной fact-таблице несколько раз, каждый раз в своей роли. dim_date физически одна, но к fct_orders она присоединяется трижды: как дата заказа, как дата отгрузки, как дата доставки.

Несколько foreign keys на одну dimension

Механика простая: fact-таблица содержит несколько foreign-key столбцов, и все они ссылаются на одну и ту же dimension-таблицу. Каждый столбец — отдельная роль.

CREATE TABLE fct_orders (
    order_key        BIGINT,
    customer_key     INT      REFERENCES dim_customer(customer_key),
    order_date_key   INT      REFERENCES dim_date(date_key),  -- роль 1
    ship_date_key    INT      REFERENCES dim_date(date_key),  -- роль 2
    delivery_date_key INT     REFERENCES dim_date(date_key),  -- роль 3
    order_amount     NUMERIC(12,2),
    PRIMARY KEY (order_key)
);

Все три столбца *_date_key указывают на dim_date. Физически dimension одна, но fact-таблица «видит» её с трёх сторон. Это законно: foreign key — это просто ссылка, и ничто не запрещает нескольким столбцам одной таблицы ссылаться на одну и ту же родительскую таблицу.

Почему вообще все эти даты — одна dimension, а не три? Потому что вопросы, которые задают к дате заказа, дате отгрузки и дате доставки, одинаковы. Аналитика интересует год, квартал, месяц, день недели, флаг выходного, fiscal-период — для любой из трёх дат. Если бы каждая дата требовала своего набора атрибутов, это были бы разные dimensions. Но набор атрибутов у них тождествен — отличается лишь роль, в которой дата выступает в конкретном заказе. А тождественный набор атрибутов — это и есть признак одной dimension. Три отдельные таблицы означали бы три копии одного и того же календаря: одни и те же 7300 строк, продублированные трижды, которые надо синхронно генерировать и обновлять. Любая ошибка в одной копии — и отчёты по разным датам перестают быть сопоставимыми.

Проблема возникает на этапе запроса. Если просто написать JOIN dim_date ON ... три раза, СУБД не поймёт, к какой роли относится столбец month — их станет три с одинаковым именем. Нужен способ дать каждой роли понятное имя.

Решение через alias в запросе

Самый прямой способ — присоединить dim_date трижды с разными псевдонимами (alias) таблицы. Каждый alias — это отдельная роль, и столбцы из него адресуются через имя роли.

SELECT
    o.order_key,
    od.full_date   AS order_date,
    od.day_name    AS order_day_of_week,
    sd.full_date   AS ship_date,
    dd.full_date   AS delivery_date,
    dd.full_date - sd.full_date AS days_in_transit
FROM fct_orders o
JOIN dim_date od ON od.date_key = o.order_date_key      -- alias od = роль "заказ"
JOIN dim_date sd ON sd.date_key = o.ship_date_key       -- alias sd = роль "отгрузка"
JOIN dim_date dd ON dd.date_key = o.delivery_date_key   -- alias dd = роль "доставка"
WHERE od.year = 2026
ORDER BY o.order_key;

 order_key | order_date | order_day_of_week | ship_date  | delivery_date | days_in_transit
-----------+------------+-------------------+------------+---------------+----------------
 100501    | 2026-01-03 | Суббота           | 2026-01-05 | 2026-01-09    |       4
 100502    | 2026-01-03 | Суббота           | 2026-01-04 | 2026-01-06    |       2
 100503    | 2026-01-04 | Воскресенье       | 2026-01-08 | 2026-01-13    |       5

Три alias — od, sd, dd — это и есть три роли одной dim_date. Запрос спокойно фильтрует по году заказа (od.year), сравнивает дату доставки с датой отгрузки (dd.full_date - sd.full_date) и выводит день недели именно для даты заказа. СУБД для каждого alias строит отдельный JOIN, как будто это разные таблицы, хотя данные читаются из одной.

Обратите внимание, насколько естественно alias решает проблему именования. До введения alias запрос «дай месяц» был бы неоднозначен — месяца три. С alias каждый месяц адресуется явно: od.month — месяц заказа, sd.month — месяц отгрузки. Псевдоним таблицы делает роль частью адреса столбца. Это не трюк, а штатный механизм SQL: один JOIN — одна роль — один alias. Если в запросе три раза присоединяется одна таблица, у вас три alias и три роли, и они никогда не путаются.

Стоит сказать и про производительность. Может показаться, что три JOIN к одной таблице — это втрое больше работы. На практике dim_date крошечная (тысячи строк) и почти всегда целиком лежит в памяти; три JOIN к ней — дешёвая операция, оптимизатор обычно выполняет их как быстрый hash join по индексу date_key. Расходы на role-playing ничтожны по сравнению с альтернативой — тремя физическими копиями календаря, которые занимают втрое больше места и требуют тройной поддержки.

Решение через views: роли как именованные объекты

Писать alias в каждом запросе — рабочий, но не самый удобный путь. Аналитик в BI-инструменте видит одну таблицу dim_date и должен помнить, какой alias к какому date-ключу подключать. Kimball рекомендует второй подход — создать views поверх физической dimension, по одной на роль.

CREATE VIEW dim_order_date AS
    SELECT date_key   AS order_date_key,
           full_date  AS order_date,
           day_name   AS order_day_name,
           month_name AS order_month_name,
           is_weekend AS order_is_weekend
    FROM dim_date;

CREATE VIEW dim_ship_date AS
    SELECT date_key   AS ship_date_key,
           full_date  AS ship_date,
           day_name   AS ship_day_name,
           month_name AS ship_month_name
    FROM dim_date;

View — это сохранённый запрос, а не копия данных; он не занимает места под строки. Зато теперь у каждой роли есть собственное имя объекта и собственные имена столбцов с понятным префиксом. Запрос становится прозрачным:

SELECT o.order_key, od.order_date, od.order_day_name, sd.ship_date
FROM fct_orders o
JOIN dim_order_date od ON od.order_date_key = o.order_date_key
JOIN dim_ship_date  sd ON sd.ship_date_key  = o.ship_date_key;

На практике в зрелых warehouse чаще выбирают подход с views — и не только ради удобства аналитиков. Именованная роль-view документирует модель: само существование объекта dim_ship_date сообщает читателю схемы, что у заказа есть отдельная веха отгрузки. Схема становится самоописывающей. С чистым alias-подходом роли существуют только в головах разработчиков и в тексте конкретных запросов — нового человека в команде это замедляет. Views делают набор ролей явной, видимой частью модели данных.

Оба подхода корректны. Alias проще для разовых SQL-запросов; views удобнее, когда dimension используют аналитики через BI-инструмент и важно, чтобы каждая роль выглядела как самостоятельная dimension.

Есть и тонкость, о которой стоит знать. View поверх dim_date наследует все её данные автоматически: если в физическую dim_date добавили новый атрибут или новые строки на будущие годы, все role-playing views немедленно «видят» это изменение, потому что view — это запрос, выполняемый каждый раз заново. Не нужно ничего пересоздавать или синхронизировать. Это ещё один аргумент против трёх физических таблиц: там каждое изменение календаря пришлось бы вносить трижды и следить, чтобы копии не разъехались. С views единый источник — физическая dim_date — и любое его изменение мгновенно отражается во всех ролях.

Где ещё встречаются роли

Date — самый частый, но далеко не единственный кандидат на роль-плей. Эта ситуация в моделировании встречается постоянно: роль-плей возникает каждый раз, когда одна и та же сущность участвует в одном факте несколько раз, но по-разному, в разных смысловых ролях.

• Сотрудник в fct_sales: продавец, кассир, менеджер, оформивший возврат. Все ссылаются на dim_employee.
• Аэропорт в fct_flights: аэропорт вылета и аэропорт прилёта — обе роли смотрят в dim_airport.
• Время суток в fct_calls: время начала и время окончания звонка — обе роли смотрят в dim_time.

Признак, что перед вами role-playing dimension: в fact-таблице несколько столбцов с одинаковым «типом», и каждый по смыслу указывает на один и тот же справочник. Объединять их в один столбец нельзя — это разные роли. Заводить отдельные физические таблицы — расточительно. Правильно — одна физическая dimension и несколько ролей.

Особенно ярко role-playing проявляется в accumulating snapshot fact-таблицах — тех, что отслеживают процесс с несколькими вехами. Заказ проходит этапы: оформлен, оплачен, собран, отгружен, доставлен. У такой fact-таблицы пять date-foreign-key — по одному на веху, и все пять смотрят в dim_date. Это пять ролей одной dimension в одной строке. Без role-playing dimension такую fact-таблицу вообще трудно спроектировать аккуратно: пять копий календаря были бы абсурдом. С role-playing она получается естественно — пять *_date_key, один справочник, пять alias или пять views в запросах.

Полезно зафиксировать и обратный случай — когда несколько похожих столбцов НЕ являются ролями одной dimension. Если в fct_orders есть order_date_key и birth_date_key клиента, это не две роли: дата заказа и дата рождения относятся к разным сущностям (к заказу и к клиенту) и попадают в fact-таблицу по разным причинам. Role-playing — это когда столбцы описывают один и тот же аспект (например, «когда» в жизненном цикле одного заказа) с разных сторон. Дата рождения клиента вообще принадлежит dim_customer, а не fact-таблице. Различать «несколько ролей одной dimension» и «просто несколько дат разной природы» — часть навыка проектировщика.

Попробуй сам

Спроектируйте fact-таблицу fct_flights (одна строка — один рейс) с двумя role-playing dimensions.

1. Определите, какие два столбца будут ролями dim_airport (подсказка: откуда и куда летит самолёт) и какие два — ролями dim_time (когда вылетел и когда приземлился).
2. Напишите CREATE TABLE fct_flights с этими foreign keys, указав в комментариях роль каждого.
3. Напишите SELECT, который для каждого рейса выводит название аэропорта вылета, название аэропорта прилёта и длительность рейса (разница времён). Используйте alias-подход.
4. Перепишите тот же запрос через views dim_origin_airport и dim_destination_airport. Сравните читаемость.

Проверьте себя: сколько физических таблиц аэропортов у вас в схеме? Должна быть ровно одна.