Подробно изучите фильтрацию в DAX.

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

Делиться

Введение

Фильтры всегда используются при разработке выражений DAX, таких как меры DAX, или при написании запросов DAX.

Но что именно происходит, когда мы применяем фильтры?

Эта статья как раз и посвящена этому вопросу.

Я начну с простых запросов и добавлю варианты, чтобы изучить, что происходит «под капотом».

Я использую DAX Studio и опцию отображения времени выполнения каждого запроса на сервере.

Если вы хотите узнать больше об этой функции и о том, как интерпретировать результаты, прочтите первую статью в разделе «Ссылки» в конце этой статьи.

Начнём с базового запроса:

EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS('Product'[BrandName] ,»Online Sales», [Sum Online Sales] ) )

Когда мы активируем настройку времени выполнения на сервере и выполняем запрос, мы получаем статистику выполнения и запрос/запросы, необходимые для получения данных от механизмов хранения (SE):

Как видите, для получения результатов нам достаточно всего одного запроса к механизму хранения (Storage Engine, SE).

Запрос выполняется всего за 47 мс и обрабатывается почти полностью через SE (95,7%).

Чем больше времени инженер-программист сможет уделить запросу, тем лучше, поскольку именно он извлекает данные из хранилищ данных и таблиц.
Кроме того, SE может использовать несколько ядер ЦП, тогда как Formula Engine (FE) может использовать только одно. Мы не можем так же легко изучить, что именно происходит в FE, как в случае с запросами SE.

Более подробную информацию о различиях между этими двумя двигателями вы можете найти в упомянутой выше статье.

Краткое замечание:

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

Этот вариант гораздо более общий, чем тот.

Если вы пропустили, я добавил ссылку на статью и дополнительные материалы по данной теме в раздел «Ссылки» ниже.

Добавить простые фильтры

Далее добавим к запросу простой фильтр по цвету товара — красный:

EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS('Product'[BrandName] ,»Online Sales», [Sum Online Sales] ) ,'Product'[ColorName] = «Red» )

Вот запрос и результаты, ограниченные товаром красного цвета:

Если мы посмотрим на статистику запросов, то увидим следующее:

Как видите, весь запрос выполняется в рамках одного запроса SE.

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

Это видно в столбце «Строки», поскольку запрос возвращает всего 14 строк.

Но что происходит, когда мы используем функцию FILTER() для фильтрации товаров:

EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS('Product'[BrandName] ,»Online Sales», [Sum Online Sales] ) ,FILTER('Product' ,'Product'[ColorName] = «Red») )

Как вам, возможно, известно, использование функции FILTER() не рекомендуется из-за особенностей её работы.

Более подробную информацию по этой теме вы можете найти во второй статье, ссылка на которую приведена в разделе «Ссылки» ниже.

Результат не меняется:

Но как это повлияет на план выполнения и запросы SE?

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

Однако, по мере изменения нашего кода, мы увидим, что использование функции FILTER() не всегда является хорошей идеей.

Добавить несколько фильтров

А что произойдет, если мы добавим к запросу несколько фильтров?

EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS('Product'[BrandName] ,»Online Sales», [Sum Online Sales] ) ,'Product'[ColorName] = «Red» ,'Geography'[ContinentName] = «Europe» )

Хотя результат нас не особо интересует, давайте посмотрим на статистику запроса:

Повторюсь, запрос может быть обработан одним запросом SE, содержащим оба фильтра.

Запрос выполняется настолько быстро, что процент времени выполнения на фронтенде относительно высок, и при этом занимает всего 6 мс.

При изменении запроса для использования функции FILTER() сам запрос SE также не меняется:

Это показывает, что при выполнении подобных запросов движок может оптимизировать выполнение, чтобы найти наиболее эффективный способ обработки DAX-запроса.

В любом случае, результат не меняется. Он идентичен в обоих случаях, как и должно быть, потому что мы не меняем сам фильтр. Но, пожалуйста, наберитесь терпения; я вернусь к функции FILTER() и к тому, почему важно понимать её действие, чуть позже.

Перемещение фильтров в измерения

Далее посмотрим, что произойдет, если переместить фильтр в измерение.

До настоящего момента запрос был построен таким образом, что показатель [Сумма онлайн-продаж] получал свой фильтр извне.

Давайте попробуем вот что:

