Les på engelsk

Del via


Støtte for opprinnelig spørring i egendefinerte Power Query-koblinger

Obs!

Denne artikkelen dekker avanserte emner om implementering av opprinnelig spørringsstøtte for egendefinerte koblinger, samt spørringsdelegering oppå dem. Denne artikkelen forutsetter at du allerede har kunnskap om disse begrepene.

Hvis du vil lære mer om egendefinerte Power Query-koblinger, kan du gå til Oversikt over Power Query SDK.

I Power Query kan du kjøre egendefinerte opprinnelige spørringer mot datakilden for å hente dataene du leter etter. Du kan også aktivere muligheten til å opprettholde spørringsdelegering gjennom hele denne prosessen og påfølgende transformasjonsprosesser som gjøres i Power Query.

Målet med denne artikkelen er å vise hvordan du kan implementere en slik funksjon for den egendefinerte koblingen.

Forutsetning

Denne artikkelen bruker som utgangspunkt et eksempel som bruker SQL ODBC-driveren for datakilden. Implementeringen av den opprinnelige spørringsfunksjonen støttes for øyeblikket bare for ODBC-koblinger som overholder SQL-92-standarden.

Eksempelkoblingen bruker SQL Server Native Client 11.0-driveren . Kontroller at du har denne driveren installert for å følge denne opplæringen.

Du kan også vise den ferdige versjonen av eksempelkoblingen fra Fullfør-mappen i GitHub-repositoriet.

Endre SQLCapabilities for koblingen

I posten for SqlCapabilities eksempelkoblingen kan du finne et postfelt med navnet Sql92Translation og verdien PassThrough for den. Dette nye feltet er nødvendig for at den opprinnelige spørringen skal sendes ved hjelp av Power Query uten validering.

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"
]),

Kontroller at dette feltet vises i koblingen før du går videre. Hvis ikke, vil du møte advarsler og feil senere når det gjelder å bruke en funksjon som ikke støttes fordi den ikke er deklarert av koblingen.

Bygg koblingsfilen (som .mez eller.pqx) og last den inn i Power BI Desktop for manuell testing og for å definere målet for den opprinnelige spørringen.

Test de opprinnelige spørringsfunksjonene for koblingen manuelt

Obs!

I denne artikkelen skal vi bruke eksempeldatabasen AdventureWorks2019. Du kan imidlertid følge med alle SQL Server-databaser du ønsker, og gjøre de nødvendige endringene når det gjelder detaljene i den valgte databasen.

Måten støtte for opprinnelig spørring implementeres på i denne artikkelen, er at brukeren blir bedt om å angi tre verdier:

  • Servernavn
  • Databasenavn
  • Opprinnelig spørring på databasenivå

Gå nå til Hent data-opplevelsen i Power BI Desktop, og finn koblingen med navnet SqlODBC Sample.

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

Skriv inn parameterne for serveren og databasenavnet for koblingsdialogboksen. Velg deretter OK.

Screenshot of connector dialog with server and database as parameters.

Det vises et nytt navigatørvindu. I Navigator kan du vise den opprinnelige navigasjonsvirkemåten fra SQL-driveren som viser den hierarkiske visningen av serveren og databasene i den. Høyreklikk AdventureWorks2019-databasen, og velg deretter Transformer data.

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

Dette valget bringer deg til redigeringsprogrammet for Power Query og en forhåndsvisning av hva som i praksis er målet for den opprinnelige spørringen, siden alle opprinnelige spørringer skal kjøre på databasenivå. Undersøk formellinjen i det siste trinnet for bedre å forstå hvordan koblingen skal gå til målet for de opprinnelige spørringene før du kjører dem. I dette tilfellet viser formellinjen følgende informasjon:

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

Kilde er navnet på det forrige trinnet som i dette tilfellet ganske enkelt er den publiserte funksjonen til koblingen med parameterne sendt. Listen og posten i den hjelper bare med å navigere en tabell til en bestemt rad. Raden defineres av vilkårene fra posten der feltnavnet må være lik AdventureWorks2019, og Feltet Type må være lik Database. Når raden er plassert, [Data] gir utsiden av listen {} Power Query tilgang til verdien i datafeltet , som i dette tilfellet er en tabell. Du kan gå tilbake til forrige trinn (Kilde) for bedre å forstå denne navigasjonen.

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

Test opprinnelig spørring

Når målet nå er identifisert, oppretter du et egendefinert trinn etter navigasjonstrinnet ved å velge fx-ikonet på formellinjen.

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

Erstatt formelen i formellinjen med følgende formel, og velg deretter ENTER.

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

Når du har brukt denne endringen, skal det vises en advarsel under formellinjen der du ber om tillatelse til å kjøre den opprinnelige spørringen mot datakilden.

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

Velg Rediger tillatelse. En ny dialogboks for opprinnelig databasespørring vises som prøver å advare deg om mulighetene for å kjøre opprinnelige spørringer. I dette tilfellet vet vi at sql-setningen er trygg, så velg Kjør for å utføre kommandoen.

Screenshot showing how to approve a native database query dialog.

