Parâmetros de consulta M dinâmica no Power BI Desktop

Este artigo descreve como criar e trabalhar com parâmetros de consulta M dinâmicos no Power BI Desktop. Com parâmetros de consulta M dinâmicos, os autores de modelo podem configurar os valores de filtro ou segmentação que os visualizadores de relatório podem usar para um parâmetro de consulta M. Os parâmetros de consulta M dinâmicos proporcionam aos autores de modelos mais controle sobre como as seleções de filtro são incorporadas em consultas de origem do DirectQuery.

Os autores de modelo entendem a semântica pretendida de seus filtros e, muitas vezes, sabem como escrever consultas eficientes em sua fonte de dados. Com parâmetros de consulta M dinâmicos, os autores de modelo podem garantir que as seleções de filtro sejam incorporadas em consultas de origem no ponto certo para obter os resultados pretendidos com um desempenho ideal. Os parâmetros de consulta M dinâmicos podem ser especialmente úteis para otimização de desempenho de consulta.

Assista a Sujata explicar e usar parâmetros de consulta M dinâmicos no vídeo a seguir e, em seguida, experimente-os por conta própria.

Pré-requisitos

Para trabalhar nesses procedimentos, você precisa ter uma consulta M válida que use uma ou mais tabelas DirectQuery.

Criar e usar parâmetros dinâmicos

O exemplo a seguir passa um só valor para um parâmetro dinamicamente.

Adicionar parâmetros

1. No Power BI Desktop, selecione Página Inicial>Transformar dados>Transformar dados para abrir o Editor do Power Query.

2. No Editor do Power Query, selecione Novos Parâmetros em Gerenciar Parâmetros na faixa de opções.

   

3. Na janela Gerenciar Parâmetros, preencha as informações sobre o parâmetro. Para obter mais informações, consulte Criar um parâmetro.

   

4. Selecione Novo para adicionar mais parâmetros.

   

5. Quando terminar de adicionar parâmetros, selecione OK.

Referenciar os parâmetros na consulta M

1. Depois de criar os parâmetros, você pode referenciá-los na consulta M. Para modificar a consulta M, abra o Editor avançado enquanto a consulta estiver selecionada.

   

2. Faça referência aos parâmetros na consulta M, como destacado em amarelo na imagem a seguir:

   

3. Quando terminar de editar a consulta, selecione Concluído.

Criar tabelas de valores

Crie uma tabela para cada parâmetro com uma coluna que forneça os valores possíveis disponíveis para serem definidos dinamicamente com base na seleção de filtro. Neste exemplo, você deseja que os parâmetros StartTime e EndTime sejam dinâmicos. Como esses parâmetros exigem um parâmetro Date/Time, você gera as entradas possíveis para definir dinamicamente a data do parâmetro.

1. Na faixa do Power BI Desktop, em Modelagem, selecione Nova tabela.

   

2. Crie uma tabela para os valores do parâmetro StartTime, por exemplo:

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

   

3. Crie uma segunda tabela para os valores do parâmetro EndTime, por exemplo:

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

   

   Observação

   Use um nome de coluna que não esteja em uma tabela real. Se você usar o mesmo nome que uma coluna de tabela real, o valor selecionado se aplicará como um filtro na consulta.

Associar os campos aos parâmetros

Agora que criou as tabelas com os campos Date, você pode associar cada campo a um parâmetro. Associar um campo a um parâmetro significa que, à medida que o valor do campo selecionado é alterado, o valor passa para o parâmetro e atualiza a consulta que faz referência ao parâmetro.

1. Para associar um campo, na exibição Modelo do Power BI Desktop, selecione o campo recém-criado e, no painel Propriedades, selecione Avançado.

   Observação

   O tipo de dados de coluna deve corresponder ao tipo de dados do parâmetro M.

   

2. Selecione a lista suspensa em Associar ao parâmetro e o parâmetro que você deseja associar ao campo:

   

   Como este exemplo é para definir o parâmetro como um único valor, mantenha a Seleção múltipla definida como Não, que é o padrão:

   

   Se você definir a coluna mapeada como Não para Seleção múltipla, deverá usar um modo de seleção única na segmentação ou exigir uma seleção única no cartão de filtro.

   Se os casos de uso exigirem a passagem de vários valores para um parâmetro, defina o controle como Sim e verifique se a consulta M está configurada para aceitar vários valores. Aqui está um exemplo de RepoNameParameter, que permite valores múltiplos:

   

3. Repita estas etapas se precisar associar outros campos a outros parâmetros.

   

Agora, você pode fazer referência a esse campo em uma segmentação ou como um filtro:

Habilitar Selecionar tudo

Neste exemplo, o modelo do Power BI Desktop tem um campo chamado País, que é uma lista de países/regiões associados a um parâmetro M chamado countryNameMParameter. Esse parâmetro está habilitado para Seleção múltipla, mas não para Selecionar tudo. Para poder usar a opção Selecionar tudo em uma segmentação ou cartão de filtro, execute as seguintes etapas adicionais:

Para habilitar Selecionar tudo para País:

1. Nas propriedades Avançadas para País, habilite a alternância Selecionar tudo, que habilita a entrada Selecionar todos os valores. Edite Selecionar todos os valores ou observe o valor padrão.

   

   Selecionar todos os valores é passado para o parâmetro como uma lista que contém o valor definido. Portanto, ao definir esse valor ou usar o valor padrão, certifique-se de que o valor seja exclusivo e não exista no campo associado ao parâmetro.

2. Inicie o Editor do Power Query, selecione a consulta e selecione Editor Avançado. Edite a consulta M para usar Selecionar todos os valores para fazer referência à opção Selecionar tudo.

   

3. No Editor Avançado, adicione uma expressão booliana que será avaliada como true se o parâmetro estiver habilitado para Seleção múltipla e contiver Selecionar todos os valores e, caso contrário, retornará false:

   

4. Incorpore o resultado da expressão booliana Selecionar tudo na consulta de origem. O exemplo tem um parâmetro de consulta booliano na consulta de origem chamado includeAllCountries, que é definido como o resultado da expressão booliana da etapa anterior. Você pode usar esse parâmetro em uma cláusula de filtro na consulta, de modo que false para o booliano seja filtrado para os nomes de país ou região selecionados e true efetivamente não aplique nenhum filtro.

   

5. Depois de atualizar sua consulta M para considerar esse novo valor Selecionar tudo, você pode usar a função Selecionar tudo em segmentações de entrada ou filtros.

   

Para referência, aqui está a consulta completa para o exemplo anterior:

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"

Risco potencial à segurança

Leitores de relatório que podem definir dinamicamente os valores para parâmetros de consulta M poderiam acessar mais dados ou disparar modificações no sistema de origem usando ataques de injeção. Essa possibilidade depende de como você faz referência aos parâmetros na consulta M e quais valores você passa para os parâmetros.

Por exemplo, se você tiver uma consulta Kusto parametrizada construída da seguinte maneira:

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

Não há problemas com um usuário amigável que passe um valor apropriado para o parâmetro, por exemplo, Games:

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

No entanto, um invasor poderia conseguir passar um valor que modifique a consulta para obter acesso a mais dados, por exemplo, 'Games'//:

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

Nesse exemplo, o invasor pode obter acesso a informações sobre jogos que ainda não foram lançados ao alterar parte da consulta em um comentário.

Atenuar o risco

Para reduzir o risco à segurança, evite a concatenação de cadeias de caracteres dos valores de parâmetro M dentro da consulta. Em vez disso, consuma os valores de parâmetro em operações M que se dobram para a consulta de origem, de modo que o mecanismo M e o conector construam a consulta final.

Se uma fonte de dados der suporte à importação de procedimentos armazenados, considere armazenar a lógica de consulta lá e invocá-la na consulta M. Como alternativa, se disponível, use um mecanismo de passagem de parâmetros interno para a linguagem e o conectores de consulta de origem. Por exemplo, o Azure Data Explorer tem recursos internos de parâmetros de consulta criados para proteger contra ataques de injeção.

Estes são alguns exemplos de atenuações:

• Exemplo que usa as operações de filtragem da consulta M:

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

• Exemplo declarando o parâmetro na consulta de origem ou passando o valor do parâmetro como uma entrada para uma função de consulta de origem:

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

• Exemplo de chamada direta de um procedimento armazenado:

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

Considerações e limitações

Há algumas considerações e limitações a serem levadas em conta quando você usa parâmetros de consulta M dinâmicos:

• Um único parâmetro não pode ser associado a vários campos e vice-versa.
• Parâmetros de consulta M dinâmicos não dão suporte a agregações.
• Parâmetros de consulta M dinâmicos não oferecem suporte a RLS (segurança em nível de linha).
• Os nomes dos parâmetros não podem ser palavras reservadas de DAX (Data Analysis Expressions) nem conter espaços. Você pode acrescentar Parameter ao final do nome do parâmetro para ajudar a evitar essa limitação.
• Os nomes de tabela não podem conter espaços nem caracteres especiais.
• Se o parâmetro for do tipo de dados Date/Time, você precisará convertê-lo na consulta M como DateTime.Date(<YourDateParameter>).
• Se estiver usando fontes SQL, você poderá receber uma caixa de diálogo de confirmação sempre que o valor do parâmetro for alterado. Essa caixa de diálogo se deve a uma configuração de segurança: Exigir aprovação do usuário para novas consultas a banco de dados nativo. Você pode encontrar e desativar essa configuração na seção Segurança das Opções do Power BI Desktop.
• É possível que os parâmetros de consulta M dinâmicos não funcionem ao acessar um modelo semântico no Excel.
• Não há suporte a parâmetros de consulta M dinâmicos no Servidor de Relatórios do Power BI.
• Não há suporte para a alternância de fontes de dados usando parâmetros de consulta M dinâmicos no serviço do Power BI. Consulte atualização e fontes de dados dinâmicas para obter informações adicionais.

Tipos de parâmetros prontos para uso sem suporte

• Qualquer
• Duration
• Verdadeiro/Falso
• Binário

Filtros sem suporte

• Segmentação ou um filtro de tempo relativo
• Data relativa
• Segmentação de hierarquia
• Filtro de inclusão de vários campos
• Filtro de exclusão/filtros de negação
• Realce cruzado
• Filtro de drill-down
• Filtro de drill cruzado
• Filtro N superior

Operações sem suporte

• E
• Contém
• Menor que
• Maior que
• Começa com
• Não começa com
• Não é
• Não contém
• Está em Branco
• Não está em branco

Conteúdo relacionado

Para obter mais informações as funcionalidades do Power BI Desktop, confira os seguintes recursos:

• DirectQuery no Power BI
• O que é o Power BI Desktop?
• Visão geral da Consulta no Power BI Desktop
• Tipos de dados no Power BI Desktop
• Tutorial: Formatar e combinar dados no Power BI Desktop
• Realizar tarefas comuns de consulta no Power BI Desktop