Работа с календарями в BI — с DAX и без него

Привет, Хабр! При работе с Business Intelligence и дашбордами практически в любой предметной области встречаются даты и календари, поэтому от выбора представления дат и их составных частей (день, месяц, квартал, полугодие, год и т. д.), ключей дат и таблицы с датами зависит производительность всех дашбордов. В этой статье я расскажу о том, как можно оптимизировать работу с датами в Visiology — с использованием DAX и без него. Интересно? Добро пожаловать под кат! 🙂

Сразу хочу сказать, что DAX — это очень удобно, если вы работаете с датами. Ниже по тексту будут примеры, как можно решить ряд популярных аналитических задач в Visiology, также как и в Power BI, при помощи выражений DAX. Но, чтобы съесть вкусную пиццу, ее нужно сразу приготовить (или заказать, например, у Виталика). То есть «правильные» даты нужно сгенерировать.

Если вы еще не работали с такой «тонкой материей», как календарь, то можете задаться вопросом: «А, собственно, почему?». Можно даже найти в реализации DAX от Microsoft специальные функции, генерирующие календарь. Но это скорее еще один пример того, когда дополнительные функции DAX оказываются не слишком полезными. Да, можно сгенерировать единственный столбец с датой. Но данные из источников могут приходить с разными форматами дат (например, YYYYMMDD), кроме того, часто для дашбордов требуются части дат: год YYYY, месяц в формате YYYYMM, полугодия в формате YYYY1 для первого и YYYY2 для второго, и т.д.

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

Статистика по месяцам

Обратимся к сфере продаж. В этой области часто возникает задача работы с датами. Допустим, у нас есть таблица sales со столбцами номер договора order_number, дата договора order_datekey (число в формате YYYYMMDD) и сумма договора amount. Нам требуется дашборд со столбцами: месяц YYYYMM и сумма по заказам для этого месяца. Например, таблица sales может выглядеть вот так:

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

• Преобразовать число 20200101 в дату 1 января 2020 и далее форматировать ее для получения числа 202001, соответствующего январю 2020

• Преобразовать 20200101 в строку, взять первые 6 символов, при необходимости преобразовать строку 202001 в число, либо разделить число 20200101 на 100 и округлить

Но подход с многократными преобразованиями снижает производительность и усложняет запросы. В связи с этим обычной практикой как для Visiology, так и для Power BI, является создание таблицы для календаря вручную . В такой таблице можно хранить и ключ даты (например, формате YYYYMMDD), саму дату в формате даты, и при необходимости все её производные (например, день, месяц, квартал, полугодие) в виде чисел или строк.

Примером таблицы-календаря для этого случая может быть таблица calendar с числовым полем datekey, в котором дата хранится в формате YYYYMMDD, также в поле date хранится обычная дата, а в числовом поле month хранится месяц в формате YYYYMM — все это делает работу с датами удобнее при подготовке дашборда.

Далее можно добавить связи один ко многим между datekey из calendar и order_datekey из sales в Visiology.

Наконец, можно без дополнительных преобразований построить дашборд с полями calendar[month] и суммой по sales[amount]. Дашборд с месяцами calendar[month] и суммой продаж в месяц sales[amount] в Visiology может выглядеть, например, так.

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

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

DEFINE     VAR __DS0Core =         SUMMARIZECOLUMNS (           'calendar'[month],           "amount", SUM ( 'sales'[amount] )         )  EVALUATE TOPN ( 3000, __DS0Core, 'calendar'[month], ASC, [amount], ASC ) ORDER BY     'calendar'[month] ASC,     [amount] ASC

А теперь чуть сложнее… и по периодам

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

Например, в таблице заказов sales содержится номер заказа order_number, дата создания заказа order_date в формате даты и дата доставки заказа delivery_date в формате даты, и нужно посчитать сумму amount заказов в доставке по месяцам.

