Привет, Хабр!

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

Наверняка вы сталкивались с ситуацией, когда данные в отчетах Power BI не обновились, а узнать об этом можно только после сообщений в чате. Или когда поиск нужной информации в хранилище данных (DWH) превращается в настоящий квест с запросами типа SELECT * FROM. Инженеры данных и BI-разработчики ежедневно решают не только задачи по интеграции и анализу данных, но и занимаются рутинной работой, такой как:

Поиск нужных объектов в DWH;
Написание шаблонных SQL-запросов;
Ручной запуск обновлений данных и отчетности;
Анализ логов и устранение ошибок в пайплайнах и отчетах.

Чтобы минимизировать эти рутинные процессы, мы разработали два внутренних инструмента:

BI Tools — веб-сервис для удобного поиска данных в DWH, генерации шаблонных SQL-запросов и управления процессами обновления данных;
PBI Usage — набор скриптов для мониторинга активности пользователей в Microsoft Power BI, контроля обновления отчетов и автоматической отправки уведомлений об ошибках в VK Teams.

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

BI Tools: быстрый доступ к DWH без танцев с бубном

Что нас не устраивало?

В основе нашего DWH лежит Anchor Model (якорная модель хранения данных). Это мощная и гибкая архитектура, но без хорошего инструмента разобраться в ней сложно. Чтобы построить нужную витрину, инженерам данных и BI-разработчикам приходилось:

Искать нужные «якоря» и их атрибуты вручную — по памяти или по названию, что не всегда быстро и удобно;
Разбираться в связях между объектами — иногда это превращалось в настоящий детектив;
Ручками писать SQL-запросы для получения промежуточных и итоговых данных;
Запускать пайплайны для обновления данных после внесения изменений или по запросу пользователей.

На это тратилось слишком много времени. Плюс часто возникали вопросы: «Что это за имя таблицы?» и «Где человекочитаемое описание колонок?».

BI-разработчик разбирается в связях объектов

Что мы использовали для решения?

Бэкенд сервиса реализовали на Java, фронтенд — на React. В качестве СУБД используется служебная схема внутри корпоративного DWH на PostgreSQL. Работа с якорной моделью осуществляется с помощью специальных таблиц и скриптов на Python, работающих с служебными таблицами PostgreSQL и извлекаемыми данными из DDL при работе anchor_model.py. Есть интеграция с Apache Airflow для запуска DAG-ов.

Как BI Tools решает проблему?

Мы разработали веб-сервис, который автоматизирует рутинные задачи:

Поиск и просмотр «якорей» в хранилище — сервис сразу показывает, какие атрибуты есть у объекта и как он связан с другими сущностями;
Генерация SQL-запросов — сервис сформирует корректный SQL-запрос для извлечения данных и отобразит результат (первые N строк);
Автоматическое формирование документации — работа модуля anchor_model.py, про который мы рассказывали в предыдущей статье, устроена так, что без обязательного указания описания инженер не может создать объект, а BI Tools собирает и отображает всю эту информацию в удобном виде;
Запуск ансамбля DAG-ов нажатием одной кнопки — если в отчете устарели данные и нет времени ждать обновления по расписанию или нужно проверить отображение после внесения изменений, можно инициировать обновление прямо через интерфейс сервиса.

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

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

Интересный факт:

Часть функционала BI Tools стала возможной только благодаря жестким правилам именования объектов якорной модели и их строгой типизации. Так как это заложено в скрипты, которые создают сущности якорной модели, правила соблюдаются автоматически. Правильные ограничения — это не только дисциплина, но и огромный плюс. 🙂

PBI Usage: контроль отчетов в Microsoft Power BI

Какие проблемы нужно было решить?

Microsoft Power BI — мощный и удобный инструмент для разработки отчетности, но когда дело доходит до мониторинга, не все так гладко. Нам нужно было решить три ключевые задачи:

Анализ активности пользователей. Хотелось понимать, кто и как часто заходит в отчеты, выполняет ли в них какие-либо действия. Это помогло бы:
- Выявлять наиболее востребованные отчеты и уделять им больше внимания;
- Оптимизировать отчетность, своевременно исключая малоиспользуемые или устаревшие отчеты и дашборды.
Контроль обновления отчетов. В группе BI в компании Sminex на сегодняшний день 74 отчета, и контролировать обновление каждого вручную сложно. Часто бывает так, что отчет не обновился, но первыми об этом узнают пользователи. Причины могут быть разные, но мы получаем первую волну негатива, даже если ошибка не наша.
Быстрое реагирование на ошибки. Раньше мы узнавали о сбоях только тогда, когда кто-то жаловался. Нам нужно было научиться фиксировать сбои и оперативно уведомлять ответственных для быстрого исправления.
Анализ точек оптимизации работы отчетов. Хотелось понимать, сколько данных обрабатывает отчет и как эффективно он рисует визуализации, чтобы выявить точки оптимизации.

Как мы решили проблему?

Мы разработали PBI Usage — набор скриптов, который:

Фиксирует посещаемость и активные действия в отчетах, включая метрики DAU, WAU, MAU и средний уровень активности пользователей, что открывает путь для более сложных интегральных метрик работы BI-системы;
Отслеживает обновление отчетов — сразу фиксирует, какие отчеты обновились успешно, а где произошел сбой;
Отправляет алерты в VK Teams — если отчет не обновился, скрипт автоматически формирует сообщение с разметкой, текстом ошибки и тэгом ответственного разработчика.

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

Что мы использовали для решения?

Бэкенд реализован на Python, фронта нет. В качестве СУБД используется служебная схема внутри корпоративного DWH на PostgreSQL.

Информация об обновлении отчетов, посещаемости и активных действиях в отчетах извлекается из логов Power BI. Данные записываются в СУБД, после чего визуализируются в различных дашбордах. Интеграция с VK Teams реализована через API.

При получении события «Обновление отчета завершено с ошибкой» скрипт формирует сообщение с разметкой, извлекает текст ошибки и тегает ответственного разработчика.

Практические примеры

Пример 1.

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

Например, у таски в Jira есть множество атрибутов. В сервисе находим якорь jira_issue, выбираем нужные атрибуты и получаем сформированный запрос, а также выборку топ-N строк из результата этого запроса. Уже с этим запросом начинаем работать и разрабатывать витрину для отчета.

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

Пример 2.

Разработчик должен найти атрибут «Внешний генподрядчик» для витрины. Он заходит в BI Tools, находит нужный якорь и видит все атрибуты с описаниями. Ему сразу становится понятно, что нужен атрибут с названием is_external_gp.

Пример 3.

Мы узнали, что нужное нам поле хранится в базе данных Jira в таблице с непотребным названием AO_60DB71_SPRINT. Нам нужно понять, выполнялась ли поставка данных, содержащихся в этой таблице в DWH. Обычно ответ на этот вопрос мы бы получили в поиске по проекту с кодом наших ETL-процессов. Но можно проще – мы заходим в дашборд и там с помощью фильтров находим нужную нам таблицу. И видим, что оттуда привезено. Радуемся 🙂

Пример 4.

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

Пример 5.

Необходимо сформировать список самых активных пользователей для рассылки опроса CSI. Заходим в отчет PBI_Usage, выбираем нужный период, например, за последние полгода, и скачиваем из диаграммы Количество действий по пользователям файл с данными.

Результаты и планы

Мы больше не тратим время на поиск данных в DWH и написание шаблонных SQL-запросов, не пропускаем сбои в обновлении отчетов и сразу узнаем о проблемах. Документация объектов в DWH теперь формируется автоматически. В будущем мы планируем развивать эти инструменты и предоставлять доступ к ним не только ИТ-специалистам, но и широкому кругу пользователей.

Иногда, вместо того чтобы мириться с рутиной, проще создать свой инструмент. BI Tools и PBI Usage — это как раз такие решения: они не только экономят время, но и повышают качество данных, давая бизнесу достоверный источник данных для принятия управленческих решений.

Бизнес-сериал: формируем BI-систему в строительстве почти в прямом эфире. Часть III