Dobragem de consultas em consultas nativas

No Power Query, você pode definir uma consulta nativa e executá-la em sua fonte de dados. O artigo Importar dados de um banco de dados usando consulta de banco de dados nativo explica como fazer esse processo com várias fontes de dados. Mas, usando o processo descrito nesse artigo, sua consulta não aproveitará nenhuma dobra de consulta das etapas de consulta subsequentes.

Este artigo mostra um método alternativo para criar consultas nativas em relação à fonte de dados usando a função Value.NativeQuery e manter o mecanismo de dobramento de consulta ativo para as etapas subsequentes da consulta.

Observação

Recomendamos que você leia a documentação sobre dobramento de consulta e os indicadores de dobramento de consulta para entender melhor os conceitos usados ao longo deste artigo.

Conectores de dados com suporte

O método descrito nas próximas seções se aplica aos seguintes conectores de dados:

• Amazon Redshift
• Dataverse(ao usar computação aprimorada)
• Google BigQuery
• PostgreSQL
• SAP HANA
• Snowflake
• SQL Server

Conectar-se ao destino da fonte de dados

Observação

Para mostrar esse processo, este artigo usa o conector SQL Server e o banco de dados de exemplo AdventureWorks2019. A experiência pode variar de conector para conector, mas este artigo mostra os conceitos básicos sobre como habilitar recursos de dobramento de consulta em consultas nativas para os conectores com suporte.

Ao se conectar à fonte de dados, é importante que você se conecte ao nó ou ao nível em que deseja executar sua consulta nativa. Para o exemplo neste artigo, esse nó será o nível de banco de dados dentro do servidor.

Depois de definir as configurações de conexão e fornecer as credenciais para sua conexão, você será levado para a caixa de diálogo de navegação da fonte de dados. Nessa caixa de diálogo, você verá todos os objetos disponíveis aos quais você pode se conectar.

Nessa lista, você precisa selecionar o objeto em que a consulta nativa é executada (também conhecida como destino). Para este exemplo, esse objeto é o nível do banco de dados.

Na janela do navegador no Power Query, clique com o botão direito do mouse no nó do banco de dados na janela do navegador e selecione a opção Transformar Dados. Selecionar essa opção cria uma nova consulta da exibição geral do banco de dados, que é o destino necessário para executar sua consulta nativa.

Depois que a consulta chegar ao editor de Power Query, somente a etapa De origem deverá ser exibida no painel Etapas Aplicadas. Esta etapa contém uma tabela com todos os objetos disponíveis em seu banco de dados, semelhante à forma como eles foram exibidos na janela Navegador.

Usar a função Value.NativeQuery

O objetivo desse processo é executar o código SQL a seguir e aplicar mais transformações com Power Query que podem ser dobradas de volta para a origem.

SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'

A primeira etapa foi definir o destino correto, que nesse caso é o banco de dados em que o código SQL será executado. Depois que uma etapa tiver o destino correto, você poderá selecionar essa etapa , nesse caso, origem nas Etapas Aplicadas e, em seguida, selecionar o botão fx na barra de fórmulas para adicionar uma etapa personalizada. Neste exemplo, substitua a Source fórmula pela seguinte fórmula:

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'  ", null, [EnableFolding = true])

O componente mais importante dessa fórmula é o uso do registro opcional para o próximo parâmetro da função que tem o campo de registro EnableFolding definido como TRUE.

Observação

Você pode ler mais sobre a função Value.NativeQuery no artigo da documentação oficial.

Depois de inserir a fórmula, será mostrado um aviso que exigirá que você habilite as consultas nativas a serem executadas para sua etapa específica. Você pode clicar em continuar para que esta etapa seja avaliada.

Essa instrução SQL produz uma tabela com apenas três linhas e duas colunas.

Testar dobramento de consulta

Para testar o dobramento de consulta de sua consulta, você pode tentar aplicar um filtro a qualquer uma de suas colunas e ver se o indicador de dobramento de consulta na seção etapas aplicadas mostra a etapa como dobrada. Nesse caso, você pode filtrar a coluna DepartmentID para ter valores que não são iguais a dois.

Depois de adicionar esse filtro, você pode verificar se os indicadores de dobramento de consulta ainda mostram a dobragem de consulta acontecendo nesta nova etapa.

Para validar ainda mais qual consulta está sendo enviada para a fonte de dados, clique com o botão direito do mouse na etapa Linhas Filtradas e selecione a opção que lê Exibir plano de consulta para verificar o plano de consulta para essa etapa.

No modo de exibição do plano de consulta, você pode ver que um nó com o nome Value.NativeQuery no lado esquerdo da tela que tem um texto de hiperlink que lê Detalhes da exibição. Você pode clicar neste texto de hiperlink para exibir a consulta exata que está sendo enviada para o banco de dados SQL Server.

A consulta nativa é encapsulada em torno de outra instrução SELECT para criar uma subconsulta do original. O Power Query fará o seu melhor para criar a consulta mais ideal, considerando as transformações usadas e a consulta nativa fornecida.

Dica

Para cenários em que você obtém erros porque o dobramento de consulta não era possível, é recomendável que você tente validar suas etapas como uma subconsulta de sua consulta nativa original para verificar se pode haver alguma sintaxe ou conflitos de contexto.