Перевод книги «Building a Scalable Data Warehouse with Data Vault 2.0» подготовлен автором сайта

ГЛАВА 7. Многомерное моделирование (Dimensional Modeling)

Аннотация

Моделирование Data Vault не является заменой Dimensional Modeling, которое является отраслевым стандартом для определения витрины данных (уровня, используемого для предоставления данных конечному пользователю). Поскольку данная книга предназначена для охвата всего процесса построения хранилища данных от начала до конца, она также охватывает и Dimensional Modeling.

Первая часть этой главы сосредоточена на базовых сущностях Dimensional Modeling — фактовых сущностях и размерных сущностях.

• Fact Entities (Сущности фактов) — хранят измерения, метрики или факты бизнес-процесса (иными словами содержат числовые показатели бизнес-операций — факты и ключи к соответствующим таблицам размерностей).
• Dimension Entities (Cущности измерений) — содержат описательные атрибуты для фактов.

Будут рассмотрены различные типы размерностей, такие как медленно изменяющиеся измерения, а также способы выполнения запросов к данным из многомерной модели. Вторая часть этой главы объясняет, как работать с несколькими звёздными схемами, особенно с использованием согласованных измерений. Последняя часть сосредоточена на техниках многомерного проектирования и представляет снежинку и схему «Звезда» как частный случай схемы снежинки.

Ключевые слова

• Dimension modeling (Многомерное моделирование)
• Fact Entities (Фактовые сущности)
• Техники проектирования
• Snowflake Schemas (Схема «Снежинка»)
• Star Schemas (Схема «Звезда»)

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

Однако большинство бизнес-пользователей не знакомы с моделированием Data Vault 2.0. Во многих случаях конечным пользователям сначала требуется надлежащее обучение, чтобы они могли напрямую обращаться к Raw Data Vault или Business Vault, которые моделируются по тем же принципам. Им также необходимо понимать, как соединять многочисленные сущности, чтобы получать ценные и пригодные для использования сырые данные, которые могут быть обработаны в полезную информацию. Поэтому прямой доступ к уровню корпоративного хранилища данных ограничен опытными пользователями, которые хотят использовать собственные запросы и которым нужны сырые данные для этих целей. Большинство же конечных пользователей будут использовать информационные витрины для доступа к подготовленной информации, которую они могут напрямую использовать для выполнения своих задач.

Ещё одна проблема заключается в том, что большинство фронтенд-инструментов, используемых бизнесом для анализа информации, предоставляемой хранилищем данных, не могут напрямую использовать структуры Data Vault. Например, построение OLAP-куба в Microsoft SQL Server Analysis Services (SSAS) лучше всего работает, если источник данных смоделирован в виде звёздной схемы, которая является реляционной версией Dimension модели.

Многомерное моделирование было представлено широкой аудитории в индустрии хранилищ данных Ральфом Кимбаллом в 1997 году. Однако он не был его изобретателем. Термины «размерности» и «факты», которые являются основными конструкциями в многомерном моделировании, восходят к 1960-м годам, когда проводился совместный исследовательский проект между Дартмутским университетом и General Mills. Первая размерная модель (dimension model) была представлена компаниями AC Nielsen и IRI для описания ранних многомерных витрин данных для данных о розничных продажах. Но именно серия книг Ральфа Кимбалла способствовала популяризации размерного/многомерного моделирования в индустрии хранилищ данных, и оно стало стандартом моделирования. В результате многомерное моделирование поддерживается многими фронтенд-инструментами сегодня. Поэтому оно хорошо известно и понятно конечным пользователям и является оптимальным выбором для моделирования информационных витрин, которые служат в качестве фронтенд-слоя.

Введение

Системы хранилищ данных поддерживают бизнес, помогая решать аналитические задачи — то есть, отвечать на вопросы, касающиеся бизнес-процессов. Например, бизнес-пользователи в авиапромышленности могут захотеть проанализировать следующие вопросы:

• Как изменялась степень загрузки на определённых рейсах за последний финансовый год?
• Какова эффективность нашей бонусной программы для часто летающих пассажиров в отношении продаж?
• Кто наши часто летающие пассажиры? Каков их средний объём продаж?

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

