ДАКСуй отсюда: 3 колбасных примера для реальной аналитики

Привет, Хабр! Сегодня я хотел бы поговорить подробнее о мета-языке языке DAX, который активно используется продвинутыми аналитиками во всем мире. Но я уверен, что сфера использования этого мощного инструмента охватывает не только супер-профессионалов. Именно DAX делает аналитику доступнее для бизнес-пользователей, которые могут сделать сложные расчеты без познаний в программировании вообще. В этой статье мы рассмотрим три примера, когда DAX помогает гораздо проще и быстрее решить одну из типовых задач, с которыми сталкиваются аналитики. Использовали ли вы этот способ раньше — не важно! Тех, кто впервые слышит о DAX, я постараюсь порадовать примерами, а опытных пользователей приглашаю к дискуссии.

Меня зовут Антон Кондауров, и я занимаюсь разработкой движка запросов DAX в Visiology. Итак, зачем же нужен этот DAX и с чем его едят? Для ответа на этот вопрос нужно понять, как работают современные BI-платформы. Одни из них обращаются напрямую к СУБД (этот режим также называют direct-query, push-down или live-connect), чтобы получить данные для определенных расчетов. В этом случае не обойтись без написания скриптов на SQL (хотя бы потому, что СУБД редко понимают запросы на других языках). В результате для каждого очередного расчета BI-системе приходится посылать очередной запрос SQL к СУБД и интерпретировать полученный ответ.

Иначе работают BI-платформы, у которых есть собственный аналитический движок. Из российских продуктов — это Visiology. Данные подгружаются в собственное хранилище, оптимизируются и размещаются таким образом, чтобы быстрее давать ответы на запросы пользователей. Тут, конечно, тоже не обойтись без SQL, но он зашит глубоко внутри. Сама механика этого процесса требует отдельной статьи — и об этом можно почитать в публикации нашего архитектора Никиты Ильина “Укрощение ClickHouse: почему ДанКо делает Visiology намного быстрее”

Но вернемся к варианту с live connection. Что же в этом плохого? Да, собственно, ничего! По крайней мере, пока мы не приходим к сотням пользователей, терабайтам данных и стремлению специалистов (обращаю внимание, не только аналитиков!) изучить закономерности, имеющиеся в данных корпорации. В этих случаях при злоупотреблении SQL страдает производительность…а некоторые задачи оказывается достаточно сложно реализовать.

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

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

Пример 1. Кто скупил всю недорогую колбасу?

Представляете, вы приходите в мясной магазин, а там вообще нет никакой колбасы дешевле 1000 рублей за килограмм. Согласитесь, неприятно. Вы идете к менеджеру и говорите: “Как же так, где вся доступная колбаса?”. А он, чтобы разобраться с вопросом, начинает сразу искать, куда она делась. 

Если использовать для этого SQL, то выкладки будут выглядеть примерно следующим образом:

SELECT `product`.`category` AS `Категория`,       `client`.`name`      AS `Имя клиента`,       max(`sales`.`price`) AS `Максимальная цена ниже 1000` FROM (SELECT `sales`.`price`       AS `price`,             `sales`.`productkey`  AS `productkey`,             `sales`.`customerkey` AS `customerkey`      FROM `sales`      WHERE `sales`.`price` < 100) AS `salesBelow1000`         INNER JOIN `product`                    ON `sales`.`productkey` =                       `product`.`dimproduct_productkey`         INNER JOIN `client`                    ON `sales`.`customerkey` =                       `client`.`dimcustomer_customerkey` GROUP BY `product`.`category`,         `client`.`name`;

А если написать этот запрос на языке DAX, логика будет проще, и операторов потребуется меньше:

EVALUATE SUMMARIZECOLUMNS (     Продукты'[Категория],     'Клиенты'[Имя клиента],     "Максимальная цена ниже 1000",         CALCULATE (             MAX ( 'Продажи'[Цена] ),             FILTER ( 'Продажи', 'Продажи'[Цена] < 1000 )         ) )

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

Пример 2. Сколько-сколько мы продали???

Допустим, предыдущий пример вас не убедил. Ну а что, SQL не такой и сложный. Давайте двигаться дальше. Что делать, если руководитель задает вопрос:

— А сколько сервелата мы продали за время работы нашей фирмы?

— В сумме? — спрашиваете вы

— Нет, в разрезе каждого месяца…

Это уже не такая тривиальная задача для обращения к СУБД “в лоб”, и поэтому SQL будет посложнее:

SELECT `filtered_calendar`.`year`  AS `Год`,       `filtered_calendar`.`month` AS `Месяц`,       sum(`sales`.`price`)        AS `Накопленное количество по продажам с начала года до текущей даты` FROM `sales`         INNER JOIN (SELECT `calendar_grid`.`date`  AS `date`,                            `calendar_grid`.`year`  AS `year`,                            `calendar_grid`.`month` AS `month`                     FROM (SELECT `calendar_grid`.`year`                             AS `year`,                                  `calendar_grid`.`month`                            AS `month`,                                  `calendar_grid`.`date`                             AS `date`,                                  `calendar_grid`.`date` >=                                  makeDate(toYear(max(`date_to_aggregate`)), 1, 1) AND                                  `calendar_grid`.`date` <= max(`date_to_aggregate`) AS `condition`                           FROM (SELECT `calendar_1`.`year`    AS `year`,                                        `calendar_1`.`month`   AS `month`,                                        `calendar_2`.`datekey` AS `date`,                                        `calendar_1`.`datekey` AS `date_to_aggregate`                                 FROM `calendar` AS `calendar_1`                                          INNER JOIN `calendar` AS `calendar_2`                                                     ON `calendar_1`.`year` =                                                        `calendar_2`.`year`) AS `calendar_grid`                           GROUP BY `calendar_grid`.`year`,                                    `calendar_grid`.`month`,                                    `calendar_grid`.`date`                              ) AS `calendar_grid`                     WHERE `calendar_grid`.`condition` = true) AS `filtered_calendar`                    ON `sales`.`datekey` =                       `filtered_calendar`.`date` GROUP BY `filtered_calendar`.`year`,         `filtered_calendar`.`month`;

А на DAX все выглядит элегантно и красиво!

EVALUATE SUMMARIZECOLUMNS (     'Даты'[Год],     'Даты'[Месяц],     "Накопленное количество по продажам с начала года до текущей даты",         TOTALYTD ( SUM ( 'Продажи'[Количество] ), 'Даты'[Дата] ) )

Пример 3. Сколько колбасы нужно на Новый год?

Думаю, большинство уже убедились, что написать DAX в этом случае намного проще. Но если все еще нет, то мы движемся дальше в направлении Data Discovery!

…Начальник щелкает языком и спрашивает: “А сколько же, интересно мы продаем колбасы перед Новым годом? Не нужно ли закупиться в этом году основательнее?” Нам ничего не остается, кроме как написать вот такого типа SQL:

SELECT `calculationresult_no_nulls`.`amount` AS `Суммарное количество по продажам в последний рабочий день года`,       `calculationresult_no_nulls`.`year`   AS `Год` FROM (SELECT `calculationresult`.`year`       AS `year`,             `calculationresult`.`sum_amount` AS `amount`      FROM (SELECT `filtered_sales`.`year`        AS `year`,                   sum(`filtered_sales`.`amount`) AS `sum_amount`            FROM (SELECT `grid_to_filter`.`amount` AS `amount`,                         `grid_to_filter`.`year`   AS `year`                  FROM (SELECT `last_day_sales`.`year`           AS `year`,                               `last_day_sales`.`amount_2`       AS `amount`,                               if(`last_day_sales`.`datekey` =                                  max(`datekey_2`), true, false) AS `predicate`                        FROM (SELECT `grid`.`year`     AS `year`,                                     `grid`.`amount`   AS `amount_2`,                                     `grid`.`datekey`  AS `datekey`,                                     `facts`.`datekey` AS `datekey_2`                              FROM `sales` AS `facts`                                       INNER JOIN `dates`                                                  ON `facts`.`datekey` =                                                     `dates`.`datekey`                                       INNER JOIN (SELECT `dates`.`year`    AS `year`,                                                          `facts`.`amount`  AS `amount`,                                                          `facts`.`datekey` AS `datekey`                                                   FROM `sales` AS `facts`                                                            INNER JOIN `dates` AS `dates`                                                                       ON `facts`.`datekey` =                                                                          `dates`.`datekey`) AS `grid`                                                  ON `dates`.`year` =                                                     `grid`.`year`) AS `last_day_sales`                        GROUP BY `last_day_sales`.`year`,                                 `last_day_sales`.`amount_2`,                                 `last_day_sales`.`datekey`) AS `grid_to_filter`                  WHERE `grid_to_filter`.`predicate` = true) AS `filtered_sales`            GROUP BY `filtered_sales`.`year`) AS `calculationresult`      WHERE `calculationresult`.`sum_amount` IS NOT NULL) AS `calculationresult_no_nulls` ORDER BY `year` ASC,         `Суммарное количество по продажам в последний рабочий день года` ASC;

Ну или взять и написать такого типа DAX, чтобы оценить

Суммарное количество продаж, приходящихся на последний рабочий день года :=     CALCULATE (         SUM ( 'Продажи'[Количество] ),         FILTER ( 'Продажи', 'Продажи'[Дата] = MAX ( 'Продажи'[Дата] ) )     )  DEFINE     VAR TotalQuantityLastDay =         SUMMARIZECOLUMNS (             'Даты'[Год],             "Суммарное количество по продажам в последний рабочий день года", 'Продажи'[Суммарное количество по продажам в последний рабочий день года]         )  EVALUATE TOPN (     1000,     TotalQuantityLastDay,     'Даты'[Год], ASC,     [Суммарное количество по продажам в последний рабочий день года], ASC )

В итоге получили дашборд со всеми задачами.

Заключение

Подводя итог этой небольшой статье, я хочу отметить, что DAX намного удобнее для выполнения любых запросов, которые хотя бы чуть-чуть отличаются от прямой визуализации того, что хранится в вашей СУБД. Более того, именно использование DAX делает более простыми процессы загрузки информации в Visiology (об этом мы расскажем подробнее чуть позже), разделение ролевого доступа (“Укрощение ClickHouse: почему ДанКо делает Visiology намного быстрее”) и решение многих других задач.

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