Введение

Проектирование хранилищ данных (DWH) — важнейший этап создания аналитических систем, который напрямую влияет на их производительность, гибкость и масштабируемость. Одним из ключевых аспектов этого процесса является моделирование данных. Правильная модель данных не только упрощает загрузку, трансформацию и анализ информации, но и позволяет учесть изменения во времени и поддерживать требования бизнеса.

В этой статье мы разберем основные подходы к моделированию данных: методологии Кимбелла (Kimball), Data Vault 2.0 и Anchor Modeling. Особое внимание уделим типам медленно изменяющихся измерений (Slowly Changing Dimensions, SCD), их роли в построении аналитических систем и выбору подходящих методик для решения различных задач. Понимание этих концепций поможет проектировать надежные и масштабируемые DWH, соответствующие современным требованиям к аналитике.

Модели данных / Моделирование данных

Моделирование данных — это фундаментальный процесс для проектирования и создания хранилищ данных (Data Warehouses), который помогает структурировать данные и обеспечить их эффективное использование. Различают три ключевых уровня моделирования данных: концептуальная, логическая и физическая модели. Каждый из них имеет свои цели, задачи и уровень детализации. Подход заключается в том, чтобы начать с концептуальной модели, где вы определяете сущности в вашей организации сверху вниз и с точки зрения высокого уровня и моделируете их вместе. Обычно для этого используется диаграмма связей сущностей (ERD).

Концептуальная модель данных представляет собой высокоуровневое представление (сверху вниз).

Цель: Понять бизнес-требования и определить основные сущности и связи между ними.

Концептуальная модель — это высокоуровневое представление данных, которое используется для обсуждения с бизнес-экспертами. Она отвечает на вопрос: «Какие данные важны для бизнеса и как они связаны?» На этом этапе определяются ключевые сущности (например, «Клиенты», «Продукты», «Заказы») и их взаимосвязи, но не углубляются в технические детали.

Логическая модель данных обеспечивает более подробное представление взаимосвязей данных.

Цель: Структурировать данные, детализировать сущности и связи, сохраняя независимость от конкретной платформы.

Логическая модель — это более формализованный подход, где описываются атрибуты сущностей, типы данных, связи, а также первичные и внешние ключи. Здесь добавляется детализация, необходимая для понимания структуры данных, но модель остается независимой от конкретной базы данных или инструмента.

Физическая модель данных определяет фактическую реализацию в базе данных или системе хранения данных (снизу вверх).

Цель: Реализовать модель в конкретной системе управления базами данных (СУБД).

Физическая модель описывает, как данные будут реально храниться, включая детали, связанные с производительностью, масштабируемостью и техническими ограничениями. На этом уровне добавляются таблицы, индексы, виды, ограничения, схемы и другие специфические элементы базы данных.

Kimball — некоторые ключевые термины и концепции

Типы ключей

Dimension Surrogate Keys

Таблица измерений проектируется с одной колонкой, которая служит уникальным первичным ключом. Этот первичный ключ не может быть естественным ключом операционной системы, так как для одного и того же естественного ключа может существовать несколько строк измерений при отслеживании изменений во времени. Кроме того, естественные ключи для измерений могут создаваться несколькими источниками данных, и эти ключи могут быть несовместимыми или плохо управляемыми.

Система DWH/BI должна взять на себя контроль над первичными ключами всех измерений. Вместо использования явных естественных ключей или естественных ключей с добавлением дат, рекомендуется создавать анонимные целочисленные первичные ключи для каждого измерения. Эти суррогатные ключи измерений представляют собой простые целые числа, которые назначаются последовательно, начиная с значения 1, всякий раз, когда требуется новый ключ.

Измерение «Дата» является исключением из правила суррогатных ключей; это высокопредсказуемое и стабильное измерение может использовать более осмысленный первичный ключ.

С сайта Microsoft про Суррогатный ключ

В таблице измерения примера есть суррогатный ключ, который называется Salesperson_SK.

Суррогатный ключ — это уникальный идентификатор с одним столбцом, созданный и хранящийся в таблице измерений. Это столбец первичного ключа, используемый для связи с другими таблицами в модели измерения.

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

