Моделирование отчета для BI в виде сводной таблицы со сравнением периодов

Известно, что бизнес-пользователи: management, operations, marketing привыкли оперировать красивыми дашбордами и графиками в удобных BI-инструментах.

Предлагаю разобрать реальный практический кейс:
Вы получаете ТЗ в виде прототипа в gSheet с желаемым отчетом.

Ваши действия?

🌐 @data_apps | Навигация по каналу

⚙️ Pivot Table for Period over Period analysis

1. Декомпозиция отчета

Практически, я вижу pivot-таблицу с несколькими уровнями вложенности по строкам и столбцам.
Это относительно легко сделать в Excel (gSheet), но относительно сложно в BI-инструментах.

Уточняю требования и непонятные моменты.

Q: Мне непонятна логика месяцев в столбцах. Они должны последовательно идти по возрастанию? В таблице 3 месяца, один из них за 2022. Какие периоды, на какую глубину и с чем хочется сранивать?

A: Текущий период сравнивается с: аналогичный период прошлого года, предыдущий месяц текущего года

2. Структура данных в СУБД (в моем случае это Clickhouse)

Для понимания, в СУБД (из запросов к которой строятся все отчеты Datalens) всё хранится в виде плоских двумерных таблиц.

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

3. Возможности BI-инструмента (в моем случае это Datalens)

Цель: Хочу нарисовать сводную таблицу и делать Period over Period аналитику: месяц к месяцу, год к году, месяц к тому же месяцу год назад.

И здесь начинаются нюансы работы с конкретными BI.

Пока удалось нарисовать сводную таблицу как на картинке. Есть 3 вопроса:

1. Как в фильтре можно просто задать текущий и прошлый месяц без хардкода?

2. Как задать в фильтре текущий месяц и тот же месяц год назад?

3. Можно ли раскрашивать ячейки (числа) в зависимости от динамики изменения с прошлым периодом (больше - зеленый, меньше - красный)?

🌐 @data_apps | Навигация по каналу

⚡️Online dbt meetup ⚡️

Салют! Всех, кто интересуется dbt и анализом данных приглашаю на митап.

Вас ждут 8 интересных докладов за 2 дня!
Я буду модератором-ведущим в первый день и расскажу об Оркестрации dbt jobs для Dev, Test, Prod без головной боли

→ Подробности о докладах и регистрация на митап

🌐 @data_apps | Навигация по каналу

🚀 Ключевые метрики компании на дашборде - путь от hardcoded cube к live calculated measures

Сейчас финализирую работу по рефакторингу дашборда в Looker

🔸 WBR (Weekly Business Review) - самый главный дашборд компании

— WBR существует уже 3+ года и всё это время активно дорабатывался
— На дашборде 45+ метрик со сравнением динамики и целевых показателей
— 20K+ просмотров, 10+ аналитиков-разработчиков-контрибуторов, 50+ людей использующих дашборд

🔸 Зачем? dbt-модель WBR превратилась в неподдерживаемое полотно кода без намека на гибкость

— Огромный порог входа для того чтобы внести малейшие изменения или добавить график с новой метрикой
— Т.к. код монолит, то любая маленькая ошибка приводит к тому, что не подсчитаны все метрики
— Затратный процесс расчета модели (время + ресурсы)
— Хардкод в плане доступных измерений и фильтров (кроме них ничего нет)

🌐 @data_apps | Навигация по каналу

🔸 Что в результате? Красивый и гибкий подход Dashboard as Code

Значительно улучшает опыт работы с дашбордом как для аналитиков, так и пользователей

— Полностью избавился от слоя предварительной материализации (dbt model) = сверхбыстрый T2M
— Теперь подсчитываем метрики на лету из таблиц детального слоя = корректность отчетов
— Дашборд реализован на 100% в виде LookML кода и версионируется в git = надежность
— Изменения и редактирование элементов выполняется теперь за минуты = поддерживаемость

Моя работа заняла несколько месяцев, и еще кое-что остается.

🔥 Дайте реакций, если хотите подробную статью с деталями реализации, выводами и рекомендациями.

🌐 @data_apps | Навигация по каналу

Слайды моего доклада с [dbt meetup]:

🔸 Оркестрация dbt jobs для Dev, Test, Prod без головной боли

— Сформулируем требования
— Что нужно для запуска dbt jobs?
— Какие бывают Environments
— Критерии выбора решения для запусков
— Обзор решений: devcontainer, dbtCloud, Github Actions, Gitlab CI, Airflow / Prefect / Dagster, Argo Workflows
— Матрица оценок по критериям
— Выводы: что, в каких случаях и почему лучше использовать

Доступны гиперссылки и .gif-анимация.

Задавайте вопросы, если появятся.

#meetup

