При использовании DAX аналитикам важно следить не только за корректностью результатов, но и за производительностью системы при обработке запросов. Одним из инструментов повышения эффективности является корректное использование функции SUMMARIZE. Всем, кто работает с большими объемами данных, активно изучает синтаксис DAX, а также интересующимся особенностями SUMMARIZE — добро пожаловать под кат!

Начнем с того, что по DAX, хотя и стал популярен благодаря Microsoft, но сегодня этот язык представляет нечто намного большее и используется, в частности, аналитиками на базе платформы Visiology. И хотя существует официальная документация по DAX от Microsoft, неофициальная документация может давать больше информации. В частности, я активно пользуюсь dax.guide и sqlbi.com. Кстати, при ее изучении вы сможете понять, почему в Visiology вводится поддержка не всех форматов синтаксиса. Как минимум, некоторые из приемов MS Power BI лучше вообще не использовать.

Скажите «Нет» expressions в SUMMARIZE

Это справедливо для работы с SUMMARIZE. В приведенных выше источниках говорят не только о преимуществах его использования, но также о проблемах производительности при расчете выражения в SUMMARIZE, о depricated-параметрах имени выражения <Name> и, собственно, самого выражения <Expression>.

Действительно, раз профессионалы говорят, что это deprecated, то и не нужно пользоваться функцией для этих целей. К тому же для расчета выражений, как известно, существует функция SUMMARIZECOLUMNS. Так зачем делать то, что работает хуже?

Visiology расширяет синтаксис поддерживаемого DAX в соответствии с запросами пользователей. И поэтому в версии DAX от Visiology просто не может быть артефактов, которые появились в MS по мере естественного роста. То, что deprecated в MS DAX, не будет реализовано в Visiology. Именно поэтому в Visiology нет поддержки SUMMARIZE с параметрами expression. Вместо этого для расчета выражений используется SUMMARIZECOLUMNS. Это и быстрее, и удобнее. А трансформировать уже имеющиеся выражения с SUMMARIZE помогает чат-бот ViTalk GPT.

Повышая производительность

Но я вам обещал как негативные, так и позитивные примеры работы с этим оператором. Конечно, существуют разные кейсы, когда SUMMARIZE полезен. Например, давайте посмотрим, в каких случаях SUMMARIZE позволяет повысить производительность. Ведь этот оператор может быть эффективным, когда используется для группировки без выражения.

В качестве примера рассмотрим таблицу продаж sales с полями номер заказа ordernumber, ключ даты datekey в формате YYYYMMDD, ID магазина storekey и сумма заказа amount, таблицу магазинов store с полями ID магазина storekey, имя магазина storename и календарь calendar с полями ключ даты datekey в формате YYYYMMDD, месяц month и сама дата в формате даты date, т.е. схему данных:

Допустим, нам нужно посчитать значение флага «активный магазин» 0 или 1 для каждого магазина, причем мы присваиваем 1, если сумма продаж по магазину SUM(sales[orderamount]) превышает заданную сумму за промежуток времени (например, 5000000000), будем считать также, что промежуток времени ограничивается фильтрами дашборда, и его не нужно явно учитывать в DAX запросе. Кроме того, нам нужно посчитать и количество таких магазинов, что вполне естественно.

Понятно, что задача решается созданием новой меры. Также в задаче удобен флаг со значениями 0 и 1, т.к. дает возможность суммирования и определения общего количества таких магазинов.

Мера на основе IF для расчетов с группировкой по магазинам

Меру можно создать при помощи IF на основе условия на сумму продаж SUM(sales[orderamount]), однако для корректной работы такую меру всегда нужно использовать вместе с группировкой по магазинам, т.е. с sales[storekey]:

Is Store Actual =
IF ( SUM ( sales[orderamount] ) > 5000000000, 1, 0 )

Эту меру можно добавить, например, в таблицу store и назвать store[Is Store Actual].

Дашборд для такой меры выглядит следующим образом:

Visiology генерирует следующий DAX для такой таблицы:

DEFINE
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            'store'[storename],
            “SUM(orderamount)”, SUM('store'[orderamount]),
            "Is Store Actual", 'store'[Is Store Actual]
        )

EVALUATE
TOPN (
    3000,
    __DS0Core,
    'store'[storename], ASC,
    [Is Store Actual], ASC
)

ORDER BY
    'store'[storename] ASC,
    [Is Store Actual] ASC

У такой меры store[Is Store Actual] есть недостаток — она верно работает только в некотором контексте, а именно, при наличии группировки по sales[storekey], или, например, при группировке по уникальным именам магазинов — по store[storename]. Поэтому с данной мерой нет возможности подсчитать суммарное количество активных магазинов в виде одного числа, как скалярный результат.

Мера с SUMMARIZE

Хорошо бы сделать эту меру несколько более универсальной, «зашить в неё» группировку по магазину, чтобы использовать меру в разных контекстах…ну или хотя бы посчитать количество таких магазинов.

Когда нужно получить агрегированные данные таблицы с группировкой по столбцам (условно, с GROUP BY), как и в случае нашей меры (группировка по ID магазина sales[storekey] с суммированием sales[amount]), помогает SUMMARIZE со столбцами группировки, как один из аргументов SUMX:

Actual Stores SUMMARIZE = SUMX (
    SUMMARIZE ( sales, sales[storekey] ),
    CALCULATE (IF ( SUM ( sales[orderamount] ) > 5000000000, 1, 0 ) )
)

Важно, что CALCULATE в этой мере используется, чтобы эта мера верно работала в таблице без группировки по sales[storekey], т.е. для подсчета общего количества активных магазинов.

Соответственно, когда мера используется в таблице с группировкой по sales[storekey], SUMX суммирует всего лишь «одно слагаемое» для каждого магазина sales[storekey]. При наличии группировки можно обойтись даже и без SUMX, но мера будет не такой универсальной и не сможет использоваться с любым контекстом — она будет давать верные результаты только в таблице с группировкой, например, по sales[storekey]. 

SUMX он на верхнем уровне хоть и служит лишь для сложения результатов по разным магазинам (когда не задана группировка по sales[storekey]), зато повышает универсальность меры.

Кстати, Visiology генерирует DAX для этой таблицы, совпадающий с DAX для предыдущего дашборда с точностью до имени меры.

Для 50 млн записей DAX запрос выполняется примерно за 1-2 секунды.

При использовании этой меры в каком-то смысле «производительность гарантирована» за счет группировки в SUMMARIZE по ID магазина. То есть благодаря SUMMARIZE в плане выполнения запроса вместо общего количества записей в таблице фактов sales появляются только записи sales, сгруппированные по sales[storekey], и количество сгруппированных записей всегда будет на порядок или даже на порядки ниже, чем исходное количество записей в таблице sales. Мера с SUMMARIZE может применяться и с группировкой по ID магазина, и самостоятельно (для подсчета суммы по всем магазинам), как видно на следующем дашборде.

Visiology генерирует следующий DAX запрос:

DEFINE
    VAR __DS0Core =
        SUMMARIZECOLUMNS ( "Actual Stores SUMMARIZE", 'sales'[Actual Stores SUMMARIZE] )

EVALUATE
TOPN ( 3000, __DS0Core, [Actual Stores SUMMARIZE], ASC )
ORDER BY [Actual Stores SUMMARIZE] ASC

Пример сравнения SUMMARIZE с другой мерой — одинаковые результаты, но разная производительность

В итоге можно сказать, что SUMMARIZE позволяет создавать эффективные меры с точки зрения производительности, причем их можно использовать в разных контекстах, а именно, как с полями группировки (например, store[storename]), так и без полей группировки. Возникает вопрос, с чем сравнить меру с SUMMARIZE, чтобы понять, так ли он нужен? Могут быть разные варианты, можно рассмотреть один из них, менее оптимальный, по сравнению с SUMMARIZE, на таблице с группировкой по sales[storekey]:

Actual Stores DISTINCTCOUNT = CALCULATE (
DISTINCTCOUNT(sales[storekey]),
FILTER(sales, SUM(sales[orderamount]) > 5000000000)
)

При наличии в таблице группировки по sales[storekey] CALCULATE всегда рассчитывается на продажах одного магазина sales[storekey], поэтому DISTINCTCOUNT(sales[storekey]) возвращает 1 при выполнении условия по сумме продаж больше 5000000000, и 0 иначе, что и требуется. Хочется отметить, что мера с DISTINCTCOUNT взята для сравнения лишь в качестве примера, не как best practice в именно для данного случая.

В результате мы получим дашборд следующего типа с мерой sales[Actual Stores DISTINCTCOUNT]. Запрос на 50 млн записей выполняется за несколько секунд. Это значительно больше пары секунд и дольше, чем генерация дашборда с SUMMARIZE:

DAX запрос, который генерирует Visiology для этой таблицы, совпадает с другими с точностью до имени меры, а результаты с группировкой по sales[storekey] совпадают с результатами меры sales[Actual Stores Summarize].

Видно, что здесь используется DISTINCTCOUNT(sales[storekey]), и эта операция и весь план выполнения запроса работают медленнее по сравнению с SUMMARIZE и его фактической GROUP-BY-логике для  sales[storekey]. То есть с точки зрения производительности для получения сгруппированной таблицы полезно использовать именно SUMMARIZE.

Заключение

Таким образом, разумное применение SUMMARIZE (без expression) позволяет строить производительные меры, которые можно более свободно использовать в разных контекстах, как с группировкой по столбцу (в нашем случае ID магазина), так и самостоятельно, без такого столбца.

Надеюсь, описанные приемы работы с SUMMARIZE могут быть полезны для дашбордов. Успехов в Business Intelligence :)