• Консолидация нескольких источников данных (избегая столкновения повторяющихся идентификаторов).
• Консолидируйте естественные ключи с несколькими столбцами в более эффективный одноколоночный ключ.
• Отслеживайте журнал измерений с медленно изменяющимся типом 2.
• Ограничить ширину таблицы фактов для оптимизации хранилища (выбрав наименьший возможный тип данных целочисленного числа).

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

Natural, Durable, and Supernatural Keys

Естественные ключи, создаваемые операционными системами-источниками, подчиняются бизнес-правилам, которые находятся вне контроля системы DWH/BI.

Например, номер сотрудника (естественный ключ) может быть изменен, если сотрудник уволится, а затем будет вновь принят на работу. Если хранилище данных должно иметь единый ключ для этого сотрудника, необходимо создать новый устойчивый ключ, который будет постоянным и не изменится в подобной ситуации. Такой ключ иногда называют устойчивым сверхъестественным ключом (durable supernatural key).

Лучшие устойчивые ключи имеют формат, независимый от исходного бизнес-процесса, и поэтому должны представлять собой простые целые числа, назначаемые последовательно, начиная с 1. В то время как сотрудник может иметь несколько суррогатных ключей со временем, отражающих изменения его профиля, устойчивый ключ никогда не меняется.

С сайта Microsoft про Естественный ключ

В таблице измерений примера также есть естественный ключ, который называется EmployeeID.

Естественный ключ — это ключ, хранящийся в исходной системе. Он позволяет сопоставить данные измерения с исходной системой, которая обычно выполняется процессом извлечения, загрузки и преобразования (ETL) для загрузки таблицы измерений. Иногда естественный ключ называется бизнес-ключом, и его значения могут быть значимыми для бизнес-пользователей.

Иногда измерения не имеют естественного ключа. Это может быть так для измерения даты или измерения подстановки или при создании данных измерения путем нормализации неструктурированного файла.

Fact Table Surrogate Keys

Суррогатные ключи используются для реализации первичных ключей практически во всех таблицах измерений. Кроме того, суррогатные ключи фактов в виде одной колонки могут быть полезны, хотя и не являются обязательными. Суррогатные ключи таблицы фактов, которые не связаны с какими-либо измерениями, назначаются последовательно в процессе загрузки ETL и используются для следующих целей:

1. В качестве одноколонного первичного ключа таблицы фактов.
2. Для служения мгновенным идентификатором строки таблицы фактов без необходимости навигации через несколько измерений, что упрощает работу ETL.
3. Для обеспечения возможности отката или продолжения прерванного процесса загрузки.
4. Для того чтобы операции обновления таблицы фактов могли быть разделены на менее рискованные операции вставки (insert) и удаления (delete).

Типы измерений (Dimensional Data Modeling)

Хорошая статья: Моделирование измерений в хранилище Microsoft Fabric: таблицы измерений

Conformed Dimension (Согласованное измерение)

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

Outrigger Dimension (Вспомогательное измерение)

Вспомогательные измерения – это таблицы измерений, которые связаны с несколькими другими измерениями в модели данных. Обычно такие таблицы включаются в иерархию, хотя они напрямую не связаны с таблицей фактов.

Shrunken Dimension (Урезанное измерение)

Урезанное измерение – это подмножество более крупного измерения в модели данных. Оно содержит только часть атрибутов основного измерения и используется в специфическом контексте. Например, урезанное измерение может представлять продукт, включающий только атрибуты, относящиеся к конкретному магазину.

Role-Playing Dimension (Ролевое измерение)

Ролевое измерение может использоваться по-разному в одной и той же таблице или других таблицах модели данных. Это фактически одно и то же измерение, но представленное с разных точек зрения. Примером может быть измерение «Дата», которое одновременно используется как «Дата заказа» и «Дата отгрузки» в таблице фактов о продажах.

Dimension to Dimension Table (Таблица связи измерений)

Это таблица, представляющая сложные иерархии или отношения в схемах типа «звезда» или «снежинка».
Примером может быть таблица фактов о доходах, которая отражает связь между несколькими измерениями, такими как продукт, локация и время.

