Compartilhar via

Suporte a consultas nativas em conectores personalizados do Power Query

Observação

Este artigo aborda tópicos avançados sobre a implementação do suporte de consulta nativa para conectores personalizados, bem como a dobragem de consultas sobre eles. Este artigo pressupõe que você já tenha um conhecimento funcional desses conceitos.

Para saber mais sobre conectores personalizados do Power Query, acesse a visão geral do SDK do Power Query.

No Power Query, você pode executar consultas nativas personalizadas na fonte de dados para recuperar os dados que você está procurando. Você também pode ativar a funcionalidade para manter o dobramento de consultas durante todo esse processo e os processos de transformação subsequentes realizados dentro do Power Query.

O objetivo deste artigo é mostrar como você pode implementar essa funcionalidade para seu conector personalizado.

Pré-requisitos

Este artigo usa como ponto de partida um exemplo que usa o driver ODBC do SQL para sua fonte de dados. Atualmente, a implementação da funcionalidade de consulta nativa só tem suporte para conectores ODBC que aderem ao padrão SQL-92.

O conector de exemplo usa o driver SQL Server Native Client 11.0 . Verifique se você tem esse driver instalado para acompanhar este tutorial.

Você também pode exibir a versão concluída do conector de exemplo da pasta Concluir no Repositório GitHub.

Modificar as Capacidades SQL do conector

SqlCapabilities No registro do conector de exemplo, você pode encontrar um campo de registro com o nome Sql92Translation e o valor PassThrough para ele. Esse novo campo é necessário para que a consulta nativa seja passada usando o Power Query sem nenhuma validação.

SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
    // Place custom overrides here
    // The values below are required for the SQL Native Client ODBC driver, but might
    // not be required for your data source.
        SupportsTop = false,
        SupportsDerivedTable = true,
        Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
        GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
        FractionalSecondsScale = 3,
        Sql92Translation = "PassThrough"
]),

Verifique se esse campo aparece no conector antes de seguir em frente. Caso contrário, você enfrentará avisos e erros posteriormente quando se trata de usar um recurso que não tem suporte porque ele não é declarado pelo conector.

Crie o arquivo do conector (como .mez ou.pqx) e carregue-o no Power BI Desktop para teste manual e para definir o destino para sua consulta nativa.

Testar manualmente os recursos de consulta nativa do conector

Observação

Para este artigo, usaremos o banco de dados de exemplo AdventureWorks2019. Mas você pode acompanhar qualquer banco de dados do SQL Server de sua escolha e fazer as alterações necessárias quando se trata das especificidades do banco de dados escolhido.

A maneira como o suporte à consulta nativa será implementado neste artigo é que o usuário será solicitado a inserir três valores:

  • Nome do servidor
  • Nome do banco de dados
  • Consulta nativa no nível do banco de dados

Agora, no Power BI Desktop, vá para a experiência Obter Dados e localize o conector com o nome SqlODBC Sample.

Captura de tela do conector encontrado na experiência de obtenção de dados do Power BI Desktop.

Para a caixa de diálogo do conector, insira os parâmetros do seu servidor e o nome do seu banco de dados. Em seguida, selecione OK.

Captura de tela da caixa de diálogo do conector com o servidor e o banco de dados como parâmetros.

Uma nova janela do navegador é exibida. No Navegador, você pode exibir o comportamento de navegação nativa do driver SQL que exibe a exibição hierárquica do servidor e dos bancos de dados dentro dele. Clique com o botão direito do mouse no banco de dados AdventureWorks2019 e selecione Transformar Dados.

Captura de tela da opção transformar dados do menu contextual dentro da janela Navegador.

Essa seleção leva você ao editor do Power Query e uma visualização do que é efetivamente o destino da consulta nativa, pois todas as consultas nativas devem ser executadas no nível do banco de dados. Inspecione a barra de fórmulas da última etapa para entender melhor como seu conector deve navegar até o destino de suas consultas nativas antes de executá-las. Nesse caso, a barra de fórmulas exibe as seguintes informações:

= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]

Fonte é o nome da etapa anterior que, nesse caso, é simplesmente a função do seu conector que foi publicada com os parâmetros transferidos. A lista e o registro dentro dela apenas ajudam a navegar uma tabela para uma linha específica. A linha é definida pelos critérios do registro em que o nome do campo deve ser igual ao AdventureWorks2019 e o campo Tipo deve ser igual ao Banco de Dados. Depois que a linha estiver localizada, a [Data] parte externa da lista {} permitirá que o Power Query acesse o valor dentro do campo Dados , que nesse caso é uma tabela. Você pode voltar à etapa anterior (Origem) para entender melhor essa navegação.

Captura de tela de uma tabela que mostra os valores e campos que foram usados para a etapa de navegação.

Testar consulta nativa

Com o destino agora identificado, crie uma etapa personalizada após a etapa de navegação selecionando o ícone fx na barra de fórmulas.

Captura de tela do botão fx dentro da fórmula usada para criar uma etapa personalizada.

Substitua a fórmula dentro da barra de fórmulas pela fórmula a seguir e selecione Enter.

= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
  FROM [Person].[Address]")

Depois de aplicar essa alteração, um aviso deverá aparecer abaixo da barra de fórmulas solicitando permissão para executar a consulta nativa na fonte de dados.

Captura de tela da permissão necessária para executar essa mensagem de aviso de consulta de banco de dados nativo.

