Power BI и DirectQuery

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

В этой статье рассматриваются следующие вопросы:

  • Различные варианты подключения к данным Power BI.
  • Рекомендации по использованию DirectQuery вместо импорта.
  • Ограничения и последствия использования DirectQuery.
  • Рекомендации по успешному использованию DirectQuery.
  • Диагностика проблем с производительностью DirectQuery.

В этой статье рассматривается рабочий процесс DirectQuery при создании отчета в Power BI Desktop, а также подключение через DirectQuery в служба Power BI.

Примечание

DirectQuery также является функцией SQL Server Analysis Services. Эта функция имеет много общих сведений с Direct Query в Power BI, но есть и важные различия. В этой статье рассматривается в основном DirectQuery с Power BI, а не SQL Server Analysis Services.

Дополнительные сведения об использовании DirectQuery с SQL Server Analysis Services см. в статье Использование DirectQuery для наборов данных Power BI и служб Analysis Services (предварительная версия). Вы также можете скачать PDF-файл DirectQuery в SQL Server 2016 Analysis Services.

Режимы подключения к данным Power BI

Power BI подключается к большому количеству различных источников данных, таких как:

  • Веб-службы, такие как Salesforce и Dynamics 365.
  • Такие базы данных, как SQL Server, Access и Amazon Redshift.
  • Простые файлы в Excel, JSON и других форматах.
  • Другие источники данных, такие как Spark, веб-сайты и Майкрософт Exchange.

Данные из этих источников можно импортировать в Power BI. Для некоторых источников также можно подключиться с помощью DirectQuery. Общие сведения об источниках, поддерживающих DirectQuery, см. в разделе Источники данных, поддерживаемые DirectQuery. Источники с поддержкой DirectQuery в основном являются источниками, которые могут обеспечить высокую производительность интерактивных запросов.

По возможности следует импортировать данные в Power BI. При импорте используется высокопроизводительная подсистема запросов Power BI и предоставляется полнофункциональный интерактивный интерфейс.

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

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

В следующих разделах рассматриваются три варианта подключения к данным: импорт, DirectQuery и динамическое подключение. Оставшаяся часть статьи посвящена DirectQuery.

Подключения с помощью импорта

При подключении к источнику данных, например SQL Server, и импорте данных в Power BI Desktop возникают следующие результаты:

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

  • После загрузки все данные, определенные запросами, импортируются в кэш Power BI.

  • Создание визуального элемента в Power BI Desktop запрашивает кэшированные данные. Хранилище Power BI гарантирует, что запрос выполняется быстро и все изменения в визуальном элементе отражаются немедленно.

  • Визуальные элементы не отражают изменения базовых данных в хранилище данных. Чтобы обновить данные, необходимо повторно импортировать данные.

  • При публикации отчета в служба Power BI в виде PBIX-файла создается и отправляется набор данных, включающий импортированные данные. Затем можно запланировать обновление данных, например каждый день повторно импортировать данные. В зависимости от расположения исходного источника данных может потребоваться настроить локальный шлюз данных для обновления.

  • Открытие существующего отчета или создание нового отчета в служба Power BI повторно запрашивает импортированные данные, обеспечивая интерактивность.

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

Подключения с помощью DirectQuery

При использовании DirectQuery для подключения к источнику данных в Power BI Desktop возникают следующие результаты:

  • Чтобы выбрать источник, используйте команду Получить данные . Для реляционных источников по-прежнему можно выбрать набор таблиц, определяющих запрос, логически возвращающий набор данных. Для многомерных источников, таких как SAP Business Warehouse (SAP BW), выберите только источник.

  • После загрузки данные не импортируются в хранилище Power BI. Вместо этого при создании визуального элемента Power BI Desktop отправляет запросы к базовому источнику данных для получения необходимых данных. Время, необходимое для обновления визуального элемента, зависит от производительности базового источника данных.

  • Любые изменения базовых данных не сразу отражаются в существующих визуальных элементах. Все равно необходимо выполнить обновление. Power BI Desktop повторно отправляет необходимые запросы для каждого визуального элемента и обновляет визуальный элемент при необходимости.

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

  • Открытие существующего отчета или создание нового отчета в служба Power BI запрашивает базовый источник данных для получения необходимых данных. В зависимости от расположения исходного источника данных может потребоваться настроить локальный шлюз данных для получения данных.

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

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

Динамические подключения

При подключении к SQL Server Analysis Services можно импортировать данные или использовать динамическое подключение к выбранной модели данных. Использование активного подключения похоже на DirectQuery. Данные не импортируются, а базовый источник данных запрашивается для обновления визуальных элементов.