Junk Dimension (Сборное или «мусорное» измерение)

Сборное измерение объединяет данные с низкой кардинальностью (атрибуты, которые редко изменяются или имеют ограниченное множество значений) в одну таблицу измерений. Оно используется для уменьшения количества измерений в таблице фактов и упрощения модели данных.

Degenerate Dimension (Вырожденное измерение)

Вырожденное измерение – это измерение, производное от таблицы фактов, также известное как факт-измерение (Fact Dimension). Оно содержит данные, которые используются для группировки или фильтрации, но не связаны с другими измерениями.

Swappable Dimension (Заменяемое измерение)

Заменяемое измерение – это измерение, которое можно заменить на другое схожее измерение без влияния на структуру или работу хранилища данных. Это позволяет упростить модель данных и повысить производительность.

Step Dimension (Шаговое измерение)

Шаговое измерение представляет процесс или последовательность событий в модели данных. Оно используется для анализа производительности процессов, выявления узких мест или поиска точек для оптимизации. Примером может быть таблица, описывающая этапы производственного процесса.

Slowly Changing Dimensions, SCD Types

Медленно изменяющиеся измерения (Slowly Changing Dimensions, SCD) — это техника обработки изменений в dimensions, которая позволяет сохранять историю изменений или заменять устаревшие значения в зависимости от бизнес-требований. Грамотный выбор типа SCD помогает обеспечить баланс между производительностью, объемом хранилища и аналитическими возможностями.

Типы SCD подразделяются на шесть основных категорий, каждая из которых подходит для определенных сценариев.

SCD Тип 0 (Сохранение оригинального значения)

При использовании типа 0 значение атрибута в измерении никогда не изменяется, поэтому факты всегда группируются по этому оригинальному значению.

Тип 0 подходит для любых атрибутов, обозначенных как «оригинальные», например, исходный кредитный рейтинг клиента или устойчивый идентификатор.

Это также применимо к большинству атрибутов в измерении даты.

SCD Тип 1 (Перезапись значений без сохранения истории)

При использовании типа 1 старое значение атрибута в строке измерения заменяется новым; атрибуты типа 1 всегда отражают последнее значение.
Этот подход уничтожает историю изменений. Хотя он прост в реализации и не создает дополнительных строк в измерении, важно пересчитать агрегированные таблицы фактов и OLAP-кубы, затронутые изменением.

Применяется, когда история не важна, и бизнес всегда работает с актуальными данными.

• Преимущества: простота реализации.
• Недостатки: потеря исторической информации.

Пример: изменение орфографии в имени клиента или корректировка адреса.

Пример:

SCD Тип 2 (Добавление новой строки — Сохранение полной истории изменений)

При изменениях типа 2 в измерении создается новая строка с обновленными значениями атрибутов.
Для этого требуется обобщение первичного ключа измерения, поскольку для каждого члена измерения может существовать несколько строк. При создании новой строки назначается новый суррогатный первичный ключ, который используется как внешний ключ во всех таблицах фактов с момента обновления, пока не произойдет следующее изменение.

Минимально для изменений типа 2 необходимо добавить три дополнительных столбца:

• Дата или временная метка начала действия строки (start_date).
• Дата или временная метка окончания действия строки (end_date).
• Индикатор текущей строки.

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

• Преимущества: позволяет точно отслеживать историю.
• Недостатки: увеличение объема данных.

Пример: изменение уровня лояльности клиента или смена региона проживания.

Пример:

SCD Тип 3 (Добавление нового атрибута)

Изменения типа 3 добавляют новый атрибут в измерение для сохранения старого значения атрибута. Новое значение при этом заменяет основное значение атрибута, как в типе 1. Этот подход иногда называют «альтернативной реальностью». Пользователь может группировать и фильтровать данные фактов по текущему значению или по альтернативному. SCD 3 типа используется сравнительно редко.

Для хранения истории добавляются дополнительные столбцы в таблицу, которые фиксируют предыдущее значение атрибута и дату изменения.

Применяется, когда требуется сохранять только последние изменения.

