Реляционная алгебра для DAX: ALL в итераторе SUMX для SUMMARIZECOLUMNS

Привет, Хабр! Язык запросов DAX популярен и эффективен для построения дашбордов в Business Intelligence, и за счет свой функциональной природы DAX в чем-то ближе к реляционной алгебре, по сравнению с SQL. Особенности DAX удобно рассмотреть на основе примеров DAX-запросов, переведенных на реляционную алгебру. В частности, использование ALL в итераторе SUMX в рамках наиболее популярной DAX функции SUMMARIZECOLUMNS позволяет рассмотреть некоторые нюансы DAX. Если интересно описание ALL в DAX с точки зрения реляционной алгебры — добро пожаловать под кат! 🙂

Схема данных и реляционные отношения

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

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

Сейчас же добавляется ALL в итератор SUMX, т.е. рассматривается DAX вида:

EVALUATESUMMARIZECOLUMNS (   'Product'[Product Name],   'Customer'[Customer Name],   "Total Quantity", SUMX ( ALL ( 'Sales' ), 'Sales'[Quantity] ) )

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

Можно напомнить, что было выделено отношение продажи 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)

В итоге из предыдущей статьи следующий DAX:

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

соответствует реляционному выражению:

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

или, с использованием JOIN ⋈, можно записать

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

ALL в DAX

Из описания ALL в DAX Guide видно, что он служит для сброса всех фильтров:

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

Рассмотрим добавление ALL в итератор SUMX для отмены фильтр-контекста строки для таблицы фактов Sales, объединенной со справочниками продуктов Product и клиентов Customer:

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

В данном случае благодаря ALL игнорируется фильтр по ‘Product'[Product Name] и ‘Customer'[Customer Name] из SUMMARIZECOLUMNS, и в SUMX производится суммирование ‘Sales'[Quantity] по всем строкам Sales.

Конечно, такой случай всегда выводит в Total Quantity одно и то же значение, но тем не менее, кейс выгдялит интересным.

Реляционное представление ALL в DAX

Как мы видим, с появлением ALL нам уже не нужно считать сумму ‘Sales'[Quantity] с группировкой по продуктам и клиентам вида _{productname, customername} F _{SUM quantity} — теперь эти три поля не будут вместе в одной операции группировки по productname, customername и суммирования quantity, поскольку нужна сумма по всем строкам из Sales, объединенной со справочниками.

Обозначим объединение таблицы фактов со справочниками через

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

Или через операцию JOIN ⋈ получим:

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

Для подсчета ALL в итераторе SUMX нам нужно сохранить связи между таблицами при суммировании в SalesStar, в итоге выполняется агрегация (суммирование) без группировки и подсчитывается необходимая сумма по ‘Sales'[Quantity]:

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

Далее используем операцию переименования единственного атрибута в F _{SUM quantity} (SalesStar), это отношение с одним кортежем и одним атрибутом, т.е. по сути отношение, содержащее единственный скалярный результат.

Операция переименования ρ позволяет переименовать отношение и/или его атрибуты непосредственно в выражении реляционной алгебры:

suppose we have a relation R of degree n , R(A₁, A₂, . . . , A_n) we can rename the relation, the attributes or both:

• ρ_{S(B1,B2,…,Bn)}(R) 

• ρ_S(R)

• ρ_{(B1,B2,…,Bn)}(R)

Таким образом, с операцией переименования атрибута, получим новое отношение Q:

Q = ρ_{(sumquantityallsales)}(F _{SUM quantity} (SalesStar))

В Q содержится результат F _{SUM quantity} (SalesStar), т.е. единственный кортеж с единственным атрибутом, причем имя атрибута переименовано из SUM quantity в sumquantityallsales, и представляет собой необходимую сумму ‘Sales'[Quantity] по всем записям. Видно, что при добавлении ALL теперь F _{SUM quantity} подсчитано без группировки.

В итоге реляционное выражение для исходного DAX с ALL будет следующим:

π _{productname, customername, sumquantityallsales} (σ_{salesproductkey = productkey AND salescustomerkey = customerkey} (S×P×C×Q))

Также можно переписать через JOIN ⋈:

π _{productname, customername, sumquantityallsales} (S ⋈ _{salesproductkey = productkey} P ⋈ _{salescustomerkey = customerkey} C×Q)

Далее упрощаем за счет использования SalesStar и получаем

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

Q = ρ_{(sumquantityallsales)}(F _{SUM quantity} (SalesStar))

π _{productname, customername, sumquantityallsales} (SalesStar × Q)

С точки зрения производительности можно присоединить скаляр sumquantityallsales позже, после группировки по productname, customername, поэтому можно также записать:

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

Q = ρ_{(sumquantityallsales)}(F _{SUM quantity} (SalesStar))

π _{productname, customername, sumquantityallsales} (Q × π _{productname, customername} (SalesStar))

Причем видно, что Q — это отношение с одним кортежем с одним атрибутом, т.е. Q — это отношение, которое хранит скалярный результат суммы quantity из Sales по всем записям из объединенного отношения таблицы фактов Sales, справочника продуктов Product и справочника клиентов Customer, что и требовалось в «Total Quantity» из SUMAMRIZECOLUMNS с добавленным ALL.

Надеюсь, пример описания DAX с использованием ALL через реляционную алгебру был интересен. Желаю успешных и быстрых дашбордов! 🙂