Параметры динамического запроса M в Power BI Desktop

  • Статья

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

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

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

Примечание.

Это видео может использовать более ранние версии Power BI Desktop или служба Power BI.

Необходимые компоненты

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

Создание и использование динамических параметров

В следующем примере одно значение передается в параметр динамически.

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

  1. В Power BI Desktop выберите "Преобразование данных>дома>", чтобы открыть Редактор Power Query.

  2. В Редактор Power Query выберите "Новые параметры" в разделе "Управление параметрами" на ленте.

    Screenshot that shows the Ribbon menu.

  3. В окне "Управление параметрами" заполните сведения о параметре. Дополнительные сведения см. в разделе "Создание параметра".

    Screenshot that shows parameter information.

  4. Нажмите кнопку "Создать" , чтобы добавить дополнительные параметры.

    Screenshot that shows New to create another parameter.

  5. После добавления параметров нажмите кнопку "ОК".

Ссылка на параметры в запросе M

  1. После создания параметров их можно ссылаться на них в запросе M. Чтобы изменить запрос M, пока выбран запрос, откройте расширенный редактор.

    Screenshot that shows opening the Advanced Editor.

  2. Ссылка на параметры в запросе M, как выделено желтым цветом на следующем рисунке:

    Screenshot that shows referencing the parameter.

  3. После редактирования запроса нажмите кнопку "Готово".

Создание таблиц значений

Создайте таблицу для каждого параметра с столбцом, предоставляющим возможные значения, доступные для динамического задания на основе выбора фильтра. В этом примере необходимо StartTimeEndTime , чтобы параметры были динамическими. Так как для этих параметров требуется Date/Time параметр, вы создаете возможные входные данные для динамического задания даты для параметра.

  1. На ленте Power BI Desktop в разделе "Моделирование" выберите "Создать таблицу".

    Screenshot that shows selecting New table.

  2. Создайте таблицу для значений StartTime параметра, например:

    StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

    Screenshot that shows the first table.

  3. Создайте вторую таблицу для значений EndTime параметра, например:

    EndDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

    Screenshot that shows the second table.

    Примечание.

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

Привязка полей к параметрам

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

  1. Чтобы привязать поле, в представлении модели Power BI Desktop выберите только что созданное поле и в области "Свойства" нажмите кнопку "Дополнительно".

    Примечание.

    Тип данных столбца должен соответствовать типу данных параметра M.

    Screenshot that shows binding the field to a parameter.

  2. Выберите раскрывающийся список в разделе "Привязка к параметру " и выберите параметр, который требуется привязать к полю:

    Screenshot that shows binding the parameter to the field.

    Так как этот пример предназначен для установки параметра на одно значение, сохраните значение "Несколько выборок " значение "Нет", которое является значением по умолчанию:

    Screenshot that shows multi-select set to No.

    Если вы задаете сопоставленный столбец no for Multi-select, необходимо использовать один режим выбора в срезе или требовать один выбор в карта фильтра.

    Если в вариантах использования требуется передать несколько значений в один параметр, задайте для элемента управления значение "Да " и убедитесь, что запрос M настроен для принятия нескольких значений. Ниже приведен пример RepoNameParameterдля нескольких значений:

    Screenshot that shows a multivalue example.

  3. Повторите эти действия, если у вас есть другие поля для привязки к другим параметрам.

    Screenshot that shows configuring more parameters.

Теперь вы можете ссылаться на это поле в срезе или в качестве фильтра:

Screenshot that shows referencing the fields.

Включение выбора всех

В этом примере модель Power BI Desktop имеет поле " Страна", которое является списком стран и регионов, привязанных к параметру M с именем countryNameMParameter. Этот параметр включен для нескольких выборов, но не включен для выбора всех. Чтобы использовать параметр Select all в срезе или фильтре карта, сделайте следующее:

Screenshot that shows an example of a multiselect M parameter.

Чтобы включить выбор всех для страны, выполните приведенные действия.

  1. В расширенных свойствах для страны включите переключатель Select all, который включает ввод всех значений. Измените значение "Выбрать все" или запишите значение по умолчанию.

    Screenshot that shows Select all for an M parameter.

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

  2. Запустите Редактор Power Query, выберите запрос и выберите Расширенный редактор. Измените запрос M, чтобы использовать значение Select all, чтобы ссылаться на параметр Select all .

    Screenshot that shows an M query.

  3. В Расширенный редактор добавьте логическое выражение, которое оцениваетtrue, включен ли параметр для multi-select и содержит значение Select all, а в противном случае возвращает:false

    Screenshot that shows an example Boolean expression for Select all.

  4. Включите результат выражения Select all Boolean в исходный запрос. В примере имеется логический параметр запроса в исходном includeAllCountries запросе, который имеет значение результата логического выражения из предыдущего шага. Этот параметр можно использовать в предложении фильтра в запросе, таким образом, чтобы false для логических фильтров к выбранным странам или регионам true не применялись никакие фильтры.

    Screenshot that shows the Select all Boolean used in the source query.

  5. После обновления запроса M для учетной записи нового значения Select all, можно использовать функцию Select all в срезах или фильтрах.

    Screenshot that shows Select all in a slicer.

Для справки приведен полный запрос к предыдущему примеру:

let
    selectedcountryNames = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      Text.Combine({"'", Text.Combine(countryNameMParameter, "','") , "'"})
    else
      Text.Combine({"'" , countryNameMParameter , "'"}),

    selectAllCountries = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      List.Contains(countryNameMParameter, "__SelectAll__")
    else
      false,

    KustoParametersDeclareQuery = Text.Combine({"declare query_parameters(", 
                                 "startTimep:datetime = datetime(", DateTime.ToText(StartTimeMParameter, "yyyy-MM-dd hh:mm"), "), " , 
                                 "endTimep:datetime = datetime(", DateTime.ToText(EndTimeMParameter, "yyyy-MM-dd hh:mm:ss"), "), ",   
                                 "includeAllCountries: bool = ", Logical.ToText(selectAllCountries) ,",",
                                 "countryNames: dynamic = dynamic([", selectedcountryNames, "]));" }),

   ActualQueryWithKustoParameters = 
                                "Covid19
                                | where includeAllCountries or Country in(countryNames)
                                | where Timestamp > startTimep and Timestamp < endTimep
                                | summarize sum(Confirmed) by Country, bin(Timestamp, 30d)",

    finalQuery = Text.Combine({KustoParametersDeclareQuery, ActualQueryWithKustoParameters}),

    Source = AzureDataExplorer.Contents("help", "samples", finalQuery, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Timestamp", "Date"}, {"sum_Confirmed", "Confirmed Cases"}})
in
    #"Renamed Columns"

Потенциальная угроза безопасности

Читатели отчетов, которые могут динамически задавать значения для параметров запроса M, могут получить доступ к дополнительным данным или активировать изменения в исходной системе с помощью атак внедрения. Эта возможность зависит от того, как ссылаться на параметры в запросе M и какие значения передаются в параметры.

Например, у вас есть параметризованный запрос Kusto, созданный следующим образом:

Products
| where Category == [Parameter inserted here] & HasReleased == 'True'
 | project ReleaseDate, Name, Category, Region

Нет проблем с понятным пользователем, который передает соответствующее значение для параметра, например: Games

| where Category == 'Games' & HasReleased == 'True'

Однако злоумышленник может передать значение, которое изменяет запрос, чтобы получить доступ к дополнительным данным, например: 'Games'//

Products
| where Category == 'Games'// & HasReleased == 'True'
| project ReleaseDate, Name, Category, Region

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

Устранение риска

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

Если источник данных поддерживает импорт хранимых процедур, попробуйте сохранить логику запроса там и вызвать ее в запросе M. Кроме того, при наличии используйте механизм передачи параметров, встроенный в язык исходного запроса и соединители. Например, azure Data Обозреватель имеет встроенные возможности параметров запроса, предназначенные для защиты от атак на внедрение.

Ниже приведены некоторые примеры этих мер по устранению рисков:

  • Пример использования операций фильтрации запроса M:

    Table.SelectRows(Source, (r) => r[Columns] = Parameter)

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

    declare query_parameters (Name of Parameter : Type of Parameter);

  • Пример прямого вызова хранимой процедуры:

    let CustomerByProductFn = AzureDataExplorer.Contents("Help", "ContosoSales"){[Name="CustomerByProduct"]}[Data] in
CustomerByProductFn({1, 3, 5})

Рекомендации и ограничения

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

  • Один параметр нельзя привязать к нескольким полям и наоборот.
  • Параметры динамического запроса M не поддерживают агрегаты.
  • Параметры динамического запроса M не поддерживают безопасность на уровне строк (RLS).
  • Имена параметров не могут быть зарезервированными словами анализа данных (DAX) и не содержат пробелы. Вы можете добавить Parameter в конец имени параметра, чтобы избежать этого ограничения.
  • Имена таблиц не могут содержать пробелы или специальные символы.
  • Если параметр является типом Date/Time данных, необходимо привести его в запрос M как DateTime.Date(<YourDateParameter>).
  • Если вы используете источники SQL, при каждом изменении значения параметра может появиться диалоговое окно подтверждения. Это диалоговое окно связано с параметром безопасности: требуется утверждение пользователя для новых запросов собственной базы данных. Этот параметр можно найти и отключить в разделе "Безопасность" параметров Power BI Desktop.
  • Параметры динамического запроса M могут не работать при доступе к семантической модели в Excel.
  • Параметры динамического запроса M не поддерживаются в Сервер отчетов Power BI.

Неподдерживаемые типы параметров вне поля

  • Любое
  • Длительность
  • True/False
  • Binary

Неподдерживаемые фильтры

  • Относительный срез времени или фильтр
  • Относительная дата
  • Срез иерархии
  • Многофилдовый фильтр включает фильтр
  • Исключение фильтров/ не фильтров
  • Перекрестное выделение
  • Фильтр детализации
  • Перекрестный фильтр детализации
  • Верхний N-фильтр

Неподдерживаемые операции

  • And
  • Содержит
  • Меньше
  • Больше
  • Начинается с
  • Не начинается с
  • Нет
  • Не содержит
  • Пустой
  • Не является пустым

Связанный контент

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