Понятия мер и измерений в DWH. Пример модели звезды в PostgreSQL.

В статье, ниже, приведен пример SQL-кода (DDL + PL/pgSQL-функция по data seeding) с построением логической модели звезды в PostgreSQL.

Введение.

Современные хранилища данных, они же КХД (DWH, Data WareHouse), представляют из себя централизованное хранилище, где собираются данные из различных источников, далее они очищаются и структурируются для будущей аналитики, которая проводится в BI-системах (Apache Superset, Tableau, PowerBI и другие).

Основной слой ядра (core layer) может быть основан на базе одной из моделей, как:

• звезда.
• снежинка.
• DataVault.
• якорная модель.

Выбор той или иной логической модели данных КХД зависит от ряда факторов, как:

• необходимость поддержки масштабируемости данных, как и поддержки “слабой связности”, в данном аспекте хороши якорная модель и DataVault за счет элемента примитива данных, который берет на себя это свойство, в случае DataVault - это link, а в случае якорной - tie.
• в размерах центрального КХД и необходимости интеграции центрального КХД (как правило, это MPP РСУБД, такие как Greenplum) с NoSQL-сервисами или DataLake-хранилищами, основанными на HDFS или S3.

Для BI-аналитика важно работать со следующими понятиями:

• с мерами.
• и с измерениями.

Меры и измерения могут быть агрегированы в виде OLAP-куба, грани которого можно логически представить так:

в узлах OLAP-куба располагаются меры

Давайте подробнее разберем понятия “мер и измерений”.

Меры (measures).

Меры - это числовые данные, интерпретируемые как ключевые показатели. Они, как правило, подлежат агрегации: суммированию, среднему или максимальному значению и другим методам агрегации. Примеры:

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

Измерения (dimensions).

Измерения - это атрибуты по которым анализируются меры. Измерения обычно описывают “контекст” для мер, они часто имеют иерархическую структуру, которая позволяет исследовать данные на разных уровнях детализации. Примеры:

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

Модель звезды (star schema).

Модель звезды - это одна из наиболее распространенных структур баз данных в хранилищах данных, она может использоваться как дополнительный слой поверх основного слоя, к примеру в модели DataVault:

• есть основой слой данных, raw data vault, где данные представлены в виде хабов, линков и сателлитов.
• есть business vault слой данных, где могут построить модель звезды поверх основного слоя данных.

Модель звезды может, так же, использоваться как и основной слой данных. Сам выбор логической модели сильно зависит от требований к самому КХД, как: его размер, необходимость поддержки масштабируемости данных, необходимость в отсутствии жесткой связности между бизнес-сущностями.

Для более большим и промышленных моделей чаще всего выбирают якорную модель или DataVault. Звезду или снежинку могут выбрать, если КХД не будет масштабироваться и у него строго-фиксированное количество бизнес-сущностей.

Модель звезды получила свое название из-за того, что ее схема визуально напоминает звезду, где центральная “таблица фактов” связана с несколькими “таблицами измерений”. Давайте посмотрим на ER-диаграмму (entity-relationship):

ERD модели звезды в PostgreSQL, визуализированного при помощи DBeaver

Действительно напоминает визуально звезду. А теперь, давайте разберем что такое таблица фактов и таблицы измерений и попробуем реализовать модель звезды в PostgreSQL:

Таблица фактов (fact table).

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

Таблицы измерений (dimension tables).

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

Пример реализации модели звезды на PostgreSQL.

Рассмотрим простой пример создания схемы звезды в PostgreSQL для анализа продаж. Для начала создадим таблицы измерений:

к сожалению, Дзен перестал поддерживать функционал вставки кода, на момент написания статьи, поэтому приходится вставлять картинку, а снизу ссылку на код в GitHub Gist

И создадим таблицу факта продаж:

Теперь, давайте проинициализируем некоторыми статичными данными таблицы измерений:

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

И сделаем финальные шаги:

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

содержимое таблицы факта продаж "public.fact_sales"

Как далее используется данная таблица продаж аналитиком?

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

Пример подобного SQL-запроса, который агрегирует общие продажи по месяцам:

результат агрегирующего SQL-запроса на основе таблицы факта продаж

Чем отличается модель звезды и модель снежинки?

Основное различие, по-сути, в степени нормализации таблиц измерений:

• в модели звезды, таблицы измерений - денормализованы, т.е. все атрибуты измерений хранятся в одной таблице. Это позволяет упростить и ускорить выполнение аналитических запросов, т.к. требуется меньшее количество соединений (JOINs).
• в модели снежинки, таблицы измерений имеют более высокую степень нормализации. Это означает, что измерения могут быть разбиты на более мелкие таблицы, чтобы избежать аномалии в виде дублирования данных. Каждая таблица измерений связана с таблицей фактов, и измерения могут иметь дополнительные связи с "подизмерениями". Но в свою очередь, это требует большей производительности из-за большего количества JOINs, так и больших трудозатрат на поддержку модели снежинки. При моделях с более высокой степенью нормализации важно, чтобы РСУБД на базе которой строят КХД поддерживала такие методы оптимизации, как JOIN ELIMINATION.

Для более удобной работы с исходным кодом из данной статьи, я привожу ссылку на полный SQL-код, который был использован для построения логической модели звезды в PostgreSQL:

Постскриптум:

• при написании статьи использовалась следующая версия PostgreSQL v16.3
• к сожалению, Дзен перестал поддерживать функционал вставки исходного программного кода с syntax highlighting, на момент написания данной статьи. Про схожие проблемы рассказывают и другие Дзен-авторы: https://dzen.ru/a/XtUGaNtXXh9qY-gb поэтому вместо исходного кода, Вы видите комбинацию из картинки и ссылки на SQL-код в GitHub Gist. Прошу прощения за неудобства!