Фредрик Бонневи Далер (старший консультант по технологиям), Ульрика Аксельссон (технологический аналитик) и Миккель Бойе Альгрен (старший консультант по технологиям) в BearingPoint
Многие компании хранят в своих хранилищах большие объемы данных. Чтобы обеспечить удобство работы с пользователем, отчеты должны обладать высокой производительностью и степенью детализации для детализации данных. Однако потребность в почти мгновенном доступе к большим объемам данных во многих случаях трудно согласовать с ограничениями размера наборов данных Power BI в памяти. Создание высокопроизводительных отчетов по большим объемам данных возможно, но для этого требуется сочетание правильного выбора дизайна и надежных принципов моделирования.
Мы также углубимся в некоторые аспекты потенциального изменения правил игры, которое произошло с недавним появлением Fabric, режима Direct Lake для доступа к данным.
В этой статье мы выделяем некоторые важные аспекты, которые следует учитывать при работе с большими объемами данных в Power BI:
- Последствия использования Import или DirectQuery и возможности режима Direct Lake.
- Понимание того, как работает хранилище в Power BI.
- Практические правила уменьшения размера модели.
Выбор между импортом и DirectQuery — это компромисс между потребностями в производительности, гибкостью моделирования и необходимыми усилиями по оптимизации хранилища. Является ли Direct Lake катализатором?
Нередко компании имеют наборы данных транзакций с миллионами строк данных в день, содержащие множество столбцов, многие из которых могут иметь высокую мощность (большой набор уникальных значений). Одним из фундаментальных вариантов проектирования набора данных Power BI является использование режима DirectQuery или импорта для таблиц фактов.
Аспект, который делает это решение трудным, заключается в том, что ни Import, ни DirectQuery не являются безупречными решениями. В зависимости от типа подписки использование режима импорта устанавливает ограничение на объем данных, которые мы можем использовать без значительных усилий по предварительному агрегированию. С другой стороны, DirectQuery не имеет ограничений по размеру базовых исходных данных, но имеет такие недостатки, как более длительное время ответа,ограниченная гибкость моделирования и ограничения на количество строк, которые могут быть возвращены в запросе. . Ниже приведены некоторые соображения при выборе между двумя вариантами:
DirectQuery не загружает большую часть данных в Power BI, но имеет ограничения по производительности и гибкости моделирования
Использование таблиц фактов с DirectQuery имеет несколько последствий. Вот список некоторых из наиболее важных из них:
- Данные загружаются в Power BI только тогда, когда отчет запрашивает базовый источник данных.
- Ограниченное кэширование данных, и каждый объект визуализации отправляет запрос непосредственно в базу данных.
- Каждое взаимодействие с отчетом (сортировка, использование срезов и т. д.) запускает новый запрос.
- Существуют ограничения на количество одновременных запросов, которые можно отправить, и количество возвращаемых строк.
- При использовании DirectQuery существуют ограничения моделирования как в DAX, так и в PowerQuery (для получения дополнительной информации см. эту статью https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations- и-преимущества).
- Высокие затраты на запросы к базовым источникам данных. Затраты на такие решения, как BigQuery и Snowflake, зависят от количества запросов к данным. В зависимости от того, как настроен набор данных Power BI, объем запрашиваемых данных и количество пользователей, установка DirectQuery может повлечь за собой высокие затраты на хранилище данных.
Преимущества DirectQuery:
- Позволяет избежать проблем с загрузкой и хранением данных в больших моделях.
- Данные в отчете немедленно обновляются вместе с изменениями в источнике.
Недостатки DirectQuery:
- Производительность отчета зависит от скорости запроса источника.
- Производительность отчетов снижается при визуализации больших объемов данных.
- При большом количестве одновременных пользователей на взаимодействие с пользователем влияет, если набор данных приближается к пределу по количеству сгенерированных запросов.
- Меньшая гибкость моделирования. Некоторыми последствиями являются увеличение времени подготовки аналитических данных, снижение автономности команд и аналитиков/специалистов по работе с данными, более высокая нагрузка на команды по работе с данными или, в более общем смысле, более сложная демократизация данных.
Для повышения производительности мы можем использовать динамические параметры PowerQuery (m-параметры). Это позволяет применять фильтры на самом внутреннем уровне исходных запросов.
Таблицы агрегации могут быть созданы для импорта-версий часто используемых запросов. Несмотря на то, что этот метод полезен для повышения производительности модели, он не очень хорошо работает для динамических отчетов, где пользователи часто детализируют данные.
Режим импорта обладает высокой производительностью и гибкостью моделирования, но имеет ограничения на размер модели и время обновления данных.
- Существуют ограничения на размер набора данных. Премиум на пользователя имеет ограничение размера в 100 ГБ, а емкость P2 Premium имеет ограничение в 50 ГБ (как и S2 для Azure Analysis Services). На практике ограничения размера составляют примерно половину исходного размера, поскольку половину емкости необходимо выделить во время обновления модели (https://learn.microsoft.com/en-us/power-bi/enterprise/service -премиум-большие-модели#соображения-и-ограничения). Обходные пути могут включать разделение данных на более мелкие наборы данных, но это может быть не идеальным решением, поскольку требует поддержки нескольких наборов данных.
- Импорт данных увеличивает время подготовки новых данных. Обновление набора данных импорта увеличивает время доставки новых данных конечным пользователям. Мы обнаружили, что в некоторых случаях время обновления может достигать 2,5 часов. Секционирование может быть подходящим инструментом для сокращения времени обновления, когда объем данных является основным фактором, влияющим на потребление времени, но в некоторых случаях оказывается, что на самом деле основным драйвером является сжатие столбцов и индексирование. Этой проблемы можно избежать с помощью DirectQuery.
Предположим, что мы предпочитаем гибкость и производительность моделирования, и признаем, что нам необходимо оптимизировать наши наборы данных Power BI, чтобы учесть ограничения хранилища в режиме импорта. Что нам следует учитывать? Для этого можно использовать несколько техник и инструментов. В следующих разделах мы сначала объясним основы работы хранилища в наборах данных Power BI, а затем обсудим методы и инструменты для их оптимизации.
Direct Lake обещает как высокую производительность, так и живое подключение к файлам паркета
В то время как режим DirectQuery медленный, но работает в реальном времени, режим импорта работает быстро, но требует дублирования данных, обновлений для обновления набора данных, а также обслуживания наборов данных Power BI в целом. Режим Direct Lake обещает объединить лучшее из обоих миров.
Режим Direct Lake считывает непосредственно файлы паркета в OneLake (который построен на основе ADLS Gen 2). Это устраняет необходимость в обслуживании наборов данных Power BI и позволяет обновлять отчеты, как только файл паркета изменяется в OneLake. Кроме того, не требуется перевода на другие языки запросов или выполнения в других системах баз данных, что, по мнению Microsoft, приведет к производительности, сопоставимой с режимом импорта.
Источник: Microsoft (https://learn.microsoft.com/en-us/power-bi/enterprise/directlake-overview)
На момент написания этой статьи Fabric и Direct Lake все еще находятся в публичной предварительной версии. Кроме того, чтобы использовать эту функцию, вам необходима лицензия Премиум на емкость или лицензия Microsoft Fabric. Подробности смотрите здесь: https://learn.microsoft.com/en-us/power-bi/enterprise/directlake-overview#preреквизиты.
Еще неизвестно, сможет ли Direct Lake устранить все недостатки двух текущих вариантов или же появятся случаи использования, в которых каждый режим будет иметь преимущество перед другим. Хотя Direct Lake заслуживает пристального внимания, нынешнее полностью доступное решение требует балансировки между режимами импорта и DirectQuery. В следующих разделах мы углубимся в то, как данные хранятся в Power BI и как решать проблемы с хранилищем, когда объем данных становится большим.
Наборы данных Power BI оптимизируют внутреннее хранилище, но существуют также инструменты, упрощающие оптимизацию хранилища вручную
Понимание того, как данные хранятся в Power BI, несколько неясно. Настройка хранилища и производительности наборов данных Power BI требует специальных знаний о том, как работает Power BI. Эта тема часто не рассматривается в учебниках, программах сертификации или документации Power BI. Из-за этого аналитикам данных и инженерам сложно понять, что следует учитывать при настройке наборов данных Power BI для обработки больших объемов данных.
Данные Power BI хранятся в базе данных VertiPaq, которая представляет собой столбчатую базу данных. VertiPaq предоставляет несколько преимуществ. Каждый столбец имеет собственную структуру и физически отделен от других столбцов. Такое разделение позволяет применять эффективные методы сжатия независимо к каждой колонке. В зависимости от значений в конкретном столбце VertiPaq выбирает наиболее подходящий алгоритм сжатия, чтобы минимизировать требования к хранению.
Еще одним важным аспектом является то, что VertiPaq представляет собой базу данных, хранящуюся в памяти. Это означает, что данные загружаются и обрабатываются непосредственно в памяти, что приводит к более быстрому извлечению и анализу данных, но именно поэтому режим импорта имеет ограничения по объему хранилища.
Отличным инструментом для оптимизации наборов данных Power BI является VertiPaq Analyzer (https://www.sqlbi.com/tools/vertipaq-analyzer/). Этот мощный инструмент предоставляет углубленный анализ и рекомендации по оптимизации моделей данных Power BI. Анализируя производительность и сжатие данных механизма VertiPaq, он помогает выявить потенциальные узкие места и предлагает улучшения для увеличения времени ответа на запросы, снижения потребления памяти и оптимизации конструкции модели данных для повышения общей производительности.
Существует несколько методов оптимизации хранения в наборах данных Power BI. Углубленное рассмотрение подходов и соображений описано в блестящей статье: «Как уменьшить размер модели Power BI на 90 %!» (https://towardsdatascience.com/how-to-reduce-your-power-bi model-size-by-90–76d7c4377f2d.) В разделах ниже кратко изложена суть и приведены соответствующие примеры:
Есть несколько способов уменьшить размер модели: уменьшение количества столбцов и мощности являются важными рычагами
Вот несколько общих правил уменьшения размера модели данных:
- Включайте в отчет только необходимые столбцы и строки.
- Оптимизируйте количество столбцов и оцените преимущества разделения столбцов.
- Минимизируйте использование вычисляемых столбцов, поскольку они не сжимаются оптимальным образом.
- Используйте соответствующие типы данных в зависимости от требуемой детализации.
Сосредоточимся на правилах 1 и 2:
- Импортируйте только те столбцы, которые действительно необходимы.
- Это уменьшает размер файла и оптимизирует потребление памяти.
- В важном примере из нашего опыта мы обнаружили столбец «Идентификатор транзакции», который не использовался ни в одной из визуализаций поверх набора данных. Столбец содержал отдельное строковое значение для каждой транзакции и занимал 40 % от общего размера модели.
2. Уменьшите мощность столбца.
- Более высокая мощность затрудняет оптимальное сжатие данных механизму хранения данных.
- Разделите столбцы или измените данные, чтобы уменьшить количество элементов, если это возможно. Типичным примером является разделение столбца Timestamp на дату и время. Если столбец метки времени за год будет иметь максимальную мощность 31 536 000 (365 * 24 * 60 * 60), разделение столбца на отдельные столбцы «Данные» и «Время» уменьшит мощность до 86 765 (365 + 24 * 60 * 60). ). Это сокращение на 98% (!).
- Разделение целочисленных столбцов или десятичных значений может привести к значительной экономии.
- Оптимизируйте столбцы даты и времени, удалив ненужную детализацию, если она соответствует требованиям пользователей.
- По возможности агрегируйте данные, чтобы уменьшить кардинальность и количество строк.
- Отключите параметр «Автоматическая дата/время» для загрузки данных, чтобы исключить ненужные таблицы дат (https://towardsdatascience.com/tiq-part-1-how-to-destroy-your-power-bi-model-with-auto-date- время-8fec32b22aff).
В заключение, управление большими объемами данных в Power BI требует тщательного выбора как при проектировании, так и при реализации. Режим импорта привлекателен, поскольку он обеспечивает производительность и гибкость моделирования, но требует оптимизации размера набора данных. Для достижения этой цели инструмент VertiPaq Analyser и предоставленные практические правила могут помочь гарантировать, что ваш набор данных останется в пределах ограничений по размеру.
Об авторах
Фредрик Бонневи Далер — старший консультант по технологиям в BearingPoint в Осло, имеющий опыт работы с данными и аналитикой в различных отраслях, включая финансовую отрасль, розничную торговлю и продукты обработки данных. Он сосредоточил внимание на бизнес-ориентированном подходе для предоставления информации заинтересованным сторонам. Прежде чем присоединиться к BearingPoint в 2019 году, Фредрик получил степень магистра машиностроения в Норвежском университете науки и технологий. Адрес электронной почты: fredrik.bonnevie.dahler@bearingpoint.
Ульрика Аксельссон — консультант по технологиям в команде данных и аналитики компании BearingPoint в Осло, имеет опыт работы с Power BI в нескольких проектах. Ульрика получила степень магистра промышленной инженерии и менеджмента в Университете Умео в 2020 году. Адрес электронной почты: [email protected].
Миккель Альгрен — старший консультант группы данных и аналитики компании BearingPoint в Осло. Он имеет большой опыт создания коммерческой ценности за счет использования данных и аналитики. Он имеет степень магистра NTNU в области промышленной экономики и управления технологиями со специализацией в области информатики и оптимизации, а также степень бакалавра NHH в области экономики и делового администрирования. Адрес электронной почты: [email protected]
