Преимущества DAX на примерах

Популярным языком запросов от Microsoft является DAX. В отличие от диалектов SQL, DAX позволяет аналитикам сфокусироваться на решении задач бизнес-аналитики, вместо того, чтобы заниматься рутинными техническими задачами (например, вопросами производительности).

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

В этой статье рассматриваются преимущества DAX на конкретных примерах, таким образом, если Вам интересен Business Intelligence на DAX — добро пожаловать 🙂

Все запросы из статьи доступны на https://dax.do.

Анализ продаж недорогих продуктов в разрезе категорий продуктов и клиентов

Рассмотрим пример на схеме звезда для данных продаж Sales продуктов Product по клиентам Customer. Считаем, что продукты с ценой ниже 100 являются недорогими. Получим данные о продажах недорогих продуктов, сгруппированных по категории продукта ‘Product'[Category], клиенту ‘Customer'[Customer Name], а также рассчитаем максимальную цену ниже 100 для каждой группы.

Конечно, пример может выглядеть несколько синтетическим, тем не менее, близок к реальным задачам. Как же выглядит DAX для решения этой задачи? По аналогии с диалектами SQL, нужно ли вспоминать ключи таблиц и условия для их объединения, перечислять одни поля, которые нужно выбрать, и другие, по которым нужно сгруппировать, использовать оконные функции или подзапросы? Нет, в этом DAX всего 10 строчек (https://dax.do/VIRRo1ssjbUvWT).

EVALUATE SUMMARIZECOLUMNS (     'Product'[Category],     'Customer'[Customer Name],     "Max Price Below 100",         CALCULATE (             MAX ( 'Sales'[Unit Price] ),             FILTER ( 'Sales', 'Sales'[Unit Price] < 100 )         ) )

Схема данных задается в UI дизайнере и не расписывается в каждом запросе. В рамках SUMMARIZECOLUMNS сразу и выбираем поля ‘Product'[Category] и ‘Customer'[Customer Name], и группируем по ним. В CALCULATE ( MAX ( … ) ) считаем максимальные значения для каждой группы ‘Product'[Category] и ‘Customer'[Customer Name], и, наконец, в FILTER ( ‘Sales’, ‘Sales'[Unit Price] < 100 ) выбираем недорогие продукты, дешевле 100.

Накопленное количество по продажам в разрезе месяцев и лет

Рассчитаем накопленное количество по продажам Sales[Quantity] c 1 января каждого года до текущей даты в разрезе месяцев и лет.

Казалось бы, решение должно быть объемным, вспоминая диалекты SQL. Но нет, всего 6 строчек, так как в DAX есть Time Intelligence и соответствующая функция TOTALYTD (https://dax.do/kPL7EwICLCfYsX).

EVALUATE SUMMARIZECOLUMNS (     'Date'[Calendar Year],     'Date'[Calendar Year Month],     "Sales Quantity YTD", TOTALYTD ( SUM ( 'Sales'[Quantity] ), 'Date'[Date] ) )

Суммарное количество по продажам в последние дни каждого года

Для иллюстрации гибкости и лаконичности DAX можно рассмотреть меры и переменные. Меры определяются в рамках таблиц, синтаксически ссылки на меры выглядят как ссылка на столбцы, и меры рассчитываются во время выполнения запроса. Переменные, в свою очередь, упрощают понимание DAX запроса.

Рассчитаем суммарное количество по продажам в последние дни каждого года. В рамках диалектов SQL это выглядит достаточно объемной задачей, однако в DAX это решается в 20 строк кода с 1 мерой ‘Sales'[Total Quantity Last Day] и одной переменной TotalQuantityLastDay (в примере https://dax.do/DAQtVSbaAufR1s не создана новая мера ‘Sales'[Total Quantity Last Day] и используется существующая мера ‘Sales'[Total Quantity]).

Total Quantity Last Day :=     CALCULATE (         SUM ( 'Sales'[Quantity] ),         FILTER ( 'Sales', 'Sales'[Order Date] = MAX ( 'Sales'[Order Date] ) )     )  DEFINE     VAR TotalQuantityLastDay =         SUMMARIZECOLUMNS (             'Date'[Calendar Year],             "Total Quantity Last Day", 'Sales'[Total Quantity Last Day]         )  EVALUATE TOPN (     1000,     TotalQuantityLastDay,     'Date'[Calendar Year], ASC,     [Total Quantity Last Day], ASC )

В заключение хочется отметить, что существует не только реализация DAX от Microsoft, но и альтернативные, например, Visiology. Надеюсь, эти примеры помогли привлечь интерес к DAX или раскрыть новые горизонты BI анализа 🙂