Внедрение DWH, которое помогает структурировать 2 ТБ разрозненных данных и ускорить отчетность в ресторанной сети

Конкуренция на рынке ресторанного бизнеса требует постоянного формирования новых стратегий и высокой скорости принятия решений на основе данных.

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

Эти сложности можно решить, если использовать систему бизнес-аналитики совместно с DWH (Data Warehouse) – корпоративным хранилищем данных.

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

***

В компании возникла необходимость повысить качество сбора и обработки больших объемов (около 100 млн строк в месяц) разноформатных данных из разных подразделений и увеличить скорость формирования отчетности для оценки маржинальности ресторанов сети.

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

Было принято решение внедрить DWH – единый репозиторий структурированных данных, который объединяет информацию из разных источников и способен масштабироваться в соответствии с постоянным развитием бизнеса.

Проектируемое хранилище данных должно было удовлетворять следующим требованиям:

• Гетерогенность

Хранилище должно собирать разноформатные данные из разрозненных учетных систем и структурировать их.

• Гибкость и расширяемость

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

• Историчность

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

• Производительность, скорость обработки

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

• Сохранность больших объемов данных

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

Модель хранилища

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

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

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

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

Архитектура хранилища

Архитектура DWH представляет собой многоуровневую структуру LSA – Layered Scalable Architecture и состоит из слоев:

1. Staging Layer (Слой первичных данных)

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

2. Core Layer (Ядро)

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

Разработанное для сети ресторанов ядро хранилища содержит два подслоя:

• ODS (Operational Data Store) – хранилище оперативных данных, которые используются для построения отчетности в реальном времени, оповещений о проблемах на основе метрик и других операций.
• DDS (Detail Data Store) - хранилище максимально детализированных для источников данных, где форматы данных преобразованы к единому виду, а исходные данные обогащаются данными из системы.

3. CDM (Common Data Marts)

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

4. REP (Represents)

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

5. Сервисный слой (Service Layer)

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

Подробнее о принципах архитектуры DWH

Tech Stack

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

• Для хранения данных выбрана СУБД СliсkHouse. СУБД выдерживает высокие нагрузки на запись и чтение, легко масштабируется, интегрируется с разнообразными источниками и демонстрирует производительность на больших аналитических запросах.
• Оркестрация (управление временем выполнения и условиями старта задач), запуск задач в ручном режиме, базовый уровень логирования и документирования системы, алертинг осуществляются посредством инструмента управления данными Dagster, который изначально проектировался с прицелом на оркестрацию ETL процессов.
• Логику изменения данных обеспечивает DBT (Data Build Tool) - набор консольных утилит, предоставляющих продвинутые функции Lineage данных и запуска SQL скриптов на основе шаблонного кода. Инструмент прост для доработок и содержит встроенные системы тестирования данных и кода
• Для извлечения данных в зависимости от источника использовались нативные средства Python или прямое подключение к базам данных MS SQL и Oracle.
• Управление системой и документирование, описание процессов появления и трансформации данных, аналитика по состоянию данных реализована посредством Data Hub.
• Хранение текущей версии и истории изменения кода для трансформации данных, откат к предыдущим версиям, автоматическое тестирование и автоматическое развертывание новых версий в тестовой и продуктовых средах реализовано с помощью GitLab.

Этапы работ

1. Аудит источников данных

Источниками данных выступили системы учета:

• Кассовая система
• Оператор Фискальных Данных
• Аналитическая система ST Analyze
• Решение для планирования, бюджетирования и прогнозирования Oracle Hyperion (рассматривались только данные для отчета P&L)

Данные имеют разный формат: реляционные SQL базы, NoSQL XML-файлы свободной структуры с данными о продажах, текстовые файлы с разделителем, изображения.

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

Сбор данных был реализован следующим образом:

