Получение 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             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 и дашбордах :)