Например, при использовании импорта для подключения к SQL Server Analysis Services вы определяете запрос к внешнему источнику SQL Server Analysis Services и импортируете данные. Если вы подключаетесь в режиме реального времени, вы не определяете запрос, и вся внешняя модель отображается в списке полей.

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

  • Наборы данных Power BI, например подключение к набору данных Power BI, который уже опубликован в службе, для создания нового отчета по нему.

  • Microsoft Dataverse.

При публикации SQL Server Analysis Services отчетов, использующих динамические подключения, поведение в служба Power BI похоже на отчеты DirectQuery следующими способами:

  • Открытие существующего отчета или создание нового отчета в служба Power BI запрашивает базовый источник SQL Server Analysis Services, для которого может потребоваться локальный шлюз данных.

  • Плитки панели мониторинга автоматически обновляются по расписанию, например каждый час.

Динамическое подключение также отличается от DirectQuery несколькими способами. Например, динамические подключения всегда передают удостоверение пользователя, открывающего отчет, в базовый источник SQL Server Analysis Services.

Варианты использования DirectQuery

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

DirectQuery в Power BI предоставляет наибольшие преимущества в следующих сценариях:

  • Данные часто изменяются, и вам нужны отчеты практически в реальном времени.
  • Необходимо обрабатывать большие данные без предварительной статистической обработки.
  • Базовый источник определяет и применяет правила безопасности.
  • применение ограничений к независимости данных;
  • источником является многомерный источник, содержащий меры (например, SAP BW).

Данные часто изменяются, и вам нужны отчеты практически в реальном времени

Модели с импортированными данными можно обновлять не чаще одного раза в час с помощью Power BI Pro или Power BI Premium подписок. Если данные постоянно изменяются и отчеты должны отображать последние данные, использование импорта с запланированным обновлением может не соответствовать вашим потребностям. Вы можете выполнять потоковую передачу данных непосредственно в Power BI, хотя в этом случае существуют ограничения на тома данных, поддерживаемые в этом случае.

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

Слишком большой объем данных

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

Вам не всегда нужно импортировать полные подробные данные. Редактор Power Query упрощает предварительное агрегирование данных во время импорта. Технически можно импортировать именно статистические данные, необходимые для каждого визуального элемента. Хотя DirectQuery является самым простым подходом к большим данным, импорт агрегированных данных может предложить решение, если базовый источник данных слишком медленный для DirectQuery.

Эти сведения относятся только к использованию Power BI. Дополнительные сведения об использовании больших моделей в Power BI см. в статье Большие наборы данных в Power BI Premium. Нет никаких ограничений на частоту обновления данных.

Базовый источник определяет правила безопасности

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

DirectQuery позволяет учетным данным средства просмотра отчетов передаваться в базовый источник, который применяет правила безопасности. DirectQuery поддерживает единый вход (SSO) для Azure SQL источников данных, а также через шлюз данных на локальные серверы SQL Server. Дополнительные сведения см. в статье Общие сведения о едином входе (SSO) для шлюзов в Power BI.

применение ограничений к независимости данных;

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

Базовый источник данных использует меры

Базовый источник данных, например SAP HANA или SAP BW, содержит меры. Меры означают, что импортированные данные уже находятся на определенном уровне агрегирования, как определено запросом. Визуальный элемент, запрашивающий данные на более высоком уровне агрегирования, например TotalSales by Year, дополнительно агрегирует статистическое значение. Это агрегирование подходит для аддитивных мер, таких как Sum и Min, но может быть проблемой для не аддитивных мер, таких как Average и DistinctCount.

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

В настоящее время DirectQuery через SAP HANA обрабатывает данные так же, как реляционный источник, и выполняет поведение, аналогичное импорту. Дополнительные сведения см. в разделе DirectQuery и SAP HANA.

Ограничения DirectQuery

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

Общие последствия