Når du har kjørt spørringen, vises en forhåndsvisning av spørringen i redigeringsprogrammet for Power Query. Denne forhåndsvisningen bekrefter at koblingen er i stand til å kjøre opprinnelige spørringer.

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

Implementere opprinnelig spørringslogikk i koblingen

Med informasjonen som er samlet inn fra de forrige inndelingene, er målet nå å oversette slik informasjon til kode for koblingen.

Måten du kan utføre denne oversettelsen på, er ved å legge til et nytt Postfelt for NativeQueryProperties i koblingens Publiser-post , som i dette tilfellet er SqlODBC.Publish posten. Posten NativeQueryProperties spiller en viktig rolle i å definere hvordan koblingen skal samhandle med Value.NativeQuery funksjonen.

Det nye postfeltet består av to felt:

  • Navigasjonstrinn: Dette feltet definerer hvordan navigasjonen skal utføres eller håndteres av koblingen. Den inneholder en liste over poster som beskriver trinnene for å navigere til de bestemte dataene du vil spørre ved hjelp av Value.NativeQuery funksjonen. I hver post definerer den hvilke parametere som kreves eller er nødvendige for at navigasjonen skal nå ønsket mål.
  • DefaultOptions: Dette feltet bidrar til å identifisere hvordan bestemte valgfrie parametere skal inkluderes eller legges til Value.NativeQuery i alternativposten. Den inneholder et sett med standardalternativer som kan brukes når du spør datakilden.

Navigasjonstrinnene kan kategoriseres i to grupper. Den første inneholder verdiene som er angitt av sluttbrukeren, for eksempel navnet på serveren eller databasen, i dette tilfellet. Den andre inneholder verdiene som er avledet av den spesifikke koblingsimplementeringen, for eksempel navnet på felt som ikke vises for brukeren under hent dataopplevelsen. Disse feltene kan omfatte Name, Kind, Dataog andre, avhengig av tilkoblingsimplementeringen.

I dette tilfellet var det bare ett navigasjonstrinn som besto av to felt:

  • Navn: Dette feltet er navnet på databasen som ble sendt av sluttbrukeren. I dette tilfellet var AdventureWorks2019det , men dette feltet bør alltid sendes som det er fra det sluttbrukeren skrev inn under hent dataopplevelsen.
  • Type: Dette feltet er informasjon som ikke er synlig for sluttbrukeren, og som er spesifikk for koblingen eller driverimplementeringen. I dette tilfellet identifiserer denne verdien hvilken type objekt som skal åpnes. For denne implementeringen vil dette feltet være en fast verdi som består av strengen Database.

Slik informasjon vil bli oversatt til følgende kode. Denne koden skal legges til som et nytt felt i SqlODBC.Publish posten.

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

Viktig

Navnet på feltene skiller mellom store og små bokstaver og må brukes som vist i eksemplet ovenfor. All informasjon som sendes til feltene, enten ConstantValue, IndexNameeller FieldDisplayName må være avledet fra koblingens M-kode.

For verdier som sendes fra det brukeren skrev inn, kan du bruke paret FieldDisplayName og IndexName. For verdier som er faste eller forhåndsdefinerte og ikke kan sendes av sluttbrukeren, kan du bruke paret ConstantValue og IndexName. I denne forstand består NavigationSteps-posten av to felt:

  • Indekser: Definerer hvilke felt og hvilke verdier som skal brukes til å navigere til posten som inneholder målet for Value.NativeQuery funksjonen.
  • FieldAccess: Definerer hvilket felt som inneholder målet, som vanligvis er en tabell.

DefaultOptions

Feltet DefaultOptions lar deg sende valgfrie parametere til Value.NativeQuery funksjonen når du bruker den opprinnelige spørringsfunksjonen for koblingen.

Hvis du vil bevare spørringsdelegering etter en opprinnelig spørring, og forutsatt at koblingen har spørringsdelegeringsfunksjoner, kan du bruke følgende eksempelkode for EnableFolding = true.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Når disse endringene er på plass, bygger du koblingen og laster den inn i Power BI Desktop for testing og validering.

Teste og validere koblingen

Start koblingen fra Hent data-opplevelsen i Power BI Desktop med den nye egendefinerte koblingen på plass. Når du starter koblingen, vil du legge merke til at dialogboksen nå har et langt tekstfelt med navnet Opprinnelig spørring , og i parentes har den de nødvendige feltene for at den skal fungere. Skriv inn de samme verdiene for serveren, databasen og SQL-setningen du tidligere skrev inn da du testet koblingen.

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

Når du har valgt OK, vises en forhåndsversjon av den utførte opprinnelige spørringen i en ny dialogboks.

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

Velg OK. En ny spørring lastes nå inn i redigeringsprogrammet for Power Query, der du kan utføre ytterligere testing av koblingen etter behov.

Obs!

Hvis koblingen har funksjoner for spørringsdelegering og eksplisitt har definert EnableFolding=true som en del av den valgfrie posten for , kan du teste koblingen ytterligere i redigeringsprogrammet for Value.NativeQueryPower Query ved å kontrollere om ytterligere transformasjoner brettes tilbake til kilden eller ikke.