Разработка схемы хранилища данных
Как и другие реляционные базы данных, хранилище данных содержит таблицы, в которых хранятся данные для анализа. Чаще всего эти таблицы имеют схему, оптимизированную для многомерного моделирования, которая поддерживает агрегирование числовых мер (фактов), сопоставленных с событиями, по нескольким измерениям атрибутов связанных сущностей. Например, можно агрегировать меры, связанные с заказом на продажу (сумма, количество товаров и так далее) по таким атрибутам, как дата продажи, клиент, магазин и так далее.
Таблицы в хранилище данных
Для реляционных хранилищ данных типичным сценарием является определение схемы с таблицами двух типов: для измерений и фактов, соответственно.
Таблицы измерений
Таблицы измерений описывают бизнес-сущности, например продукты, люди, места или даты. Таблицы измерений содержат столбцы для атрибутов соответствующей сущности. Например, сущность клиента может содержать имя, фамилию, адрес электронной почты и почтовый адрес (который, в свою очередь, состоит из номера дома, названия улицы, города, почтового индекса и страны или региона). Помимо столбцов для атрибутов таблица измерений содержит ключевой столбец для уникального ключа, который однозначно идентифицирует каждую строку в этой таблице. Более того, таблица измерений часто содержит два ключевых столбца.
- Суррогатный ключ, который используется в пределах хранилища данных и однозначно идентифицирует каждую строку в таблице измерений этого хранилища данных (обычно выражается целым числом с приращением).
- Альтернативный ключ (также именуется естественным или бизнес-ключом), который используется для идентификации определенного экземпляра сущности в исходной системе обработки транзакций, из которой поступила запись конкретной сущности (например, код продукта или идентификатор клиента).
Примечание.
Зачем нужны два ключа? На то есть несколько веских причин.
- Хранилище данных может заполняться данными из нескольких исходных систем, что создает риск дублирования или несовместимости бизнес-ключей.
- Простые числовые ключи обычно эффективнее для использования в запросах с объединением большого числа таблиц, какие часто применяются в хранилищах данных.
- Атрибуты сущностей могут изменяться с течением времени, например, клиент может изменить свой адрес. Если хранилище данных должно поддерживать отчеты за прошлые периоды, вы можете сохранять отдельную запись для каждого экземпляра сущности за разные периоды. Например, это позволяет учитывать заказы на продажу для конкретного клиента в том городе, где он проживал на момент размещения каждого заказа. В этом случае у нескольких записей клиентов будет одинаковый бизнес-ключ, напрямую связанный с этим клиентом, но разные суррогатные ключи для каждого адреса, которые использовались этим клиентом в разное время.
Ниже представлен пример таблицы измерений для клиента.
| CustomerKey | CustomerAltKey | Имя. | Эл. почта | Улица | City | PostalCode | CountryRegion |
|---|---|---|---|---|---|---|---|
| 123 | I-543 | Navin Jones | navin1@contoso.com | 1 Main St. | Seattle | 90000 | Соединенные Штаты |
| 124 | R-589 | Mary Smith | mary2@contoso.com | 234 190th Ave | Buffalo | 50001 | Соединенные Штаты |
| 125 | I-321 | Antoine Dubois | antoine1@contoso.com | 2 Rue Jolie | Париж | 20098 | Франция |
| 126 | I-543 | Navin Jones | navin1@contoso.com | 24 125th Ave. | Нью-Йорк | 50000 | Соединенные Штаты |
| ... | ... | ... | ... | ... | ... | ... | ... |
Примечание.
Обратите внимание, что эта таблица содержит две записи для клиента Navin Jones. Обе эти записи имеют один и тот же альтернативный ключ для идентификации конкретного человека (I-543), но каждая из них имеет собственный суррогатный ключ. Это позволяет определить, что в некоторый момент этот клиент переехал из Сиэтла в Нью-Йорк. Продажи, заказанные клиентом в период проживания в Сиэтле, сопоставляются с ключом 123, а в период после переезда в Нью-Йорк — с ключом 126.
Помимо таблиц измерений, которые представляют бизнес-сущности, хранилище данных часто включает таблицу измерений для времени. Эта таблица позволяет аналитикам данных агрегировать данные по временным интервалам. В зависимости от типа анализируемых данных может использоваться разная степень максимально допустимой детализации (это значение называется зерном) для измерения времени: часы, секунды, миллисекунды, наносекунды и даже меньше.
Ниже представлен пример таблицы измерения с детализацией до даты:
| КлючДаты | DateAltKey | DayOfWeek | ДеньМесяца | Weekday | месяц | MonthName | Квартал | Год |
|---|---|---|---|---|---|---|---|---|
| 19990101 | 01-01-1999 | 6 | 1 | Пятница | 1 | января | 1 | 1999 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20220101 | 01-01-2022 | 7 | 1 | Суббота | 1 | января | 1 | 2022 |
| 20220102 | 02-01-2022 | 1 | 2 | Воскресенье | 1 | января | 1 | 2022 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20301231 | 31-12-2030 | 3 | 31 | Вторник | 12 | декабря | 4 | 2030 |
Интервал времени, охватываемый записями в таблице, должен включать самые ранние и самые поздние точки во времени для всех связанных событий в соответствующей таблице фактов. Обычно в ней есть и записи для каждого промежуточного интервала на выбранному уровне детализации.
Таблицы фактов
В таблицах фактов хранятся сведения о наблюдениях или событиях. Это могут быть заказы на продажу, баланс запасов, обменные курсы или зафиксированная температура. Таблица фактов содержит столбцы для числовых значений, которые можно агрегировать по измерениям. В дополнение к этим числовым столбцам таблица фактов содержит ключевые столбцы со ссылками на уникальные ключи в связанных таблицах измерений.
Ниже приводится пример таблицы фактов со сведениями о заказах на продажу.
| КлючДатыЗаказа | CustomerKey | КлючХранилища | ProductKey | Заказ№ | LineItemNo | Количество | UnitPrice | Налоги | ItemTotal |
|---|---|---|---|---|---|---|---|---|---|
| 20220101 | 123 | 5 | 701 | 1001 | 1 | 2 | 2,50 | 0,50 | 5.50 |
| 20220101 | 123 | 5 | 765 | 1001 | 2 | 1 | 2.00 | 0,20 | 2,20 |
| 20220102 | 125 | 2 | 723 | 1002 | 1 | 1 | 4,99 | 0,49 | 5.48 |
| 20220103 | 126 | 1 | 823 | 1003 | 1 | 1 | 7,99 | 0.80 | 8,79 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
Ключевые столбцы с измерением определяют зерно для таблицы фактов. Например, таблица фактов с заказами на продажу содержит ключи по датам, клиентам, магазинам и продуктам. Заказ может включать несколько продуктов, поэтому зерно подразумевает разные строки позиций для каждого продукта, проданного клиентам в магазинах за каждый день.
Примеры схем хранилища данных
В большинстве транзакционных баз данных, которые используются в бизнес-приложениях, данные нормализуются для снижения дублирования. Но в типичном хранилище данных, наоборот, данные измерений денормализуются, чтобы уменьшить количество соединений для получения данных.
Часто хранилище данных организовано как схеме типа звезда, в которой таблица фактов имеет прямые связи с таблицами измерений, как в следующем примере:
Атрибуты сущности можно использовать для статистической обработки мер в таблицах фактов по нескольким иерархическим уровням, например для получения общего дохода от продаж по странам или регионам, по городам, почтовым индексам и даже отдельным клиентам. Атрибуты для каждого уровня могут храниться в одной таблице измерений. Но если сущность имеет большое количество иерархических уровней атрибутов или некоторые атрибуты могут использоваться совместно для нескольких измерений (например, клиенты и магазины имеют однотипные географические адреса), есть смысл применить к таблицам измерений нормализацию и создать схему типа снежинка, как в следующем примере:
В нашем примере таблица DimProduct нормализована путем создания отдельных таблиц измерений для категорий продуктов и поставщиков, и добавлена таблица DimGeography для представления географических атрибутов и клиентов, и магазинов. Каждая строка в таблице DimProduct содержит значения ключа для соответствующих строк в таблицах DimCategory и DimSupplier, а каждая строка в таблицах DimCustomer и DimStore содержит значение ключа для соответствующей строки в таблице DimGeography.