• ClickHouse собирает данные напрямую из других внешних БД, минуя промежуточные выгрузки, что сокращает расходы на разработку Extract слоя.
• Данные из любых файловых источников собираются напрямую БД (JSON, CSV, Parquet).
• Для данных из RestAPI, GraphQL, SOAP используется промежуточный слой в виде Python.
• Настроено подключение к шине данных (RabbitMQ, Kafka) для наполнения Staging и ODS (Operational Data Store) слоев данных в реальном времени

2. Потребители данных

Были определены все системы, которые должны получать доступ к хранилищу:

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

3. Развертывание Support служб и настройка процесса CI/CD для разработки

• На серверах заказчика развернуты службы оркестрации, логирования и сбора метаданных - настроен Dagster, установлены GitLab и DataHub
• Настроена связка проекта с Git-репозиториями для организации процесса непрерывного развертывания CI/CD (Continuous Integration & Continuous Delivery).

4. Наполнение Слоя первичных данных (Staging Layer)

Наполнение слоя происходило в зависимости от источника данных, который подается на вход в хранилище.

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

• XML-файлы из кассовой системы

Данные кассовой системы извлекаются из XML-источников. Процесс чтения и выгрузки данных, представленных на сетевом диске, реализован на средствах Python + DBT. Сохранение данных по таблицам производится с учетом времени их обновления в источнике данных (обновленные позже данные перезаписываются на место загруженных ранее). Организовано инкрементальное обновление данных на слое. Все данные из XML-файлов хранятся в слое сырых данных.

• Оператор Фискальных Данных (ОФД)

Данные от ОФД реплицируются на сервера заказчика 1к1 с определенной задержкой. Подключение Postgres к СlickHouse осуществлено с использованием нативных JDBC драйверов. Дополнительно проведена настройка, позволяющая загружать только измененные и добавленные данные на основании MD5 хешей записей в таблице базы данных.

• ST Analyze

На этапе выгрузки сырых данных осуществлена пообъектная репликация данных в Staging слой. Использовано ODBC подключение к исходным данным и реализована инкрементальная загрузка данных. В результате сформирована схема данных на слое, где размещена обновляемая копия данных QSR в контуре хранилища данных.

• Oracle Hyperion

Настроена интеграция с данными из Excel - файлов, выгруженными из Oracle Hyperion. Данные используются для построения P&L отчета и обновляются при обновлении выгрузок.

5. Формирование требований к слою витрин данных (Data Marts Layer)

Витрина данных – аналитическая структура, поддерживающая работу одного из приложений, подразделения, бизнес-раздела.

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

В результате этапа:

• Обследованы таблицы QSR (подготовлено описание таблиц в сервисном слое)
• Сформированы правила определения соответствий (mapping) данных из источников
• Сформированы требования к витрине, которые не склонны к двойственности трактования и отвечают на все бизнес-запросы заказчика
• Разрабатываемые витрины обеспечивают быструю и бесшовную миграцию приложений QlikView на новый источник данных
• При миграции соблюдена полнота и достоверность данных
• Артефактом данного этапа является BRD (Business Requirements Document) на витрины, основанные на источнике XML-файлов с данными кассовой системы

6. Разработка ядра хранилища (Core Data Layer)

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

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

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

В результате этапа:

• Сформированы таблицы с детальными данными по отдельным доменам (продажи, ОФД и т. д.)
• Сформированы таблицы со справочниками
• Сформированы mapping таблицы с соответствием значений атрибутов между разными доменами
• Осуществлено проектирование промежуточных витрин данных и начата подготовка конечных пользовательских витрин данных
• Настроена автоматическая актуализация таблиц на основании обновлений Staging слоя

7. Работа со справочниками

В рамках этапа был проведен анализ справочников (рестораны, меню) и подготовка таблиц соответствия.

Справочники из временного внешнего источника данных (Excel-файлы) были загружены в хранилище в исходном виде. На основании направленных заказчиком правил был сформирован справочник Календарь. Настроено обновление Справочников на основании источников данных.

На данном этапе не формировался функционал работы с вводом данных и изменением значений.

8. Формирование сервиcного слоя (Service Layer)

