Автор перевода: Снежана Киселева (ТГК: Анализ данных и BI)

Вас просили заменить пустые значения нулями в отчетах? Возможно, стоит дважды подумать, прежде чем это делать!

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

И одно из них, что привлекло мое внимание, касалось так называемых «разреженных мер»:

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

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

Подготовка

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

Начнем с написания нашей простой меры 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, сгенерированный движком формул, был довольно простым:

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

Добавляем ещё ингредиентов…

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

Это снова было очень быстро! Теперь я проверю метрики в DAX Studio:

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

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

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

Это потому, что Formula Engine был достаточно умён, чтобы исключить даты, когда у продукта 364 не было продаж, используя фильтр NonEmpty, и поэтому количество записей равно 58: у нас есть 58 различных дат, когда продажи продукта 364 не были пустыми:

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

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

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

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

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

4,6 миллиона записей?! Почему это происходит?! Давайте я посчитаю за вас: 2,517 * 1,826 = 4,596,042… Итак, здесь у нас было полное перекрестное соединение (cross-join) между таблицами Product и Dates, заставляющее проверять каждую отдельную пару (комбинацию дата-продукт)! Это произошло потому, что мы заставили движок возвращать 0 для каждой пары, которая в противном случае вернула бы пустое значение (и, следовательно, была бы исключена из сканирования)!

Вот упрощенный обзор того, что произошло:

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

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

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

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

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

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

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

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

Хм, похоже, мы не поплатились производительностью. Давайте посмотрим план выполнения этого DAX-запроса:

Вывод

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

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

Больше материала про анализ данных и BI я публикую в своём телеграм-канале. Буду рада вашей подписке :)

Почему не стоит заменять пустые значения нулями в Power BI