Delen via

Systeemeigen queryondersteuning in aangepaste Power Query-connectors

  • Artikel

Notitie

In dit artikel worden geavanceerde onderwerpen behandeld over de implementatie van systeemeigen queryondersteuning voor aangepaste connectors, evenals het vouwen van query's bovenaan. In dit artikel wordt ervan uitgegaan dat u al een werkende kennis van deze concepten hebt.

Ga naar Overzicht van Power Query SDK voor meer informatie over aangepaste Power Query-connectors.

In Power Query kunt u aangepaste systeemeigen query's uitvoeren op uw gegevensbron om de gegevens op te halen die u zoekt. U kunt ook de mogelijkheid inschakelen om het vouwen van query's in dit proces te behouden en de daaropvolgende transformatieprocessen die in Power Query worden uitgevoerd.

Het doel van dit artikel is om te laten zien hoe u dergelijke mogelijkheden voor uw aangepaste connector kunt implementeren.

Vereisten

In dit artikel wordt een voorbeeld gebruikt dat gebruikmaakt van het SQL ODBC-stuurprogramma voor de gegevensbron. De implementatie van de systeemeigen querymogelijkheid wordt momenteel alleen ondersteund voor ODBC-connectors die voldoen aan de SQL-92-standaard.

De voorbeeldconnector maakt gebruik van het stuurprogramma SQL Server Native Client 11.0 . Zorg ervoor dat dit stuurprogramma is geïnstalleerd om deze zelfstudie te volgen.

U kunt ook de voltooide versie van de voorbeeldconnector bekijken vanuit de map Voltooien in de GitHub-opslagplaats.

De SQLCapabilities van uw connector wijzigen

In de SqlCapabilities record van de voorbeeldconnector vindt u een recordveld met de naam Sql92Translation en de waarde PassThrough . Dit nieuwe veld is nodig om de systeemeigen query door te geven met behulp van Power Query zonder validatie.

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"
]),

Zorg ervoor dat dit veld wordt weergegeven in de connector voordat u verdergaat. Zo niet, dan ziet u later waarschuwingen en fouten als het gaat om het gebruik van een mogelijkheid die niet wordt ondersteund, omdat deze niet door de connector wordt gedeclareerd.

Bouw het connectorbestand (als .mez of.pqx) en laad het in Power BI Desktop voor handmatig testen en om het doel voor uw systeemeigen query te definiëren.

De systeemeigen querymogelijkheden van uw connector handmatig testen

Notitie

Voor dit artikel gebruiken we de voorbeelddatabase AdventureWorks2019. U kunt echter ook elke SQL Server-database van uw keuze volgen en de benodigde wijzigingen aanbrengen als het gaat om de specifieke kenmerken van de gekozen database.

De manier waarop systeemeigen queryondersteuning wordt geïmplementeerd in dit artikel, is dat de gebruiker wordt gevraagd om drie waarden in te voeren:

  • Servernaam
  • Databasenaam
  • Systeemeigen query op databaseniveau

Ga nu in Power BI Desktop naar de ervaring Gegevens ophalen en zoek de connector met de naam SqlODBC-voorbeeld.

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

Voer voor het dialoogvenster connector de parameters voor uw server en de databasenaam in. Selecteer vervolgens OK.

Screenshot of connector dialog with server and database as parameters.

Er wordt een nieuw navigatorvenster weergegeven. In Navigator kunt u het systeemeigen navigatiegedrag bekijken vanuit het SQL-stuurprogramma waarin de hiërarchische weergave van de server en de databases daarin wordt weergegeven. Klik met de rechtermuisknop op de database AdventureWorks2019 en selecteer Gegevens transformeren.

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

Deze selectie brengt u naar de Power Query-editor en een voorbeeld van wat het doel van uw systeemeigen query is, omdat alle systeemeigen query's op databaseniveau moeten worden uitgevoerd. Inspecteer de formulebalk van de laatste stap om beter te begrijpen hoe uw connector naar het doel van uw systeemeigen query's moet navigeren voordat u deze uitvoert. In dit geval geeft de formulebalk de volgende informatie weer:

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

Bron is de naam van de vorige stap die in dit geval gewoon de gepubliceerde functie van uw connector is met de doorgegeven parameters. De lijst en de record in de lijst helpen alleen om door een tabel naar een specifieke rij te navigeren. De rij wordt gedefinieerd door de criteria uit de record waarin de veldnaam gelijk moet zijn aan AdventureWorks2019 en het veld Type moet gelijk zijn aan Database. Zodra de rij zich bevindt, [Data] heeft Power Query toegang {} tot de waarde in het veld Gegevens , die in dit geval een tabel is. U kunt teruggaan naar de vorige stap (bron) om deze navigatie beter te begrijpen.

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

Systeemeigen query testen

Nu het doel is geïdentificeerd, maakt u een aangepaste stap na de navigatiestap door het fx-pictogram in de formulebalk te selecteren.

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

Vervang de formule in de formulebalk door de volgende formule en selecteer Vervolgens Enter.

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

Nadat u deze wijziging hebt toegepast, wordt er een waarschuwing weergegeven onder de formulebalk die toestemming vraagt om de systeemeigen query uit te voeren op uw gegevensbron.

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

Selecteer Machtiging bewerken. Er wordt een nieuw dialoogvenster systeemeigen databasequery weergegeven dat u probeert te waarschuwen over de mogelijkheden van het uitvoeren van systeemeigen query's. In dit geval weten we dat deze SQL-instructie veilig is, dus selecteer Uitvoeren om de opdracht uit te voeren.

