Получение SQL для PostgreSQL из DAX на основе AI

Привет, Хабр! Популярным аналитическим языком является DAX, и он используется во множестве проектов. Соответственно, значительная часть бизнес-логики дашбордов реализована на DAX, и при переходе с Power BI на другой продукт требуется время на перевод DAX логики из Power BI. В связи с этим актуальны инструменты расширения списка платформ, на которых можно использовать DAX без Power BI.

Тем, кто интересуется «переводом» DAX на PostgreSQL — добро пожаловать под кат 🙂

Основой всех DAX дашбордов можно считать SUMMARIZECOLUMNS. Можно сказать, что группировка, фильтрация на основе UI фильтров и DAX выражений, соответствующих мерам, во многом составляют суть аналитического языка DAX.

В связи с этим имеет смысл рассмотреть перевод SUMMARIZECOLUMNS запроса DAX на SQL для PostgreSQL с использованием AI.

Рассмотрим, например, часть стандартной dax.do схемы с таблицей фактов Sales и справочником клиентов Customer.

Рассмотрим запрос DAX без глубокого бизнес-смысла, лишь содержащий набор относительно нетривиальных конструкций DAX.

EVALUATE SUMMARIZECOLUMNS (     Customer[Cars Owned],     FILTER ( Sales, Sales[Quantity] > AVERAGE ( Sales[Quantity] ) ),     FILTER ( Customer, Customer[Cars Owned] > 1 ),     "Calculated Quantity",         CALCULATE (             SUMX ( Sales, Sales[Quantity] * RELATED ( Customer[Cars Owned] ) ),             REMOVEFILTERS ( Sales[Quantity] ),             FILTER ( Customer, Customer[Cars Owned] < 4 )         ) )

Видим фильтрацию в самом SUMMARIZECOLUMNS через его аргументы по двух таблицам FILTER ( Sales, Sales[Quantity] > AVERAGE ( Sales[Quantity] ) ) и FILTER ( Customer, Customer[Cars Owned] > 1 ), которая может соответствовать условной UI фильтрации. Также видим выражение "Calculated Quantity", которое состоит из CALCULATE с итератором SUMX и RELATED, фильтром FILTER и REMOVEFILTERS внутри CALCULATE.

Видно, что запрос относительно нетривиальный, т.к., например, для выражения внутри CALCULATE нужно учесть четыре выражения, касающиеся фильтров — фильтр на уровне SUMMARIZECOLUMNS, сброс фильтра REMOVEFILTERS ( Sales[Quantity] ) и «выполнить слияние» фильтров Customer[Cars Owned] > 1 && Customer[Cars Owned] < 4.

Предположим, что у нас есть PostgreSQL таблицы sales и customer следующего вида, причем даже не указываются ключи, не создаются индексы, т.е. максимально «сырая» таблица и вся схема.

CREATE TABLE customer (    CustomerKey    INTEGER,    "Cars Owned"   INTEGER ); CREATE TABLE sales (    "Order Number" INTEGER,    CustomerKey    INTEGER,    Quantity       INTEGER );

Для примера заполним таблицу sales 50 миллионами тестовых данных, время выполнения запроса для 50 млн строк составило около 40 секунд.

INSERT INTO sales("Order Number", CustomerKey, Quantity) SELECT number + 100000000 AS "Order Number",        number % 20        AS CustomerKey,        number % 10        AS Quantity FROM generate_series(1, 50000000) AS number;

Также заполним таблицу customer 20 строками:

INSERT INTO customer(CustomerKey, "Cars Owned") SELECT number % 20        AS CustomerKey,        number % 5         AS Quantity FROM generate_series(0, 19) AS number;

Далее на основе запросов к AI, которые не намного сложнее описанных раньше, переведем DAX запрос в PostgreSQL и посмотрим на результат.

Для упрощения работы AI создадим VIEW с денормализованной таблицей sales:

CREATE VIEW sales_denormilized AS SELECT s."Order Number",        s.CustomerKey,        s.Quantity,        c."Cars Owned" FROM sales s          INNER JOIN customer c ON s.CustomerKey = c.CustomerKey;

Соответственно, придется в исходном DAX для AI поменять Customer[Cars Owned] на Sales[Cars Owned]. В результате AI дает следующий SQL для PostgreSQL:

SELECT "Cars Owned",        SUM("Calculated Quantity") AS "Calculated Quantity" FROM (          SELECT "Cars Owned",                 (Quantity * "Cars Owned") AS "Calculated Quantity"          FROM sales_denormilized          WHERE Quantity > (SELECT AVG(Quantity) FROM sales_denormilized)            AND "Cars Owned" > 1            AND "Cars Owned" < 4      ) subquery GROUP BY "Cars Owned"

В SQL от AI виден лишний subquery, но в общем и целом результат выглядит разумным, учитывается и фильтрация с учетом среднего значения Sales[Quantity], и фильтрация по "Cars Owned". Конечно, с точки зрения производительности запрос от AI неидеален, но работоспособен десятков миллионов записей.

Запрос для 50 млн строк выполнился примерно за 11 секунд (и выполняется за 6 секунд, если сохранить результаты sales_denormilized в таблицу и использовать таблицу).

Перевод DAX в PostgreSQL был осуществлен автоматически на основе анализа DAX и с использованием AI для получения SQL для PostgreSQL, также использовалась описанная предварительная автоматическая обработка DAX.

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

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