Ниже приведены некоторые общие последствия и ограничения использования DirectQuery.

  • Если данные изменяются, необходимо обновить, чтобы отобразить последние данные. Учитывая использование кэшей, нет никакой гарантии, что визуальные элементы всегда будут отображать последние данные. Например, визуальный элемент может отображать транзакции за последний день. Изменение среза может обновить визуальный элемент, чтобы отобразить транзакции за последние два дня, включая последние, недавно полученные транзакции. Но возврат исходного значения среза может привести к тому, что в нем снова будет отображаться кэшированное предыдущее значение. Выберите Обновить , чтобы очистить кэши, и обновить все визуальные элементы на странице, чтобы отобразить последние данные.

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

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

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

  • Чтобы отразить изменения схемы, необходимо обновить Power BI Desktop. После публикации отчета обновление в служба Power BI обновляет визуальные элементы в отчете. Но если базовая исходная схема изменяется, служба Power BI не обновляет список доступных полей автоматически. Если таблицы или столбцы удаляются из базового источника, это может привести к сбою запроса при обновлении. Чтобы обновить поля в модели, чтобы отразить изменения, необходимо открыть отчет в Power BI Desktop и выбрать Обновить.

  • Ограничение в 1 миллион строк может возвращать для любого запроса. Существует фиксированное ограничение в 1 миллион строк, которые могут возвращать в любом отдельном запросе в базовый источник. Это ограничение обычно не имеет практических последствий, и визуальные элементы не отображают столько точек. Однако ограничение может возникнуть в тех случаях, когда Power BI не полностью оптимизирует отправленные запросы и запрашивает промежуточный результат, превышающий ограничение.

    Ограничение также может возникать при создании визуального элемента на пути к более разумному конечному состоянию. Например, включение Customer и TotalSalesQuantity может достичь этого предела, если число клиентов превышает 1 миллион, пока не будет применен какой-то фильтр. Возвращается ошибка: набор результатов запроса к внешнему источнику данных превысил максимально допустимый размер в 100 0000 строк.

    Примечание

    Емкость premium позволяет превысить ограничение в один миллион строк. Дополнительные сведения см. в статье Максимальное число промежуточных наборов строк.

  • Вы не можете изменить модель с импорта на режим DirectQuery. Вы можете переключить модель из режима DirectQuery в режим импорта, если импортировать все необходимые данные. Невозможно вернуться в режим DirectQuery, в первую очередь из-за набора функций, который не поддерживает режим DirectQuery. Для многомерных источников, таких как SAP BW, нельзя также переключиться с DirectQuery на режим импорта из-за разных методов обработки внешних мер.

Влияние на производительность и нагрузку

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

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

Последствия для безопасности

Если базовый источник данных не использует единый вход, отчет DirectQuery всегда использует те же фиксированные учетные данные для подключения к источнику после его публикации в служба Power BI. Сразу после публикации отчета DirectQuery необходимо настроить учетные данные пользователя для использования. Пока вы не настроите учетные данные, попытка открыть отчет в служба Power BI приведет к ошибке.

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

  • При подключении к наборам данных Power BI и службам Analysis Services в режиме DirectQuery всегда используется единый вход, поэтому безопасность аналогична динамическим подключениям к службам Analysis Services.

  • Альтернативные учетные данные не поддерживаются при подключении DirectQuery к SQL Server из Power BI Desktop. Вы можете использовать текущие учетные данные Windows или учетные данные базы данных.

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

Ограничения преобразования данных

DirectQuery ограничивает преобразования данных, которые можно применить в Редактор Power Query. Импортированные данные позволяют легко применить сложный набор преобразований для очистки и изменения их формы, прежде чем использовать их для создания визуальных элементов. Например, можно проанализировать документы JSON или свести данные из столбца в форму строки. Эти преобразования в DirectQuery являются более ограниченными.

При подключении к источнику оперативной аналитической обработки (OLAP), например SAP BW, вы не можете определить какие-либо преобразования, и вся внешняя модель берется из источника. Для реляционных источников, таких как SQL Server, вы по-прежнему можете определить набор преобразований для каждого запроса, но эти преобразования ограничены из соображений производительности.

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

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

Ограничения моделирования

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

  • Определение связей между таблицами.
  • Добавление новых вычислений, таких как вычисляемые столбцы и меры.
  • Переименование и скрытие столбцов и мер.
  • Определение иерархий.
  • Определение форматирования столбцов, формирования сводных данных по умолчанию и порядка сортировки.
  • Группирование или кластеризация значений.

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

