Dela via

Stöd för interna frågor i anpassade Power Query-anslutningsappar

  • Artikel

Kommentar

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.

Förutsättningar

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.

Ändra SQLCapabilities för din anslutningsapp

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.

Testa anslutningsappens interna frågefunktioner manuellt

Kommentar

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.

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

I dialogrutan för anslutningsappen anger du parametrarna för servern och databasnamnet. Välj sedan OK.

Screenshot of connector dialog with server and database as parameters.

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.

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

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.

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

Testa intern fråga

Nu när målet har identifierats skapar du ett anpassat steg efter navigeringssteget genom att välja fx-ikonen i formelfältet.

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

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.

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

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.

Screenshot showing how to approve a native database query dialog.

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.

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

Implementera intern frågelogik i anslutningsappen

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, Dataoch 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 AdventureWorks2019det , 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, IndexNameeller 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.

DefaultOptions

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.

Testa och verifiera anslutningsappen

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.

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

När du har valt OK visas en tabellförhandsgranskning av den körda interna frågan i en ny dialogruta.

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

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.

Kommentar

Om anslutningsappen har frågedelegeringsfunktioner och uttryckligen har definierats EnableFolding=true som en del av den valfria posten för Value.NativeQuerykan du ytterligare testa anslutningsappen i Power Query-redigeraren genom att kontrollera om ytterligare transformeringar viks tillbaka till källan eller inte.