Screenshot showing how to approve a native database query dialog.

Nadat u de query hebt uitgevoerd, wordt een voorbeeld van uw query weergegeven in de Power Query-editor. Met deze preview wordt gecontroleerd of uw connector systeemeigen query's kan uitvoeren.

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

Systeemeigen querylogica implementeren in uw connector

Nu de informatie uit de vorige secties is verzameld, is het doel om dergelijke informatie te vertalen in code voor uw connector.

De manier waarop u deze vertaling kunt uitvoeren, is door een nieuw NativeQueryProperties-recordveld toe te voegen aan de publicatierecord van uw connector, wat in dit geval de SqlODBC.Publish record is. De NativeQueryProperties record speelt een cruciale rol bij het definiëren van de manier waarop de connector met de Value.NativeQuery functie communiceert.

Het nieuwe recordveld bestaat uit twee velden:

  • NavigationSteps: In dit veld wordt gedefinieerd hoe de navigatie moet worden uitgevoerd of verwerkt door uw connector. Het bevat een lijst met records waarin de stappen worden beschreven voor het navigeren naar de specifieke gegevens die u wilt opvragen met behulp van de Value.NativeQuery functie. Binnen elke record wordt gedefinieerd welke parameters vereist of nodig zijn om dergelijke navigatie te bereiken naar uw gewenste doel.
  • DefaultOptions: Dit veld helpt te bepalen hoe bepaalde optionele parameters moeten worden opgenomen of toegevoegd aan de Value.NativeQuery optiesrecord. Het biedt een set standaardopties die kunnen worden gebruikt bij het uitvoeren van query's op de gegevensbron.

Uw navigatiestappen kunnen worden onderverdeeld in twee groepen. De eerste bevat de waarden die worden ingevoerd door de eindgebruiker, zoals de naam van de server of de database, in dit geval. De tweede bevat de waarden die worden afgeleid door de specifieke connector-implementatie, zoals de naam van velden die niet worden weergegeven aan de gebruiker tijdens het ophalen van gegevens. Deze velden kunnen onder andere Namezijn, KindDataen andere, afhankelijk van de implementatie van uw connector.

In dit geval bestond er slechts één navigatiestap uit twee velden:

  • Naam: Dit veld is de naam van de database die is doorgegeven door de eindgebruiker. In dit geval was AdventureWorks2019het, maar dit veld moet altijd worden doorgegeven als van wat de eindgebruiker heeft ingevoerd tijdens het ophalen van gegevens.
  • Soort: Dit veld is informatie die niet zichtbaar is voor de eindgebruiker en specifiek is voor de connector- of stuurprogramma-implementatie. In dit geval geeft deze waarde aan welk type object moet worden geopend. Voor deze implementatie is dit veld een vaste waarde die bestaat uit de tekenreeks Database.

Dergelijke informatie wordt vertaald naar de volgende code. Deze code moet worden toegevoegd als een nieuw veld aan uw SqlODBC.Publish record.

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

Belangrijk

De naam van de velden is hoofdlettergevoelig en moet worden gebruikt zoals wordt weergegeven in het bovenstaande voorbeeld. Alle informatie die wordt doorgegeven aan de velden, ofwel ConstantValue, IndexNameof FieldDisplayName moet worden afgeleid van de M-code van de connector.

Voor waarden die worden doorgegeven van wat de gebruiker heeft ingevoerd, kunt u het paar FieldDisplayName en IndexName. Voor waarden die zijn vast of vooraf gedefinieerd en die niet door de eindgebruiker kunnen worden doorgegeven, kunt u het paar ConstantValue en IndexName. In dit opzicht bestaat de record NavigationSteps uit twee velden:

  • Indexen: Definieert welke velden en welke waarden moeten worden gebruikt om naar de record te navigeren die het doel voor de Value.NativeQuery functie bevat.
  • FieldAccess: Definieert welk veld het doel bevat, wat meestal een tabel is.

DefaultOptions

DefaultOptions In het veld kunt u optionele parameters doorgeven aan de Value.NativeQuery functie wanneer u de systeemeigen querymogelijkheid voor uw connector gebruikt.

Als u het vouwen van query's wilt behouden na een systeemeigen query en ervan uitgaande dat uw connector mogelijkheden voor het vouwen van query's heeft, kunt u de volgende voorbeeldcode voor EnableFolding = truegebruiken.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Met deze wijzigingen bouwt u de connector en laadt u deze in Power BI Desktop om te testen en te valideren.

De connector testen en valideren

Start in Power BI Desktop met uw nieuwe aangepaste connector de connector vanuit de ervaring Gegevens ophalen. Wanneer u de connector start, ziet u dat het dialoogvenster nu een lang tekstveld heeft met de naam Systeemeigen query en tussen haakjes de vereiste velden bevat om het te laten werken. Voer dezelfde waarden in voor de server, database en de SQL-instructie die u eerder hebt ingevoerd bij het testen van de connector.

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

Nadat u OK hebt geselecteerd, wordt een tabelvoorbeeld van de uitgevoerde systeemeigen query weergegeven in een nieuw dialoogvenster.

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

Selecteer OK. Er wordt nu een nieuwe query geladen in de Power Query-editor, waar u de connector desgewenst verder kunt testen.

Notitie

Als uw connector mogelijkheden voor het vouwen van query's heeft en expliciet heeft gedefinieerd EnableFolding=true als onderdeel van de optionele record voor Value.NativeQuery, kunt u de connector verder testen in de Power Query-editor door te controleren of verdere transformaties terugvouwen naar de bron of niet.