Suporte nativo a consultas em conectores personalizados do Power Query

Nota

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

Para saber mais sobre os conectores personalizados do Power Query, aceda a Descrição Geral do SDK do Power Query.

No Power Query, pode executar consultas nativas personalizadas na sua origem de dados para recuperar os dados que procura. Também pode ativar a capacidade de manter a dobragem de consultas ao longo deste processo e dos processos de transformação subsequentes efetuados 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 SQL para sua fonte de dados. Atualmente, a implementação do recurso de consulta nativo é suportada apenas 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ê tem esse driver instalado para acompanhar este tutorial.

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

Modificar as SQLCapabilities do seu 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. Este novo campo é necessário para que a consulta nativa seja passada utilizando o Power Query sem qualquer 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"
]),

Certifique-se de que este campo aparece no conector antes de avançar. Caso contrário, você enfrentará avisos e erros mais tarde quando se trata de usar um recurso que não é suportado porque 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.

Teste manualmente os recursos de consulta nativos do seu conector

Nota

Para este artigo, usaremos o banco de dados de exemplo AdventureWorks2019. Mas você pode acompanhar qualquer banco de dados 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 será solicitado a inserir três valores:

• Nome do servidor
• Nome da base 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.

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

É apresentada uma nova janela do navegador. No Navigator, 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.

Esta seleção leva-o ao editor do Power Query e a uma pré-visualização do que é efetivamente o destino da sua consulta nativa, uma vez que todas as consultas nativas devem ser executadas ao nível da base 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]

Source é o nome da etapa anterior que, neste caso, é simplesmente a função publicada do seu conector com os parâmetros passados. 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 onde o campo Nome deve ser igual a AdventureWorks2019 e o campo Tipo deve ser igual a Banco de Dados. Quando a linha estiver localizada, o [Data] exterior da lista {} permite ao Power Query aceder ao valor dentro do campo Dados , que neste caso é uma tabela. Você pode voltar para a etapa anterior (Fonte) para entender melhor essa 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.

Substitua a fórmula dentro da barra de fórmulas pela fórmula seguinte e, em seguida, 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 em sua fonte de dados.

Selecione Editar permissão. É exibida uma nova caixa de diálogo Consulta de Banco de Dados Nativa 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.

Depois de executar a consulta, é apresentada uma pré-visualização da mesma no editor do Power Query. Esta pré-visualização valida que o conector é capaz de executar consultas nativas.

Implementar lógica de consulta nativa em seu conector

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

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

O novo campo de registo consiste em dois campos:

• NavigationSteps: Este campo define como a navegação deve ser realizada ou tratada pelo seu 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. Dentro de cada registro, ele define quais parâmetros são necessários ou necessários para que essa navegação atinja o alvo desejado.
• DefaultOptions: Este 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.

NavegaçãoPassos

Suas etapas de navegação podem ser categorizadas 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, neste 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 para o usuário durante a experiência de obtenção de dados. Esses campos podem incluir Name, , , e outros, KindDatadependendo da implementação do conector.

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

• Nome: Este campo é o nome do banco de dados que foi passado pelo usuário final. Neste caso, foi AdventureWorks2019, mas este campo deve sempre ser passado como está a partir do que o usuário final inseriu durante a experiência de obtenção de dados.
• Tipo: Este campo é uma informação que não é 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 esta implementação, este campo será um valor fixo que consiste na cadeia de caracteres Database.

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

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 como mostrado no exemplo acima. Todas as informações passadas para os campos, ou , IndexNameou ConstantValueFieldDisplayName devem ser derivadas do código M do conector.

Para valores que serão passados a partir 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 geralmente é uma tabela.

DefaultOptions

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

Para preservar a dobragem de consulta após uma consulta nativa e supondo que seu 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 o novo conector personalizado no lugar, inicie o conector a partir da experiência Obter Dados . Ao iniciar o conector, você notará que a caixa de diálogo agora tem um longo campo de texto com o nome Consulta nativa e, entre parênteses, tem os campos necessários para que funcione. Insira os mesmos valores para o servidor, o banco de dados e a instrução SQL que você inseriu anteriormente ao testar o conector.

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

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

Nota

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