Stöd för interna frågor i anpassade Power Query-anslutningsappar
Anteckning
Den här artikeln beskriver avancerade avsnitt om implementering av inbyggt frågestöd för anpassade anslutningsappar, samt frågedelegering ovanpå dem. Den här artikeln förutsätter att du redan har en fungerande kunskap om dessa begrepp.
Mer information om anpassade Power Query-anslutningsappar finns i Översikt över Power Query SDK.
I Power Query kan du köra anpassade interna frågor mot din datakälla för att hämta de data som du letar efter. Du kan också aktivera funktionen för att upprätthålla frågedelegering under hela den här processen och efterföljande transformeringsprocesser som utförs i Power Query.
Målet med den här artikeln är att visa hur du kan implementera en sådan funktion för din anpassade anslutningsapp.
Den här artikeln använder som utgångspunkt ett exempel som använder SQL ODBC-drivrutinen för sin datakälla. Implementeringen av den interna frågefunktionen stöds för närvarande endast för ODBC-anslutningsappar som följer SQL-92-standarden.
Exempelanslutningsappen använder sql Server Native Client 11.0-drivrutinen . Se till att du har den här drivrutinen installerad för att följa med i den här självstudien.
Du kan också visa den färdiga versionen av exempelanslutningsappen från mappen Slutför i GitHub-lagringsplatsen.
SqlCapabilities
I posten för exempelanslutningsappen hittar du ett postfält med namnet Sql92Translation
och värdet PassThrough för det. Det här nya fältet är nödvändigt för att den interna frågan ska skickas med Power Query utan någon 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"
]),
Kontrollera att det här fältet visas i anslutningsappen innan du går vidare. Annars får du varningar och fel senare när det gäller att använda en funktion som inte stöds eftersom den inte deklareras av anslutningsappen.
Skapa anslutningsfilen (som .mez eller.pqx) och läs in den i Power BI Desktop för manuell testning och definiera målet för din interna fråga.
Anteckning
I den här artikeln använder vi exempeldatabasen AdventureWorks2019. Men du kan följa med valfri SQL Server-databas och göra nödvändiga ändringar när det gäller de specifika egenskaperna för den valda databasen.
Det sätt på vilket inbyggt frågestöd implementeras i den här artikeln är att användaren uppmanas att ange tre värden:
- Servernamn
- Databasnamn
- Intern fråga på databasnivå
Nu i Power BI Desktop går du till Hämta data-upplevelsen och hittar anslutningsappen med namnet SqlODBC-exempel.
I dialogrutan för anslutningsappen anger du parametrarna för servern och databasnamnet. Välj sedan OK.
Ett nytt navigeringsfönster visas. I Navigatör kan du visa det interna navigeringsbeteendet från SQL-drivrutinen som visar den hierarkiska vyn för servern och databaserna i den. Högerklicka på databasen AdventureWorks2019 och välj sedan Transformera data.
Det här valet tar dig till Power Query-redigeraren och en förhandsversion av vad som i praktiken är målet för din interna fråga eftersom alla interna frågor ska köras på databasnivå. Granska formelfältet i det sista steget för att bättre förstå hur anslutningsappen ska navigera till målet för dina interna frågor innan du kör dem. I det här fallet visar formelfältet följande information:
= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]
Källan är namnet på föregående steg som i det här fallet helt enkelt är den publicerade funktionen för anslutningsappen med de parametrar som skickas. Listan och posten i den hjälper bara till att navigera en tabell till en viss rad. Raden definieras av villkoret från posten där fältet Namn måste vara lika med AdventureWorks2019 och fältet Typ måste vara lika med Databasen. När raden finns [Data]
kan Power {}
Query komma åt värdet i fältet Data , vilket i det här fallet är en tabell. Du kan gå tillbaka till föregående steg (källa) för att bättre förstå den här navigeringen.
Nu när målet har identifierats skapar du ett anpassat steg efter navigeringssteget genom att välja fx-ikonen i formelfältet.
Ersätt formeln i formelfältet med följande formel och välj sedan Retur.
= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
FROM [Person].[Address]")
När du har tillämpat den här ändringen bör en varning visas under formelfältet och begära behörighet att köra den interna frågan mot datakällan.
Välj Redigera behörighet. En ny dialogruta för intern databasfråga visas som försöker varna dig om möjligheterna att köra interna frågor. I det här fallet vet vi att den här SQL-instruktionen är säker, så välj Kör för att köra kommandot.
När du har kört frågan visas en förhandsgranskning av frågan i Power Query-redigeraren. Den här förhandsversionen verifierar att anslutningsappen kan köra interna frågor.
Med den information som samlats in från föregående avsnitt är målet nu att översätta sådan information till kod för din anslutningsapp.
Det sätt som du kan göra den här översättningen SqlODBC.Publish
på är genom att lägga till ett nytt NativeQueryProperties-postfält i anslutningsappens Publiceringspost, som i det här fallet är posten. Posten NativeQueryProperties
spelar en viktig roll när det gäller att definiera hur anslutningsappen ska interagera med Value.NativeQuery
funktionen.
Det nya postfältet består av två fält:
- NavigationSteps: Det här fältet definierar hur navigeringen ska utföras eller hanteras av anslutningsappen. Den innehåller en lista med poster som beskriver stegen för att navigera till de specifika data som du vill köra frågor mot med hjälp av
Value.NativeQuery
funktionen. I varje post definierar den vilka parametrar som krävs eller behövs för att sådan navigering ska nå önskat mål. - DefaultOptions: Det här fältet hjälper dig att identifiera hur vissa valfria parametrar ska inkluderas eller läggas till i
Value.NativeQuery
alternativposten. Den innehåller en uppsättning standardalternativ som kan användas när du frågar datakällan.
Dina navigeringssteg kan kategoriseras i två grupper. Den första innehåller de värden som anges av slutanvändaren, till exempel namnet på servern eller databasen, i det här fallet. Den andra innehåller de värden som härleds av den specifika anslutningsimplementeringen, till exempel namnet på fält som inte visas för användaren under hämta dataupplevelsen. De här fälten kan vara Name
, Kind
, Data
och andra beroende på implementeringen av anslutningsappen.
I det här fallet fanns det bara ett navigeringssteg som bestod av två fält:
- Namn: Det här fältet är namnet på databasen som skickades av slutanvändaren. I det här fallet var
AdventureWorks2019
det , men det här fältet bör alltid skickas som det är från vad slutanvändaren angav under hämtar dataupplevelsen. - Typ: Det här fältet är information som inte är synlig för slutanvändaren och som är specifik för implementeringen av anslutningsappen eller drivrutinen. I det här fallet identifierar det här värdet vilken typ av objekt som ska nås. För den här implementeringen är det här fältet ett fast värde som består av strängen
Database
.
Sådan information kommer att översättas till följande kod. Den här koden bör läggas till som ett nytt fält i din SqlODBC.Publish
post.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
}
]
Viktigt
Fältens namn är skiftlägeskänsliga och måste användas enligt exemplet ovan. All information som skickas till fälten, antingen ConstantValue
, IndexName
eller FieldDisplayName
måste härledas från anslutningsappens M-kod.
För värden som skickas från det användaren angav kan du använda paret FieldDisplayName
och IndexName
. För värden som är fasta eller fördefinierade och inte kan skickas av slutanvändaren kan du använda paret ConstantValue
och IndexName
. I den meningen består posten NavigationSteps av två fält:
- Index: Definierar vilka fält och vilka värden som ska användas för att navigera till posten som innehåller målet för
Value.NativeQuery
funktionen. - FieldAccess: Definierar vilket fält som innehåller målet, vilket vanligtvis är en tabell.
Med DefaultOptions
fältet kan du skicka valfria parametrar till Value.NativeQuery
funktionen när du använder den interna frågefunktionen för anslutningsappen.
Om du vill bevara frågedelegering efter en intern fråga, och förutsatt att anslutningsappen har frågedelegeringsfunktioner, kan du använda följande exempelkod för EnableFolding = true
.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
},
DefaultOptions = [
EnableFolding = true
]
]
Med dessa ändringar på plats skapar du anslutningsappen och läser in den i Power BI Desktop för testning och validering.
I Power BI Desktop med din nya anpassade anslutningsapp på plats startar du anslutningsappen från get data-upplevelsen . När du startar anslutningsappen ser du att dialogrutan nu har ett långt textfält med namnet Intern fråga och inom parentes har den de fält som krävs för att den ska fungera. Ange samma värden för servern, databasen och SQL-instruktionen som du angav tidigare när du testade anslutningsappen.
När du har valt OK visas en tabellförhandsgranskning av den körda interna frågan i en ny dialogruta.
Välj OK. En ny fråga läses nu in i Power Query-redigeraren där du kan göra ytterligare testning av anslutningsappen efter behov.
Anteckning
Om anslutningsappen har frågedelegeringsfunktioner och uttryckligen har definierats EnableFolding=true
som en del av den valfria posten för Value.NativeQuery
kan du ytterligare testa anslutningsappen i Power Query-redigeraren genom att kontrollera om ytterligare transformeringar viks tillbaka till källan eller inte.