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