По всем этим причинам имеет смысл организовать данные в слое информационных витрин (information mart) таким образом, чтобы бизнес-пользователи могли быстро агрегировать информацию, а не быстро её модифицировать. Именно поэтому размерная модель (dimensional model) проектируется с учётом того, как измеряются данные. Поскольку метрики (measurement) часто основаны на бизнес-процессе, сам бизнес-процесс должен быть центром моделирования. Кроме того, необходим контекст, чтобы придать метрикам смысл, например, чтобы упорядочить метрики по аэропортам, как показано в Таблице 7.1.

Таблица 7.1 Пропорция пассажиров, работников, посетителей и провожающих/встречающих в выбранных аэропортах (с использованием искусственных данных)

В этой таблице представлено одна метрика — число лиц, которые были либо пассажирами, либо провожающими/встречающими, либо работниками, либо посетителями аэропорта, выраженное в виде соотношения между этими четырьмя ролями. Четыре роли — это первое измерение (dimension), а аэропорт — второе. Обе размерности охватывают данные и определяют уровень детализации для подсчёта лиц.

Эти две фундаментальные концепции — метрики (measurements) и контекст (context) — являются основой размерного моделирования.

Схема «Звезда» (Star Schemas)

Когда размерная модель реализована в реляционной базе данных, она называется звёздной схемой. Это объясняется тем, что размерная модель реляционных таблиц при взгляде сверху выглядит как звезда (см. Рисунок 7.1).

Рисунок 7.1 Пример схемы «Звезда» для посещений аэропортов (физическая реализация)

Исходный рисунок (в плохом качестве)

Модель на Рисунке 7.1 представляет модель, лежащую в основе данных Таблицы 7.1. В центре модели находится таблица фактов, окружённая четырьмя таблицами измерений.

Основное отличие схемы «Звезда» на Рисунке 7.1 от нормализованной модели в третьей нормальной форме (3NF), которая часто используется в операционных системах, заключается в том, что размерная модель обычно использует денормализованные таблицы, особенно:

• Таблицы Фактов: таблица в центре этой схемы содержит метрики/меры/показатели бизнеса, которые в случае Рисунка 7.1 представляют собой транзакции полётов. Эти measurements, как правило, являются метриками, которые бизнес желает проанализировать.
• Таблицы Измерений: таблицы размерностей группируют факты по категориям и предоставляют дополнительные описательные атрибуты к этим категориям. Эти категории называются размерностями и помогают конечному пользователю ориентироваться в модели и выбирать только нужное подмножество данных для анализа.

В дополнение к этим основным таблицам, в размерном моделировании часто используются и другие типы таблиц, например, связующие таблицы (bridge tables). В большинстве случаев можно создать размерную модель, которая содержит тот же контент, что и модель в 3NF. Однако, будучи представленной как звёздная схема, она легче воспринимается бизнес-пользователями и оптимизирована для выполнения запросов.

Это связано с денормализацией, которая обычно происходит при преобразовании модели 3NF в размерную модель. Иерархии и справочники предобъединены, что требует от оптимизатора СУБД учитывать меньшее количество объединений и временных таблиц. Также становится проще агрегировать данные, поскольку все измерения уже содержатся в центральной таблице фактов. Некоторые редакции Microsoft SQL Server также поддерживают оптимизацию звёздного объединения (star join optimization), функцию, которая значительно сокращает объём данных, считываемых с диска для ответа на запросы.

Следующие разделы более подробно объясняют основные сущности размерного моделирования и звёздных схем.

Таблицы фактов (Fact Tables)

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

В других случаях таблицы фактов могут содержать отношения между бизнес-объектами, например, уровни запасов на определённые даты. Эти уровни запасов являются формой отношений, потому что они показывают, какой продукт доступен в каком складском расположении в определённый момент времени. Каждое такое отношение представлено в таблице фактов одной записью. На рисунке 7.2 показан пример таблицы фактов, представляющей рейс.

Рисунок 7.2 Таблица фактов для рейсов (физическая модель)

Таблица фактов содержит два разных типа столбцов:

• Во-первых, внешние ключи на таблицы измерений, которые рассматриваются в следующем разделе. Сюда входят все четыре ссылки, которые являются частью составного первичного ключа, а также TailNumberKey, CancelledKey и DivertedKey.
• Во-вторых, значения измерений, сами факты. Многие из них указывают продолжительность задержек, например, DepartureDelay, WeatherDelay или SecurityDelay. Другие показывают продолжительность подпроцессов, например, время руления при въезде и выезде, время в полёте и пройденное расстояние.

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

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

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

Гранулярность таблицы фактов (Grain of a Fact Table)

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

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

Такой подход называется свёрткой (roll-up) и используется для обобщения данных по подмножеству измерений факта (subset of fact dimensions). Однако, хранение таблиц фактов на самом низком уровне гранулярности не всегда осуществимо, если серверная инфраструктура не предоставляет достаточных ресурсов для хранения самого низкого уровня детализации. В этом случае предварительная агрегация данных до более высокого уровня детализации обеспечивает более быстрые ответы бизнес-пользователям при прямом запросе к таблицам фактов. Обратите внимание, что это утверждение уже не актуально при использовании многомерных OLAP-кубов, потому что они агрегируют данные внутри куба прозрачно для конечного пользователя.

Таблицы размерностей/измерений (Dimension Tables)

Измерения/Размерности/Dimensions (а значит, и реляционные таблицы размерностей) предоставляют контекст для фактов. Они очень важны для понимания хранилища данных. Без измерений (dimensions) невозможно было бы понять метрики, предоставляемые таблицей фактов, потому что все метки и прочая описательная информация берутся из таблиц измерений. Как уже объяснялось в предыдущем разделе, они также используются для определения, как факты будут агрегироваться (сворачиваться). Кроме того, они могут использоваться для фильтрации фактов в соответствии с записью dimension или одним из её описательных атрибутов. Также возможно сортировать агрегированные метрики по измерению или одному из её атрибутов. На рисунке 7.3 показан пример таблицы размерности.

Рисунок 7.3 Таблица измерений пассажиров (физическая модель)

В отличие от таблиц фактов, таблица измерения использует значение ключа PassengerKey в качестве первичного ключа таблицы. Это связано с тем, что этот ключ часто используется в таблице фактов, ссылающейся на таблицу измерения. Чтобы сократить объём необходимого хранилища и повысить производительность соединений, имеет смысл поступать именно так и избегать использования натурального ключа. В остальном этот ключ не имеет значения для бизнеса. В дополнение к этому техническому ключу Измерения (Dimensions) часто содержат бизнес-ключи, которые идентифицируют записи в таблице dimension. На рисунке 7.3 это атрибут PassportNumber. Записи часто представляют собой бизнес-объекты и изменения этих объектов в операционной системе. Хотя натуральный ключ для одного и того же бизнес-объекта остаётся неизменным, даже если описательные данные были изменены в операционной системе, ключ изменяется при каждом изменении данных. Этот эффект используется таблицей фактов для ссылки на версию данных, которая была активна в момент, когда произошло бизнес-событие. Хеш-ключ из модели Data Vault 2.0 используется в качестве ключа в многомерной модели. Это упрощает процесс загрузки и помогает виртуализировать информационные витрины.

Несмотря на ключи в таблице, таблица измерения содержит описательные данные, которые описывают dimension entry (сущность измерения). Очень часто таблицы размерностей состоят из множества описательных атрибутов. Их количество может достигать 50 или 100 столбцов. С другой стороны, такие таблицы часто не содержат большого количества строк. Существуют некоторые размерности, такие как размерность Passenger, представленная на рисунке 7.3, которые могут содержать много записей, но большинство измерений довольно малы, содержащие, возможно, не более сотни записей.

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

• Полностью аддитивные меры (Fully additive measures): полностью аддитивны в том смысле, что такие значения, как суммы и количества, могут быть просуммированы до допустимого общего значения.
• Полуаддитивные меры (Semi-additive measures): могут быть просуммированы по некоторым доступным размерностям, но не по всем.
• Неаддитивные меры (Nonadditive measures): не поддаются сложению.

Таким образом, это также зависит от бизнес-потребностей, станет ли атрибут мерой в таблице фактов или описательным атрибутом в одной из её размерностей.

Запросы к схемам «звезда»

Конечные пользователи, знакомые со схемами типа «звезда», часто следуют простой схеме при прямом запросе данных из размерной модели:

• Выбор необходимых фактов: сначала конечный пользователь решает, какие факты следует выбрать, определяя используемые таблицы фактов.
• Выбор необходимых измерений: добавляя размерности к запросу через соединения (join), конечные пользователи добавляют необходимый контекст.
• Ограничение области фактов: факты фильтруются в соответствии со значениями измерений (либо из таблицы фактов по ключам, либо из присоединённых размерностей).
• Агрегация фактов: исходные факты агрегируются с помощью агрегатных функций, таких как SUM() или COUNT().

Поскольку такой подход настолько распространён, многие производители СУБД оптимизировали свои реляционные движки под подобные запросы, включая Microsoft за счёт поддержки оптимизации соединений типа «звезда».

Следующий оператор может быть использован для запроса к таблице фактов на рисунке 7.2:

Этот оператор сначала соединяет таблицу измерений DimAirport с таблицей фактов, используя OriginAirportKey. Обратите внимание, что таблица DimAirport может быть использована дважды:

• сначала для аэропорта отправления,
• затем для аэропорта назначения.

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

Оператор также показывает, как отдельные части SQL-оператора используются в запросах к Dimension модели:

• Оператор SELECT определяет меры и размерности, которые должны быть представлены конечному пользователю. Также указываются любые формулы, включая агрегатные функции, которые должны быть применены к результату.
• Оператор FROM определяет таблицу фактов, к которой выполняется запрос. Во многих случаях это только одна таблица фактов. Однако также возможно выполнение запроса к нескольким таблицам фактов.
• Оператор JOIN присоединяет контекст фактов к итоговому результату. Во многих случаях имеется несколько операторов JOIN, каждый из которых соединяет отдельную таблицу измерения.
• Оператор WHERE ограничивает область фактов, используемых в результирующем наборе. Во многих случаях доступны данные из дополнительных записей размерности (например, регионы продаж), которые не имеют отношения к выполняемой задаче. Поэтому конечные пользователи обычно ограничивают данные интересующими их записями размерностей.
• Оператор GROUP BY определяет, как данные группируются перед агрегацией и представлением конечному пользователю. Агрегация в операторе SELECT основана на этом GROUP BY. Таким образом, оператор GROUP BY используется для изменения гранулярности (grain) результата по сравнению с гранулярностью исходной таблицы фактов.

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

Несколько звёзд (Multiple Stars)

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

Согласованные размерности/измерения (Conformed Dimensions)

Согласованные измерения — это размерности, которые используются несколькими звёздами. Они применяются для сравнения мер из каждой схемы звезда. Повторное использование согласованных размерностей является очень распространённым для «поддержки подлинного анализа бизнес-процессов в разных областях». Это возможно только в том случае, если все схемы звезда, которые должны анализироваться в рамках такого сквозного анализа, используют одну и ту же таблицу измерений с точно такими же значениями ключей (первичными ключами). Использование хеш-ключей из модели Data Vault 2.0 упрощает соблюдение этого требования. Такой анализ называется drill-across и объединяет информацию из нескольких бизнес-процессов или событий. Только тогда становится возможным анализ данных из различных таблиц фактов с помощью согласованного измерения. На рисунке 7.4 приведён пример.

Рисунок 7.4 Две схемы звезда, соединённые согласованными измерениями (физический дизайн)

Исходная картинка:

В этом примере имеются две схемы звезда с их таблицами фактов в центре:
FactConnection слева и FactAirportVisit с рисунка 7.1 — справа. Каждая из них имеет ряд подключённых измерений. Обе используют две общие таблицы согласованных измерений — DimAirport и DimDate, которые показаны в центре схемы. Для поддержки сквозного анализа между FactConnection и FactAirportVisit крайне важно, чтобы обе согласованные размерности имели абсолютно одинаковую структуру и данные.