DEFINE MEASURE 'All Measures'[Online Sales A. Datum] = CALCULATE( SUMX('Online Sales', ( 'Online Sales'[UnitPrice] * 'Online Sales'[SalesQuantity]) — 'Online Sales'[DiscountAmount] ) ,'Product'[BrandName] = «A. Datum» ) EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS('Product'[BrandName] ,»Online Sales A. Datum», [Online Sales A. Datum] ) )

Как видите, фильтр применяется внутри показателя [Онлайн-продажи A. Дата].

Разумеется, итоговое число одинаково в каждой строке результата, поскольку в поле «Бренд» указано значение «A. Дата»:

Но исполнение несколько отличается:

На этот раз у нас два запроса SE.

1. Запрос для получения данных о продажах бренда «A. Datum». Этот запрос содержит фильтр по данному бренду.
2. Второй запрос используется для получения списка всех брендов, включенных в результирующий набор.

Первый запрос для нас наиболее важен, поскольку он по-прежнему отображает фильтр для набора брендов в рамках показателя.

Этот запрос может быть полностью обработан SE с помощью простого фильтра очень эффективным способом.

Однако в большинстве случаев нам нужно добавить несколько показателей в запрос (или визуализацию в отчете).

Что произойдет, если мы добавим в запрос показатель [Сумма онлайн-продаж]?

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

Но статистика запросов представляет интерес:

Как видно на выделенной красным линии в запросе SE, фильтр по бренду больше не присутствует.

Поскольку механизм распознает, что фильтр в мере применяется к тому же столбцу, что и в запросе, он перемещает фильтр в FE и возвращает результат.

А что произойдет, если мы отфильтруем другой столбец в показателе, например, цвет:

ОПРЕДЕЛИТЬ МЕРУ 'Все меры'[Продажи в интернете, красный] = ВЫЧИСЛИТЬ( SUMX('Продажи в интернете', ( 'Продажи в интернете'[Цена за единицу] * 'Продажи в интернете'[Количество продаж]) — 'Продажи в интернете'[Сумма скидки] ) ,'Продукт'[Название цвета] = «Красный» ) ОЦЕНИТЬ ВЫЧИСЛЯЕМУЮ ТАБЛИЦУ( SUMMARIZECOLUMNS('Продукт'[Название бренда] ,»Продажи в интернете», [Сумма продаж в интернете] ,»Продажи в интернете, красный», [Продажи в интернете, красный] ) )

Опять же, результат не представляет особого интереса. Нас интересует статистика запросов:

Как видите, на этот раз у нас два запроса по названию бренда. Один без фильтра по цвету, а другой с фильтром по цвету.

Оба запроса возвращают одинаковое количество строк (14) – по одной для каждого бренда.

Функция FE объединяет два результата в одну таблицу.

Весь запрос по-прежнему обрабатывается в основном через SE, что превосходно.

А теперь давайте добавим функцию FILTER() к фильтру:

В этом примере я изменяю показатель, чтобы отфильтровать два значения с помощью оператора IN:

,'Product'[BrandName] IN { «A. Datum», «Adventure Works» }

В этом варианте запрос SE аналогичен предыдущим.

Фильтр передается непосредственно в условие WHERE запроса.

Но что произойдет, если я изменю это на вот это:

,FILTER('Product' ,'Product'[BrandName] IN { «A. Datum», «Adventure Works» } )

Во-первых, меняется результат:

Причина в том, что функция FILTER() работает совершенно иначе.

Он сохраняет существующий контекст фильтра и добавляет новый.

Я объяснил это явление в другой статье, ссылку на которую добавил в качестве второй в разделе «Ссылки» ниже.

Более того, SE больше не может обработать это в рамках одного запроса:

Первые два запроса извлекают значения для фильтрации по бренду (см. запросы, отмеченные розовым цветом).

Обратите внимание на большое количество строк (324 и 2560), возвращенных первыми двумя запросами. Это результат обработки промежуточных данных, необходимых для выполнения вычислений.

Третий запрос использует эти промежуточные результаты для фильтрации данных (отмечены красным).

В результате третьего запроса отображаются всего две строки — те самые две строки, которые мы видим в общем результате.

Как описано в моей другой статье, функцию FILTER() следует использовать с осторожностью.

Он не только значительно медленнее, но и работает совершенно иначе, чем простой фильтр.

В любом случае, я могу восстановить прежнее поведение, добавив ALL() в вызов FILTER():