Следующие ограничения являются общими для всех источников DirectQuery. К отдельным источникам могут применяться дополнительные ограничения.

  • Нет встроенной иерархии дат: При импортированных данных каждый столбец date/datetime также имеет встроенную иерархию дат, доступную по умолчанию. Например, если вы импортируете таблицу заказов на продажу, содержащую столбец OrderDate, и используете OrderDate в визуальном элементе, можно выбрать соответствующий уровень даты, например год, месяц или день. Эта встроенная иерархия дат недоступна в DirectQuery. Если в базовом источнике есть таблица Date , как это обычно бывает во многих хранилищах данных, можно использовать функции анализа данных (DAX) в обычном режиме.

  • Поддержка даты и времени только на уровне секунд: Для наборов данных, использующих столбцы времени, Power BI отправляет запросы к базовому источнику DirectQuery только до уровня детализации секунд, а не миллисекунд. Удалите данные в миллисекундах из исходных столбцов.

  • Ограничения вычисляемых столбцов: Вычисляемые столбцы могут быть только внутристрочно, то есть они могут ссылаться только на значения других столбцов той же таблицы без использования агрегатных функций. Кроме того, разрешенные скалярные функции DAX, такие как LEFT(), ограничены теми функциями, которые можно отправить в базовый источник. Функции зависят от конкретных возможностей источника. Функции, которые не поддерживаются, не перечислены в автозавершении при создании запроса DAX для вычисляемого столбца и приводят к ошибке при использовании.

  • Нет поддержки функций DAX "родители-потомки": В режиме DirectQuery невозможно использовать семейство DAX PATH() функций, которые обычно обрабатывают структуры "родители-потомки", такие как диаграммы учетных записей или иерархии сотрудников.

  • Без кластеризации: При использовании DirectQuery нельзя использовать возможность кластеризации для автоматического поиска групп.

Ограничения отчетности

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

Одним из общих ограничений является то, что максимальная длина данных в текстовом столбце для наборов данных DirectQuery составляет 32 764 символа. Отчеты о более длинных текстах приводят к ошибке.

Следующие возможности создания отчетов Power BI могут вызвать проблемы с производительностью в отчетах на основе DirectQuery:

  • Фильтры мер: Визуальные элементы, использующие меры или агрегаты столбцов, могут содержать фильтры в этих мерах. Например, на следующем рисунке показана функция SalesAmount по категориям, но только для категорий с более чем 20 млн продаж.

    Снимок экрана: меры, содержащие фильтры

    Этот подход приводит к отправке двух запросов в базовый источник:

    • Первый запрос извлекает категории, которые соответствуют условию SalesAmount больше 20 миллионов.
    • Второй запрос извлекает необходимые данные для визуального элемента, включая категории, соответствующие условию WHERE .

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

  • Фильтры TopN: Вы можете определить расширенные фильтры, чтобы отфильтровать только верхние или нижние N значения, ранжированные по определенной мере. Например, фильтры могут включать 10 основных категорий. Этот подход снова отправляет два запроса в базовый источник. Однако первый запрос возвращает все категории из базового источника, а затем TopN определяется на основе возвращенных результатов. В зависимости от кратности используемого столбца такой подход может привести к проблемам с производительностью или сбоям запросов из-за ограничения на миллион строк в результатах запроса.

  • Средний: Любая статистическая обработка, например Sum или Count Distinct, отправляется в базовый источник. Однако обычно median агрегат не поддерживается базовым источником. Для medianподробные данные извлекаются из базового источника, а медиана вычисляется из возвращаемых результатов. Такой подход является разумным для вычисления медиана относительно небольшого числа результатов.

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

  • Расширенные текстовые фильтры, такие как "contains": Расширенная фильтрация по текстовому столбцу позволяет выполнять такие фильтры, как contains и begins with. Эти фильтры могут привести к снижению производительности некоторых источников данных. В частности, не используйте фильтр по умолчанию contains , если требуется точное соответствие. Хотя результаты могут быть одинаковыми в зависимости от фактических данных, производительность может значительно отличаться из-за индексов.

  • Срезы с несколькими выборами: По умолчанию срезы позволяют делать только один выбор. Разрешение множественного выбора в фильтрах может привести к проблемам с производительностью. Например, если пользователь выбирает 10 интересующих продуктов, каждый новый выбор приводит к отправке запросов в источник. Хотя пользователь и может выбрать следующий элемент до завершения первого запроса, это вызовет дополнительную нагрузку на базовый источник.

  • Итоги по визуальным элементам таблицы: По умолчанию в таблицах и матрицах отображаются итоги и промежуточные итоги. Во многих случаях получение значений для таких итогов требует отправки отдельных запросов в базовый источник. Это требование применяется при использовании DistinctCount агрегирования или во всех случаях, когда используется DirectQuery для SAP BW или SAP HANA. Эти итоги можно отключить с помощью панели Формат .

Рекомендации По DirectQuery

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

Производительность базового источника данных