Чтобы поддерживать такие согласованные размерности, необходимо, чтобы любые изменения в согласованной размерности не нарушали работу одной из размерных звёзд, использующих эту размерность. Это может — и, скорее всего, произойдёт — на последующих спринтах проекта. Всякий раз при изменении структуры или содержания согласованной размерности необходимо пересмотреть влияние на каждую зависимую схему звезда и провести соответствующее тестирование. Поэтому использование согласованных размерностей влечёт за собой влияние изменений, которое может ограничить гибкость команды разработки. Этот избыточный объём сопровождения согласованных размерностей часто приводит к практике, при которой разработчики не изменяют существующие согласованные размерности, чтобы избежать тестирования. Вместо этого они создают новые дополнительные согласованные размерности, чтобы избежать затрат на переинжиниринг, что приводит к несогласованным размерностям. В других случаях они копируют структуру и данные существующих согласованных размерностей в новые схемы звезда и расширяют их новыми функциями, оставляя старую согласованную размерность без изменений (и, следовательно, без необходимости тестирования), снова добавляя несогласованные размерности. Мы называем эту практику «dimension-itis», что приводит к неконтролируемому количеству размерностей, которые вроде бы одинаковые, но… несогласованные.

Обратная практика — это добавление большего количества атрибутов и данных в существующие согласованные размерности, чтобы избежать создания новых. Этот подход применяется, когда разработчики хотят избежать создания новых согласованных размерностей из-за объёма необходимого тестирования существующих фактов и измерений. Конечный результат такого подхода — так называемая «деформированная» размерность (“deformed” dimension): такая размерность не может больше устойчиво изменяться из-за неясных и неконтролируемых изменений в структуре и сложных или запутанных зависимостей, и становится кошмаром с огромными затратами на переинжиниринг при необходимости изменений в будущем. Команды, создающие такие деформированные размерности, как правило, избегают их документирования, что создаёт дополнительные сложности для их преемников.

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

Проектирование измерений (Dimension Design)

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

Медленно изменяющиеся измерения (Slowly Changing Dimensions, SCD)

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

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

Slowly Changing Dimension Type 0 (Медленно изменяющееся измерение Типа 0) сохраняет исходные значения атрибутов измерения. Если в системе-источнике происходит изменение, значение в таблице измерений не изменяется. Хотя этот тип встречается реже, чем следующие три, он может использоваться для хранения исходного кредитного рейтинга клиента или любого другого первоначального значения.

Изменения в системе-источнике перезаписывают данные в Slowly Changing Dimension Type 1 (Медленно изменяющемся измерении Типа 1). Атрибут измерения всегда отражает наиболее актуальное значение. Следовательно, история не отслеживается. Если на этом атрибуте основана агрегация (например, в предложении GROUP BY оператора), результаты этих агрегаций будут меняться в зависимости от нового значения. В таблице 7.2 приведён пример SCD типа 1.

Таблица 7.2 Медленно изменяющаяся размерность типа 1

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

Вместо перезаписи существующих данных, изменения в системе-источнике добавляют новую строку в таблицу Slowly Changing Dimension Type 2 (Медленно изменяющееся измерение Типа 2). Используя такой подход, часто можно корректно отслеживать историю. Поэтому это является преобладающей техникой отслеживания истории в информационных витринах. Пример размерности типа 2 представлен в таблице 7.3.

Таблица 7.3. Медленно изменяющаяся размерность типа 2

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

В этом примере Эми Миллер, с ключом 1b3ba82..., вышла замуж за Петера Хайнца, с ключом 28ab342..., и переехала к нему в Калифорнию. Поэтому её новая запись с ключом 444bbaa... отражает эти изменения. Старая запись остаётся нетронутой, чтобы сохранить историческую информацию. Старые факты продолжают использовать запись с ключом 1b3ba82..., но новые факты ссылаются на запись размерности с ключом 444bbaa....

Изменения в медленно изменяющейся размерности типа 3 не добавляют дополнительных строк, а отслеживают историю в дополнительных столбцах. Для каждого атрибута, подлежащего историзации, добавляется новый столбец для каждого изменения, произошедшего в системе-источнике и подлежащего отображению бизнес-пользователю. Это упрощает анализ изменений со временем. Пример приведён в таблице 7.4.

Таблица 7.4. Медленно изменяющаяся размерность типа 3

Этот пример показывает, как бизнес отслеживает предыдущее состояние пассажира с помощью дополнительного столбца. Когда пассажир переезжает, как в случае с Эми Миллер (теперь Эми Хайнц) в записи с ключом 1b3ba82..., старый адрес перезаписывается, а атрибуты, которые должны быть исторически отслежены, копируются в дополнительные столбцы. В данном случае столбец Previous State обновляется, чтобы отразить предыдущее состояние пассажира.

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

Таблица 7.5. Медленно изменяющаяся размерность типа 3 с несколькими столбцами

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

В дополнение к представленным типам размерностей, в некоторых случаях встречаются и следующие:

• Slowly changing dimension type 4 (Медленно изменяющаяся размерность типа 4): этот тип выносит изменчивые атрибуты в отдельную мини-размерность.
• Slowly changing dimension type 5 (Медленно изменяющаяся размерность типа 5): гибрид типов 4 и 1 (4 + 1 = 5). Позволяет получить доступ к текущим атрибутам мини-размерности вместе с другими из основной размерности без связывания через таблицу фактов.
• Slowly changing dimension type 6 (Медленно изменяющаяся размерность типа 6): этот тип добавляет текущие атрибуты к размерности типа 2.
• Slowly changing dimension type 7 (Медленно изменяющаяся размерность типа 7): этот тип обеспечивает ту же функциональность, что и тип 6, но с использованием двух внешних ключей в таблице фактов: один ссылается на размерность типа 2 с отслеживаемыми атрибутами, а другой — на текущую строку.

Иерархии

Иерархии (Hierarchies) — ещё одна важная концепция в размерном моделировании. Они позволяют производить детализацию данных для более глубокого анализа. Рассмотрим следующий пример:
В Excel PivotTable представлена мировая выручка от пассажиров за несколько лет в виде общих значений (см. рисунок 7.5).

Рисунок 7.5 Выручка от пассажиров по годам и регионам

Столбцы на рисунке 7.5 показывают финансовые годы (FY) из размерности даты, доступные в сводной таблице. Строки отображают географические регионы из размерности географии. Кроме того, присутствуют общие итоги, показывающие совокупное значение для каждого столбца или строки. Например, выручка от пассажиров за финансовый год 2008 в Соединённых Штатах составила $19,471,989.04.

Географическая иерархия состоит из следующих уровней:

• Страна
• Штат/Провинция
• Город
• Почтовый индекс

Возможно выполнение детализации (drill-down) до любого уровня, который доступен в сводной таблице (которая основана на OLAP-кубе), но не глубже. Рисунок 7.6 показывает логическую модель, в которой представлена размерность географии с её иерархией.

Рисунок 7.6 Размерность географии (логическая модель)

Обозначения следуют методологии моделирования ADAPT (Application Design for Analytical Processing Technologies) для размерных баз данных. Эта методология часто используется в индустрии для моделирования и документирования размерных моделей. Первый элемент изображает саму размерность Geography. Размерность включает одну иерархию, также называемую Geography. Остальные элементы представляют собой отдельные уровни иерархии и должны следовать в указанном порядке. Мы будем использовать методологию моделирования ADAPT на протяжении всей книги.

Аналогично иерархии Geography, иерархия Fiscal Calendar состоит из нескольких уровней:

• Финансовый год
• Финансовое полугодие
• Финансовый квартал
• Месяц
• Дата

Эта иерархия очень распространена в системах хранилищ данных и часто используется конечными пользователями. Она использует другую дату начала года, что часто практикуется коммерческими организациями. Например, предприятия в сфере торговли часто начинают финансовый год не с 31 декабря. Это связано с большим числом возвратов после Рождества, совершаемых клиентами, недовольными подарками. Поскольку объём таких возвратов достаточно велик, он оказывает влияние на общую выручку за год. Однако в большинстве случаев возвраты происходят в январе следующего календарного года. Если компания закрывает отчётный год 31 декабря, возвраты будут отнесены к следующему финансовому периоду и исказят финансовые результаты. Поэтому такие компании часто выбирают другой месяц в качестве окончания финансового периода. Один из примеров — Best Buy, розничный продавец электроники в США. Финансовый год этой компании заканчивается 1 февраля. Рисунок 7.7 показывает логическую модель размерности Date.

Рисунок 7.7 Размерность даты (логическая модель)

Обратите внимание, что в размерности доступны две иерархии: Calendar и Fiscal Calendar. Обе имеют свои собственные уровни Год, Полугодие и Квартал, но разделяют уровни Месяц и Дата. Обе иерархии используют все атрибуты размерности, но организованы по-разному. В зависимости от потребностей бизнес-аналитика используется та или иная иерархия. Обе являются корректными; ценность каждой иерархии зависит от контекста бизнес-анализа.

Чтобы более подробно проанализировать информацию на рисунке 7.5, можно нажать знак «плюс» рядом с меткой строки и выполнить детализацию по иерархии географии, как показано на рисунке 7.8.

Рисунок 7.8 Выручка от пассажиров в Соединённых Штатах по годам

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

Рисунок 7.9 Выручка от пассажиров в штате Алабама по годам

На рисунке 7.9 проводится более глубокий анализ выручки от пассажиров для штата Алабама. Показан каждый город в Алабаме, и выручка от пассажиров для каждого города представлена по годам. Столбец даты представляет собой аналогичную иерархию. Также возможно выполнить детализацию этой иерархии.

На рисунке 7.10 показаны только данные за финансовый год 2007, но более детализировано. Обратите внимание, что отображаются первые три месяца финансового года, который начинается в июле. Для каждого месяца, квартала и полугодия в правой части таблицы показаны итоговые значения. Такой подход позволяет бизнес-аналитикам глубоко понять представленную информацию.

Рисунок 7.10 Выручка от пассажиров в Алабаме по годам, в деталях

Иерархия географии реализована физически из таблицы размерности, как показано на рисунке 7.11.

Рисунок 7.11 Размерность географии (физическая модель)

Каждый уровень иерархии основан на одном из физических атрибутов таблицы размерности. Связь между атрибутом и уровнем иерархии задаётся в Microsoft SQL Server Analysis Services. Аналогичные иерархии будут реализованы в этой главе при построении OLAP-куба и добавлении размерности даты как иерархии.

Дизайн «Снежинка» (Snowflake Design)

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

Рассмотрим пример корпоративных групп в авиационной индустрии и отдельных авиаперевозчиков. Каждая авиакомпания входит в состав более крупной группы, которая владеет этим перевозчиком. Например, United Continental Holdings, Inc. владеет как United Airlines, так и Continental Airlines, а также другими, более мелкими перевозчиками.

Если схема звезда должна отразить эту связь, обе размерности — DimGroup и DimCarrier — должны быть напрямую связаны с таблицей фактов, как показано на рисунке 7.12.

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

Этот пример повторяет представленный на рисунке 7.4. Однако он показывает только одну схему звезда. Обратите внимание на две размерности DimGroup и DimCarrier, которые независимо связаны с таблицей фактов. Тем не менее, между этими двумя размерностями существует неявная связь. Эта связь реализована в ETL-задании, которое загружает таблицу. ETL-задание гарантирует, что в таблицу фактов загружаются только допустимые комбинации CarrierKey и GroupKey. Модель не реализует никаких правил, которые препятствовали бы загрузке недопустимых комбинаций. С другой стороны, эта модель проста в использовании для бизнес-пользователей, потому что они могут напрямую соединять всю необходимую контекстную информацию. Именно поэтому схема звезда допускает только напрямую связанные таблицы измерений.

Например, чтобы получить среднюю задержку группы, можно использовать следующий оператор:

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

Схема «снежинка», с другой стороны, допускает косвенные таблицы измерений. Это полезно, если необходимо явно смоделировать отношения между атрибутами измерений. Если бы модель на рисунке 7.12 была реализована как схема «снежинка», она выглядела бы примерно как на рисунке 7.13.

Рисунок 7.13 Схема снежинка с таблицей фактов в центре и косвенно связанными измерениями (физическая модель)

Как видно из диаграммы, термин «снежинка» происходит от внешнего вида модели. Таблицы измерений отходят от центральной таблицы фактов, подобно лучам снежинки. Вместо того чтобы напрямую ссылаться на DimGroup из FactConnection, группа ссылается косвенно через DimCarrier. GroupKey, который идентифицирует группу в таблице фактов, перемещён в DimCarrier. Чтобы вернуть среднюю задержку группы, как в предыдущем запросе, необходимо сначала соединить DimCarrier, а затем DimGroup.

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

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