← вся теориятеория к собесу · DWH и моделирование данных

Звезда и снежинка в DWH

Размерная модель: звезда Кимбалла

Размерное моделирование - язык витрин: факты-события и измерения-справочники вокруг них. Собес проверяет, объявляешь ли ты грань факта до полей и различаешь ли аддитивные меры от полуаддитивных - на этом сыплются даже практики.

Стержень: сначала грань («одна строка = одна позиция чека»), потом всё остальное. Смешал грани - получил задвоенные суммы.

// Формулировки: «звезда против снежинки?», «что такое грань факта?», «какие бывают меры по аддитивности?».

Звезда, грань, аддитивность

Звезда (Кимбалл): центральная факт-таблица событий с внешними ключами плюс денормализованные таблицы измерений с атрибутами. Денормализация здесь осознанная - простые джойны и быстрые агрегаты важнее экономии места.

Грань (grain) объявляют первой: что есть одна строка факта. Все меры и ключи обязаны соответствовать грани; положить в один факт и заказы, и позиции - гарантированное задвоение сумм.

// Меры по аддитивности: аддитивные суммируются по всему (выручка), полуаддитивные - не по времени (остаток склада за месяц ≠ сумма остатков дней), неаддитивные (проценты, коэффициенты) считают из компонентов, а не усредняют.

grain: что есть одна строка факта; объявляется до полей
полуаддитивная мера: суммируется по одним измерениям, не по времени (остаток)

Ключи и conformed dimensions

Измерения идентифицируют суррогатным ключом, а не бизнес-ключом источника: это изолирует DWH (data warehouse) от смены нумерации в CRM (customer relationship management), даёт целочисленные джойны и позволяет держать SCD2-версии (slowly changing dimension type 2) одной сущности под разными ключами.

Conformed dimensions - общие измерения (дата, клиент, товар), разделяемые разными фактами: один справочник клиента на продажи и на обращения. Иначе отчёты между витринами не сходятся, и начинается спор о том, чей клиент «правильный».

// Именно conformed-измерения позволяют drill-across - сопоставлять разные факты через общее измерение, а не джойнить факты напрямую (это ошибка, ведущая к задвоению).

суррогатный ключ: технический ключ измерения вместо бизнес-ключа источника
conformed dimension: общее измерение, разделяемое витринами

Типы фактов

Факты бывают трёх типов под разные вопросы. Транзакционный - одно событие на строку (продажа). Periodic snapshot - срез состояния на регулярную дату (остаток на конец дня). Accumulating snapshot - строка-процесс с датами этапов, обновляемая по мере движения (заказ от корзины до доставки).

Отдельно - factless-факты: строки без мер, фиксирующие сам факт события-связи (посещение, назначение скидки); мера здесь - счётчик существования.

// Снежинка (нормализованные измерения) на практике почти всегда проигрывает звезде: экономия места мизерна, а лишние джойны и потеря понятности дороги. Нормализуют измерение только при реальной необходимости.

accumulating snapshot: факт-процесс с датами этапов, обновляемый по ходу
degenerate dimension: атрибут-идентификатор (номер чека) прямо в факте

Как отвечать: «Что такое грань факта и почему её объявляют первой?»

Грань это ответ на вопрос, что представляет одна строка факт-таблицы: одна позиция чека, один заказ, один остаток на дату. Объявляют её первой, потому что грань задаёт всё остальное: какие меры допустимы (они должны быть на том же уровне детализации) и какие измерения подключаются. Если грань не зафиксировать и смешать в одной таблице разные уровни - например, строки заказа и строки позиций, то при агрегации суммы задвоятся: сумма заказа посчитается столько раз, сколько в нём позиций. Поэтому правило: сначала объявляю грань словами, проверяю, что каждая мера ей соответствует, и только потом добавляю поля. Разные уровни детализации это разные факт-таблицы, а не одна с флагом.

Почему это сильный ответ: определение грани, объяснение, почему смешение граней задваивает суммы, и вывод (разные уровни = разные таблицы) - видно, что человек ловил это на реальных витринах.

На чём валят

−Не объявить грань и класть в один факт заказы и позиции - суммы задваиваются.
−Суммировать полуаддитивные меры по времени: «остаток за месяц» = сумма остатков дней.
−Джойнить факты друг с другом напрямую вместо drill-across через общие измерения.
−Бизнес-ключ источника как ключ измерения - смена нумерации в CRM кладёт DWH.
−Снежинка ради «правильной нормализации» - аналитики тонут в джойнах.

Проверьте себя

Пять вопросов из банка по этой подтеме. Всего их 18, остальные разбираются в тренажёре.