Убедитесь, что простые визуальные элементы обновляются в течение пяти секунд, чтобы обеспечить разумное интерактивное взаимодействие. Если обновление визуальных элементов занимает больше 30 секунд, вполне вероятно, что дальнейшие проблемы, возникающие после публикации отчета, сделают решение нерабочим.

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

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

  • Для повышения производительности можно использовать связи на основе целочисленных столбцов, а не для объединения столбцов других типов данных.

  • Создайте соответствующие индексы. Создание индекса обычно означает использование индексов хранилища столбцов в источниках, которые их поддерживают, например SQL Server.

  • Обновите все необходимые статистические данные в источнике.

Проектирование модели

При определении модели следуйте этим рекомендациям:

  • Избегайте сложных запросов в Редакторе Power Query. Редактор Power Query преобразует сложный запрос в отдельный SQL-запрос. Один запрос отображается в подзапросе выборки каждого запроса, отправленного в эту таблицу. Если этот запрос будет сложным, это может привести к проблемам производительности каждого отправленного запроса. Вы можете получить фактический SQL-запрос для набора шагов, щелкнув правой кнопкой мыши последний шаг в разделе Примененные шаги в Редактор Power Query и выбрав Просмотреть собственный запрос.

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

  • Избегайте связей в вычисляемых столбцах. В базах данных, в которых необходимо выполнять соединения с несколькими столбцами, Power BI не позволяет создавать связи на основе нескольких столбцов в качестве первичного или внешнего ключа. Распространенный обходной путь — объединение столбцов с помощью вычисляемого столбца и создание соединения на основе этого столбца.

    Это решение является разумным для импортированных данных, но для DirectQuery оно приводит к соединению в выражении. Этот результат обычно не позволяет использовать какие-либо индексы и приводит к снижению производительности. Единственный обходной путь — материализовать несколько столбцов в один столбец в базовом источнике данных.

  • Избегайте связей в столбцах uniqueidentifier. Power BI изначально не поддерживает uniqueidentifier тип данных. Определение связи между uniqueidentifier столбцами приводит к запросу с соединением, которое включает приведение. Это снова вызовет снижение производительности. Единственным обходным решением является материализация столбцов альтернативного типа в базовом источнике данных.

  • Скрытие столбца "to" для связей. Столбец to связей обычно является первичным ключом в to таблице. Этот столбец должен быть скрыт, но если он скрыт, он не отображается в списке полей и не может использоваться в визуальных элементах. Часто столбцы, на которых основаны связи, на самом деле являются системными столбцами, например суррогатными ключами в хранилище данных. По-прежнему лучше скрывать такие столбцы.

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

        ProductKey_PK   (Destination of a relationship, hidden)
        ProductKey (= [ProductKey_PK],   visible)
        ProductName
        ...
    
  • Изучите все вычисляемые столбцы и изменения типов данных. Вычисляемые таблицы можно использовать при использовании DirectQuery с составными моделями. Эти возможности не обязательно вредны, но они приводят к запросам, содержащим выражения, а не простые ссылки на столбцы. Эти запросы могут привести к тому, что индексы не будут использоваться.

  • Избегайте двунаправленной перекрестной фильтрации связей. Использование двунаправленной перекрестной фильтрации может привести к неправильной производительности инструкций запросов. Дополнительные сведения о двунаправленной перекрестной фильтрации см. в статье Включение двунаправленной перекрестной фильтрации для DirectQuery в Power BI Desktop или скачайте технический документ Двунаправленная перекрестная фильтрация. Примеры в документе предназначены для SQL Server Analysis Services, но основные моменты также относятся к Power BI.

  • Попробуйте установить флажок Предполагать целостность данных. Параметр Предполагать целостность ссылок для связей позволяет использовать INNER JOIN запросы, а не OUTER JOIN инструкции. Это руководство обычно повышает производительность запросов, хотя это зависит от особенностей источника данных.

  • Не используйте относительную фильтрацию данных в Редакторе Power Query. Вы можете определить относительную фильтрацию данных в Редакторе Power Query. Например, можно отфильтровать строки, в которых дата находится за последние 14 дней.

    Снимок экрана: фильтрация строк за последние 14 дней.

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

    Снимок экрана: фильтрация строк в собственном SQL-запросе.

    Скорее всего, эти данные не нужны. Чтобы убедиться, что фильтр применяется на основе даты на момент запуска отчета, примените фильтр дат в отчете. Вы можете создать вычисляемый столбец, который вычисляет количество дней назад с помощью DAX DATE() функции , и использовать этот вычисляемый столбец в фильтре.

Создание отчетов

