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

Сегодня я хотел бы поговорить подробнее о языке DAX, который активно применяется продвинутыми аналитиками во всем мире. Но я уверен, что сфера использования этого мощного инструмента охватывает не только суперпрофессионалов. Именно 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` < 1000) 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 (об этом мы расскажем подробнее чуть позже), разделение ролевого доступа («RLS в действии: как решить вопросы доступа в BI за 5 минут») и решение многих других задач.

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

Спасибо за уделенное время, желаю успехов в задачах Business Intelligence! :)