#dimensional_modeling1 / 5
Что такое гранулярность (grain) факт-таблицы и почему её фиксируют первой?
A)Grain — это общий физический размер всей факт-таблицы на диске в гигабайтах
B)Grain — число измерений, подключённых к факт-таблице в схеме
C)Grain — что означает одна строка факта; фиксируют первым от двойного счёта
D)Grain — периодичность, с которой факт-таблица обновляется загрузкой
показать ответ и разбор
+C)Grain — что означает одна строка факта; фиксируют первым от двойного счёта
// разбор: Грануляр — уровень, который описывает одна строка факта: «одна позиция чека», «один платёж». Его определяют раньше метрик и измерений, потому что смешение уровней в одной таблице задваивает суммы и делает агрегаты неверными. Единый грануляр — фундамент корректной модели.
#dimensional_modeling2 / 5
Star vs snowflake: когда нормализация измерений вообще оправдана?
A)Схема snowflake заметно быстрее star для аналитических BI-запросов за счёт меньшего размера нормализованных измерений
B)Star и snowflake — полные синонимы, разницы в модели нет
C)Snowflake хранит факты, а star хранит измерения без фактов
D)Snowflake нормализует измерения: меньше избыточности, но больше джойнов
показать ответ и разбор
+D)Snowflake нормализует измерения: меньше избыточности, но больше джойнов
// разбор: Snowflake разбивает измерения на нормализованные подтаблицы — экономит место и убирает дублирование, но добавляет джойнов и усложняет запросы. Star с денормализованными измерениями обычно быстрее и понятнее для BI. Нормализация оправдана для очень больших или часто меняющихся измерений.
#dimensional_modeling3 / 5
Чем OLTP-система отличается от OLAP?
A)Система OLTP предназначена прежде всего для тяжёлой массовой аналитики, а OLAP — для мелких транзакций
B)OLTP и OLAP — это просто два названия одной и той же базы данных
C)OLTP — частые мелкие транзакции; OLAP — аналитика по многим строкам
D)OLTP работает с текстом, а OLAP — с числами
показать ответ и разбор
+C)OLTP — частые мелкие транзакции; OLAP — аналитика по многим строкам
// разбор: OLTP (online transaction processing) обслуживает частые мелкие операции записи/чтения по отдельным строкам (оформить заказ) — оптимизирован под целостность и скорость транзакций. OLAP (analytical processing) отвечает на аналитические запросы, сканирующие миллионы строк и агрегирующие их. Отсюда разные хранение (строковое vs колоночное) и модели данных.
#dimensional_modeling4 / 5
Что такое conformed dimension (согласованное измерение)?
A)Специальное измерение, которое существует в одной изолированной факт-таблице
B)Временное измерение, живущее на этапе загрузки данных
C)Измерение без суррогатного ключа, целиком на натуральных ключах
D)Общее измерение с единым смыслом, разделяемое многими факт-таблицами
показать ответ и разбор
+D)Общее измерение с единым смыслом, разделяемое многими факт-таблицами
// разбор: Conformed dimension — измерение с единым определением и ключами, переиспользуемое несколькими факт-таблицами (одна «Дата», один «Клиент» на все витрины). Именно оно позволяет корректно сравнивать и объединять метрики из разных фактов: без согласованных измерений «выручка» из одной витрины и «заказы» из другой окажутся несопоставимы.
#dimensional_modeling5 / 5
Чем transaction, periodic snapshot и accumulating snapshot факт-таблицы отличаются?
A)Это три синонимичных названия одной и той же обычной факт-таблицы
B)Различаются физическим форматом хранения файла на диске
C)Transaction — событие; periodic — срез за период; accumulating — строка по вехам процесса
D)Все три допустимы в схеме snowflake, но не в star
показать ответ и разбор
+C)Transaction — событие; periodic — срез за период; accumulating — строка по вехам процесса
// разбор: Transaction-факт хранит по строке на событие (одна продажа). Periodic snapshot — регулярный срез состояния за период (остаток на складе на конец дня). Accumulating snapshot — одна строка на экземпляр процесса, которая обновляется по мере прохождения вех (заказ создан → оплачен → отгружен → доставлен). Выбор типа диктует смысл строки и способ её обновления.

дальше

Теорию прочитали. Навык ставится повторением

В Сеньорчике эта подтема идёт в ежедневных сессиях: движок возвращает её, пока ответы не станут уверенными, и ведёт прогресс отдельно по каждой подтеме. Теория внутри тоже бесплатна, лимит только на количество вопросов в день.

Открыть тренажёр Вся тема: DWH и моделирование данных