При создании отчета, использующего подключение DirectQuery, следуйте этим рекомендациям:

  • Рассмотрите возможность использования вариантов сокращения запросов: Power BI предоставляет возможности отчета для отправки меньшего количества запросов и отключения определенных взаимодействий, которые приводят к ухудшению взаимодействия, если выполнение результирующих запросов занимает много времени. Эти параметры применяются при взаимодействии с отчетом в Power BI Desktop, а также при использовании отчета пользователями в служба Power BI.

    Чтобы получить доступ к этим параметрам в Power BI Desktop, последовательно выберите Файл>Параметры и настройки>Параметры и выберите Сокращение числа запросов.

    Снимок экрана: параметры сокращения запросов.

    Выбор на экране Сокращение запросов позволяет отобразить кнопку Применить для срезов или фильтров. Запросы не отправляются, пока вы не нажмете кнопку Применить в фильтре или срезе. Затем запросы используют выбранные параметры для фильтрации данных. Эта кнопка позволяет сделать несколько срезов и отфильтровать выбор перед их применением.

  • Сначала примените фильтры: при создании визуального элемента всегда применяйте все возможные фильтры. Например, чтобы не перетаскивать TotalSalesAmount и ProductName, а затем фильтровать по определенному году, примените фильтр к году в начале.

    Каждый этап создания визуального элемента отправляет запрос. Хотя можно внести еще одно изменение до завершения первого запроса, такой подход по-прежнему оставляет ненужную нагрузку на базовый источник. Применение фильтров на раннем этапе обычно делает эти промежуточные запросы менее дорогостоящими. Если не применить фильтры на раннем этапе, это может привести к превышению ограничения в 1 миллион строк.

  • Ограничьте количество визуальных элементов на странице: Когда вы открываете страницу или изменяете срез или фильтр на уровне страницы, все визуальные элементы на странице обновляются. Существует ограничение на количество параллельных запросов. По мере увеличения числа визуальных элементов некоторые визуальные элементы обновляются последовательно, что увеличивает время, необходимое для обновления страницы. Поэтому лучше ограничить количество визуальных элементов на одной странице и вместо этого иметь больше простых страниц.

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

    Снимок экрана: несколько визуальных элементов с перекрестной фильтрацией и перекрестным выделением.

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

    Вы можете использовать параметры сокращения запросов , чтобы отключить перекрестное выделение во всем отчете или в индивидуальном порядке. Дополнительные сведения: Перекрестная фильтрация визуальных элементов в отчете Power BI.

Максимальное число соединений

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

По умолчанию максимальное число одновременных подключений, открываемых DirectQuery, — 10. Чтобы изменить максимальное число текущего файла в Power BI Desktop, перейдите враздел Параметры и параметры>файла>и выберитеDirectQuery в разделе Текущий файл на левой панели.

Снимок экрана: установка максимального количества подключений DirectQuery.

Параметр включен, только если в текущем отчете есть хотя бы один источник DirectQuery. Значение применяется ко всем источникам DirectQuery и ко всем новым источникам DirectQuery, добавленным в этот отчет.

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

После публикации отчета в служба Power BI максимальное количество одновременных запросов также зависит от фиксированных ограничений, установленных в целевой среде, в которой публикуется отчет. Power BI, Power BI Premium и Сервер отчетов Power BI накладывают различные ограничения. В таблице ниже перечислены верхние пределы активных подключений для каждого источника данных для каждой среды Power BI. Эти ограничения применяются к облачным и локальным источникам данных, таким как SQL Server, Oracle и Teradata.

Среда Верхний предел на источник данных
Power BI Pro 10 активных подключений
Power BI Premium 30 активных подключений
Сервер отчетов Power BI 10 активных подключений

Примечание

Максимальное число подключений DirectQuery применяется ко всем источникам DirectQuery при включении расширенных метаданных, что является параметром по умолчанию для всех моделей, созданных в Power BI Desktop.

DirectQuery в службе Power BI

Все источники данных DirectQuery поддерживаются из Power BI Desktop, а некоторые источники также доступны непосредственно в служба Power BI. Например, бизнес-пользователь может использовать Power BI для подключения к своим данным в Salesforce и немедленно получить панель мониторинга без использования Power BI Desktop.

Непосредственно в служба Power BI доступны только следующие два источника с поддержкой DirectQuery:

  • Spark
  • Azure Synapse Analytics (прежнее название — Хранилище данных SQL)

Даже для этих двух источников рекомендуется начать использовать DirectQuery в Power BI Desktop. Хотя изначально установить подключение в служба Power BI легко, существуют ограничения на дальнейшее улучшение результирующего отчета. Например, в службе невозможно создать какие-либо вычисления, использовать множество аналитических функций или обновить метаданные для отражения изменений в базовой схеме.

