Капстоун: проектирование star schema

В прошлом уроке мы построили нормализованную OLTP-схему проката велосипедов. Она хороша для рабочей системы, но для аналитических вопросов («выручка по месяцам и типам велосипедов», «загрузка станций») она неудобна — каждый ответ потребует множества JOIN по нормализованным таблицам. Этот урок строит для той же предметной области другую модель — star schema — применяя четырёхшаговый процесс Kimball из модуля про размерное моделирование.

Напомним суть проблемы из модуля «От OLTP к OLAP»: нормализованная схема оптимизирована под запись и целостность, а аналитика — это тяжёлое чтение с агрегациями. Star schema разворачивает данные так, чтобы аналитический вопрос отвечался простым JOIN факта с измерениями. Идём по четырём шагам Kimball строго по порядку.

Шаг 1: выбираем бизнес-процесс

Четырёхшаговый процесс начинается с бизнес-процесса — события реального мира, которое мы хотим анализировать. Бизнес-процессы мы уже фактически выписали в уроке 1 как аналитические требования.

Главный процесс проката — аренда велосипеда. Это событие, которое порождает выручку, нагружает станции, отражает поведение клиента — то есть покрывает почти все аналитические требования. Это будет наш центральный бизнес-процесс и центральная fact-таблица.

Мог бы быть и второй процесс — платёж. Но платёж в нашем кейсе тесно привязан к аренде (1:0..1), и сумму платежа удобнее анализировать в контексте аренды. Поэтому начнём с одной fact-таблицы вокруг аренды; платёж войдёт в неё как measure. Это сознательное решение, и в уроке 5 мы обсудим, когда его стоило бы пересмотреть.

Шаг 2: объявляем grain

Grain (зерно) — что представляет одна строка fact-таблицы. Из модуля про размерное моделирование: объявление grain — самое важное решение, всё остальное зависит от него. Kimball настойчиво рекомендует самый низкий, атомарный grain — он даёт максимум гибкости.

Для проката атомарное событие — одна завершённая аренда. Объявляем grain так:

Одна строка fact-таблицы = одна завершённая аренда велосипеда.

Это предельно конкретная формулировка, и она сразу отвечает на множество вопросов. Не «аренды за день» (это было бы агрегированное зерно), не «строка на станцию» — именно одна строка на одну аренду. Любую агрегацию (по дням, по станциям, по типам) можно потом получить из атомарного grain суммированием; обратно — из агрегата детализацию не вернёшь. Поэтому атомарный grain.

Шаг 3: выбираем dimensions

Dimensions — измерения, в разрезе которых анализируют факт. Приём поиска dimensions: к зафиксированному grain задать вопросы «кто, что, где, когда». Каждый осмысленный ответ — кандидат в dimension.

Применим к нашему grain «одна завершённая аренда»:

• Кто арендовал? -> Клиент -> dim_customer.
• Что арендовал? -> Велосипед (его тип, домашняя станция) -> dim_bike.
• Где началась и закончилась? -> Станция -> dim_station. И это role-playing: одна dimension станции в двух ролях — старта и конца (как было с двумя FK в OLTP).
• Когда произошла? -> Дата -> dim_date. Календарная dimension; ей посвящён следующий урок, пока просто фиксируем её наличие.

Получаем четыре dimensions: customer, bike, station, date. Ключевое отличие от OLTP: dimension-таблицы денормализованы. Если в OLTP тип велосипеда и его домашняя станция были разнесены по таблицам с FK, то в dim_bike все описательные атрибуты велосипеда лежат вместе, плоско:

CREATE TABLE dim_bike (
    bike_key          INTEGER PRIMARY KEY,   -- surrogate key
    bike_id           INTEGER,               -- business key из OLTP
    bike_type         VARCHAR(20),           -- 'regular' / 'electric'
    home_station_name VARCHAR(100),          -- денормализовано: имя станции прямо здесь
    home_station_city VARCHAR(100)
);

Денормализация здесь — не ошибка, а правильный выбор (вспомните модуль про нормализацию): в аналитическом слое плоская dimension означает, что атрибут берётся без лишних JOIN. То, что в OLTP было бы нарушением 3NF, в star schema — норма.