• Преимущества: компактная модель данных.
• Недостатки: ограниченные возможности для анализа долгосрочной истории.

Пример: текущий и предыдущий менеджер клиента.

Пример:

SCD Тип 4 (Добавление мини-измерения)

Тип 4 используется, когда группа атрибутов измерения часто меняется, и их выносят в мини-измерение.

Такая ситуация иногда называется «быстро изменяющееся монстроизмерение (rapidly changing monster dimension)».

Атрибуты, часто используемые в таблицах измерений с миллионами строк, могут быть кандидатами на мини-измерения, даже если они редко меняются.

Мини-измерение имеет собственный уникальный первичный ключ; первичные ключи базового измерения и мини-измерения фиксируются в соответствующих таблицах фактов.

Изменения в измерениях фиксируются в отдельной исторической таблице. Основная таблица измерений содержит только актуальные данные, а в таблице истории сохраняются предыдущие значения.

Применяется для случаев, когда важно разделить актуальные и исторические данные для упрощения работы с ними.

• Преимущества: уменьшение размера основной таблицы, упрощение запросов для текущих данных.
• Недостатки: усложнение архитектуры из-за необходимости управления дополнительной таблицей.

Пример: история изменения цен на продукты.

Пример:

SCD Тип 5 (Добавление мини-измерения и типа 1)

Техника типа 5 используется для точного сохранения исторических значений атрибутов и обеспечения отчетности фактов в соответствии с текущими значениями атрибутов.

Тип 5 основан на мини-измерении типа 4, но также добавляет текущую ссылку типа 1 на мини-измерение в базовом измерении. Это позволяет получить доступ к текущим атрибутам мини-измерения наряду с другими атрибутами базового измерения без необходимости связываться через таблицу фактов.

При обновлении текущего назначения мини-измерения команда ETL должна перезаписать эту ссылку типа 1.

Пример:

SCD Тип 6 (Гибридный подход — Добавление атрибутов типа 1 в измерение типа 2)

Как и тип 5, тип 6 обеспечивает доступ к историческим и текущим значениям атрибутов измерения.

Тип 6 основан на технике типа 2, но добавляет текущие версии атрибутов типа 1 в строки измерения. Это позволяет фильтровать или группировать строки фактов как по значению атрибута типа 2, действовавшему в момент измерения, так и по текущему значению атрибута. В этом случае атрибут типа 1 систематически перезаписывается во всех строках, связанных с данным устойчивым ключом, при обновлении атрибута.

Этот тип сочетает в себе элементы SCD Type 1, 2 и 3. В основной таблице сохраняется текущее значение, полный исторический контекст через новые строки (как в Type 2), а также ограниченная история через дополнительные столбцы (как в Type 3).

Применяется для случаев, когда требуется универсальность и возможность аналитики на разных уровнях истории.

• Преимущества: максимальная гибкость для бизнес-анализа.
• Недостатки: высокая сложность реализации и управления.

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

Пример:

SCD Тип 7 (Двойные измерения типа 1 и типа 2)

Тип 7 используется для поддержки отчетности «как было» и «как есть».

Таблицу фактов можно связать с измерением, смоделированным одновременно как измерение типа 1 (с только текущими значениями атрибутов) и как измерение типа 2 (с корректным историческим профилем). В таблице фактов размещаются как устойчивый ключ, так и суррогатный первичный ключ измерения.

• Для типа 1 фильтруются только текущие строки, а связь идет через устойчивый ключ.
• Для типа 2 ограничения на текущие строки не накладываются, а связь идет через суррогатный ключ.

Эти два подхода реализуются как отдельные представления для BI-приложений.

Пример:

И еще 1 пример:

Выбор типа SCD в зависимости от задач

• Если важна только актуальность данных — SCD Type 1.
• Для детальной аналитики изменений — SCD Type 2.
• Если нужно хранить только последние изменения — SCD Type 3.
• Для разграничения актуальных и исторических данных — SCD Type 4.
• Для сложных бизнес-задач с множеством аналитических сценариев — SCD Type 6 и SCD Type 7.

YouTube Video

Understand Slowly Changing Dimensions

Data Vault 2.0

