Привет, Хабр!
Сегодня я хотел бы поговорить подробнее о языке 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! :)