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

Существует множество инструментов для решения задач Business Intelligence, одним из удобных инструментов является функциональный язык DAX, позволяющий работать с различными СУБД и выполнять достаточно сложные аналитические расчеты.

Поскольку язык DAX способен работать со множеством различных СУБД (например Oracle, MS SQL, MySQL, PostgreSQL, ClickHouse и т. д.), т. е. работает со множеством диалектов SQL, то в некотором смысле DAX является «надмножеством SQL» и приближается в этом смысле к реляционной алгебре. В данной статье приводится разбор типичного DAX для получения записи этого DAX в нотации реляционной алгебры. Интересующимся погружением в DAX и его реляционное представление — добро пожаловать:)

Рассмотрим схему звезду с таблицей фактов с продажами Sales, строка которой содержит ключ продукта productkey, ключ клиента customerkey и проданное количество quantity, а также таблицу Product (поля productkey и productname) и таблицу Customer (поля customerkey и customername).

Используемая схема данных для DAX (dax.do)

DAX для получения суммы количества продаж с группировкой по имени продукта и имени клиента выглядит следующим образом (также этот пример доступен на dax.do):

EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Product Name],
    'Customer'[Customer Name],
    "Total Quantity", SUMX ( 'Sales', 'Sales'[Quantity] )
)

Этот DAX использует итератор SUMX для подсчета суммы по полю 'Sales'[Quantity] в рамках группировки по 'Product'[Product Name] и 'Customer'[Customer Name].

Построим реляционное представление этого DAX. Выделим отношение продажи S, элемент s_i множества S представляет собой кортеж из ключа продукта salesproductkey_i, ключа клиента salescustomerkey_i и проданного количества quantity_i:

s_i ∈ S, s_i = (salesproductkey_i, salescustomerkey_i, quantity_i)

Также выделим отношение продукты P, элемент которого является кортежем p_i с ключом productkey_i и именем продукта productname_i:

p_i ∈ P, p_i = (productkey_i, productname_i)

Наконец, по аналогии выделим отношение клиенты C, элемент которого c_i является кортежем из ключа customekey_i и имени клиента customername_i:

c_i ∈ C, с_i = (customerkey_i, customername_i)

Существуют разновидности нотации реляционной алгебры, в этой статье для целей иллюстрации будут использованы декартово произведение ×, селекция σ, проекция π и агрегация с группировкой F на основе источника, но, конечно, можно использовать и другие реляционные операции, например, операцию JOIN, который обозначается как ⋈ и определяется через декартово произведение и селекцию. Например, для продаж S и продуктов P операция JOIN ⋈ выглядит следующим образом:

S ⋈_{salesproductkey = productkey} P = σ_{salesproductkey = productkey} ( S × P)

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

σ_{salesproductkey = productkey AND salescustomerkey = customerkey} ( S × P × C )

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

S ⋈_{salesproductkey = productkey}P ⋈_{salescustomerkey = customerkey} C

Может создасться впечатление, что дальше имеет смысл сделать проекцию π, чтобы выбрать только необходимые поля productname, customername, quantity для дальнейшей агрегации (суммирования по quantity), однако в проекции вида π _{productname, customername, quantity} (σ_{salesproductkey = productkey AND salescustomerkey = customerkey}(S×P×C)) будет группировка по quantity, что далее приведет к некорректным результатам суммирования quantity по одинаковым productname, customername, quantity.

В связи в этим выполним агрегацию F для получения результата. Слева от F в нижнем индексе пишутся поля для группировки, а справа от F в нижнем индексе пишутся поля для агрегации с указанием функции агрегации, т. е. для группировки по имя_поля_для_группировки и для суммирования имя_поля_для_суммирования получим запись _{имя_поля_для_группировки} F _{SUM имя_поля_для_суммирования}. Может выглядеть несколько громоздко, но тем не менее, возвращаясь к исходной задаче, получим:

_{productname, customername} F _{SUM quantity} (σ_{salesproductkey = productkey AND salescustomerkey = customerkey} (S×P×C))

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

Видно, что в отличие от диалектов SQL, в DAX мы не влияем явно на планы выполнения запроса, например, через указание GROUP BY или через выбор типа JOIN (INNER, LEFT SEMI и другие), или через указание условия фильтрации в части WHERE оператора SELECT, либо в части JOIN ON, не заботимся от индексах и т. д.

Все подобные вопросы среда выполнения DAX решает самостоятельно (например, через Auto-Exists и др.). Стоит также отметить, что кроме реализации DAX от Microsoft существует также отечественный продукт — Visiology.

Надеюсь, приведенный анализ покажется интересным, либо поможет прояснить особенности языка DAX :)

Пример DAX с точки зрения реляционной алгебры