Plano de consulta para o Power Query

O plano de consulta para o Power Query é um recurso que fornece uma visão melhor da avaliação da consulta. É útil ajudar a determinar por que uma consulta específica pode não dobrar em uma etapa específica.

Por meio de um exemplo prático, este artigo demonstra o caso de uso principal e os benefícios potenciais de usar o recurso de plano de consulta para examinar as etapas de consulta. Os exemplos usados neste artigo foram criados usando o banco de dados de exemplo AdventureWorksLT para o SQL Server do Azure, que você pode baixar dos bancos de dados de exemplo do AdventureWorks.

Este artigo é dividido em uma série de etapas recomendadas para interpretar o plano de consulta. Essas etapas são:

1. Examine os indicadores de dobragem de consulta.
2. Selecione a etapa de consulta para examinar seu plano de consulta.
3. Implemente alterações na consulta.

Use as etapas a seguir para criar a consulta em seu próprio ambiente do Power Query Online.

1. No Power Query – Escolha a fonte de dados, selecione a consulta em branco.

2. Substitua o script da consulta em branco pela consulta a seguir.

   let
       Source = Sql.Database("servername", "database"),
       Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
       #"Removed other columns" = Table.SelectColumns(
           Navigation,
           {
               "SalesOrderID",
               "OrderDate",
               "SalesOrderNumber",
               "PurchaseOrderNumber",
               "AccountNumber",
               "CustomerID",
               "TotalDue"
           }
       ),
       #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
       #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
   in
       #"Kept bottom rows"
   

3. Altere servername e database com os nomes corretos para seu próprio ambiente.

4. (Opcional) Se você estiver tentando se conectar a um servidor e banco de dados para um ambiente local, configure um gateway para esse ambiente.

5. Selecione Próximo.

6. No Editor do Power Query, selecione Configurar conexão e forneça as credenciais para sua fonte de dados.

Depois de seguir estas etapas, sua consulta será semelhante à da imagem a seguir.

Essa consulta se conecta à tabela SalesOrderHeader e seleciona algumas colunas dos últimos cinco pedidos com um valor TotalDue acima de 1000.

Reveja os indicadores de agrupamento da consulta

Sua primeira etapa nesse processo é examinar sua consulta e prestar muita atenção aos indicadores de dobragem de consulta. A meta é examinar as etapas marcadas como não dobradas. Em seguida, você pode ver se fazer alterações na consulta como um todo poderia levar essas transformações a se integrarem completamente.

Para este exemplo, a única etapa que não pode ser dobrada é Manter linhas inferiores, o que é fácil de identificar por meio do indicador de etapa não dobrada . Essa etapa também é a última etapa da consulta.

O objetivo agora é revisar essa etapa e entender o que está sendo reintegrado à fonte de dados e o que não pode ser reintegrado.

2. Selecione a etapa de consulta para examinar seu plano de consulta

Você identificou a etapa Linhas Inferiores Mantidas como uma etapa de interesse, pois ela não se dobra de volta para a fonte de dados. Clique com o botão direito do mouse na etapa e selecione a opção Exibir Plano de Consulta . Essa ação exibe uma nova caixa de diálogo que contém um diagrama para o plano de consulta da etapa selecionada.

O Power Query tenta otimizar sua consulta aproveitando a avaliação lenta e a dobragem de consultas, conforme mencionado nos conceitos básicos de dobragem de consulta. Esse plano de consulta representa a tradução otimizada da consulta M para a consulta nativa enviada à fonte de dados. Ele também inclui todas as transformações executadas pelo Mecanismo do Power Query. A ordem na qual os nós são exibidos segue a ordem da consulta a partir da última etapa ou saída da consulta, que é representada na extrema esquerda do diagrama. Nesse caso, é o nó Table.LastN que representa a etapa Linhas inferiores mantidas.

Na parte inferior da caixa de diálogo, há uma barra com ícones que ajudam você a ampliar ou reduzir a exibição do plano de consulta e outros botões para ajudá-lo a gerenciar a exibição. Para a imagem anterior, a opção Ajustar para exibir dessa barra foi usada para apreciar melhor os nós.

Identificar nós dobrados de outros nós

Você pode identificar os nós neste diagrama como dois grupos:

• Nós dobrados: esse nó pode ser Value.NativeQuery nós de "fonte de dados", como Sql.Database. Esses nós também podem ser identificados com o rótulo remoto em seu nome de função.
• Nós não dobrados: outros operadores de tabela, como Table.SelectRows, Table.SelectColumnse outras funções que não puderam ser dobradas. Esses nós também podem ser identificados com os rótulos Verificação completa e Transmissão.

A imagem a seguir mostra os nós dobrados dentro do retângulo vermelho. O restante dos nós não pôde ser reintegrado à fonte de dados. Você precisa revisar o restante dos nós, pois o objetivo é tentar fazer com que esses nós se integrem novamente à fonte de dados.

Você pode selecionar Exibir detalhes na parte inferior de alguns nós para exibir informações estendidas. Por exemplo, os detalhes do nó Value.NativeQuery mostram a consulta nativa (em SQL) que é enviada para a fonte de dados.

A consulta mostrada aqui pode não ser exatamente a mesma consulta enviada à fonte de dados, mas é uma boa aproximação. Para esse caso, ele informa exatamente quais colunas são consultadas da tabela SalesOrderHeader. Em seguida, como ele filtra essa tabela usando o campo TotalDue para obter apenas linhas em que o valor desse campo é maior que 1000. O nó ao lado dele, Table.LastN, é calculado localmente pelo mecanismo do Power Query, pois não pode ser dobrado.

Revisar nós não dobrados e considerar ações para fazer sua transformação dobrar

Agora você determinou quais nós não puderam ser dobrados e podem ser avaliados localmente. Esse caso tem apenas o nó Table.LastN, mas em outros cenários poderia ter muitos mais.

O objetivo é aplicar alterações à consulta para que a etapa possa ser simplificada. Algumas das alterações que você pode implementar podem variar desde reorganizar suas etapas até aplicar uma lógica alternativa à consulta mais explícita à fonte de dados. Isso não significa que todas as consultas e todas as operações sejam dobráveis aplicando algumas alterações. Mas é uma boa prática determinar por tentativa e erro se sua consulta pode ser revertida.

Como a fonte de dados é um banco de dados do SQL Server, se a meta for recuperar os últimos cinco pedidos da tabela, uma boa alternativa seria aproveitar as cláusulas TOP e ORDER BY no SQL. Como não há nenhuma cláusula BOTTOM no SQL, a Table.LastN transformação no PowerQuery não pode ser convertida em SQL. Você pode remover a Table.LastN etapa e substituí-la por:

• Um passo de classificação decrescente pela coluna SalesOrderID na tabela, uma vez que esta coluna determina qual pedido vem primeiro e qual foi inserido por último.
• Selecione as cinco primeiras linhas após a tabela ser classificada, essa transformação realiza o mesmo que se fosse Manter linhas inferiores (Table.LastN).

Essa alternativa é equivalente à consulta original. Embora essa alternativa em teoria pareça boa, você precisa fazer as alterações para ver se essa alternativa faz com que esse nó volte totalmente para a fonte de dados.

3. Implementar alterações na consulta

Implemente a alternativa discutida na seção anterior:

1. Feche a caixa de diálogo do plano de consulta e volte para o Editor do Power Query.

2. Remova a etapa Linhas Inferiores Mantidas.

3. Classifique a coluna SalesOrderID em ordem decrescente.

   

4. Selecione o ícone de tabela no canto superior esquerdo da exibição de visualização de dados e selecione a opção que lê Manter linhas superiores. Na caixa de diálogo, passe o número cinco como o argumento e pressione OK.

   

Depois de implementar as alterações, verifique novamente os indicadores de dobramento de consulta e veja se ele está mostrando um indicador dobrado.

Agora é hora de examinar o plano de consulta da última etapa, que agora é Manter as primeiras linhas. Agora só há nós dobrados. Selecione Exibir detalhesValue.NativeQuery para verificar qual consulta está sendo enviada ao banco de dados.

Embora este artigo sugira qual alternativa a ser aplicada, a meta principal é que você aprenda a utilizar o plano de consulta para investigar a otimização de consultas. Este artigo também fornece visibilidade do que está sendo enviado para sua fonte de dados e quais transformações são feitas localmente.

Você pode ajustar seu código para ver o impacto que ele tem em sua consulta. Usando os indicadores de dobramento de consulta, você também tem uma ideia melhor de quais etapas estão impedindo que sua consulta seja dobrada.