Производительность отчета DirectQuery в служба Power BI зависит от степени нагрузки на базовый источник данных. Нагрузка зависит от:

  • Количество пользователей, которые совместно используют отчет и панель мониторинга.
  • Сложность отчета.
  • Определяет ли отчет безопасность на уровне строк.

Поведение отчета в служба Power BI

При открытии отчета в служба Power BI все визуальные элементы на текущей видимой странице обновляются. Для каждого визуального элемента требуется по крайней мере один запрос к базовому источнику данных. Для некоторых визуальных элементов может потребоваться более одного запроса. Например, визуальный элемент может отображать статистические значения из двух разных таблиц фактов, содержать более сложную меру или содержать итоги не аддитивной меры, такой как Count Distinct. При переходе на новую страницу эти визуальные элементы обновляются. При обновлении отправляется новый набор запросов к базовому источнику.

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

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

Использование DirectQuery накладывает некоторые важные ограничения на некоторые возможности, которые служба Power BI предлагает для опубликованных отчетов:

  • Быстрая аналитика не поддерживается: Быстрая аналитика Power BI выполняет поиск различных подмножеств набора данных, применяя набор сложных алгоритмов для обнаружения потенциально интересных аналитических сведений. Так как для быстрой аналитики требуются высокопроизводительные запросы, эта функция недоступна в наборах данных, использующих DirectQuery.

  • Использование Explore в Excel приводит к снижению производительности: Вы можете изучить набор данных с помощью функции Обзор в Excel , которая позволяет создавать сводные таблицы и сводные диаграммы в Excel. Эта возможность поддерживается для наборов данных, использующих DirectQuery, но производительность ниже, чем создание визуальных элементов в Power BI. Если использование Excel важно для ваших сценариев, укажите эту проблему при принятии решения о том, следует ли использовать DirectQuery.

  • Excel не отображает иерархии: Например, при использовании анализа в Excel в Excel не отображаются иерархии, определенные в Azure Analysis Services моделях или наборах данных Power BI, использующих DirectQuery.

Обновление панели мониторинга

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

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

Может быть большой эффект мультипликатора. Панель мониторинга с 10 плитками, совместно используемыми для 100 пользователей, созданная в наборе данных с помощью DirectQuery с безопасностью на уровне строк, приводит к отправке не менее 1000 запросов к базовому источнику данных для каждого обновления. Тщательно проанализируйте использование безопасности на уровне строк и настройку расписания обновления.

Истечение времени ожидания запроса

Время ожидания в четыре минуты применяется к отдельным запросам в служба Power BI. Запросы, которые занимают больше четырех минут, завершаются сбоем. Это ограничение предназначено для предотвращения проблем, вызванных слишком длительным временем выполнения. DirectQuery следует использовать только для источников, которые могут обеспечить производительность интерактивных запросов.

Диагностика производительности

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

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

Такой подход изначально устраняет некоторые компоненты, например шлюз Power BI. Если проблемы с производительностью не возникают в Power BI Desktop, можно изучить особенности отчета в служба Power BI.

Анализатор производительности Power BI Desktop — это полезный инструмент для выявления проблем. Старайтесь изолировать все проблемы в одном визуальном элементе, а не в нескольких визуальных элементах на странице. Если один визуальный элемент на странице Power BI Desktop является вялым, используйте анализатор производительности для анализа запросов, которые Power BI Desktop отправляет в базовый источник.

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

Использование SQL Server Profiler для просмотра запросов

По умолчанию Power BI Desktop записывает события во время определенного сеанса в файл трассировки, который называется FlightRecorderCurrent.trc. Файл трассировки находится в папке Power BI Desktop текущего пользователя в папке AnalysisServicesWorkspaces.

Для некоторых источников DirectQuery этот файл трассировки включает все запросы, отправляемые в базовый источник данных. Следующие источники данных отправляют запросы в журнал:

  • SQL Server
  • База данных SQL Azure
  • Azure Synapse Analytics (прежнее название — Хранилище данных SQL)
  • Oracle;
  • Teradata
  • SAP HANA

Файлы трассировки можно считывать с помощью SQL Server Profiler( часть бесплатного скачиваемого SQL Server Management Studio).

Снимок экрана: SQL Server Profiler.

