Четыре шага Кимбалла

Кимбалл предлагает проектировать каждую fact table за четыре шага. Шаги идут строго по порядку. Их часто пропускают — и потом переделывают модель.

Шаг 1. Выбрать бизнес-процесс

Что мы измеряем? Ответ — глагол: «клиент покупает товары».

Как выбрать первый процесс:

• Важен бизнесу. Есть вопросы, на которые нужны ответы. Продажи почти всегда первые.
• Есть данные. Источник доступен, данные надёжные.

Не путайте процесс с отделом. «Маркетинг» — не процесс. «Клиент открыл письмо» — процесс.

Шаг 2. Объявить grain

Grain — что значит одна строка fact table. Запишите это одним предложением, без слов «и/или»:

Одна строка fact_sales — одна позиция в заказе.

Grain — главное решение в модели. От него зависят измерения и факты.

Выбирайте самый детальный (atomic) grain, который есть в источнике. Позиция заказа детальнее заказа. Заказ детальнее дня.

Grain	Можно ответить	Нельзя ответить
Позиция заказа	Выручка по товару, по категории, по заказу, по дню	—
Заказ	Выручка по заказу, по дню, средний чек	Выручка по товару и категории
День × магазин	Выручка по дню и магазину	Средний чек, выручка по товару, по клиенту

Агрегировать детальные данные можно всегда. Получить детали из агрегата — никогда. Пользователи всегда задают неожиданные вопросы.

Ловушка: смешанный grain

В одной fact table все строки должны быть одного grain. Нельзя класть в одну таблицу строки «позиция заказа» и строки «итог за день». Любой sum посчитает деньги дважды.

Ловушка: факты из шапки

У заказа есть шапка (header) и позиции (lines). Некоторые числа живут в шапке. У нас это shipping_cost — стоимость доставки всего заказа.

Если соединить шапку и позиции, доставка повторится в каждой позиции. sum(shipping_cost) даст неверный ответ. Есть три решения:

1. Распределить (allocate) факт шапки по позициям. Например, пропорционально сумме позиции. Тогда grain один, и всё складывается.
2. Отдельная fact table на уровне заказа: fact_orders.
3. Не класть факт шапки в таблицу позиций.

Кимбалл рекомендует первое решение, если бизнес согласен с правилом распределения.

Шаг 3. Найти dimensions

Когда grain объявлен, измерения видно сразу. Задайте вопросы о одной строке: кто, что, когда, где, как?

Для «одна позиция в заказе»:

• Когда? — дата заказа → dim_date
• Кто? — клиент → dim_customer
• Что? — товар → dim_product
• Где? — магазин → dim_store
• Номер заказа → остаётся в факте как degenerate dimension

Если измерение даёт несколько значений на одну строку, оно не подходит этому grain. Или grain выбран неверно.

Шаг 4. Найти facts

Какие числа есть у одной строки? Только те, что соответствуют grain.

• quantity — количество
• gross_amount — сумма до скидки
• discount_amount — скидка
• net_amount — сумма после скидки

Число, которое не подходит grain (как shipping_cost), требует решения из шага 2.

Ключ fact table

У fact table обычно нет своего surrogate key. Строку однозначно определяет набор ключей. Для fact_sales это order_id + line_number из источника. Это и есть проверка grain: такая пара должна быть уникальной.

Практика

Решайте сами, без AI. Сначала напишите запрос, потом сверьтесь с проверкой, и только потом открывайте решение.

Подготовка: база modeling.duckdb открыта.

Задание 1. Проверка grain

Проверьте, что в src.order_items пара (order_id, line_number) уникальна. Это наш grain.

Проверка: 3501 строка и 3501 уникальная пара. Grain подтверждён.

Решение

SELECT count(*)                               AS rows,
       count(DISTINCT (order_id, line_number)) AS unique_lines
FROM src.order_items;

Если числа разные — в данных дубли. Grain нарушен, нужно разбираться с источником до построения модели.

Задание 2. Двойной счёт доставки

Посчитайте общую стоимость доставки (без отменённых заказов) двумя способами:

1. Из src.orders.
2. Из соединения src.orders и src.order_items.

Проверка: правильно — 91 371. После JOIN — 98 553. Лишние 7 182 рубля появились из ниоткуда. В заказах с большим числом позиций ошибка ещё больше.

Решение

SELECT sum(shipping_cost) AS right_total
FROM src.orders
WHERE status <> 'cancelled';

SELECT sum(o.shipping_cost) AS wrong_total
FROM src.orders o
JOIN src.order_items i ON i.order_id = o.order_id
WHERE o.status <> 'cancelled';

Задание 3. Распределение доставки

Распределите shipping_cost по позициям пропорционально сумме позиции (quantity × unit_price − discount_amount). Округлите до копеек. Проверьте, что сумма распределённой доставки совпадает с правильным итогом.

Проверка: сумма — 91 371,0 (или отличается на копейки из-за округления). Если у вас 98 553, вы не разделили на сумму заказа.

Решение

CREATE OR REPLACE TABLE lines_with_shipping AS
SELECT i.order_id, i.line_number,
       i.quantity * i.unit_price - i.discount_amount AS net_amount,
       round(o.shipping_cost
             * (i.quantity * i.unit_price - i.discount_amount)
             / sum(i.quantity * i.unit_price - i.discount_amount) OVER (PARTITION BY i.order_id),
             2) AS shipping_allocated
FROM src.order_items i
JOIN src.orders o ON o.order_id = i.order_id
WHERE o.status <> 'cancelled';

SELECT sum(shipping_allocated) AS total_shipping
FROM lines_with_shipping;

Оконная функция sum(...) OVER (PARTITION BY order_id) даёт сумму всего заказа в каждой строке. Делим сумму позиции на сумму заказа — получаем долю.

Задание 4

Пройдите четыре шага для процесса «остатки на складе» (src.inventory). Без SQL.

Ответ

1. Процесс: магазин хранит товар. Каждый вечер фиксируем остаток.
2. Grain: одна строка — остаток одного товара в одном магазине на конец одного дня.
3. Dimensions: дата, товар, магазин. Клиента нет: остаток не связан с покупателем.
4. Facts: quantity_on_hand. Это semi-additive факт: по дням не складывается.

Это periodic snapshot. Мы построим его в уроке Типы fact tables.

Задание 5

Коллега предлагает grain «один заказ» вместо «одна позиция». Так строк меньше. Назовите два вопроса, на которые такая таблица не ответит.

Ответ

• Выручка по категории или по бренду: в заказе бывают товары разных категорий.
• Топ-10 товаров по количеству.
• Средняя скидка на товар.
• Какие товары покупают вместе.

Таблицу на уровне заказа можно получить из позиций через GROUP BY order_id. Обратно — нельзя.

Итог

• Четыре шага: процесс → grain → dimensions → facts. Именно в таком порядке.
• Grain записывают одним предложением. Берите самый детальный.
• Все строки fact table — одного grain. Факты из шапки распределяют или выносят в отдельную таблицу.

Глубже: глава 3 — четыре шага на примере розницы. Глава 6 — шапка и позиции заказа, распределение. Глава 18 — как проводить workshop по моделированию с бизнесом.

Дальше: Таблицы измерений.