Узнайте, как реализовать эффективное обновление данных в Power BI
Делиться
«Это сломает свёртывание запроса?» «Свёртывается ли ваш запрос?»… Возможно, кто-то задавал вам эти вопросы, но вы думали: «Запрос… Чтоооо?!»
Или, возможно, вы слышали о свертывании запросов в Power BI, но не знали, как воспользоваться этим преимуществом в реальных ситуациях.
Если вы узнали себя хотя бы в одной из двух ситуаций, указанных выше, то, пожалуйста, продолжайте читать эту статью.
Хорошо, вам любопытно узнать, что такое свёртка запросов. Но, обо всём по порядку… Прежде чем продолжить, нам нужно дать некоторые теоретические основы, которые позволят рассмотреть функцию свёртки запросов в правильном контексте.
Формирование данных
Я уже писал о формировании данных и о том, почему это одна из ключевых концепций на этапе подготовки данных. Теперь я хотел бы подробнее остановиться на этом, возможно, необычным образом:
Думаю, вы все знаете о книге Томаса Мора под названием «Утопия».
В этой истории всё идеально, и все довольны. В идеальном мире, назовём его «Утопией данных», у нас есть чистые, высококачественные данные, которые просто попадают в наши отчёты «как есть», без необходимости какой-либо коррекции или преобразования по ходу дела. К сожалению, «Утопия данных» может существовать только в книгах — реальность гораздо суровее, поскольку нам приходится сталкиваться с многочисленными трудностями, заботясь о наших данных.
При этом одним из ключевых понятий, которые нам необходимо усвоить, является формирование данных. Формирование данных — это процесс, который следует начать применять после того, как вы ознакомитесь со своими данными и осознаете возможные подводные камни, связанные с данными, которые вы планируете использовать в вашем решении для бизнес-аналитики .
Я намеренно использовал термин «Бизнес-аналитика» вместо «Power BI», поскольку это общая концепция, которую следует использовать и за пределами решений Power BI.
Проще говоря, формирование данных — это процесс консолидации данных ДО того, как они станут частью вашей модели данных. Важно помнить слово «ДО»! Таким образом, формирование данных выполняется до того, как они попадут в отчёт. Формирование данных может осуществляться в разных местах и, в зависимости от того, где вы применяете методы формирования данных, на разных этапах процесса подготовки данных.
ГДЕ следует выполнять формирование данных?
Исходная база данных — это самый очевидный выбор и в большинстве случаев наиболее предпочтительный сценарий. Он основан на традиционных принципах хранения данных: извлечение-преобразование-загрузка (ETL). В этом сценарии вы определяете, какие данные хотите извлечь (не все данные из базы данных нужны, и импортировать их все обычно нецелесообразно). Затем вы решаете, нужно ли преобразовывать данные по ходу процесса для лучшего соответствия вашим отчётным требованиям — например, нужно ли конвертировать валюты или согласовывать названия стран и городов.
Узнаете ли вы город на следующем изображении?
Да, это Нью-Йорк. Или это Нью-Йорк? Или это Нью-Йорк-Сити? Какое из этих трёх названий верно? Да, все они верны, но если вы импортируете данные в свою модель таким образом, вы получите неверные результаты, поскольку каждый Нью-Йорк, Нью-Йорк-Сити и Нью-Йорк-Сити будет рассматриваться как отдельная сущность. Этот и многие другие потенциальные проблемы необходимо решить на этапе формирования данных, и именно поэтому важно уделить время обработке данных.
Power Query
Если вы не выполняете преобразования данных на стороне источника, следующим шагом станет Power Query — встроенный инструмент Power BI, позволяющий выполнять любые виды преобразований данных. Согласно официальной документации Microsoft, вы можете применять более 300 различных преобразований!
Ключевое преимущество Power Query заключается в том, что вы можете выполнять сложные преобразования данных, практически не имея навыков программирования! Кроме того, все шаги, которые вы применили в процессе преобразования данных, сохраняются, поэтому при каждом обновлении набора данных эти шаги будут автоматически применяться для формирования данных и подготовки их к использованию в отчётах.
В основе Power Query лежит движок Mashup, обеспечивающий бесперебойную обработку данных. Power Query использует очень мощный язык программирования M для обработки данных. И теперь вы, вероятно, спрашиваете себя: какое отношение вся эта история с шейпингом данных, Power Query, движком Mashup, языком M и т. д. имеет к свёртыванию запросов? Я вас не виню, это справедливый вопрос, но мы скоро вернёмся, чтобы ответить на него.
Что такое сворачивание запроса?
Для некоторых источников данных, таких как реляционные базы данных, а также для нереляционных источников данных, например, OData, AD или Exchange, механизм Mashup способен «переводить» язык M на язык, который будет «понимать» базовый источник данных — в большинстве случаев это SQL.
Передавая сложные вычисления и преобразования непосредственно в источник, Power Query использует возможности надежных реляционных баз данных, которые созданы для максимально эффективной обработки больших объемов данных.
Эту способность движка Mashup Power Query создавать единый оператор SQL, объединяющий все операторы M, лежащие в основе ваших преобразований, мы называем сверткой запросов.
Или, упростим это: если движок Mashup способен сгенерировать один SQL-запрос, который будет выполнен на стороне источника данных, мы говорим, что запрос сворачивается .
Источники данных, поддерживающие свертывание запросов
Как уже упоминалось, наиболее очевидными бенефициарами свёртывания запросов являются реляционные базы данных, такие как SQL Server, Oracle или MySQL. Однако не только базы данных SQL используют концепцию свёртывания запросов. По сути, любой источник данных, поддерживающий какой-либо язык запросов, может использовать преимущества свёртывания запросов. К таким источникам данных относятся OData, SSAS, списки SharePoint, Exchange и Entra ID.
С другой стороны, при использовании в наборах данных Power BI таких источников данных, как файлы Excel, файлы хранилища BLOB, плоские файлы и т. д., запрос не может быть свёрнут.
Преобразования данных, поддерживающие свертывание запросов
Однако, когда речь идёт об источниках данных, которые в целом поддерживают свёртывание запросов, важно помнить, что не все преобразования можно свёртывать и передавать в источник данных. Поэтому, для ясности, тот факт, что база данных SQL поддерживает свёртывание запросов, не обязательно означает, что ваш запрос будет свёрнут! Некоторые преобразования Power Query просто невозможно передать в базу данных SQL.
Очень часто некоторые едва заметные различия в преобразованиях Power Query могут иметь решающее значение для конечного результата и для того, будет ли ваш запрос выполнен или нет. Я покажу вам некоторые из этих едва заметных различий в следующих разделах.
В общем случае следующие преобразования, применяемые в Power Query, можно «перевести» в один оператор SQL:
- Удаление столбцов
- Переименование столбцов
- Фильтрация строк со статическими значениями или параметрами Power Query, поскольку они обрабатываются как предикаты предложения WHERE в SQL
- Группировка и суммирование, которые эквивалентны предложению SQL Group by.
- Объединение свёртываемых запросов, основанных на одном источнике, поскольку эта операция может быть преобразована в JOIN в SQL. Когда я говорил о слиянии свёртываемых запросов, я имел в виду, что это будет работать, если вы объединяете две таблицы SQL-сервера, но не будет работать, если вы пытаетесь объединить таблицу SQL и файл Excel.
- Добавление сворачиваемых запросов, основанных на том же источнике — это преобразование относится к оператору UNION ALL в SQL
- Добавление пользовательских столбцов с простой логикой. Что означает простая логика? Использование функций M, имеющих эквиваленты в языке SQL, например, математических функций или функций обработки текста.
- Преобразования Pivot и Unpivot
С другой стороны, вот некоторые преобразования, которые предотвратят сворачивание запроса:
- Объединение запросов на основе разных источников, как объяснялось ранее
- Добавление (объединение) запросов на основе разных источников — та же логика, что и в предыдущем случае
- Добавление пользовательских столбцов со сложной логикой или использование некоторых функций M, не имеющих аналогов в SQL
- Добавление индексных столбцов
- Изменение типа данных столбца. Это типичный случай «всё зависит от». Скоро я покажу, от чего это зависит, но просто имейте в виду, что изменение типа данных столбца может быть как свёртываемым, так и несвёртываемым преобразованием.
Теперь давайте рассмотрим, почему важно добиться такого поведения — или, может быть, лучше сказать, почему вас должно волновать, свернут запрос или нет?
Почему вам следует обратить внимание на свертывание запросов?
При использовании режима импорта в Power BI процесс обновления данных будет работать более эффективно при сворачивании запроса как с точки зрения скорости обновления, так и потребления ресурсов.
Если вы работаете с режимом DirectQuery или Dual Storage, то, поскольку вы обращаетесь напрямую к базе данных SQL, все ваши преобразования ДОЛЖНЫ быть свернуты, иначе ваше решение не будет работать.
Наконец, свёртывание запросов также имеет ключевое значение для инкрементального обновления — оно настолько важно, что Power BI выдаст предупреждение, как только определит, что свёртывание запросов невозможно. Это не нарушит инкрементальное обновление как таковое, но без свёртывания запросов инкрементальное обновление не будет выполнять свою основную задачу — сократить объём данных, которые необходимо обновить в вашей модели данных, — поскольку без свёртывания запросов движку Mashup необходимо извлечь все данные из источника, а затем применить последующие шаги для фильтрации данных.
Учитывая все это, вам следует стремиться к сворачиванию запросов везде, где это возможно.
Медленный отчет — не вините сворачивание запроса!
Здесь есть одно важное предупреждение, и это один из ключевых выводов из этой серии сообщений в блоге: если ваш отчет работает медленно, или визуализация ваших визуальных элементов занимает много времени, или размер вашей модели данных велик, то свертывание запросов не имеет к этому никакого отношения!
Только в том случае, если обновление данных или инкрементное обновление выполняется медленно и неэффективно, вам следует более подробно изучить этапы работы с Power Query.
Все или ничего?
Ещё несколько моментов, которые следует учитывать при свёртывании запросов. Это не процесс типа «всё или ничего». Это означает, что если у вас есть, скажем, 10 этапов преобразования в Power Query, и ваш запрос свёртывается до 6-го этапа, вы всё равно получите некоторую выгоду от частичного свёртывания запроса. Однако, как только свёртывание запроса нарушается, добиться этого уже невозможно.
Упрощённо: если у вас есть 10 шагов преобразования, и свёртывание запроса нарушается на 5-м шаге, все предыдущие шаги будут свёрнуты, но как только свёртывание нарушается, его невозможно будет восстановить, даже если у вас есть преобразования, которые по умолчанию поддерживают свёртывание запроса на шагах с 6 по 10 — как в нашем примере, где фильтрация должна быть свёртываемым шагом, эти шаги не будут свёрнуты. Имейте это в виду и старайтесь максимально отодвинуть все несвёртываемые шаги вниз по конвейеру.
Как узнать, свернут ли запрос?
Итак, теперь мы уже не новички. Мы знаем, что такое свёртывание запросов, почему стоит к нему стремиться, и знаем несколько тонких приёмов, которые могут иметь огромное значение.
Теперь пора узнать, как проверить, сворачивается ли конкретный запрос. Первый и самый очевидный способ — щёлкнуть правой кнопкой мыши по шагу и посмотреть, как выглядит опция «Просмотреть собственный запрос».
Если он неактивен, этот шаг, вероятно, не свернётся. С другой стороны, если вы можете нажать на эту опцию, это означает, что ваш запрос будет свёрнут. Полагаю, вы путаете слово «ВЕРОЯТНО»!
Но это верное слово, поскольку нельзя быть на 100% уверенным, что если опция «Просмотр собственного запроса» отключена, ваш запрос не будет свёрнут. Позже я покажу, как эта опция может заставить нас думать, что свёртывание запроса нарушено, хотя на самом деле свёртывание действительно происходит.
Вместо этого, если вы хотите убедиться, сворачивается ли ваш запрос, вы можете использовать функцию диагностики запросов в редакторе Power Query Editor или SQL Server Profiler, как старый добрый и надежный способ проверки запросов, отправляемых в базу данных модулем Power BI.
Кроме того, в Power Query Online есть интересная функция: каждый шаг помечается значком, показывающим, свёртывается ли этот шаг, не свёртывается или неизвестен. Как я уже говорил, на данный момент эта функция доступна только в Power Query Online, поэтому будем надеяться, что команда Power BI вскоре добавит её в версию для ПК.
Дьявол кроется в деталях…
Ладно… Вы, наверное, слышали поговорку «дьявол кроется в деталях». Теперь пора понять, как незначительные нюансы могут иметь огромное значение в процессе преобразования данных.
Начнем с одного из самых любопытных случаев в редакторе Power Query…
Дьявол №1 — Объединение слиянием
Это очень интересно, поскольку сложно предположить, что происходит в фоновом режиме. Допустим, я хочу объединить два запроса в один. Я буду использовать базу данных Adventure Works и мне нужно объединить таблицы FactInternet Sales и DimCustomer.
Я удалю некоторые столбцы из таблицы фактов, оставив только столбец CustomerKey, поскольку он является внешним ключом к таблице DimCustomer, и столбец Sales Amount. Я присоединю таблицу DimCustomer как есть, без каких-либо дополнительных действий перед слиянием.
Объединение таблиц эквивалентно операции JOIN в SQL. По сути, мы выбираем столбец, к которому хотим применить операцию MERGE, и тип объединения (левое, внешнее или внутреннее).
Проблема в том, что по умолчанию при объединении двух запросов Power Query генерирует вложенный оператор соединения, который невозможно корректно перевести в SQL.
Если я перейду на вкладку «Инструменты» и нажму «Шаг диагностики», то увижу, что механизм Mashup выполнил два отдельных запроса к моей базовой базе данных SQL Server — другими словами, эти два запроса не могут быть выполнены как один оператор SQL, а это значит, что этот запрос не был свернут!
Как решить эту проблему? Давайте просто выберем пустой запрос и напишем код M вручную, чтобы добиться точно такого же результата.
Главное то, что мы будем использовать похожую, но все же другую функцию M: Table.Join .
Все аргументы функции точно такие же, как и раньше, давайте теперь проверим результат.
Помните, я как-то говорил вам, что когда View Native Query неактивен, ваш запрос, вероятно, не сворачивается, но он не на 100% правильный? И это хороший пример. Если вы посмотрите на View Native Query, он всё равно покажет, что наш запрос не сворачивается…
…но давайте перейдем к диагностике и проверим, так ли это.
Ох, нас обманули — этот шаг действительно провалился! Как видно на иллюстрации выше, мы сгенерировали один SQL-запрос и отправили его в исходную базу данных SQL Server для выполнения.
Итак, в этом примере мы обнаружили две проблемы: первая — тип соединения, который мы смогли решить, подправив автоматически сгенерированный код M. Вторая — некорректное поведение опции View Native Query. В следующей части серии я покажу вам ещё один пример, когда View Native Query лжёт.
Сворачивание запросов в Power BI — уловки, обман и окончательный тест производительности
Полагаю, вы уже знакомы с концепцией свёртывания запросов в Power BI, особенно с её важностью для процессов обновления данных и инкрементального обновления. Мы также начали изучать некоторые интересные особенности поведения преобразований Power Query, и в этой заключительной части статьи я покажу вам ещё несколько интересных выводов.
Наконец, мы завершим это финальным тестом производительности — я покажу вам точные цифры для двух одинаковых запросов — один сворачивается, а другой нет!
Изменение типов данных
Одно из самых распространённых преобразований в Power Query — это изменение типа данных. Рекомендуется использовать правильные типы данных в модели данных. Например, если вам не нужна детализация по часам, минутам и секундам в отчётах, лучше от них избавиться и изменить тип данных соответствующего столбца с «Дата/время» на «Только дата».
Однако благими намерениями вымощена дорога в ад:)… Итак, позвольте мне показать вам одно тонкое отличие, которое может сделать ваш запрос чертовски медленным, даже если вы придерживаетесь рекомендации использовать правильный тип данных!
Как видно на иллюстрации выше, мой столбец OrderDate имеет тип данных «Дата/Время». И я хочу изменить его на «Только дата». Есть (как минимум) два возможных способа сделать это. Первый — щелкнуть правой кнопкой мыши по столбцу, раскрыть раскрывающийся список «Изменить тип» (как я сделал на иллюстрации) и выбрать тип «Дата» (сразу под полем «Дата/Время»).
Здесь произошло несколько важных событий, поэтому позвольте мне объяснить каждое из них:
- На панели «Примененные шаги» вы можете заметить, что наш шаг преобразования был записан.
- В самом столбце вы можете увидеть, что часть времени исчезла.
- Когда я открыл диалоговое окно «Просмотр собственного запроса», вы увидели, что движок Mashup успешно перевел наше преобразование в функцию T-SQL CONVERT()
- Формула M, применяемая к этому этапу преобразования, выглядит так: Table.TransformColumnTypes()
Давайте теперь рассмотрим другой вариант изменения типа данных нашего столбца:
Чуть ниже нашего предыдущего параметра «Изменить тип» находится параметр «Преобразовать». Развернув раскрывающийся список, вы увидите параметр «Только дата». Давайте нажмём на него и посмотрим, что произойдёт:
Выглядит очень похоже, правда? Но давайте разберёмся, что произошло сейчас:
- Вместо шага «Измененный тип» теперь у нас есть шаг, называемый «Извлеченная дата».
- Сам столбец выглядит точно так же, как в предыдущем примере — в нем нет части времени.
- Упс, запрос больше не сворачивается! Как видите, опция «Просмотреть собственный запрос» неактивна!
- На этот раз применена формула M: Table.TransformColumns()
Итак, одно-единственное отличающееся слово в формуле M (Table.TransformColumnTypes вместо Table.TransformColumns) настолько сильно повлияло на наш запрос, что его невозможно было перевести в SQL!
Вывод из этой истории: будьте осторожны и внимательны при выборе вариантов изменения типов данных!
Лжец, лжец…
В предыдущей части статьи я обещал показать вам еще один пример, когда опция «Просмотреть собственный запрос» может заставить вас подумать, что сворачивание запросов нарушено, даже если на самом деле это не так…
Предположим, мы хотим сохранить только X верхних строк нашей таблицы. В моём случае я хочу сохранить 2000 верхних строк таблицы фактов:
После того как я применил этот шаг и проверил View Native Query, я могу заметить, что мой запрос сворачивается, поскольку мое преобразование было переведено в предложение TOP в SQL:
Предположим, я хочу применить преобразование «Абсолютное значение» к столбцу «Объем продаж». Обычно это преобразование легко выполняется, поскольку в T-SQL есть функция ABS:
Однако если я щелкну правой кнопкой мыши на этом шаге, то увижу, что опция «Просмотреть собственный запрос» неактивна, поэтому я предполагаю, что этот шаг нарушил сворачивание моего запроса!
Давайте проверим это в нашем инструменте диагностики запросов:
О боже! Этот шаг действительно провалился! Значит, нас снова обманула опция «Просмотр собственного запроса»!
Ключевой момент здесь такой: всякий раз, когда вы предполагаете, что определенный шаг преобразования может быть свёрнут (как в этом примере, когда мы знали, что SQL имеет функцию ABS для поддержки нашего преобразования), дважды проверьте, что на самом деле происходит «под капотом»!
Окончательный тест производительности
Хорошо, если мне до сих пор не удалось убедить вас в том, почему вам следует стремиться к сворачиванию запросов, позвольте мне теперь вытащить мой последний туз из рукава!
Я хочу показать вам разницу в производительности обновления данных между запросами, которые возвращают абсолютно одинаковые результаты — один из них сворачивается, а другой — нет!
Тест №1. Сворачивание запросов ВКЛ.
Для этого тестирования я буду использовать таблицу FactOnlineSales из учебной базы данных Contoso. Эта таблица содержит около 12,6 миллионов строк и хорошо демонстрирует важность концепции свёртки запросов.
В первом примере я применил 9 различных шагов преобразования, и все они являются складными, как вы можете видеть на следующей иллюстрации:
Не обращайте внимания на код SQL, сгенерированный движком Mashup: если вы профессионал в области SQL, вы, конечно, можете написать гораздо более оптимальный код SQL. Однако имейте в виду, что при использовании автоматически сгенерированных движком Mashup скриптов вы не получаете самый оптимальный SQL — вы просто получаете правильный SQL !
Я нажму «Закрыть и применить» и включу секундомер, чтобы измерить, сколько времени длится обновление данных.
Этот запрос загрузил 2,8 миллиона записей в мой отчёт Power BI за 32 секунды. Данные загружались партиями по 100 000–150 000 записей, что является хорошим показателем того, что свёртывание запросов выполнено правильно.
Тест №2. Сворачивание запросов отключено.
Теперь я вернусь в редактор Power Query и намеренно разорву свертывание запроса на 3-м шаге (вспомните пример выше с изменением типа «Дата/время» на «Дата»), используя преобразование, для которого я знаю, что оно не сворачивается:
По правде говоря, здесь я добьюсь частичного сворачивания, поскольку первые два шага будут сворачиваться, но все последующие шаги после преобразования «Извлеченная дата» не будут сворачиваться!
Давайте снова включим секундомер и посмотрим, что произойдет:
Первое, на что стоит обратить внимание: загрузка этого запроса в наш отчёт Power BI заняла 4 минуты 41 секунду, что примерно в 10 раз больше , чем в предыдущем случае, когда запрос был свёрнут. На этот раз объём загружаемых данных составлял от 10 000 до 20 000 записей.
Но что ещё более тревожно — вы видите, что общее количество загруженных записей составило почти 11 миллионов!!! Вместо 2,8 миллиона в предыдущем примере! Почему это происходит? В предыдущих разделах я объяснял, что когда движок Mashup не может преобразовать язык M в SQL, ему нужно извлечь ВСЕ данные (с момента, когда свёртывание запроса было нарушено), а ПОТОМ применить преобразования ко всему массиву импортированных данных!
Конечный результат точно такой же — в нашем отчёте Power BI 2 830 017 записей, — но при использовании свёртки запросов все необходимые преобразования были выполнены на стороне базы данных SQL, и движок Mashup получил уже подготовленный набор данных. В то время как во втором сценарии, после того как мы прервали свёртку запросов, движок Mashup извлёк все оставшиеся строки (примерно 11 миллионов) и только после этого смог применить другие этапы преобразования.
И это был всего лишь базовый пример с одной-единственной таблицей, да и объём данных не так уж велик! Только представьте себе масштаб последствий для более крупного набора данных, содержащего несколько таблиц.
Заключение
Что ж, в этой статье мы рассмотрели многое. Мы узнали о концепции формирования данных, познакомились с основами Power Query, а также узнали, что такое свёртывание запросов и почему нам следует приложить все усилия для его реализации.
Я также поделился с вами некоторыми базовыми примерами и полезными приемами того, как добиться свертывания запросов в некоторых распространенных случаях использования.
В заключение, имейте в виду, что свёртывание запросов находится в стадии разработки, и команда Power BI постоянно совершенствует эту функцию. Поэтому некоторые из проблем со свёртыванием запросов, которые я вам здесь показал, могут быть уже решены. Поэтому обязательно следите за последними улучшениями.
Спасибо за прочтение!
Источник: towardsdatascience.com