🌐 @data_apps | Навигация по каналу

😎 Уже более полугода использую Census App для задач reverse ETL

Сегодня получил письмо касательно интеграции синков данных с dbtCloud jobs и уже выполнил все рекомендации.

As part of working with dbt Labs to improve dbt Cloud triggers, we have been migrating to webhook-based integration which requires different dbt Cloud permissions.

Legacy dbt Cloud Triggers will be disabled in two weeks on August 14th, 2023.

To upgrade, the Service Token provided to Census requires at least the developer permission (for Enterprise) or Member permission (for Team) in order to configure new webhooks. You can then provide the new Service Token in your Organization Settings. For more information, see our documentation.

Хочется еще раз резюмировать опыт использования инструмента:

— 15+ sources = источников данных (все популярные движки DWH)
— 130+ destinations = приемников данных = операционных систем
— Моделирование наборов данных: SQL query, dbt models, Looker Looks
— Audience hub = Создание сегментов
— Автоматических маппинг атрибутов - очень удобно при использовании 10+ полей
— Data Sync: расписание, dbtCloud integration (после завершения dbt build), API calls, Sequence (DAGs)
— Если вы передаете до 50 атрибутов, то использование сервиса не будет стоить ничего!

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

#reverse_etl

🌐 @data_apps | Навигация по каналу

🌐 Ранее в блоге я делал несколько постов о задачах класса reverse ETL и инструментах для их решения:

— Have you ever heard of Operational Analytics?
— Операционализация аналитики c инструментами класса reverse ETL – опыт использования Census
— Looker action -> Braze (Customer engagement platform)
— There is a number of successful companies who aim to provide reverse-ETL as a service

#reverse_etl

🌐 @data_apps | Навигация по каналу

❄️ Snowflake - перспективный лидер, и я ищу подводные камни

Сейчас в процессе тестирования СУБД Snowflake на замену Amazon Redshift

Пока выполнено:

— Сделал и загрузил дамп данных: Redshift -> S3 (Parquet) -> Snowflake. Для этого написал макрос.
— Собрал dev container для работы с dbt
— Настроил работу внешних таблиц: EXTERNAL STAGES
— Внёс все изменения по синтаксису, чтобы отработал слой моделей staging.*

Возникают сложности с:

— Регистр колонок и Reserved words в названиях колонок
— Работа с JSON Flattening. Синтаксис отличается от Redshift
— Работа с внешними таблицами (STAGES, EXTERNAL TABLES) устроена чуть по-другому

Потенциальные проблемные места:

— Конечно, Costs. Используются разные VIRTUAL WAREHOUSES для разных типов нагрузок: EL, T, BI, reverse ETL
— Чтение 1К+ файлов из S3 очень медленно. 5 минут чтобы прочесть JSON-файлы с курсами валют на каждые 4 часа (±2 года истории)

Буду держать в курсе.

#database #modeling #snowflake

🌐 @data_apps | Навигация по каналу

❓ Что делать если в СУБД отсутствуют необходимые функции и преобразования?

🔸Использовать User Defined functions или External functions (они же Lambda functions).

Мой кейс - это работа с гео-индексами H3 (не поддерживается в Amazon Redshift):

— Агрегация точек в шестигранники-гексагоны
— Обезличивание данных при сохранении полезности
— Работа с разным масштабом гексагонов (детально - обобщенно)
— Построение тепловых карт (Heatmaps)

🔸Как это делается в двух словах?

— Создаем Lambda функцию - это почти unlimited возможности на любых языках программирования
— Регистрируем функцию в SQL: CREATE EXTERAL FUNCTION lambda_udf AS ...
— Убеждаемся что всё корректно: конфигурация function, аргументы и типы данных, выделяемые ресурсы, таймауты и т.д.
— Тестируем функцию: input - output, вызываем в SQL SELECT lambda_udf(a, b) as result
— Мониторинг: следим за потреблением ресурсов и производительностью

🔸В чем могут быть проблемы?

— +1 точка отказа
— Сообщения об ошибках в СУБД неинформативны
— В логах Lambda сложно что-то понять
— AWS: not enough capacity in your Region
— Дикий debug, когда ошибка возникает на какой-то из 10М строк и неизвестно на какой

🔸Что с этим делать?

Моё мнение такое:

— Использовать те СУБД, которые обладают требуемыми функциональными возможностями
— По возможности избегать Lambda и UDF
— Если возможности нет - писать ПРОСТЫЕ функции: 1 задача, 1 действие, 1 результат

Это одна из причин, по которой я рассматриваю использование Snowflake. Там работа с H3 поддерживается из коробки.

#database #modeling #udf #lambda #h3 #redshift #snowflake

🌐 @data_apps | Навигация по каналу