Plano de consulta para o Power Query

O plano de consulta para o Power Query é uma funcionalidade que fornece uma melhor vista da avaliação da sua consulta. É útil para ajudar a determinar por que uma consulta pode não ser integrada numa etapa específica.

Através de um exemplo prático, este artigo demonstra o principal caso de uso e os benefícios potenciais de usar o recurso de plano de consulta para revisar suas 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 de bancos de dados de exemplo AdventureWorks.

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

1. Revise os indicadores de simplificação de consulta.
2. Selecione a etapa de consulta para revisar seu plano de consulta.
3. Implementar alterações na sua consulta.

Utilize os seguintes passos para criar a consulta no seu próprio ambiente do Power Query Online.

1. No Power Query - Escolher fonte de dados, selecione 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, certifique-se de configurar um gateway para esse ambiente.

5. Selecione Seguinte.

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

Depois de seguir estes passos, a sua consulta será semelhante à da imagem seguinte.

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

1. Analise os indicadores de simplificação da consulta

O primeiro passo neste processo é verificar a sua consulta e prestar muita atenção aos indicadores de agrupamento da consulta. O objetivo é rever as etapas que estão marcadas como não dobradas. Em seguida, pode verificar se alterar a consulta geral pode levar a que essas transformações se incorporem completamente.

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

O objetivo agora é revisar essa etapa e entender o que está sendo dobrado de volta para a fonte de dados e o que não pode ser dobrado.

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

Você identificou a etapa Linhas inferiores mantidas como uma etapa de interesse, uma vez que não se reconcilia com a fonte de dados. Clique com o botão direito do rato na etapa e selecione a opção Exibir plano de consulta. Esta 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 a sua consulta, tirando partido da avaliação retardada e da fusão de consultas, conforme mencionado em Noções básicas de fusão de consultas. Este plano de consulta representa a tradução otimizada da sua consulta M para a consulta nativa que é enviada para a fonte de dados. Também inclui quaisquer transformações realizadas pelo Power Query Engine. A ordem em que os nós aparecem segue a ordem da sua consulta a partir da última etapa ou saída da sua consulta, que é representada na extremidade esquerda do diagrama. Nesse caso, é o nó Table.LastN que representa o passo de Manter linhas inferiores.

Na parte inferior da caixa de diálogo, há uma barra com ícones que ajudam você a ampliar ou reduzir o modo de 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 à visualização da barra de ferramentas foi usada para visualizar 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: Este nó pode ser Value.NativeQuery ou nós de "fonte de dados", como Sql.Database. Esses nós também podem ser identificados com o rótulo remoto sob o nome da 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 dobrado de volta para a fonte de dados. Você precisa rever o restante dos nós, já que o objetivo é procurar integrar esses nós de volta à 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 para a fonte de dados, mas é uma boa aproximação. Nesse caso, ele informa exatamente quais colunas são consultadas na 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, Table.LastN, é calculado localmente pelo motor do Power Query, uma vez que não pode ser dobrado.

Reveja os nós não flexionados e considere ações para tornar a sua transformação dobrável

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

O objetivo é aplicar alterações à sua consulta para que a etapa possa ser simplificada. Algumas das alterações que você pode implementar podem variar desde a reorganização de suas etapas até a aplicação de uma lógica alternativa à sua consulta que seja mais explícita para a 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 a sua consulta pode ser revertida.

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

• Uma etapa decrescente de classificação pela coluna SalesOrderID na tabela, pois essa coluna determina qual ordem vai primeiro e qual foi inserida por último.
• Selecione as cinco linhas superiores Como a tabela foi classificada, essa transformação obtem o mesmo resultado que se fosse uma Mantendo as últimas linhas (Table.LastN).

Esta alternativa é equivalente à consulta original. Embora esta alternativa pareça boa em teoria, precisas fazer as alterações necessárias para verificar se esta alternativa permite que este nó se ligue completamente à fonte de dados.

3. Implementar alterações à sua consulta

Implemente a alternativa discutida na seção anterior:

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

2. Remova a etapa de manter as linhas inferiores .

3. Ordene a coluna SalesOrderID em ordem decrescente.

   

4. Selecione o ícone da tabela no canto superior esquerdo da vista de pré-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 argumento e pressione OK.

   

Depois de implementar as alterações, verifique novamente os indicadores de dobragem da consulta e veja se aparece um indicador dobrado.

Agora é hora de rever o plano de consulta da última etapa, que agora é Manter as linhas superiores. Atualmente, há apenas nós dobrados. Selecione Exibir detalhes em Value.NativeQuery para verificar qual consulta está sendo enviada ao banco de dados.

Embora este artigo esteja sugerindo qual alternativa aplicar, o objetivo principal é que você aprenda a usar o plano de consulta para investigar a dobragem de consulta. 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. Ao utilizar os indicadores de encadeamento de consultas, tem também uma ideia mais clara de quais etapas estão a impedir que a sua consulta seja encadeada.