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.
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.
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.
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.
Skriv inn parameterne for serveren og databasenavnet for koblingsdialogboksen. Velg deretter OK.
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.
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.
Når målet nå er identifisert, oppretter du et egendefinert trinn etter navigasjonstrinnet ved å velge fx-ikonet på formellinjen.
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.
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.
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.
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
, Data
og 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
AdventureWorks2019
det , 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
, IndexName
eller 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.
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.
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.
Når du har valgt OK, vises en forhåndsversjon av den utførte opprinnelige spørringen i en ny dialogboks.
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.NativeQuery
Power Query ved å kontrollere om ytterligere transformasjoner brettes tilbake til kilden eller ikke.