Чтобы открыть файл трассировки для текущего сеанса, выполните следующие действия:

  1. Во время сеанса Power BI Desktop выберите Параметры файла>и параметры>Параметры, а затем выберите Диагностика.

  2. В разделе Сбор аварийных дампов выберите Открыть папку аварийного дампа/трассировки.

    Снимок экрана: ссылка для открытия папки трассировки.

    Откроется папка Power BI Desktop\Traces.

  3. Перейдите в родительскую папку, а затем в папку AnalysisServicesWorkspaces, которая содержит одну папку рабочей области для каждого открытого экземпляра Power BI Desktop. Эти папки содержат в имени суффикс целого числа, например AnalysisServicesWorkspace2058279583. Папка рабочей области удаляется по завершении связанного сеанса Power BI Desktop.

    В папке рабочей области для текущего сеанса Power BI папка \Data содержит файл трассировки FlightRecorderCurrent.trc . Запишите расположение.

  4. Откройте SQL Server Profiler и выберите Файл>Открыть>файл трассировки.

  5. Перейдите к файлу трассировки текущего сеанса Power BI или введите путь к нему и откройте FlightRecorderCurrent.trc.

SQL Server Profiler отображает все события текущего сеанса. На следующем снимках экрана выделена группа событий для запроса. Каждая группа запросов имеет следующие события:

  • Событие Query Begin и Query End , представляющее начало и конец запроса DAX, созданного путем изменения визуального элемента или фильтра в пользовательском интерфейсе Power BI, а также путем фильтрации или преобразования данных в Редактор Power Query.

  • Одна или несколько пар DirectQuery Begin событий и DirectQuery End , которые представляют запросы, отправляемые в базовый источник данных в рамках оценки запроса DAX.

Снимок экрана: SQL Server Profiler с событиями начала и завершения запроса.

Обратите внимание, что несколько запросов DAX могут выполняться параллельно, поэтому события из разных групп могут чередоваться. Значение можно использовать, ActivityID чтобы определить, какие события относятся к одной группе.

Также представляют интерес следующие столбцы:

  • TextData: текстовые сведения о событии. Для Query Begin событий и Query End подробными сведениями является запрос DAX. Для DirectQuery Begin событий и DirectQuery End подробными сведениями является SQL-запрос, отправляемый в базовый источник. TextData для выбранного события также отображается в области в нижней части экрана.
  • EndTime: Время завершения события.
  • Длительность: Длительность выполнения DAX или SQL-запроса (в миллисекундах).
  • Ошибка: Произошла ли ошибка, в этом случае событие также отображается красным цветом.

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

  1. Откройте отдельный сеанс Power BI Desktop (чтобы избежать путаницы с несколькими папками рабочей области).

  2. Выполните необходимые действия в Power BI Desktop. Включите еще несколько действий, чтобы гарантировать, что интересующие события будут сброшены в файл трассировки.

  3. Откройте SQL Server Profiler и проверьте трассировку. Помните, что файл трассировки после закрытия Power BI Desktop будет удален. Кроме того, дальнейшие действия в Power BI Desktop не отображаются немедленно. Чтобы увидеть новые события, необходимо закрыть и повторно открыть файл трассировки.

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

Общие сведения о формате запросов

Общий формат запросов Power BI Desktop использует подзапросы для каждой таблицы, на которые они ссылаются. Запрос Редактор Power Query определяет запросы на вложенную выборку. Например, предположим, что в SQL Server есть следующие таблицы TPC-DS:

Снимок экрана: таблицы TPC-DS в SQL Server.

Выполните следующий запрос:

SalesAmount (SUMX(Web_Sales, [ws_sales_price]*[ws_quantity]))
by Item[i_category]
for Date_dim[d_year] = 2000

Приводит к следующему визуальному элементу в Power BI:

Снимок экрана: визуальный результат запроса.

При обновлении этого визуального элемента создается SQL-запрос, показанный на следующем рисунке. Существует три запроса подзапроса на выборку для Web_Sales, Itemи Date_dim, каждый из которых возвращает все столбцы в соответствующей таблице, хотя визуальный элемент ссылается только на четыре столбца.

Снимок экрана: SQL-запрос, используемый в предоставленном виде.

Редактор Power Query определяет точные запросы на вложенную выборку. Такое использование запросов на подзапросы на выборку не влияет на производительность источников данных, поддерживаемых DirectQuery. Такие источники данных, как SQL Server, оптимизируют ссылки на другие столбцы.

Power BI использует этот шаблон, так как аналитик предоставляет SQL-запрос напрямую. Power BI использует запрос в соответствии с предоставленными данными без каких-либо попыток его перезаписи.

Дальнейшие действия

Дополнительные сведения о DirectQuery в Power BI см. в разделе:

В этой статье описаны аспекты DirectQuery, которые являются общими для всех источников данных. Дополнительные сведения о конкретных источниках см. в следующих статьях: