Understøttelse af oprindelige forespørgsler i brugerdefinerede connectors i Power Query
Bemærk
I denne artikel beskrives avancerede emner om implementering af understøttelse af oprindelige forespørgsler for brugerdefinerede connectors samt forespørgselsdelegering oven på dem. I denne artikel antages det, at du allerede har en arbejdsviden om disse begreber.
Hvis du vil vide mere om brugerdefinerede connectors i Power Query, skal du gå til Oversigt over Power Query SDK.
I Power Query kan du udføre brugerdefinerede oprindelige forespørgsler mod din datakilde for at hente de data, du leder efter. Du kan også aktivere muligheden for at bevare forespørgselsdelegering i 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 funktion for din brugerdefinerede connector.
Denne artikel bruger som udgangspunkt et eksempel , der bruger SQL ODBC-driveren til datakilden. Implementeringen af den oprindelige forespørgselsfunktion understøttes i øjeblikket kun for ODBC-connectors, der overholder SQL-92-standarden.
Eksempelconnectoren bruger SQL Server Native Client 11.0-driveren . Sørg for, at du har installeret denne driver for at følge med i dette selvstudium.
Du kan også få vist den færdige version af eksempelconnectoren fra mappen Udfør i GitHub-lageret.
I posten SqlCapabilities
for eksempelconnectoren kan du finde et postfelt med navnet Sql92Translation
og værdien PassThrough for den. Dette nye felt er nødvendigt, for at den oprindelige forespørgsel kan overføres ved hjælp af 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 connectoren, før du går videre. Hvis ikke, får du advarsler og fejl senere, når det kommer til at bruge en funktion, der ikke understøttes, fordi den ikke er erklæret af connectoren.
Opret connectorfilen (som .mez eller.pqx), og indlæs den i Power BI Desktop til manuel test og for at definere destinationen for din oprindelige forespørgsel.
Bemærk
I denne artikel bruger vi eksempeldatabasen AdventureWorks2019. Men du kan følge med i en hvilken som helst SQL Server-database efter eget valg og foretage de nødvendige ændringer, når det kommer til specifikationerne for den valgte database.
Den måde, som understøttelse af oprindelige forespørgsler implementeres på i denne artikel, er, at brugeren bliver bedt om at angive tre værdier:
- Servernavn
- Databasenavn
- Oprindelig forespørgsel på databaseniveau
Nu i Power BI Desktop skal du gå til Hent data-oplevelsen og finde connectoren med navnet SqlODBC Sample.
I forbindelse med connectordialogboksen skal du angive parametrene for serveren og databasenavnet. Vælg derefter OK.
Der vises et nyt navigatorvindue. I Navigator kan du få vist den oprindelige navigationsfunktion fra den SQL-driver, der viser den hierarkiske visning af serveren og databaserne i den. Højreklik på databasen AdventureWorks2019, og vælg derefter Transformér data.
Med dette valg kommer du til Power Query-editoren og et eksempel på, hvad der er målet for din oprindelige forespørgsel, da alle oprindelige forespørgsler skal køre på databaseniveau. Undersøg formellinjen i det sidste trin for bedre at forstå, hvordan din connector skal navigere til destinationen for dine oprindelige forespørgsler, før du udfører dem. I dette tilfælde viser formellinjen følgende oplysninger:
= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]
Kilde er navnet på det forrige trin, der i dette tilfælde blot er den publicerede funktion for din connector med de overførte parametre. Listen og posten i den hjælper bare med at navigere en tabel til en bestemt række. Rækken er defineret af kriterierne fra den post, hvor feltet Name skal være lig med AdventureWorks2019 , og feltet Kind skal være lig med Database. Når rækken er placeret, [Data]
kan Power Query på ydersiden af listen {}
få adgang til værdien i feltet Data , hvilket i dette tilfælde er en tabel. Du kan gå tilbage til det forrige trin (Kilde) for bedre at forstå denne navigation.
Når målet nu er identificeret, skal du oprette et brugerdefineret trin efter navigationstrinnet ved at vælge ikonet fx på formellinjen.
Erstat formlen på formellinjen med følgende formel, og vælg derefter Enter.
= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
FROM [Person].[Address]")
Når du har anvendt denne ændring, vises der en advarsel under formellinjen, der anmoder om tilladelse til at køre den oprindelige forespørgsel på din datakilde.
Vælg Rediger tilladelse. Der vises en ny dialogboks med oprindelig databaseforespørgsel , der forsøger at advare dig om mulighederne for at køre oprindelige forespørgsler. I dette tilfælde ved vi, at denne SQL-sætning er sikker, så vælg Kør for at udføre kommandoen.
Når du har kørt din forespørgsel, vises der et eksempel på din forespørgsel i Power Query-editoren. Denne prøveversion validerer, at din connector kan køre oprindelige forespørgsler.
Med de oplysninger, der er indsamlet fra de forrige afsnit, er målet nu at oversætte disse oplysninger til kode for din connector.
Den måde, du kan udføre denne oversættelse på, er ved at føje et nyt felt af typen NativeQueryProperties til din connectors Publicer-post , som i dette tilfælde er posten SqlODBC.Publish
. Posten NativeQueryProperties
spiller en afgørende rolle i definitionen af, hvordan connectoren interagerer med funktionen Value.NativeQuery
.
Det nye postfelt 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 beskriver trinnene til navigering til de specifikke data, du vil forespørge om ved hjælp af funktionen
Value.NativeQuery
. I hver post definerer den, hvilke parametre der kræves eller skal bruges, for at en sådan navigation kan nå dit ønskede mål. - DefaultOptions: Dette felt hjælper med at identificere, hvordan visse valgfri parametre skal medtages eller føjes til indstillingsposten
Value.NativeQuery
. Den indeholder et sæt standardindstillinger, der kan bruges, når du forespørger datakilden.
Dine navigationstrin kan kategoriseres i to grupper. Den første indeholder de værdier, der angives af slutbrugeren, f.eks. navnet på serveren eller databasen i dette tilfælde. Den anden indeholder de værdier, der er afledt af den specifikke connectorimplementering, f.eks. navnet på felter, der ikke vises for brugeren under hentning af data. Disse felter kan omfatte Name
, Kind
, Data
og andre, afhængigt af din connectorimplementering.
I dette tilfælde var der kun ét navigationstrin, der bestod af to felter:
- Navn: Dette felt er navnet på den database, der blev sendt af slutbrugeren. I dette tilfælde var
AdventureWorks2019
det , men dette felt skal altid overføres, som det er, fra det, som slutbrugeren indtastede under hent dataoplevelsen. - Type: Dette felt er oplysninger, der ikke er synlige for slutbrugeren, og som er specifikke for connectoren eller driverimplementering. I dette tilfælde identificerer denne værdi, hvilken type objekt der skal tilgås. Til denne implementering er dette felt en fast værdi, der består af strengen
Database
.
Disse oplysninger oversættes til følgende kode. Denne kode skal føjes som et nyt felt til posten SqlODBC.Publish
.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
}
]
Vigtigt
Der er forskel på store og små bogstaver i felternes navn, og det skal bruges som vist i eksemplet ovenfor. Alle oplysninger, der overføres til felterne, enten ConstantValue
, IndexName
eller FieldDisplayName
skal være afledt af connectorens M-kode.
For værdier, der overføres fra det, brugeren har angivet, kan du bruge parret FieldDisplayName
og IndexName
. For værdier, der er faste eller foruddefinerede og ikke kan overføres af slutbrugeren, kan du bruge parret ConstantValue
og IndexName
. I den forstand består posten NavigationSteps af to felter:
- Indekser: Definerer, hvilke felter og hvilke værdier der skal bruges til at navigere til den post, der indeholder destinationen for funktionen
Value.NativeQuery
. - FieldAccess: Definerer, hvilket felt der indeholder målet, hvilket ofte er en tabel.
Feltet DefaultOptions
giver dig mulighed for at overføre valgfrie parametre til funktionen Value.NativeQuery
, når du bruger den oprindelige forespørgselsfunktion for din connector.
Hvis du vil bevare forespørgselsdelegering efter en oprindelig forespørgsel og antage, at din connector har forespørgselsdelegeringsfunktioner, kan du bruge følgende eksempelkode til EnableFolding = true
.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
},
DefaultOptions = [
EnableFolding = true
]
]
Når disse ændringer er på plads, kan du bygge connectoren og indlæse den i Power BI Desktop til test og validering.
Start connectoren fra oplevelsen Hent data i Power BI Desktop med din nye brugerdefinerede connector på plads. Når du starter connectoren, kan du se, at dialogboksen nu har et langt tekstfelt med navnet Oprindelig forespørgsel , og at den i parentes har de påkrævede felter, før den fungerer. Angiv de samme værdier for serveren, databasen og den SQL-sætning, du tidligere har angivet, da du testede connectoren.
Når du har valgt OK, vises der en tabeleksempel på den udførte oprindelige forespørgsel i en ny dialogboks.
Vælg OK. En ny forespørgsel indlæses nu i Power Query-editoren, hvor du kan udføre yderligere test af din connector efter behov.
Bemærk
Hvis din connector har forespørgselsdelegeringsfunktioner og eksplicit har defineret EnableFolding=true
som en del af den valgfri post for Value.NativeQuery
, kan du teste din connector yderligere i Power Query-editoren ved at kontrollere, om foldning igen kan konverteres til kilden eller ej.