Просили ли вас когда-нибудь заменить пустые значения на 0 в отчётах? Возможно, стоит подумать дважды, прежде чем это делать!

Как-то раз я посмотрел прямую трансляцию с участием Джеффри Ванга, который был в гостях у Рида Хэйвенса, и среди множества ценных мыслей, которыми Джеффри поделился с аудиторией, особенно запомнился список оптимизаций, которые движок DAX выполняет при формировании оптимального плана запроса для наших мер.

И наибольшее моё внимание привлёк пункт, связанный с так называемыми «разрежёнными мерами»:

Проще говоря, как только вы определяете меру, формульный движок в VertiPaq автоматически добавляет к запросу неявный фильтр NonEmpty, который позволяет оптимизатору избежать полного кросс-соединения таблиц измерений и обрабатывать только те строки, где действительно существуют записи с комбинацией указанных атрибутов измерений. Для тех, кто ранее работал с MDX, функция NonEmpty может показаться знакомой — давайте посмотрим, как это реализовано в DAX.

Наибольший отклик у меня вызвал совет Джеффри: не заменять значения BLANK на нули (или другие явные значения) в вычислениях Power BI. Ранее я уже писал о том, как можно обрабатывать BLANK и заменять их на ноль, но в этой статье я хочу сосредоточиться именно на возможных последствиях такого решения с точки зрения производительности.

Подготовим основу

Прежде чем мы начнём, важное уточнение: рекомендация не заменять BLANK на 0 — именно рекомендация, а не строгий запрет. Если бизнес требует отображать 0 вместо пустого значения, это не значит, что нужно категорически отказываться. В большинстве случаев вы, скорее всего, даже не заметите падения производительности — но это будет зависеть от множества факторов…

Начнём с простой меры на DAX:

Sales Amt 364 Products =
CALCULATE (
    [Sales Amt],
    FILTER ( ALL ( 'Product'[ProductKey] ), 'Product'[ProductKey] = 364 )
)

С помощью этой меры я хочу вычислить общий объём продаж для товара с ProductKey = 364. Если вывести это значение в визуализацию Card и включить Performance Analyzer, чтобы посмотреть время обработки запроса, то получим следующий результат:

DAX-запрос выполнился всего за 11 мс, и когда я переключился в DAX Studio, оказалось, что сгенерированный формульным движком xmSQL был довольно простым:

Если посмотреть на физический план запроса, видно, что движок хранения (Storage Engine) нашёл только одну существующую комбинацию значений, чтобы вернуть наши данные:

Добавим ещё немного данных…

Предположим, что теперь бизнес хочет анализировать данные по продукту с ключом 364 по дням. Добавим даты в наш отчёт:

И снова всё отработало очень быстро! Проверим метрики в DAX Studio:

На этот раз запрос был расширен и включил таблицу Dates, что изменило объём работы, которую должен выполнить движок хранения — вместо одной строки теперь результат другой:

Разумеется, вы не заметите разницы в производительности между этими двумя сценариями — речь идёт всего лишь о нескольких миллисекундах.

Но это только начало; мы лишь «разогреваем» движок DAX. В обоих случаях, как можно заметить, отображаются только заполненные значения — то есть такие комбинации строк, где одновременно выполняются оба условия: ProductKey = 364 и дата, по которой действительно были продажи этого продукта. Если внимательно присмотреться к иллюстрации выше, можно заметить, что даты непоследовательны — некоторые из них отсутствуют, например, 12 января, а также период с 14 по 21 января и так далее.

Это происходит потому, что формульный движок был достаточно «умным», чтобы исключить даты, когда у продукта 364 не было продаж, с помощью фильтра NonEmpty. Именно поэтому итоговое количество записей — 58: у нас есть 58 уникальных дат, в которые продажи продукта 364 не были пустыми.

А теперь представим, что бизнес-пользователи также хотят видеть все пропущенные даты, в которых у продукта 364 не было продаж. Идея — отобразить для всех этих дат значение продаж $0. Есть несколько способов заменить BLANK на ноль, и я воспользуюсь функцией COALESCE():

Sales Amt 364 Products with 0 = COALESCE([Sales Amt 364 Products], 0)

