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

Существует множество инструментов для решения задач 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)

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 в dax.do
Результат выполнения DAX в dax.do

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

si ∈ S, si = (salesproductkeyi, salescustomerkeyi, quantityi)

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

pi ∈ P, pi = (productkeyi, productnamei)

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

ci ∈ C, сi = (customerkeyi, customernamei)

Существуют разновидности нотации реляционной алгебры, в этой статье для целей иллюстрации будут использованы декартово произведение ×, селекция σ, проекция π и агрегация с группировкой 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 для дальнейшей агрегации:

π productname, customername, quantitysalesproductkey = productkey AND salescustomerkey = customerkey(S×P×C))

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

productname, customername F SUM quantity (π productname, customername, quantitysalesproductkey = 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 :)

Комментарии (0)