В таком случае есть смысл использовать календарь для периодов. При этом в sales добавляется новое целое поле periodkey формата YYYYMMYYYYMM, где первая дата YYYYMM соответствует order_date, а вторая часть YYYYMM соответствует delivery_date.

Также создается новый календарь периодов calendar с полями ключ периода periodkey в формате YYYYMMYYYYMM, дата начала периода periodstartdate в формате даты, дата конца периода periodenddate в формате даты, а также месяц периода periodmonth в формате YYYYMM, причем поля periodstartdate и periodenddate необязательны и только для иллюстрации границ периода.

Например, генерация такого календаря в PostgreSQL выглядит так: 

SELECT (to_char(start_date, 'YYYYMM') ||         to_char(end_date, 'YYYYMM'))::bigint AS periodkey,        dt AS periodstart FROM generate_series('2000-01-01'::date,                      '2030-01-01'::date,                      interval '1 month') AS start_date  JOIN generate_series('2000-01-01'::date,                      '2030-01-01'::date,                      interval '1 month') AS end_date  ON start_date <= end_date  JOIN generate_series('2000-01-01'::date,                      '2030-01-01'::date,                      interval '1 month') AS dt  ON start_date <= dt AND end_date >= dt  ORDER BY periodkey, dt;

Новый календарь периодов calendar можно связать с таблицей фактов sales с новым полем periodkey связью один ко многим.

Теперь можно построить дашборд с полями calendar[periodmonth] и суммой по sales[amount] в Visiology.

Благодаря оптимальному формату все это работает весьма быстро: запрос для 10 миллионов записей в таблице фактов sales и 8 миллионами записей в таблице календаря выполняется примерно за 400 миллисекунд.

Соответственно, DAX запрос, сгенерированный в UI Visiology для таблицы этого дашборда, выглядит так же просто, как и в предыдущем примере. Он не содержит дополнительных функций и преобразований, несмотря на то, что вычисления в этот раз были значительно сложнее — производится группировка для каждой строки sales по каждому месяцу между датой начала события sales[order_date] и датой окончания события sales[delivery_date]:

DEFINE     VAR __DS0Core =         SUMMARIZECOLUMNS (             'calendar'[periodmonth],             "amount", SUM ( 'sales'[amount] )         )  EVALUATE TOPN ( 3000, __DS0Core, 'calendar'[periodmonth], ASC, [amount], ASC ) ORDER BY     'calendar'[periodmonth] ASC,     [amount] ASC

Выводы

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

Интересно, что хранение и обработка календаря периодов с миллионами записей не представляет никаких сложностей в Visiology за счет движка ДанКо, который оптимальным образом хранит данные внутри СУБД ClickHouse. Использование календаря для периодов позволяет рассчитать получить «исторические данные» между началом и концом периода с заданной гранулярностью (в нашем примере — месяц), даже если изначально они не были заданы.

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

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

• Также в календарь периодов можно добавлять кастомные названия периодов, коэффициенты, ставки процентов, налоги, курсы валют. Если в календарь периодов добавить коэффициенты, ставки, или результаты работы другой бизнес-логики, тогда не нужно реализовывать эту логику в DAX и, соответственно, выполнять её во время DAX запроса.

С архитектурной точки зрения генерация календаря периодов тоже выглядит очень разумным ходом:

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

• Для генерации календаря можно выбрать любую IT систему. А уже через рассчитанные коэффициенты и другие данные в календаре периодов обеспечивается взаимодействие разнородных систем.

В результате мы можем реализовать без лишних усилий достаточно сложную логику. Например, типичные условия «заманухи» интернет провайдера: «всю осень — бесплатно, дальше скидка 10% на первые полгода, потом обычный тариф», в результате можно вложить непосредственно в дашборд. Ведь эту логику можно зашить в коэффициенты для каждого месяца в рамках каждого periodkey, и при необходимости рассчитать её заранее, вне DAX.

Надеюсь, эти примеры могут быть интересны для построения дашбордов на DAX, желаю успехов в BI 🙂