Я не хочу скрывать, что этот пример особенный, поскольку примененный фильтр затрагивает тот же столбец, что и в запросе.

При изменении запроса для фильтрации по стране, система может оптимизировать выполнение и снова использовать упрощенную форму:

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

Я очень часто сталкиваюсь с подобной формой фильтрации, когда разработчики, не обладающие достаточной квалификацией, пишут меры DAX.

Использование функции FILTER() кажется интуитивно понятным, но может приводить к некорректным или запутанным результатам и работает медленнее, чем простой фильтр. Я настоятельно рекомендую прочитать мою статью об этой функции, ссылка на которую приведена ниже, а также документацию dax.guide и статьи, ссылки на которые размещены на SQLBI.com.

Кроме того, мне приходится печатать гораздо больше, чем при использовании простого фильтра.

Для ленивого человека это важная причина не использовать FILTER(), когда это не нужно.

Добавить сложный фильтр

В заключение я хочу показать, что происходит при применении фильтра с помощью функции DAX, например, CONTAINSSTRING().

EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS('Product'[BrandName] ,»Online Sales», [Sum Online Sales] ) ,CONTAINSSTRING('Online Sales'[SalesOrderNumber], «202402252C») )

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

Поскольку результат на данном этапе не важен, давайте сразу посмотрим на статистику запроса:

Хотя выполнение запроса заняло более 6 секунд, 99,6% времени было потрачено фронтендом на выполнение функции CONTAINSSTRING() для поиска совпадающих строк в данных. Эта операция очень ресурсоемка для процессора, поскольку фронтенд может использовать только одно ядро. При выполнении этого запроса на моем ноутбуке это занимает более чем на 2 секунды больше времени.

Я намеренно выбрал медленную функцию, чтобы продемонстрировать её эффекты.

Однако SE всё же смог выполнить запрос с помощью одного запроса. Тем не менее, положительный эффект от этого факта в данном случае незначителен.

Заключение

Хотя я и не ставлю перед собой цель давать вам советы о том, что делать, а чего не делать, я хотел показать вам последствия различных способов написания кода DAX и применения фильтров в ваших мерах или запросах.

DAX-движки очень эффективны в оптимизации запросов, но имеют свои ограничения.

Поэтому при написании кода DAX всегда следует проявлять осторожность.

Если производительность низкая или написанный кем-то другим код выглядит странно, нам следует проанализировать его, чтобы определить, как его улучшить.

Я хотел показать вам, как это сделать и на что следует обращать внимание при анализе вашего DAX-кода.

Помнить:

• Механизм хранения данных (SE) может использовать несколько ядер ЦП.
• Чем больше работы проделан инженером-программистом, тем лучше.
• SE может выполнять только простые агрегации и простые математические функции (например, +, -, x и /).
• Постарайтесь снизить нагрузку на движок Formula Engine (FE).
• Модель FE может использовать только одно ядро ЦП.
• Постарайтесь уменьшить материализацию данных (столбец «Строки» в статистике запроса).
• Постарайтесь уменьшить количество запросов к SE.

Я понимаю, что требования заставят нас писать код на языке DAX, что не является оптимальным решением.

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

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

Но самое главное: не торопитесь при написании кода DAX. Вы можете сэкономить время, избежав необходимости оптимизировать код DAX, написанный наспех. Говорю по собственному опыту. Это очень неприятное чувство.

Надеюсь, вы узнали что-то новое.

Ссылки

Чтобы узнать подробности о том, как интерпретировать результаты измерения времени работы сервера в DAX Studio, прочтите эту статью:

Вам интересно, как правильно использовать функцию FILTER()? Прочитайте это:

Ещё одна функция DAX, которая может негативно повлиять на производительность, — это KEEPFILTERS(). Чтобы узнать больше о функции KEEPFILTERS(), прочтите эту статью:

Вот упомянутая статья о фильтрах по дате:

Интересная статья в блоге Data Mozart о механизме хранения данных:

Как и в моих предыдущих статьях, я использую пример набора данных Contoso. Вы можете бесплатно скачать набор данных ContosoRetailDW с сайта Microsoft здесь.

Данные Contoso можно свободно использовать в соответствии с лицензией MIT, как описано в этом документе. Я изменил набор данных, чтобы привести данные к современным датам.

Источник: towardsdatascience.com