Fact table: foreign keys + measures

Прошлые уроки модуля показали место fact-таблицы — центр звезды — и процесс, который к ней ведёт. Этот урок разбирает fact-таблицу изнутри: из каких столбцов она состоит, почему именно из таких, как она устроена физически и какие столбцы в неё попадать не должны.

Fact-таблица — рабочая лошадь аналитической модели. Она хранит сами события бизнеса, она самая большая по объёму, и почти каждый аналитический запрос начинается с неё. Понять её анатомию точно — значит понять, как читается и считается аналитика.

Два сорта столбцов

Главная мысль урока проста: в правильной fact-таблице есть столбцы ровно двух сортов — foreign keys и measures. Третьего сорта в норме нет (одно исключение — degenerate dimension, о нём дальше).

• Foreign keys — ссылки на dimension-таблицы. Они отвечают на вопросы «когда, что, кто, где» — то есть несут контекст события.
• Measures — числовые показатели события: «сколько, на какую сумму, как долго». То, что измеряют и суммируют.

Эти два сорта — прямое следствие разделения «измерять и описывать» из первого урока модуля. Контекст события вынесен в dimensions, и fact ссылается на него foreign keys. Числа события живут прямо в fact как measures. Всё, что описывает событие словами, — в dimensions; всё, что измеряет его числами, — в fact.

CREATE TABLE fct_sales (
    -- СОРТ 1: foreign keys — ссылки на dimensions (контекст)
    date_key      INT NOT NULL REFERENCES dim_date(date_key),
    product_key   INT NOT NULL REFERENCES dim_product(product_key),
    customer_key  INT NOT NULL REFERENCES dim_customer(customer_key),
    store_key     INT NOT NULL REFERENCES dim_store(store_key),
    promotion_key INT NOT NULL REFERENCES dim_promotion(promotion_key),

    -- degenerate dimension: номер чека хранится прямо здесь
    receipt_number VARCHAR(20) NOT NULL,

    -- СОРТ 2: measures — числа события
    quantity         INT           NOT NULL,
    unit_price       NUMERIC(10,2) NOT NULL,
    extended_amount  NUMERIC(12,2) NOT NULL,
    discount_amount  NUMERIC(12,2) NOT NULL
);

Foreign keys: сборка foreign keys

Набор foreign keys fact-таблицы выбирается на третьем шаге процесса — это и есть выбранные dimensions. Каждая dimension, которой можно описать событие выбранного grain, даёт один foreign key.

Несколько важных свойств foreign keys в fact-таблице:

Это surrogate keys, а не natural keys. Курс уже разбирал surrogate keys в модуле про ключи. В размерной модели правило строгое: fact ссылается на dimension через surrogate key — узкий бессмысленный integer, а не через natural key вроде артикула товара или email клиента. Причины — те же, что обсуждались: surrogate key узкий и фиксированной ширины, поэтому JOIN по нему быстрее и индекс компактнее; он стабилен и изолирует fact от изменений в источнике; и, как покажет модуль про SCD, surrogate key — это то, что связывает строку fact с конкретной исторической версией dimension.

Они NOT NULL. У каждого события есть контекст по каждому разрезу. У продажи есть дата, товар, магазин — всегда. Поэтому foreign keys в fact-таблице объявляют NOT NULL.

А что, если контекст по какому-то разрезу неизвестен — товар без указанной промоакции, например? Здесь действует важный приём: не NULL, а специальная строка-заглушка в dimension. В dim_promotion заводят строку с ключом, означающим «без промоакции» (часто promotion_key = -1 или 0), и fact ссылается на неё. Так foreign key остаётся NOT NULL, а JOIN не теряет строки (NULL в ключе соединения отбрасывает строку при INNER JOIN). Это стандартный паттерн: неизвестный контекст — это не отсутствие ссылки, а ссылка на dimension-строку «неизвестно».

Measures: числа события

Measures выбираются на четвёртом шаге процесса. Это числовые показатели, измеримые на уровне одной строки grain.

Какими бывают measures fact-таблицы продаж:

• quantity — количество единиц товара.
• unit_price — цена за единицу.
• extended_amount — сумма по позиции (количество умножить на цену).
• discount_amount — сумма скидки.

Главное правило measures, которое уже звучало и которое разовьёт следующий модуль: в fact кладут сырые аддитивные числа, а не предвычисленные средние и проценты. quantity, extended_amount, discount_amount — это слагаемые, их можно корректно суммировать по любым разрезам. А вот средний чек или процент скидки в fact не хранят — их вычисляет запрос из аддитивных слагаемых: SUM(discount_amount) / SUM(extended_amount). Причина: среднее средних не равно общему среднему, отношение отношений не равно общему отношению. Сохранённый процент сложить нельзя — а сохранённую сумму можно. Подробная классификация measures на additive, semi-additive и non-additive — тема следующего модуля; пока держите принцип: в fact — аддитивные слагаемые.

Бывает и fact-таблица вообще без measures — она называется factless fact table и фиксирует сам факт события (студент посетил занятие). Это валидный частный случай, и ему тоже посвящён отдельный урок следующего модуля.

Degenerate dimension: единственное исключение

Выше сказано: в fact-таблице столбцы двух сортов. Есть одно узаконенное исключение — degenerate dimension (вырожденное измерение).

Посмотрите на receipt_number в DDL выше — номер чека. Это идентификатор операции из source-системы. По смыслу он dimension-атрибут: он описывает событие, по нему можно группировать («сколько позиций в одном чеке»). Но у него нет собственных описательных атрибутов — у номера чека нет ни «названия», ни «категории», ни «типа». Заводить ради него отдельную таблицу dim_receipt бессмысленно: эта таблица состояла бы из одного столбца, дублирующего ключ.

Поэтому такой атрибут хранят прямо в fact-таблице, без отдельной dimension-таблицы. Он называется degenerate dimension — «вырожденное», потому что это dimension, выродившаяся до одного только ключа, которому не нужна таблица. Типичные degenerate dimensions: номер чека, номер заказа, номер транзакции, номер накладной. Подробнее — в модуле про fact-таблицы; здесь важно знать, что наличие receipt_number прямо в fact — это не нарушение правила «два сорта столбцов», а его законное расширение.

Чего в fact-таблице быть не должно

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

Описательные текстовые атрибуты. Название товара, имя клиента, название города в самой fact-таблице — ошибка. Это место dimensions. Положив product_name прямо в fct_sales, вы дублируете длинную строку в каждой из миллиардов строк fact — раздувание объёма — и теряете единое место для управления этим атрибутом. Описания — в dimension, fact ссылается ключом.

Предвычисленные средние и проценты. Как разобрано выше: в fact аддитивные слагаемые, отношения считает запрос.

Агрегаты не того grain. «Сумма всего чека» в строке-позиции чека — нарушение grain (прошлый урок): при суммировании задвоится. В fact только числа своего зерна.

Строки разного grain вперемешку. Смешанный grain — критическая ошибка из прошлого урока. Все строки fact-таблицы одного зерна.

Физический портрет: длинная и узкая

Соберём анатомию в физический образ. Fact-таблица состоит из горстки foreign keys (узкие integer) плюс горстки measures (числа) плюс изредка degenerate dimension. Это десяток-другой столбцов — таблица узкая. И одна строка на каждое событие бизнеса — событий миллионы и миллиарды, таблица длинная.

Длинная и узкая — снова идеальный профиль для колоночного хранилища из прошлого модуля. Foreign keys — это узкие integer с большим повторением значений (тот же store_key встречается в миллионах продаж), и dictionary с RLE сжимают их в разы. Measures — числовые столбцы, тоже хорошо сжимаются. Аналитический запрос читает немного столбцов fact с агрегацией — ровно то, в чём column-store силён. Анатомия fact-таблицы (мало столбцов, много строк, всё числовое) и физика колоночного warehouse подогнаны друг к другу: одно объясняет, почему другое работает.

Попробуй сам

Возьмите бизнес-процесс «отгрузка заказа со склада», grain «одна позиция в одной отгрузке». Спроектируйте fact-таблицу fct_shipments.

Сначала выпишите foreign keys: на какие dimensions ссылается отгрузка позиции (дата отгрузки, товар, склад, клиент, способ доставки). Все ли они NOT NULL — и что делать, если способ доставки для какой-то строки неизвестен? Затем выпишите measures: что числового измеримо на уровне одной позиции отгрузки (количество, вес, объём, стоимость доставки). Подумайте, есть ли тут degenerate dimension — например, номер накладной.

Затем намеренно добавьте в таблицу три «неправильных» столбца — описательный текст, предвычисленное среднее и число чужого grain — и для каждого объясните, почему он не должен быть в fact и куда должен переехать. Это упражнение на распознавание самых частых ошибок проектирования fact-таблицы.