Selecione Editar Permissão. Uma nova caixa de diálogo Consulta de Banco de Dados Nativo é exibida que tenta avisá-lo sobre as possibilidades de executar consultas nativas. Nesse caso, sabemos que essa Instrução SQL é segura, portanto, selecione Executar para executar o comando.

Captura de tela mostrando como aprovar uma caixa de diálogo de consulta de banco de dados nativo.

Depois de executar a consulta, uma visualização da consulta será exibida no editor do Power Query. Esta visualização valida que o conector é capaz de executar consultas nativas.

Captura de tela da consulta nativa executada no desenvolvimento e teste do conector inicial.

Implementar lógica de consulta nativa em seu conector

Com as informações coletadas das seções anteriores, a meta agora é traduzir essas informações em código para o conector.

A maneira como você pode realizar essa tradução é adicionando um novo campo de registro NativeQueryProperties ao registro Publish do conector, que nesse caso é o SqlODBC.Publish registro. O NativeQueryProperties registro desempenha um papel crucial na definição de como o conector interagirá com a Value.NativeQuery função.

O novo campo de registro consiste em dois campos:

  • NavigationSteps: esse campo define como a navegação deve ser executada ou tratada pelo conector. Ele contém uma lista de registros que descrevem as etapas para navegar até os dados específicos que você deseja consultar usando a Value.NativeQuery função. Em cada registro, ele define quais parâmetros são necessários ou necessários para que essa navegação atinja o destino desejado.
  • DefaultOptions: esse campo ajuda a identificar como determinados parâmetros opcionais devem ser incluídos ou adicionados ao registro de Value.NativeQuery opções. Ele fornece um conjunto de opções padrão que podem ser usadas ao consultar a fonte de dados.

As etapas de navegação podem ser categorizadas em dois grupos. O primeiro contém os valores inseridos pelo usuário final, como o nome do servidor ou do banco de dados, nesse caso. O segundo contém os valores derivados pela implementação do conector específico, como o nome dos campos que não são exibidos ao usuário durante a experiência de obtenção de dados. Esses campos podem incluir Name, Kinde Dataoutros, dependendo da implementação do conector.

Para esse caso, havia apenas uma etapa de navegação que consistia em dois campos:

  • Nome: esse campo é o nome do banco de dados que foi passado pelo usuário final. Nesse caso, foi AdventureWorks2019, mas esse campo sempre deve ser passado como está do que o usuário final inseriu durante o processo de obtenção de dados.
  • Tipo: esse campo é uma informação que não está visível para o usuário final e é específica para a implementação do conector ou do driver. Nesse caso, esse valor identifica que tipo de objeto deve ser acessado. Para essa implementação, esse campo será um valor fixo que consiste na cadeia de caracteres Database.

Essas informações serão traduzidas para o código a seguir. Esse código deve ser adicionado como um novo campo ao seu SqlODBC.Publish registro.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    }
]

Importante

O nome dos campos diferencia maiúsculas de minúsculas e deve ser usado conforme mostrado no exemplo acima. Todas as informações passadas para os campos, ConstantValue, IndexName ou FieldDisplayName, devem ser derivadas do código M do conector.

Para valores que serão passados do que o usuário inseriu, você pode usar o par FieldDisplayName e IndexName. Para valores fixos ou predefinidos e que não podem ser passados pelo usuário final, você pode usar o par ConstantValue e IndexName. Nesse sentido, o registro NavigationSteps consiste em dois campos:

  • Índices: define quais campos e quais valores usar para navegar até o registro que contém o destino da Value.NativeQuery função.
  • FieldAccess: define qual campo contém o destino, que normalmente é uma tabela.

DefaultOptions

O DefaultOptions campo permite passar parâmetros opcionais para a Value.NativeQuery função ao usar a funcionalidade de consulta nativa para o conector.

Para preservar a dobra de consulta após uma consulta nativa e supondo que o conector tenha recursos de dobragem de consulta, você pode usar o código de exemplo a seguir para EnableFolding = true.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    },

    DefaultOptions = [
        EnableFolding = true
    ]
]

Com essas alterações em vigor, crie o conector e carregue-o no Power BI Desktop para teste e validação.

Testar e validar o conector

No Power BI Desktop, com seu novo conector personalizado configurado, execute o conector por meio da experiência Obter Dados. Ao iniciar o conector, você observará que a caixa de diálogo agora tem um campo de texto longo com o nome Consulta nativa e, entre parênteses, ela tem os campos necessários para funcionar. Insira os mesmos valores para o servidor, o banco de dados e a instrução SQL que você inseriu anteriormente ao testar o conector.

Captura de tela da caixa de diálogo do conector com o campo de texto longo da consulta nativa mostrado.

Depois de selecionar OK, uma visualização da tabela da consulta nativa executada é mostrada em uma nova caixa de diálogo.

Captura de tela da caixa de diálogo com uma visualização da tabela da consulta nativa executada.

Selecione OK. Uma nova consulta agora será carregada dentro do editor do Power Query, onde você pode fazer mais testes do conector conforme necessário.

Observação

Se o conector tiver recursos de dobragem de consulta e tiver definido EnableFolding=true explicitamente como parte do registro opcional para Value.NativeQuery, você poderá testar ainda mais o conector no editor do Power Query verificando se outras transformações serão dobradas de volta para a origem ou não.

  • Last updated on