Популярным языком запросов от 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 анализа :)