Поделиться через

DirectQuery в Power BI

  • Статья

В 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. Эта функция содержит множество сведений с DirectQuery в Power BI, но существуют также важные различия. В этой статье в основном рассматриваются DirectQuery с Power BI, а не SQL Server Analysis Services.

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

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

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

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

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

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

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

Возможности импорта и DirectQuery Power BI развиваются со временем. Изменения, обеспечивающие большую гибкость при использовании импортированных данных, позволяют чаще импортировать и устранять некоторые недостатки использования 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 поддерживает единый вход (единый вход) в источники данных SQL Azure и через шлюз данных для локальных серверов SQL. Дополнительные сведения см. в разделе "Обзор единого входа" для локальных шлюзов данных в Power BI.

Применяются ограничения на суверенитет данных

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

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

Базовый источник данных, например SAP HANA или SAP BW, содержит меры. Меры означают, что импортированные данные уже имеют определенный уровень агрегирования, как определено запросом. Визуальный элемент, который запрашивает данные на более высоком уровне агрегирования, например TotalSales на год, дополнительно агрегирует совокупное значение. Эта агрегирование хорошо подходит для аддитивных мер, таких как 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 миллионов клиентов, пока не применить какой-то фильтр. Ошибка, возвращаемая: набор результатов запроса к внешнему источнику данных превысил максимальный допустимый размер строк "1000000".

    Примечание.

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

  • Невозможно изменить модель из импорта в режим 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. Если в базовом источнике есть таблица даты , как и в большинстве хранилищ данных, можно использовать функции аналитики времени (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, а затем отфильтруйте до определенного года, примените фильтр к году в начале.

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

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

  • Рассмотрите возможность отключения взаимодействия между визуальными элементами. По умолчанию визуализации на странице отчета можно использовать для перекрестного фильтрации и перекрестного выделения других визуализаций на странице. Например, если выбрать 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 Зависит от ограничения SKU семантической модели
Сервер отчетов 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.

  • Использование функции "Изучение в 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. В разделе "Коллекция аварийного дампа" выберите папку Open crash dump/traces.

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

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

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

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

  4. Откройте SQL Server Profiler и выберите файл Open Trace File(Открыть>>файл трассировки).

  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, которые являются общими для всех источников данных. Дополнительные сведения о конкретных источниках см. в следующих статьях: