Формирование data-mart уровня КХД. Зажигаем звезды

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

Сравнительная схема работы с КХД и без него

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

Ответ прост: адекватно работать с реляционной структурой данных без дополнительных преобразований умеет только Power BI. Все остальные BI-системы (как российские, так и западные), когда речь идет об анализе сложных структур данных, ориентируются на то, что модель будет организована по топологии «Звезда» (и далеко не все поддерживают «Снежинку»).

Теоретически данные с уровня ядра можно загрузить в BI-систему и попытаться преобразовать их в «Звезду» с помощью встроенного ETL-функционала. Но в этом случае работать с этой моделью можно будет только внутри конкретной BI-системы, что не совсем удобно.

Поэтому имеет смысл не только организовать хранение очищенных данных (на базовом уровне), но и создать уровень с витринами/моделями (data mart). Именно этим мы и займемся.

Поток данных

Разница между «Звездой» и «Снежинкой»

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

В случае «Звезды» каждая таблица-луч соединяется с центром через свой первичный ключ.

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

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

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

Как собирается центральная часть «Звезды»

Базовый принцип: ключевые поля и поля показателей из таблиц, которые должны использоваться в модели, объединяются с помощью операции Union. Рассмотрим на примере двух таблиц: «Продажи» и «План продаж».

Шаги показаны не с точки зрения очередности операций, а как основным этапы формирования. На уровне SQL-запроса, это конечно же, делается одним запросом.

Шаг 1 — через Union собираем поля связей и показателей в одну таблицу.

Шаг 2 — создаем поле канонической даты: в нее записываются даты из полей, чьи данные должны анализироваться на одной временной оси.

Шаг 3 — создаем поле «Тип даты», содержащее название поля, из которого должны брались даты для единой временной оси.

Такая структура таблицы называются уровнями, слоями, этажами. Она позволяет создавать показатели с формулами вида:

• Выручка = sum_if(Сумма сделки, Тип даты = "Дата реализации")
• Потенциал открытых сделок = sum_if(Сумма сделки, Тип даты = "Дата открытия")
• План продаж = sum_if(Сумма плана, Тип даты = "Месяц плана")

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

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

Шаг 4 — создаем поле «Сущность», содержащее название таблицы, из которой взяты данные.

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

Теперь к этой таблице через связи или через JOIN присоединяются поля-измерения в справочниках — и модель готова.

Какие бывают варианты «Звезды»

Модели топологии «Звезда» имеют 3 варианта.

Модели топологии «Звезда»

Далеко не всегда уложить через UNION этажи центральной таблицы друг на друга достаточно для корректной работы модели. (Создание универсальных моделей данных для любой BI-системы. Читать подробнее.)

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

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

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

Делать это можно на этапе формирования core-уровня или при построении модели в data-mart.

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

1. Если в таблице 1 («Сделки») есть вторичный ключ («ContactID» в сделках), который является первичным ключом в таблице 2 («Контакты»).
2. То из таблицы 2, в которой это поле является первичным ключом («Контакты») нужно присоединить ключевые поля, отсутствующие в таблице 1 («CompanyID» присоединяются к таблице «Сделки»).
3. Для присоединенных ключей нужно повторить шаги 1 и 2, пока не исчерпается глубина связей для таблицы 1 (количество уровней связи равно n-1, где n — количество таблиц в модели);
4. Повторяем для остальных таблиц модели по мере увеличения глубины связей.

Проверьте себя, насколько вы поняли данный принцип. Ответьте на 2 вопроса:

1. Какой набор ключевых полей для уровня «Сделки» будет в итоговой центральной таблице?
   Ответ

   «ИД Сделок», «ИД Контакта», «ИД компании», «ИД Категории», «ИД Пользователя», «ИД Деятельности», «ИД Классификатора».
2. Из какой таблицы в уровень «Сделки» будет добавлен «ИД Пользователя»?
   Ответ

   Из таблицы «Контакты», т.к. она ближе по уровню вложенности.

Генерация модели данных с помощью Data Monetization Pack

Как вы наверняка поняли, преобразование реляционной структуры в «Звезду» — задача достаточно сложная и трудоемкая. Одной из ключевых сложностей является необходимость следить за целостностью связей и регулярно перепроверять ее при добавлении новых данных в модель. Это особенно важно, когда модель строится на основе десятков таблиц core-уровня произвольной структуры.

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

Для использования компонента откройте Loginom Community Edition и создайте новый пакет в расположении M3\Libs\Data Monetization Pack\Examples.

Перейдите в раздел «Ссылки» и добавьте ссылки на пакеты Clickhouse_Kit, Data_Preparation_Kit.

Добавьте в сценарий компонент «3. Структура хранилища» из Data_Preparation_Kit и активируйте его. Этот компонент показывает на первом выходе структуру прямых связей таблиц core-уровня КХД. А на втором — рекомендованные действия по процессу восстановления связей.

Помните вот это место в схеме данных core-уровня? Чтобы можно было анализировать данные продаж в разрезе всех этих справочников, в них при сборе модели нужно будет присоединить недостающие идентификаторы из таблицы «Клиенты» и «Классификаторы».

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

Добавьте в сценарий компонент «3.1 Список таблиц (мета-справочник)». У него из порта выходит полная таблица метаданных: список полей в хранилище, их описание и различные характеристики.

Это не только увлекательное чтиво, но и инструмент управления автоматизациями на уровне КХД. Из Clickhouse_Kit добавьте компонент «🔑 CH: Создать денорм. звезду».

Этот компонент используется для создания витрины на основе списка полей из метаданных по алгоритму формирования «Звезды», описанному выше.

Вы можете управлять составом витрины, фильтруя перечень входящих полей встроенным функционалом Loginom.

Зайдите в порт переменных, чтобы задать настройки формирования модели. Разберем их.

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

Название ядра — название таблицы в БД, в которой сформируется витрина. К названию также добавится префикс модели из настроек ClickhouseKit.txt.

ORDER BY — последовательность полей, по которым осуществляется сортировка (подробнее разбиралось в занятии №2). Если оставить поле пустым, набор полей будет определен автоматически: в него войдут все измерения витрины в порядке возрастания их гранулярности. На данный момент мы оставим это поле пустым.

Включать в модель только уровни с мерами — добавлять ли записи таблиц без полей показателей (чистые справочники) как этажи в центральную таблицу.

Имена полей в нижнем регистре — имена полей созданной витрины будут в нижнем регистре (важно для некоторых систем).

Алиасы в нижнем регистре — после завершения генерации будут сформированы шаблоны SELECT-запросов для загрузки данных из витрины. В самой витрине поля называются в соответствии с их названиями в Loginom. Один из шаблонов содержит вариант запроса с переименованием полей в псевдонимы на основе меток, например: SELECT «sls_gross_profit» AS «прд_валовая прибыль». Этот параметр определяет, будут ли метки отображаться в нижнем регистре или сохранят свое исходное написание.

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

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

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

С помощью фильтров до и после компонента «CH: Календарь модели» вы можете определить, какие именно поля календаря, для каких именно полей дат будут созданы в модели.

Шаблоны формул календаря лежат в файле: Марафон 3\Data Monetization Pack\Settings\ClickhouseKit_cal.txt. Значение $1 в шаблоне будет заменено на соответствующее поле даты при генерации.

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

Выполните компонент. После выполнения вы получите структуру созданной таблицы, которая возвращается с помощью команды DESCRIBE TABLE. Это подтверждает успешное создание таблицы в базе данных.

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

В выходных переменных содержатся шаблоны запросов для быстрой подстановки в загрузку данных.

Добавьте ссылку на пакет Clickhouse Connections.lgp, который расположен в M3\Libs\Data Monetization Pack\Settings\Clickhouse Kit, чтобы у вас появилось настроенное подключение к Clickhouse.

Настройте импорт из БД, использовав шаблон запроса SELECT AS из генератора витрины.

Результат:

Используйте ваши навыки работы в Loginom, чтобы создать Визуализатор, на котором будет показан план и факт по выручке в разрезе товарных групп. Экспериментируйте с моделью на ваше усмотрение (Визуализатор Куб)!

А что там происходит, внутри генератора моделей?

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

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

Практика:

• Попробуйте использовать разные наборы полей при генерации моделей;
• Попробуйте создать календари для разных полей дат.

Заключение

1. Хранить данные удобнее в виде отдельных таблиц сущностей на уровне core. Но для облегчения анализа этих данных нужно трансформировать их в «Звезду»;
2. Ручная разработка и поддержка моделей вида «Звезда» трудозатратна, но автоматизируется с помощью Data Monetization Pack.

Скачайте и установите Loginom Community Edition, если он еще не установлен.