Сервисный слой (Service Layer) обеспечивает мониторинг данных, оперативное устранение ошибок, позволяет выполнять сквозной аудит данных (data lineage), использовать общие подходы к выделению дельты изменений и управления загрузкой.

Сервисный слой реализует следующие функции:

• Управление метаданными

Для управления метаданными и визуализации потока данных (data lineage) использовался DataHub. Это платформа метаданных с открытым исходным кодом для modern data stack, основной задачей которой является помощь сотрудникам в обнаружении нужных данных.

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

• Алертинг

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

1. Не был получен ожидаемый HML-файл от источника
2. Обработка файла заняла больше времени, чем в среднем необходимо
3. Файл имеет очень низкое или очень высокое количество строк
4. Есть проблемы с сетевым подключением
5. Задания (Jobs) не отработали по расписанию
6. Очень высокая нагрузка на систему

• Логирование

Специалистами Qlever был настроен автоматический сбор, хранение и обработка логов в облачном хранилище.

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

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

Были логированы следующие события:

1. Системные логи, связанные с системными событиями
2. Серверные логи, регистрирующие обращения к серверу и возникшие при этом ошибки
3. Логи, фиксирующие запросы к DWH
4. Логи авторизации
5. Логи аутентификации
6. Логи работы Job

• Оркестрация

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

Для оркестрации в хранилище был использован инструмент Dagster. Dagster позволяет определять конвейеры потока данных между повторно используемыми логическими компонентами, тестировать их локально и запускать в облачных сервисах или других распределенных системах. C Dagster возможно строить сложные data pipeline, в том числе по обмену данными между разными приложениями. Унифицированное представление конвейеров и ресурсов, которые они производят, позволяет Dagster работать с Python и SQL.

В Dagster мониторинг и наблюдение за выполнением конвейеров обработки данных реализуется через структурированный журнал событий, который является историческим хранилищем неизменных записей вычислений в этом оркестраторе. Журнал управляет реактивными пользовательскими интерфейсами Dagit, формирует каталог ресурсов, поддерживает форматированные трассировки стека и рендеринг разметки непосредственно при просмотре запуска. Каждый выполненный data pipeline и любое созданное событие записываются в этот неизменяемый лог-журнал, позволяя ему служить системной записью для всей платформы данных.

В результате этапа настроены:

• Процесс управления расписанием задач
• Процесс логирования выполнения задач
• Процесс уведомления в случае ошибок и предупреждений
• Автоматизированное формирование сохранения информации о метаданных (реализовано средствами DataHub)

Миграция источников данных для аналитических приложений

В конце проекта специалистами Qlever была проведена комплексная проверка собранного решения на соответствие требованиям, описанным в техническом задании.

• Осуществлено нагрузочное тестирование
• Проверены корректность и своевременность выполнения Jobs по установленному расписанию
• Произведено функциональное тестирование каждого модуля DWH

Результат тестирования позволил провести миграцию источников данных на витрины для модулей аналитических приложений по функциональным подразделениям: P&L (Profit and loss) для топ-менеджмента, модули Marketing и Sales Ratings для подразделений маркетинга и продаж, SPMH (Sales per man hour) и TCPH (Transactions per man hour), позволяющие анализировать количество чеков и суммы продаж за каждый час работы, и другие.

Пользовательские приложения были скорректированы в части смены источника в скрипте, внесены изменения в листы приложения – визуализации, измерения, меры, формулы при смене наименований полей.

Полученные результаты

Во внедренном корпоративном хранилище сети ресторанов содержится и обрабатывается более 2 ТБ данных с ежемесячным приростом порядка 60 ГБ.

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

Данные разных форматов – XML-файлы из кассовой системы, данные от ОФД, из ST Analyze, Excel-отчеты из Oracle Hyperion, текстовые файлы, изображения – структурированы и обновляются, обогащаются, актуализируются по расписанию. При этом сохранена бесперебойная работа систем-источников.

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

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