Функция COALESCE, по сути, проверяет все переданные ей аргументы (в данном случае — только один) и подставляет вместо первого значения BLANK указанное вами значение. Проще говоря, она проверяет, является ли результат выражения Sales Amt 364 Products пустым. Если нет — отображается вычисленное значение; если да — вместо BLANK подставляется 0.

Подождите, что?! Почему я вижу все продукты, если у меня был фильтр только на продукт 364? И это ещё полбеды — таблица теперь рендерится больше двух секунд! Давайте посмотрим, что произошло под капотом.

Вместо одного запроса теперь сгенерировались три. Первый полностью повторяет предыдущий (58 строк). Но два других обращаются к таблицам Product и Dates, вытягивая все строки из обеих таблиц. В таблице продуктов 2517 записей, в таблице дат — 1826.

И это ещё не всё. Посмотрите на план запроса:

4,6 миллиона записей? Как такое возможно?! Посчитаем: 2 517 × 1 826 = 4 596 042… То есть, здесь был выполнен полное перекрёстное соединение (cross-join) между таблицами Product и Dates, и каждая возможная комбинация дата–продукт была проверена. Это произошло потому, что мы вынудили движок возвращать 0 для каждой такой комбинации, которая в противном случае дала бы BLANK — и, соответственно, была бы исключена из обработки.

Вот упрощённая схема того, что произошло:

Хотите верьте, хотите нет, но существует элегантное решение, позволяющее отображать пустые значения «из коробки» (но не как 0, а именно как BLANK). Всё, что нужно — кликнуть по полю с датами и выбрать опцию Show items with no data («Показывать элементы без данных»):

Это позволит отобразить и пустые ячейки, но без выполнения полного кросс-соединения между таблицами Product и Dates:

Теперь мы видим все ячейки (включая пустые), и выполнение этого запроса заняло в два раза меньше времени, чем предыдущего! Посмотрим на план запроса, сгенерированный формульным движком:

Не все сценарии критичны

По правде говоря, мы могли бы переписать нашу меру так, чтобы исключить ненужные записи, но даже в этом случае движку было бы непросто оптимально устранить пустые значения.

Кроме того, есть ситуации, когда замена BLANK на ноль практически не влияет на производительность.

Рассмотрим следующий случай: мы отображаем данные по общему объёму продаж для каждого бренда. И я добавляю в визуализацию свою метрику по продажам для продукта 364:

Как и ожидалось, всё отработало очень быстро. А что произойдёт, если я добавлю версию этой меры, в которой BLANK заменяется на 0 — ту самую, которая вызывала проблемы в предыдущем сценарии?

Хм, похоже, никакого штрафа по производительности мы не получили. Посмотрим на план запроса для этого выражения DAX:

Заключение

Как советовал Джеффри Ванг, по возможности избегайте замены пустых значений (BLANK) на нули (или другие явно заданные значения), так как это может существенно ограничить способности оптимизатора к исключению ненужного сканирования данных. Однако если по каким-либо причинам вы всё же хотите подставить вместо BLANK конкретное значение — будьте осторожны где и как именно вы это делаете.

Как обычно, всё зависит от множества факторов. Если вы:

• работаете со столбцами, в которых мало уникальных значений (низкая кардинальность), 

• не выводите данные из нескольких разных таблиц (как в нашем случае с таблицами Product и Dates), 

• или используете визуализации, не отображающие большое количество уникальных значений (например, Card),

— скорее всего, вы не столкнётесь с просадкой производительности.

Но если вы используете таблицы, матрицы или диаграммы, в которых представлено множество уникальных комбинаций, обязательно проверяйте метрики и планы запросов, прежде чем публиковать отчёт в рабочей среде.

Иногда за банальной просьбой «поставь нули вместо пустых» скрывается куда более серьёзная проблема — непонимание, как именно работают данные и кто за них отвечает. Если вы работаете с BI, требованиями или участвуете в запуске продуктов, эти темыстоит разобрать глубже. Ниже — два практических разбора для тех, кто хочет не просто «рисовать отчёты», а видеть риски и зоны роста ещё на этапе планирования.

• 7 августа в 20:00
  Как бизнес-аналитик управляет рисками при разработке IT-продукта?

• 25 августа в 20:00
  Бизнес-аналитик и Системный аналитик: зачем нужны 2 роли?

А освоить все необходимые навыки для профессиональной работы с реляционными и нереляционными базами данных можно на курсе «Базы данных».