Архитектура Data Vault 2.0

Архитектура Data Vault 2.0 решает задачи расширяемости и масштабируемости посредством модификации типичной трехслойной архитектуры хранилища данных.

Основная цель корпоративного хранилища данных (EDW) — это поставка и представление информации, то есть агрегированных, суммарных и консолидированных данных, помещенных в определенный контекст. Чтобы подчеркнуть эту основную цель EDW, используем термин информационная витрина (information mart) вместо витрины данных (data mart), который обычно используется в сообществе BI.

Другие модификации стандартной архитектуры включают:

• Промежуточный слой (staging area), который не хранит историческую информацию и не изменяет данные, за исключением приведения их к ожидаемым типам данных.
• Слой хранилища данных (data warehouse layer), смоделированный в соответствии с техникой моделирования Data Vault.
• Один или несколько слоев информационных витрин, которые зависят от слоя хранилища данных.
• Необязательное хранилище метрик (Metrics Vault), предназначенное для захвата и записи информации о выполнении процессов.
• Необязательное бизнес-хранилище (Business Vault), которое используется для хранения данных, к которым применены бизнес-правила. Во многих случаях бизнес-правила изменяют или трансформируют данные, чтобы сделать их полезной информацией. Это еще один тип информационной витрины.
• Необязательное операционное хранилище (Operational Vault), которое хранит данные, поступающие в хранилище данных из операционных систем.
• Возможности управляемого самообслуживания в BI (managed self-service BI), позволяющие бизнес-пользователям выполнять собственный анализ данных без вовлечения IT, включая возможность обратной записи информации в слой корпоративного хранилища данных.

Все необязательные слои хранилища — Metrics Vault, Business Vault и Operational Vault — являются частью Data Vault и интегрированы в слой хранилища данных. Референсная архитектура в стандарте Data Vault 2.0.

Архитектура Data Vault 2.0 основана на трех слоях:

1. Промежуточный слой (staging area): собирает исходные данные из систем-источников.
2. Слой корпоративного хранилища данных (enterprise data warehouse layer): смоделирован в формате Data Vault 2.0.
3. Слой предоставления информации (information delivery layer): информационные витрины в виде звездных схем или других структур.

Еще один пример:

Архитектура поддерживает как пакетную загрузку данных из источников, так и загрузку в реальном времени через шину корпоративных сервисов (ESB) или любую другую сервисно-ориентированную архитектуру (SOA). Кроме того, в эту архитектуру можно интегрировать неструктурированные базы данных NoSQL. Благодаря платформенной независимости Data Vault 2.0, NoSQL можно использовать на любом уровне хранилища данных, включая промежуточный слой, слой корпоративного хранилища данных и слой предоставления информации.

Таким образом:

• NoSQL база данных может использоваться как промежуточный слой для загрузки данных в реляционное хранилище Data Vault.
• NoSQL также может интегрироваться с Data Vault двусторонним образом через хешированный бизнес-ключ. В таком случае решение становится гибридным, и информационные витрины могут потреблять данные из обеих сред.

Одним из наиболее значительных отличий от типичных архитектур хранилищ данных является то, что большинство бизнес-правил применяются на этапе создания информационных витрин, то есть ближе к конечному пользователю.

В Data Vault различают:

• жесткие бизнес-правила (hard rules);
• и мягкие бизнес-правила (soft rules).

Архитектура Hub-and-Spoke: Архитектура Data Vault 2.0 основана на модели Hub-and-Spoke. Центральными компонентами являются Hubs, Links и Satellites.

Data Vault 2.0 позволяет:

• Масштабировать систему, добавляя новые хабы, линки или спутники без изменения существующей структуры.
• Обеспечить надежное хранение истории и отслеживание изменений.
• Поддерживать параллельную загрузку данных благодаря независимости хабов, линков и спутников.

Основные компоненты Data Vault 2.0

Хабы (Hubs)

Хабы содержат уникальные бизнес-ключи и служат точкой отсчета для связи с остальными данными. Они отвечают на вопрос: «Какие сущности присутствуют в системе?»

Каждый хаб состоит из:

