Dobramento de consulta Power QueryPower Query query folding

Este artigo destina-se a modeladores de dados que desenvolvem modelos no Power Pivot ou Power BI Desktop.This article targets data modelers developing models in Power Pivot or Power BI Desktop. Ele descreve o que Power Query o dobramento de consulta é e por que ele é importante em seus designs de modelo de dados.It describes what Power Query query folding is, and why it is important in your data model designs. Ele também descreve as fontes de dados e as transformações que podem obter o dobramento de consulta e como determinar se suas consultas de Power Query podem ser dobradas, de forma completa ou parcial.It also describes the data sources and transformations that can achieve query folding, and how to determine that your Power Query queries can be folded—whether fully or partially.

O dobramento de consulta é a capacidade de uma consulta Power Query gerar uma única instrução de consulta para recuperar e transformar dados de origem.Query folding is the ability for a Power Query query to generate a single query statement to retrieve and transform source data. O mecanismo de mashup Power Query se esforça para obter o dobramento de consulta sempre que possível por motivos de eficiência.The Power Query mashup engine strives to achieve query folding whenever possible for reasons of efficiency.

A dobragem de consultas é um tópico importante para modelagem de dados por vários motivos:Query folding is an important topic for data modeling for several reasons:

  • Importar tabelas de modelo: A atualização de dados ocorrerá com eficiência para importar tabelas de modelo (Power Pivot ou Power BI Desktop), em termos de utilização de recursos e duração da atualização.Import model tables: Data refresh will take place efficiently for Import model tables (Power Pivot or Power BI Desktop), in terms of resource utilization and refresh duration.
  • Tabelas de modo de armazenamento e DirectQuery: Cada DirectQuery e tabela de modo de armazenamento duplo (somente Power BI) deve ser baseada em uma consulta Power Query que possa ser dobrada.DirectQuery and Dual storage mode tables: Each DirectQuery and Dual storage mode table (Power BI only) must be based on a Power Query query that can be folded.
  • Atualização incremental: A atualização de dados incremental (somente Power BI) será eficiente, em termos de utilização de recursos e duração da atualização.Incremental refresh: Incremental data refresh (Power BI only) will be efficient, in terms of resource utilization and refresh duration. Na verdade, a janela de configuração de Atualização Incremental Power bi notificará você de um aviso caso ele determine que o dobramento de consulta para a tabela não pode ser obtido.In fact, the Power BI Incremental Refresh configuration window will notify you of a warning should it determine that query folding for the table cannot be achieved. Se ela não puder ser alcançada, o objetivo da atualização incremental será perdido.If it cannot be achieved, the objective of incremental refresh is defeated. O mecanismo de mashup então seria necessário para recuperar todas as linhas de origem e, em seguida, aplicar filtros para determinar as alterações incrementais.The mashup engine would then be required to retrieve all source rows, and then apply filters to determine incremental changes.

A dobragem de consultas pode ocorrer para uma consulta do Power Query inteira ou para um subconjunto de suas etapas.Query folding may occur for an entire Power Query query, or for a subset of its steps. Quando o dobramento de consulta não pode ser obtido, parcial ou totalmente, o mecanismo de mashup Power Query deve compensar processando as transformações de dados em si.When query folding cannot be achieved—either partially or fully—the Power Query mashup engine must compensate by processing data transformations itself. Esse processo pode envolver a recuperação dos resultados da consulta de origem, que, para grandes conjuntos de altos, tem muito uso de recursos e lento.This process can involve retrieving source query results, which for large datasets is very resource intensive and slow.

É recomendável que você se busque para obter eficiência em seus designs de modelo, garantindo que o dobramento de consulta ocorra sempre que possível.We recommend that you strive to achieve efficiency in your model designs by ensuring query folding occurs whenever possible.

Fontes que dão suporte a dobramentoSources that support folding

A maioria das fontes de dados que têm o conceito de linguagem de consulta é compatível com dobragem de consultas.Most data sources that have the concept of a query language support query folding. Essas fontes de dados podem incluir bancos de dados relacionais, feeds do OData (incluindo listas do SharePoint), Exchange e Active Directory.These data sources can include relational databases, OData feeds (including SharePoint lists), Exchange, and Active Directory. No entanto, fontes de dados como arquivos simples, BLOBs e Web normalmente não fazem isso.However, data sources like flat files, blobs, and web typically do not.

Transformações que podem atingir o dobramentoTransformations that can achieve folding

As transformações de fonte de dados relacionais que podem consultadas dobradas são aquelas que podem ser gravadas como uma única instrução SELECT.Relational data source transformations that can be query folded are those that can be written as a single SELECT statement. Uma instrução SELECT pode ser construída com as cláusulas WHERE, GROUP BY e JOIN apropriadas.A SELECT statement can be constructed with appropriate WHERE, GROUP BY, and JOIN clauses. Ele também pode conter expressões de coluna (cálculos) que usam funções internas comuns com suporte em bancos de dados SQL.It can also contain column expressions (calculations) that use common built-in functions supported by SQL databases.

Em geral, a lista a seguir descreve as transformações que podem ser dobradas por consulta.Generally, the following list describes transformations that can be query folded.

  • Removendo colunas.Removing columns.

  • Renomeando colunas (selecione aliases de coluna).Renaming columns (SELECT column aliases).

  • Filtrar linhas, com valores estáticos ou parâmetros de Power Query (predicados de cláusula WHERE).Filtering rows, with static values or Power Query parameters (WHERE clause predicates).

  • Agrupamento e Resumo (cláusula GROUP BY).Grouping and summarizing (GROUP BY clause).

  • Expandir colunas de registro (colunas de chave estrangeira de origem) para obter uma junção de duas tabelas de origem (cláusula de junção).Expanding record columns (source foreign key columns) to achieve a join of two source tables (JOIN clause).

  • Mesclagem não difusa de consultas com dobras com base na mesma origem (cláusula de junção).Non-fuzzy merging of fold-able queries based on the same source (JOIN clause).

  • Acrescentando consultas com dobras com base na mesma fonte (operador UNION ALL).Appending fold-able queries based on the same source (UNION ALL operator).

  • Como adicionar colunas personalizadas com lógica simples (expressões de coluna SELECT).Adding custom columns with simple logic (SELECT column expressions). A lógica simples implica em operações não complicadas, possivelmente incluindo o uso de funções M que têm funções equivalentes na fonte de dados SQL, como matemáticos ou funções de manipulação de texto.Simple logic implies uncomplicated operations, possibly including the use of M functions that have equivalent functions in the SQL data source, like mathematic or text manipulation functions. Por exemplo, as expressões a seguir retornam o componente year do valor da coluna OrderDate (para retornar um valor numérico).For example, the following expressions returns the year component of the OrderDate column value (to return a numeric value).

    Date.Year([OrderDate])
    
  • Dinamização e dinâmica (operadores PIVOT e UNPIVOT).Pivoting and unpivoting (PIVOT and UNPIVOT operators).

Transformações que impedem o dobramentoTransformations that prevent folding

Em geral, a lista a seguir descreve as transformações que impedem o dobramento da consulta.Generally, the following list describes transformations that prevent query folding. Esta lista não se destina a exaustiva.This is not intended to be an exhaustive list.

  • Mesclar consultas com base em fontes diferentes.Merging queries based on different sources.

  • Anexando consultas (Union-ing) com base em fontes diferentes.Appending (union-ing) queries based on different sources.

  • Como adicionar colunas personalizadas com lógica complexa.Adding custom columns with complex logic. A lógica complexa implica o uso de funções M sem funções equivalentes na fonte de dados.Complex logic implies the use of M functions that have no equivalent functions in the data source. Por exemplo, as expressões a seguir formatam o valor da coluna OrderDate (para retornar um valor de texto).For example, the following expressions formats the OrderDate column value (to return a text value).

    Date.ToText([OrderDate], "yyyy")
    
  • Adicionando colunas de índice.Adding index columns.

  • Alterando um tipo de dados de coluna.Changing a column data type.

Observe que, quando uma consulta do Power Query abrange várias fontes de dados, a incompatibilidade dos níveis de privacidade da fonte de dados pode impedir que a dobragem de consultas ocorra.Note that when a Power Query query encompasses multiple data sources, incompatibility of data source privacy levels can prevent query folding from taking place. Para obter mais informações, consulte o artigo Power bi desktop níveis de privacidade .For more information, see the Power BI Desktop privacy levels article.

Determinar quando uma consulta pode ser dobradaDetermine when a query can be folded

Na janela do Editor do Power Query, é possível determinar quando uma consulta do Power Query pode ser dobrada.In the Power Query Editor window, it is possible to determine when a Power Query query can be folded. No painel configurações de consulta , quando você clica com o botão direito do mouse na última etapa aplicada, se a opção Exibir consulta nativa estiver habilitada (não esmaecida), toda a consulta poderá ser dobrada.In the Query Settings pane, when you right-click the last applied step, if the View Native Query option is enabled (not greyed out), then the entire query can be folded.

Exemplo de determinação de que Power Query pode obter o dobramento de consulta no Power BI Desktop

Observação

A opção Exibir consulta nativa só está disponível para determinados conectores de criação de banco de dado/SQL relacionais.The View Native Query option is only available for certain relational DB/SQL generating connectors. Ele não funciona para conectores baseados em OData, por exemplo, mesmo que haja dobras ocorrendo no back-end.It doesn't work for OData based connectors, for example, even though there is folding occurring on the backend. O recurso de diagnóstico de consulta é a melhor maneira de ver o que o dobramento ocorreu para conectores não-SQL (embora as etapas que se dobram não sejam explicitamente chamadas — de que você acabou de ver a URL resultante gerada).The Query Diagnostics feature is the best way to see what folding has occurred for non-SQL connectors (although the steps that fold aren't explicitly called out—you just see the resulting URL that was generated).

Para exibir a consulta dobrada, selecione a opção Exibir consulta nativa .To view the folded query, you select the View Native Query option. Em seguida, você verá a consulta nativa que Power Query usará para dados de origem.You are then be presented with the native query that Power Query will use to source data.

Exemplo de uma consulta nativa no Power BI Desktop

Se a opção Exibir Consulta Nativa não estiver habilitada (esmaecida), isso será uma prova de que não é possível dobrar todas as etapas da consulta.If the View Native Query option is not enabled (greyed out), this is evidence that all query steps cannot be folded. No entanto, poderia significar que um subconjunto de etapas ainda pode ser dobrado.However, it could mean that a subset of steps can still be folded. Trabalhando para trás da última etapa, você pode verificar cada etapa para ver se a opção Exibir consulta nativa está habilitada.Working backwards from the last step, you can check each step to see if the View Native Query option is enabled. Se esse for o caso, você aprendeu onde, na sequência de etapas, que o dobramento de consulta não pôde mais ser alcançado.If this is the case, then you have learned where, in the sequence of steps, that query folding could no longer be achieved.

Exemplo de determinação de que Power Query não pode obter o dobramento de consulta no Power BI Desktop

Próximas etapasNext steps

Para obter mais informações sobre o dobramento de consulta e tópicos relacionados, confira os seguintes recursos:For more information about Query Folding and related topics, check out the following resources: