Partilhar via

Suporte nativo de consultas em conectores personalizados Power Query

Observação

Este artigo aborda tópicos avançados relacionados com a implementação do suporte nativo de consultas para conectores personalizados, bem como a dobragem de consultas por cima deles. Este artigo assume que já tem um conhecimento prático destes conceitos.

Para saber mais sobre conectores personalizados do Power Query, consulte Visão Geral do SDK do Power Query.

No Power Query, consegues executar consultas nativas personalizadas na tua fonte de dados para recuperar os dados que procuras. Também pode ativar a capacidade de manter query folding durante todo este processo e os processos subsequentes de transformação realizados dentro do Power Query.

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

Pré-requisitos

Este artigo utiliza como ponto de partida um exemplo que utiliza o driver SQL ODBC como fonte de dados. A implementação da capacidade nativa de consulta é atualmente suportada apenas para conectores ODBC que cumpram o padrão SQL-92.

O conector de exemplo utiliza o driver SQL Server Native Client 11.0 . Certifique-se de que tem este driver instalado para acompanhar este tutorial.

Também pode ver a versão finalizada do conector de exemplo a partir da pasta Finalizar no repositório GitHub.

Modifica as capacidades SQL do seu conector

No SqlCapabilities registo do conector de exemplo, pode encontrar um campo de registo com o nome Sql92Translation e o valor PassThrough para ele. Este novo campo é necessário para que a consulta nativa seja passada usando 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 seu conector antes de avançar. Se não, vais receber avisos e erros mais tarde quando se trata de usar uma funcionalidade que não é suportada porque não é declarada pelo conector.

Constrói o ficheiro conector (como .mez ou .pqx) e carrega-o no Power BI Desktop para testes manuais e para definir o destino da tua consulta nativa.

Teste manualmente as capacidades nativas de consulta do seu conector

Observação

Para este artigo, vamos utilizar a base de dados de exemplo AdventureWorks2019. Mas pode acompanhar qualquer base de dados SQL Server à sua escolha e fazer as alterações necessárias quando se trata dos detalhes da base de dados escolhida.

A forma como o suporte nativo de consultas será implementado neste artigo é que o utilizador será solicitado a inserir três valores:

  • Nome do servidor
  • Nome da base de dados
  • Consulta nativa ao nível da base de dados

Agora, dentro do Power BI Desktop, vá ao módulo Obter Dados e encontre o conector com o nome SqlODBC Sample.

Captura de ecrã do conector encontrado na experiência de obter dados do Power BI Desktop.

Para o diálogo do conector, introduza os parâmetros do seu servidor e o nome da base de dados. Em seguida, selecione OK.

Captura de ecrã do diálogo do conector com o servidor e a base de dados como parâmetros.

Aparece uma nova janela de navegação. No Navigator, pode visualizar o comportamento nativo de navegação a partir do driver SQL que apresenta a visão hierárquica do servidor e das bases de dados dentro dele. Clique com o botão direito na base de dados AdventureWorks2019 e selecione Transformar Dados.

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

Esta seleção leva-o ao editor Power Query e a uma pré-visualização do que é, na prática, o objetivo da sua consulta nativa, já que todas as consultas nativas devem ser executadas ao nível da base de dados. Inspecione a barra de fórmulas do último passo para perceber melhor como o seu conector deve navegar até ao destino das suas consultas nativas antes de as executar. Neste caso, a barra de fórmulas apresenta a seguinte informação:

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

Source é o nome do passo anterior que, neste caso, é simplesmente a função publicada do seu conector com os parâmetros passados. A lista e o registo dentro dela apenas ajudam a navegar uma tabela até uma linha específica. A linha é definida pelos critérios do registo onde o campo Nome tem de ser igual a AdventureWorks2019 e o campo Kind tem de ser igual a Base de Dados. Uma vez localizada a linha, o [Data] exterior da lista {} permite ao Power Query aceder ao valor dentro do campo Data , que neste caso é uma tabela. Pode voltar ao passo anterior (Fonte) para compreender melhor esta navegação.

Captura de ecrã de uma tabela que mostra os valores e campos usados para o passo de navegação.

Teste de consulta nativa

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

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

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

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

Depois de aplicar esta alteração, deverá aparecer um aviso por baixo da barra de fórmulas a pedir permissão para executar a consulta nativa na sua fonte de dados.

É necessária uma captura de ecrã da permissão para executar esta mensagem nativa de aviso de consulta à base de dados.

Selecionar Permissão de Edição. É exibido um novo diálogo de Consulta de Base de Dados Nativa que tenta alertá-lo sobre as possibilidades de executar consultas nativas. Neste caso, sabemos que esta Instrução SQL está segura, por isso seleciona Executar para executar o comando.

Captura de ecrã que mostra como aprovar um diálogo de consulta nativa a base de dados.

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

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

Implementa lógica nativa de consulta no teu conector

Com a informação recolhida nas secções anteriores, o objetivo agora é traduzir essa informação em código para o seu conector.

A forma de realizar esta tradução é adicionando um novo campo de registo NativeQueryProperties ao registo Publish do seu conector, que, neste caso, é o registo SqlODBC.Publish. O NativeQueryProperties registo 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:

  • PassosDeNavegação: Este campo define como a navegação deve ser realizada ou tratada pelo seu conector. Contém uma lista de registos que descrevem os passos para navegar até aos dados específicos que pretende consultar usando a Value.NativeQuery função. Dentro de cada registo, define quais os parâmetros necessários para que essa navegação alcance o alvo desejado.
  • DefaultOptions: Este campo ajuda a identificar como certos parâmetros opcionais devem ser incluídos ou adicionados ao Value.NativeQuery registo de opções. Fornece um conjunto de opções predefinidas que podem ser usadas ao interrogar a fonte de dados.

Os seus passos de navegação podem ser categorizados em dois grupos. A primeira contém os valores que são introduzidos pelo utilizador final, como o nome do servidor ou da base de dados, neste caso. A segunda contém os valores derivados pela implementação específica do conector, como o nome dos campos que não são exibidos ao utilizador durante a experiência de obtenção de dados. Estes campos podem incluir Name, Kind, Data, e outros, dependendo da implementação do seu conector.

Neste caso, havia apenas um passo de navegação que consistia em dois campos:

  • Nome: Este campo é o nome da base de dados que foi passada pelo utilizador final. Neste caso, era AdventureWorks2019, mas este campo deve sempre ser passado tal como está, como o utilizador final o introduziu durante a experiência de obtenção de dados.
  • Tipo: Este campo é informação que não é visível para o utilizador final e é específica da implementação do conector ou driver. Neste caso, este valor identifica que tipo de objeto deve ser acedido. Para esta implementação, este corpo será um valor fixo que consiste na cadeia Database.

Essa informação será traduzida para o seguinte código. 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

Os nomes dos campos são sensíveis a maiúsculas e minúsculas e devem ser usados conforme mostrado no exemplo acima. Toda a informação passada para os campos, seja ConstantValue, IndexName, ou FieldDisplayName deve ser derivada do código M do conector.

Para valores que serão transmitidos a partir do que o utilizador introduziu, pode usar o par FieldDisplayName e IndexName. Para valores que são fixos ou pré-definidos e que não podem ser passados pelo utilizador final, pode usar o par ConstantValue e IndexName. Neste sentido, o registo NavigationSteps consiste em dois campos:

  • Índices: Define que campos e que valores usar para navegar até ao registo que contém o destino da Value.NativeQuery função.
  • FieldAccess: Define que campo contém o alvo, que é normalmente uma tabela.

DefaultOptions

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

Para preservar o query folding após executar uma consulta nativa, e assumindo que o seu conector possui capacidades de query folding, 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 estas alterações implementadas, constrói o conector e carrega-o no Power BI Desktop para testes e validação.

Testar e validar o conector

No Power BI Desktop, com o novo conector personalizado instalado, abra o conector através da funcionalidade Obter Dados. Ao iniciar o conector, vai notar que o 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. Introduza os mesmos valores para o servidor, base de dados e a instrução SQL que introduziu anteriormente ao testar o conector.

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

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

Captura de ecrã do diálogo com uma pré-visualização da tabela da consulta nativa executada.

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

Observação

Se o conector tiver capacidades de "query folding" e tiver explicitamente definido EnableFolding=true como parte do registo opcional para Value.NativeQuery, pode testar ainda mais o conector no editor do Power Query, verificando se as transformações adicionais são aplicadas na origem ou não.

  • Last updated on