Получение 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, 20) AS number;

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

Запрос для 50 млн строк выполнился примерно за 6 секунд

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

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

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