Шаг 4: выбираем facts (measures)

Facts (measures) — числовые величины, которые мы измеряем и агрегируем. Приём: на уровне нашего grain (одна аренда) спросить «что здесь можно посчитать числом».

Для одной аренды:

• rental_amount — сумма платежа за аренду (из связанного Payment; для неоплаченной — 0 или NULL).
• duration_minutes — длительность аренды в минутах (ended_at - started_at).

И сразу классифицируем measures по аддитивности — это важная дисциплина из модуля про fact-таблицы:

• rental_amount — additive: суммируется по любым измерениям (по дням, станциям, типам, клиентам — везде осмысленно). «Выручка за апрель по электровелосипедам» = просто SUM(rental_amount).
• duration_minutes — additive как суммарное время, но для типичного отчёта берут AVG (средняя длительность). Сумма длительностей тоже осмысленна (общее время использования парка), так что величина аддитивна, но аналитику чаще нужно среднее.

Собираем fact-таблицу. Она содержит foreign keys к dimensions + measures — и больше почти ничего:

CREATE TABLE fct_rental (
    -- foreign keys к dimensions:
    customer_key        INTEGER REFERENCES dim_customer(customer_key),
    bike_key            INTEGER REFERENCES dim_bike(bike_key),
    start_station_key   INTEGER REFERENCES dim_station(station_key),
    end_station_key     INTEGER REFERENCES dim_station(station_key),
    date_key            INTEGER REFERENCES dim_date(date_key),
    -- degenerate dimension:
    rental_id           INTEGER,            -- номер аренды из OLTP
    -- measures:
    rental_amount       DECIMAL(8,2),       -- additive
    duration_minutes    INTEGER             -- additive (обычно AVG)
);

Обратите внимание на rental_id — это degenerate dimension (из модуля про fact-таблицы): идентификатор аренды живёт прямо в fact-таблице без отдельной dimension, потому что у самого «номера аренды» нет описательных атрибутов. И два FK на dim_station — role-playing в действии.

Bus matrix: планируем шире одной модели

Последний инструмент — bus matrix из модуля про dimension-таблицы. Это матрица: строки — бизнес-процессы (fact-таблицы), столбцы — dimensions. На пересечении отмечаем, использует ли процесс эту dimension.

У нас сейчас один процесс, но bus matrix полезна и для планирования будущего. Добавим гипотетический второй процесс — «обслуживание велосипеда» (maintenance):

Bus matrix показывает главное: dim_date, dim_bike, dim_station используются обоими процессами. Значит, это conformed dimensions — общие измерения с единым смыслом. Если оба процесса используют одну и ту же dim_bike, аналитик сможет сопоставлять выручку с арендой и затраты на обслуживание в одном разрезе (drill-across). Bus matrix — инструмент, который удерживает enterprise DWH согласованным: dimensions проектируются как переиспользуемые, а не заново под каждый процесс.

Star schema проката готова: один точный grain, четыре dimensions (одна role-playing), fact-таблица с классифицированными measures, degenerate dimension, и понимание, какие dimensions conformed. Теперь аналитический вопрос «выручка за апрель по типам велосипедов» — это SELECT bike_type, SUM(rental_amount) FROM fct_rental JOIN dim_bike ... JOIN dim_date ... WHERE month = 4 GROUP BY bike_type: один уровень JOIN, никакой нормализованной паутины. В следующем уроке добавим в эту модель историчность — SCD2 — и детально построим dim_date.

Попробуй сам

Продолжите кейс онлайн-библиотеки (читатель, книга, автор, жанр, выдача, штраф).

1. Шаг 1. Выберите главный бизнес-процесс для анализа. Что это — «выдача книги»?
2. Шаг 2. Объявите grain одним точным предложением. Одна строка fact = ?
3. Шаг 3. Задайте к grain вопросы «кто, что, где, когда» и выпишите dimensions. Какие атрибуты денормализуете в dim_book?
4. Шаг 4. Найдите measures (длительность выдачи, сумма штрафа). Классифицируйте каждую по аддитивности.
5. Постройте bus matrix с гипотетическим вторым процессом «поступление книги в фонд». Какие dimensions окажутся conformed?