Del via

Indbygget forespørgselsunderstøttelse i Power Query brugerdefinerede connectorer

Notat

Denne artikel dækker avancerede emner omkring implementeringen af native forespørgselsunderstøttelse for brugerdefinerede connectors samt forespørgselsfoldning ovenpå dem. Denne artikel forudsætter, at du allerede har en grundlæggende viden om disse begreber.

For at lære mere om Power Query brugerdefinerede connectors, gå til Power Query SDK Oversigt.

I Power Query kan du udføre brugerdefinerede native forespørgsler mod din datakilde for at hente de data, du leder efter. Du kan også aktivere muligheden for at opretholde query folding gennem hele denne proces og efterfølgende transformationsprocesser, der udføres i Power Query.

Målet med denne artikel er at vise, hvordan du kan implementere en sådan funktionalitet til din brugerdefinerede connector.

Forudsætninger

Denne artikel bruger som udgangspunkt et eksempel , der bruger SQL ODBC-driveren som datakilde. Implementeringen af den oprindelige forespørgselsfunktion understøttes i øjeblikket kun for ODBC-connectorer, der følger SQL-92-standarden.

Eksempelconnectoren bruger SQL Server Native Client 11.0-driveren . Sørg for, at du har denne driver installeret for at følge denne vejledning.

Du kan også se den færdige version af sample connectoren fra Finish-mappen i GitHub-repositoriet.

Ændr SQLCapabilities for din connector

I posten SqlCapabilities for sample connectoren kan du finde et postfelt med navnet Sql92Translation og værdien PassThrough for det. Dette nye felt er nødvendigt for, at den native forespørgsel kan sendes via Power Query uden nogen 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"
]),

Sørg for, at dette felt vises i din connector, før du går videre. Hvis ikke, vil du senere få advarsler og fejl, når det handler om at bruge en funktion, der ikke understøttes, fordi den ikke er deklareret af connectoren.

Byg connector-filen (som .mez eller .pqx) og indlæs den i Power BI Desktop til manuel test og for at definere målet for din native forespørgsel.

Test manuelt de native forespørgselsmuligheder i din connector

Notat

Til denne artikel vil vi bruge AdventureWorks2019 eksempeldatabasen. Men du kan følge med i enhver SQL Server-database efter eget valg og foretage de nødvendige ændringer, når det gælder detaljerne i den valgte database.

Måden native forespørgselsunderstøttelse vil blive implementeret på i denne artikel, er, at brugeren vil blive bedt om at indtaste tre værdier:

  • Servernavn
  • Databasenavn
  • Native forespørgsel på databaseniveau

Nu inde i Power BI Desktop, gå til Get Data-oplevelsen og find connectoren med navnet SqlODBC Sample.

Skærmbillede af connectoren fundet inde i get data-oplevelsen i Power BI Desktop.

For connector-dialogen skal du indtaste parametrene for din server og dit databasenavn. Vælg derefter OK.

Skærmbillede af connector-dialog med server og database som parametre.

Et nyt navigatorvindue dukker op. I Navigator kan du se den native navigationsadfærd fra SQL-driveren, der viser den hierarkiske visning af serveren og databaserne i den. Højreklik på AdventureWorks2019-databasen , og vælg derefter Transformér data.

Skærmbillede af muligheden for at transformere data fra den kontekstuelle menu inde i Navigator-vinduet.

Denne markering fører dig til Power Query-editoren og en forhåndsvisning af, hvad der reelt er målet for din native forespørgsel, da alle native forespørgsler bør køre på databaseniveau. Undersøg formellinjen for det sidste trin for bedre at forstå, hvordan din connector skal navigere til målet for dine native forespørgsler, før du udfører dem. I dette tilfælde viser formelbjælken følgende information:

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

Source er navnet på det forrige trin, som i dette tilfælde blot er den offentliggjorte funktion af din connector med de parametre, der er overgivet. Listen og posten indeni hjælper bare med at navigere en tabel til en bestemt række. Rækken defineres af kriterierne fra posten, hvor feltnavnet skal være lig med AdventureWorks2019 , og feltet Slags skal være lig med databasen. Når rækken er lokaliseret, [Data] giver ydersiden af listen {} Power Query adgang til værdien inde i datafeltet , som i dette tilfælde er en tabel. Du kan gå tilbage til det forrige trin (Kilde) for bedre at forstå denne navigation.

Skærmbillede af en tabel, der viser de værdier og felter, der blev brugt til navigationstrinnet.

Test den oprindelige forespørgsel

Med målet nu identificeret, opret et brugerdefineret trin efter navigationstrinnet ved at vælge fx-ikonet i formellinjen.

Skærmbillede af effektknappen inde i formlen, som bruges til at oprette et brugerdefineret trin.

Erstat formlen inde i formellinjen med følgende formel, og vælg derefter Enter.

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

Efter du har anvendt denne ændring, bør en advarsel vises under formellinjen, der anmoder om tilladelse til at køre den native forespørgsel mod din datakilde.

Et screenshot af tilladelsen kræves for at køre denne advarselsmeddelelse om native databaseforespørgsel.

Vælg Redigeringstilladelse. En ny dialogboks for Native Database Query vises, som forsøger at advare dig om mulighederne ved at køre native forespørgsler. I dette tilfælde ved vi, at denne SQL-sætning er sikker, så vælg Run for at udføre kommandoen.

Skærmbillede, der viser, hvordan man godkender en native databaseforespørgselsdialog.

Efter du har kørt din forespørgsel, vises en forhåndsvisning af din forespørgsel i Power Query-editoren. Denne forhåndsvisning bekræfter, at din connector er i stand til at køre native forespørgsler.

Skærmbillede af den oprindelige forespørgsel udført under den indledende udvikling og test af connectoren.

Implementer native forespørgselslogik i din connector

Med informationen fra de foregående afsnit er målet nu at oversætte sådan information til kode til din connector.

Måden, du kan opnå denne oversættelse på, er ved at tilføje et nyt NativeQueryProperties-postfelt til din connectors Publicer-post , som i dette tilfælde er posten SqlODBC.Publish . Registreringen NativeQueryProperties spiller en afgørende rolle i at definere, hvordan forbindelsen vil interagere med Value.NativeQuery funktionen.

Det nye rekordfelt består af to felter:

  • NavigationSteps: Dette felt definerer, hvordan navigationen skal udføres eller håndteres af din connector. Den indeholder en liste over poster, der skitserer trinene til at navigere til de specifikke data, du ønsker at forespørge ved hjælp af Value.NativeQuery funktionen. Inden for hver post defineres det, hvilke parametre der kræves eller er nødvendige for, at sådan navigation kan nå dit ønskede mål.
  • DefaultOptions: Dette felt hjælper med at identificere, hvordan visse valgfrie parametre skal inkluderes eller tilføjes optionsposten Value.NativeQuery . Den tilbyder et sæt standardindstillinger, der kan bruges, når man forespørger datakilden.

Dine navigationstrin kan opdeles i to grupper. Den første indeholder de værdier, som slutbrugeren indtaster, såsom navnet på serveren eller databasen i dette tilfælde. Den anden indeholder de værdier, der er afledt af den specifikke connector-implementering, såsom navnet på felter, der ikke vises for brugeren under hentedataoplevelsen. Disse felter kan inkludere Name, Kind, Data, og andre afhængigt af din connector-implementering.

I dette tilfælde var der kun ét navigationstrin, der bestod af to felter:

  • Navn: Dette felt er navnet på den database, som slutbrugeren har sendt. I dette tilfælde var AdventureWorks2019det , men dette felt bør altid sendes as-is fra det, slutbrugeren indtastede under hentedataoplevelsen.
  • Type: Dette felt er information, der ikke er synlig for slutbrugeren og er specifik for connector- eller driverimplementeringen. I dette tilfælde identificerer denne værdi, hvilken type objekt der skal tilgås. For denne implementering vil dette felt være en fast værdi, der består af strengen Database.

Sådanne oplysninger vil blive oversat til følgende kode. Denne kode bør tilføjes som et nyt felt til din SqlODBC.Publish post.

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

Vigtigt

Navnene på felterne er små og små bogstaver og skal bruges som vist i eksemplet ovenfor. Al information, der sendes til felterne, enten ConstantValue, IndexName, eller FieldDisplayName skal stamme fra connectorens M-kode.

For værdier, der skal sendes fra det, brugeren indtastede, kan du bruge parret FieldDisplayName og IndexName. For værdier, der er faste eller foruddefinerede og ikke kan sendes videre af slutbrugeren, kan du bruge parret ConstantValue og IndexName. I denne forstand består NavigationSteps-posten af to felter:

  • Indekser: Definerer hvilke felter og værdier, der skal bruges til at navigere til posten, der indeholder målet for Value.NativeQuery funktionen.
  • FieldAccess: Definerer, hvilket felt der indeholder målet, hvilket ofte er en tabel.

DefaultOptions

Feltet DefaultOptions lader dig sende valgfrie parametre til Value.NativeQuery funktionen, når du bruger den native forespørgselsfunktion til din connector.

For at bevare forespørgselsfoldning efter en native forespørgsel, og forudsat at din connector har forespørgselsfoldningsfunktioner, kan du bruge følgende eksempelkode for EnableFolding = true.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Med disse ændringer på plads bygger du connectoren og indlæser den i Power BI Desktop til test og validering.

Test og valider stikket

I Power BI Desktop med dit nye specialtilsatte stik på plads, start stikket fra Get Data-oplevelsen . Når du starter connectoren, vil du bemærke, at dialogen nu har et langt tekstfelt med navnet Native forespørgsel , og i parentes har den de nødvendige felter for at det skal fungere. Indtast de samme værdier for serveren, databasen og SQL-sætningen, som du tidligere indtastede, da du testede connectoren.

Skærmbillede af connector-dialogen med det oprindelige forespørgselsfelt i lang tekst vist.

Efter du har valgt OK, vises en tabelforhåndsvisning af den udførte native forespørgsel i en ny dialog.

Skærmbillede af dialogen med en tabelforhåndsvisning af den udførte native forespørgsel.

Vælg OK. En ny forespørgsel vil nu indlæses i Power Query-editoren, hvor du kan teste din connector yderligere efter behov.

Notat

Hvis din connector har query folding-muligheder og eksplicit er defineret EnableFolding=true som en del af den valgfrie post for Value.NativeQuery, kan du yderligere teste din connector i Power Query-editoren ved at tjekke, om yderligere transformationer foldes tilbage til kilden eller ej.

  • Last updated on