Если у вас медленный отчет DirectQuery в Power BI, один из первых вопросов, который вам нужно задать, - это сколько времени нужно для выполнения SQL-запросов, генерируемых Power BI. Однако это более сложный вопрос, чем вы думаете, и в этом посте расскажем почему.
Для начала вспомним: чем отличается получение данных Import от DirectQuery. При импорте мы загружаем данные прямо в файл, при этом локальный pbix будет иметь большой размер, но быстрее загружать данные. Также при таком подключении придётся вручную настраивать таблицы, их взаимодействия и связи между ними. При подключении DirectQuery данные подгружаются порционно, по необходимости, файл имеет малый размер, так как идёт прямое подключение к Вашей базе данных. За счёт этого связи настраивать не нужно, так как учитываются взаимодействия данных в базе данных, но подключение к таким большим данным может занять много времени и так будет каждый раз, когда Вы будете приступать к работе с отчётом.
И так, у нас есть доступ к некоторым известным данным такси Нью-Йорка в базе данных Snowflake, и там есть таблица с данными о поездках, содержащая 173 миллиона строк, из которых можно создать набор данных Power BI. Используемые данные и база данных здесь не особо важны - важно то, что это DirectQuery и большой объем данных. Вот страница отчета с единственной визуальной таблицей, показывающей количество пассажиров, агрегированных по полю лицензии:
Запрос DAX занимает 5,4 секунды, но время прямого запроса составляет всего 3,3 секунды и цифры не складываются. Вот, что фиксирует Profiler для того же обновления, что и в Performance Analyzer:
Это показывает, что между событием DirectQuery End и событием Query End есть промежуток в 2 секунды. Что, если вставить запрос в DAX Studio? Вот, что показывает вкладка Server Timings:
Это другое выполнение запроса по сравнению с двумя примерами выше, оба из которых показывают данные для одного и того же выполнения, что объясняет, почему числа здесь немного отличаются, но опять же, похоже, происходит дополнительная секунда, и DAX Studio предполагает, что это в Formula Engine.
Ответ заключается в понимании того, что на самом деле измеряет событие DirectQuery End Profiler: это количество времени между обработчиком служб Analysis Services, передающим запрос механизму Power Query, и обработчиком служб Analysis Services, получающим обратно первую строку в наборе результатов, включая время, используемое для механизма Power Query, чтобы свернуть запрос.
К сожалению, по событиям Profiler невозможно узнать, сколько времени это займет, но есть другой способ. Возвращаясь к Performance Analyzer, если вы экспортируете данные из него в JSON (нажав кнопку «Экспорт») и загрузите их в Power Query, вы сможете увидеть более подробную информацию о выполнении запроса DirectQuery. Вот данные из первого выполнения выше:
Глядя на запись в столбце метрик для события «Выполнить прямой запрос», вы можете увидеть ту же продолжительность 3,2 секунды, показанную выше в Profiler. Обратите внимание, что здесь также есть два других показателя: RowsRead - общее количество строк, возвращаемых набором результатов; и DataReadDuration, который представляет собой количество времени для чтения этих строк после получения первой строки, а также некоторые другие операции Analysis Services Engine, такие как кодирование значений столбцов, объединение с невымещенными полусоединениями, проекции агрегатов, таких как Среднее, и сохранение набора результатов в кэш в памяти. В этом случае запрос SQL возвратил 43191 строку, и это занимает 1,95 секунды, что объясняет разрыв между концом события «Выполнить прямой запрос» и концом запроса.
Последний вопрос: почему этот SQL-запрос возвращает так много строк, когда запрос DAX запрашивает только верхние 502 строки?
Причина в том, что, по крайней мере, на момент написания, подсистема служб Analysis Services может только подтолкнуть верхнюю (n) операцию к запросу DirectQuery SQL только в очень простых сценариях, где нет мер и агрегации - и в этом случае мы подводим итоги. В результате, если вы используете режим DirectQuery и имеете такой визуальный элемент, который потенциально может отображать большое количество строк и включает меру или агрегированные значения, производительность может снизиться.
Наши курсы по Power BI:
Курс Аналитик BI
Курс DAX Mastering
Курс Финансовый анализ в Power BI
Наши каналы:
Telegram