• Уникального бизнес-ключа.
• Технического идентификатора (например, surrogate key).
• Метаданных, например, временной метки загрузки (load timestamp) и источника данных (record source).

Линки (Links)

Линки описывают связи между хабами. Они служат для отображения отношений, таких как «Кто продал товар кому» или «Как связаны сущности».

Каждый линк содержит:

• Уникальные ссылки на хабы (их технические идентификаторы).
• Метаданные (временные метки, источник данных).

Спутники (Satellites)

Спутники хранят данные, которые меняются во времени. Они могут содержать атрибуты, описывающие хабы или линки, и позволяют анализировать историю данных.

Спутники включают:

• Атрибуты бизнес-данных.
• Временные метки (начало и конец действия записи).
• Источник данных.

Пример как это выглядит в формате таблиц:

YouTube подборка

Методология моделирования данных для хранилища Data Vault

Марк Порошин — Data Vault на Greenplum c помощью DBT

Anchor Modeling

Anchor Modeling (Якорная модель данных) — это гибкая методология проектирования хранилищ данных, которая фокусируется на модульности, гибкости и долгосрочной устойчивости данных. Она особенно полезна для хранилищ, которые часто меняются или растут, благодаря разделению данных на атомарные, независимые сущности.

Техника моделирования включает четыре элемента моделирования: anchor (якорь), attribute (аттрибут), tie (связь) и knot (узел), каждый из которых охватывает различные аспекты моделируемой области. Якоря соответствуют сущностям и событиям, атрибуты моделируют свойства якорей, связи — отношения между якорями, а узлы — общие свойства, такие как состояния.

Ключевая идея в том, что изменения данных фиксируются как новые записи, а не перезаписываются старые. Это позволяет легко отслеживать историю изменений и масштабировать хранилище. Такой подход делает модель более гибкой и удобной для работы с большими объемами данных и часто меняющимися структурами.
Таблицы в базе данных в основном будут находиться в шестой нормальной форме (6NF).

Основные компоненты Anchor Modeling

Пример модели (из википедии):

Anchor (якорь)

• Представляет сущность, которая сохраняется во времени (например, Клиент, Продукт, Продажа).
• Каждая таблица Anchor имеет уникальный идентификатор и минимальный набор атрибутов для идентификации.

Attribute (атрибут)

• Характеристика Anchors (например, имя клиента, цена продукта, количество товара в продаже).
• Атрибуты являются историческими, то есть сохраняют версию значений для временной осведомленности.

Tie (связь)

• Ассоциативная таблица, описывающая отношения между Anchors (например, продажа связана с клиентом и продуктом).
• Содержит идентификаторы связанных Anchors и временные метки.

Knots (узлы)

• Хранилище справочных данных, неизменных по своей природе (например, категории продуктов или типы платежей).
• Узлы связаны с Anchors или Ties для нормализации справочной информации.

Posit (позиция)

• Данные, которые фиксируют состояние атрибута, привязываясь ко времени.

Преимущества модели Anchor

• Гибкость: Добавление новых атрибутов, связей и узлов не требует изменений в существующих таблицах.
• Историчность: Все изменения данных фиксируются с помощью временных меток ValidFrom и ValidTo.
• Нормализация: Данные хранятся в атомарном виде, что уменьшает избыточность.
• Масштабируемость: Таблицы компактные, легко масштабируются.

Пример архитектуры хранилища данных для продаж в парадигме Anchor Modeling

Сущности (Anchors):

• Клиент (Customer)
• Продукт (Product)
• Продажа (Sale)

Атрибуты (Attributes):

• Клиент:
  • Имя (Name),
  • Адрес (Address),
  • Телефон (Phone)
• Продукт:
  • Наименование (ProductName),
  • Цена (Price),
  • Категория (Category)
• Продажа:
  • Дата продажи (SaleDate),
  • Количество (Quantity),
  • Сумма (Amount)

Связи (Ties):

• Продажа связана с Клиентом и Продуктом.

Узлы (Knots):

• Типы оплаты (PaymentType): Наличные, Кредитная карта и т.д.
• Категории продуктов (ProductCategory): Еда, Техника, Одежда и т.д.