Compartilhar via

Suporte à consulta nativa em conectores personalizados do Power Query

  • Artigo

Observação

Este artigo aborda tópicos avançados sobre a implementação de suporte de consulta nativo para conectores personalizados, bem como dobramento de consulta sobre eles. Este artigo pressupõe que você já tem conhecimento prático desses conceitos.

Para saber mais sobre os conectores personalizados do Power Query, vá para 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 está procurando. Você também pode ativar a capacidade de manter a dobra de consulta durante todo esse processo e processos de transformação subsequentes feitos dentro do Power Query.

O objetivo deste artigo é mostrar como você pode implementar esse recurso 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. No momento, a implementação do recurso de consulta nativa só tem suporte para conectores ODBC que aderem ao padrão SQL-92.

O conector de exemplo usa o driver do SQL Server Native Client 11.0. Certifique-se de que você tenha esse driver instalado para acompanhar este tutorial.

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

Modificar as SQLCapabilities do conector

No registro SqlCapabilities 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 avançar. Caso contrário, você verá avisos e erros mais tarde quando usar um recurso que não é suportado porque não é declarado pelo conector.

Crie o arquivo de 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, vamos usar 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 a consultas nativas será implementado neste artigo é que o usuário precisará inserir três valores:

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

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

Screenshot of the connector found inside the get data experience of Power BI Desktop.

Para a caixa de diálogo do conector, insira os parâmetros do servidor e do nome do banco de dados. Selecione OK.

Screenshot of connector dialog with server and database as parameters.

Uma nova janela do navegador é exibida. No Navegador, você pode exibir o comportamento de navegação nativo 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.

Screenshot of the transform data option from the contextual menu inside the Navigator window.

Essa seleção leva você ao editor do Power Query e a uma visualização do que é efetivamente o destino da consulta nativa, já que 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 o conector deve navegar até o destino das consultas nativas antes de executá-las. Nesse caso, a barra de fórmulas exibe as seguintes informações:

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

Origem é o nome da etapa anterior que, neste caso, é simplesmente a função publicada do conector com os parâmetros transmitidos. A lista e o registro dentro dela só ajudam a navegar uma tabela para uma linha específica. A linha é definida pelos critérios do registro em que o campo Nome deve ser igual a AdventureWorks2019 e o campo Tipo deve ser igual a Banco de Dados. Depois que a linha é localizada, a saída [Data] da lista {} permite que o Power Query acesse o valor dentro do campo Dados, que neste caso é uma tabela. Você pode voltar à etapa anterior (Origem) para entender melhor essa navegação.

Screenshot of a table that shows the values and fields that were used for the navigation step.

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.

Screenshot of the fx button inside the formula that's used to create a custom step.

Substitua a fórmula dentro da barra pela seguinte fórmula e selecione Enter.

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

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

Screenshot of the permission is required to run this native database query warning message.

Selecione Editar permissão. Uma nova caixa de diálogo Consulta Nativa de Banco de Dados é exibida que tenta avisar sobre as possibilidades de executar consultas nativas. Nesse caso, sabemos que essa instrução SQL é segura, então selecione Executar para executar o comando.

Screenshot showing how to approve a native database query dialog.

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

Screenshot of the native query executed in initial connector development and testing.

Implementar lógica de consulta nativa no conector

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

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

O novo campo de registro consiste em dois campos:

  • NavigationSteps: esse campo define como a navegação deve ser executada ou processada 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 função Value.NativeQuery. em cada registro, ele define quais parâmetros são obrigatórios ou necessários para que essa navegação alcance o destino desejado.
  • DefaultOptions: esse campo ajuda a identificar como determinados parâmetros opcionais devem ser incluídos ou adicionados ao registro de opções Value.NativeQuery. 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 classificadas em dois grupos. O primeiro contém os valores que são 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 específica do conector, 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, Kind, Data e outros dependendo da implementação do conector.

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

  • Nome: esse campo é o nome do banco de dados que foi transmitido pelo usuário final. Nesse caso, foi AdventureWorks2019, mas esse campo sempre deve ser transmitido como está a partir do que o usuário final inseriu durante a experiência 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 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 string Database.

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

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 transmitidas para os campos, ConstantValue, IndexNameou FieldDisplayName, devem ser derivadas do código M do conector.

Para valores que serão transmitidos a partir do que o usuário inseriu, você pode usar o par FieldDisplayName e IndexName. Para valores que são fixos ou predefinidos e não podem ser transmitidos pelo usuário final, você poderá 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 função Value.NativeQuery.
  • FieldAccess: define qual campo contém o destino, que geralmente é uma tabela.

DefaultOptions

O campo DefaultOptions permite que você transmita parâmetros opcionais para a função Value.NativeQuery ao usar o recurso de consulta nativa para o conector.

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

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Com essas alterações, 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 instalado, inicie o conector na experiência Obter Dados. Ao iniciar o conector, você notará que a caixa de diálogo agora tem um campo de texto longo com o nome Consulta nativa e, entre parênteses, 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.

Screenshot of the connector dialog with the native query long text field shown.

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

Screenshot of the dialog with a table preview of the executed native query.

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

Observação

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