Freigeben über

Unterstützung nativer Abfragen in benutzerdefinierten Power Query-Connectors

  • Artikel

Hinweis

Dieser Artikel behandelt fortgeschrittene Themen rund um die Implementierung von nativer Abfrage-Unterstützung für benutzerdefinierte Connectors, sowie Query Folding darauf. In diesem Artikel wird davon ausgegangen, dass Sie mit diesen Konzepten bereits vertraut sind.

Weitere Informationen über benutzerdefinierte Power Query-Connectors finden Sie unter Power Query SDK Übersicht.

In Power Query können Sie benutzerdefinierte native Abfragen für Ihre Datenquelle ausführen, um die gesuchten Daten abzurufen. Sie können auch die Fähigkeit aktivieren, das Query Folding während dieses Prozesses und nachfolgender Transformationsprozesse innerhalb von Power Query beizubehalten.

Ziel dieses Artikels ist es, zu zeigen, wie Sie eine solche Fähigkeit für Ihren benutzerdefinierten Connector implementieren können.

Voraussetzungen

Dieser Artikel verwendet als Ausgangspunkt ein Beispiel von , das den SQL ODBC-Treiber als Datenquelle verwendet. Die Implementierung der nativen Abfragefunktion wird derzeit nur für ODBC-Connectors unterstützt, die den SQL-92-Standard einhalten.

Der Beispielconnector verwendet den SQL Server Native Client 11.0 Treiber. Vergewissern Sie sich, dass Sie diesen Treiber installiert haben, um dieser Anleitung folgen zu können.

Sie können die fertige Version des Beispielconnectors auch im Ordner Finish im GitHub-Repository einsehen.

Ändern Sie die SQLCapabilities Ihres Connectors

Im SqlCapabilities -Datensatz des Beispielconnectors finden Sie ein Datensatzfeld mit dem Namen Sql92Translation und dem Wert PassThrough für ihn. Dieses neue Feld ist erforderlich, damit die systemeigene Abfrage mit Power Query ohne Validierung übergeben werden kann.

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

Vergewissern Sie sich, dass dieses Feld in Ihrem Connector erscheint, bevor Sie fortfahren. Andernfalls werden Sie später mit Warnungen und Fehlern konfrontiert, wenn es darum geht, eine Fähigkeit zu verwenden, die nicht unterstützt wird, weil sie nicht vom Connector deklariert wurde.

Erstellen Sie die Connector-Datei (als.mez oder.pqx) und laden Sie sie in Power BI Desktop, um sie manuell zu testen und das Ziel für Ihre native Abfrage zu definieren.

Manuelles Testen der nativen Abfragefunktionen Ihres Connectors

Hinweis

Für diesen Artikel wird die Beispieldatenbank AdventureWorks2019verwendet. Sie können jedoch jede beliebige SQL Server-Datenbank Ihrer Wahl verwenden und die erforderlichen Änderungen vornehmen, wenn es um die Besonderheiten der gewählten Datenbank geht.

In diesem Artikel wird die Unterstützung nativer Abfragen so implementiert, dass der Benutzer aufgefordert wird, drei Werte einzugeben:

  • Servername
  • Datenbankname
  • Native Abfrage auf der Ebene der Datenbank

Gehen Sie nun in Power BI Desktop zu Get Data und suchen Sie den Connector mit dem Namen SqlODBC Sample.

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

Geben Sie im Dialog für den Connector die Parameter für Ihren Server und den Namen Ihrer Datenbank ein. Wählen Sie dann OK aus.

Screenshot of connector dialog with server and database as parameters.

Es erscheint ein neues Navigationsfenster. In Navigatorkönnen Sie das systemeigene Navigationsverhalten des SQL-Treibers sehen, das die hierarchische Ansicht des Servers und der darin enthaltenen Datenbanken anzeigt. Klicken Sie mit der rechten Maustaste auf die Datenbank AdventureWorks2019 und wählen Sie dann Transform Data.

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

Mit dieser Auswahl gelangen Sie zum Power Query-Editor und zu einer Vorschau dessen, was tatsächlich das Ziel Ihrer nativen Abfrage ist, da alle nativen Abfragen auf Datenbankebene ausgeführt werden müssen. Sehen Sie sich die Formelleiste des letzten Schritts an, um besser zu verstehen, wie Ihr Connector zum Ziel Ihrer nativen Abfragen navigieren sollte, bevor er sie ausführt. In diesem Fall zeigt die Formelleiste die folgenden Informationen an:

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

Quelle ist der Name des vorherigen Schritts, der in diesem Fall einfach die veröffentlichte Funktion Ihres Connectors mit den übergebenen Parametern ist. Die Liste und der darin enthaltene Datensatz dienen lediglich dazu, in einer Tabelle zu einer bestimmten Zeile zu navigieren. Die Zeile wird durch die Kriterien aus dem Datensatz definiert, wobei das Feld Name gleich AdventureWorks2019 und das Feld Art gleich Datenbanksein muss. Sobald die Zeile gefunden ist, kann Power Query über [Data] außerhalb der Liste {} auf den Wert im Feld Data zugreifen, das in diesem Fall eine Tabelle ist. Zum besseren Verständnis dieser Navigation können Sie zum vorherigen Schritt zurückgehen (Source).

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

Test der nativen Abfrage

Nachdem Sie das Ziel identifiziert haben, erstellen Sie einen benutzerdefinierten Schritt nach dem Navigationsschritt, indem Sie das Symbol fx in der Formelleiste auswählen.

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

Ersetzen Sie die Formel in der Formelleiste durch die folgende Formel, und wählen Sie dann Enter.

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

Nachdem Sie diese Änderung vorgenommen haben, sollte unterhalb der Formelleiste eine Warnung erscheinen, in der Sie um die Erlaubnis gebeten werden, die systemeigene Abfrage mit Ihrer Datenquelle durchzuführen.

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

Wählen Sie Berechtigung bearbeiten. Es wird ein neues Dialogfeld Native Datenbankabfrage angezeigt, das Sie vor den Möglichkeiten der Ausführung nativer Abfragen warnen soll. In diesem Fall wissen wir, dass diese SQL-Anweisung sicher ist, also wählen Sie Run, um den Befehl auszuführen.

Screenshot showing how to approve a native database query dialog.

Nachdem Sie Ihre Abfrage ausgeführt haben, wird eine Vorschau Ihrer Abfrage im Power Query Editor angezeigt. Diese Vorschau bestätigt, dass Ihr Connector in der Lage ist, native Abfragen auszuführen.

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

Implementierung nativer Abfragelogik in Ihrem Connector

Mit den in den vorangegangenen Abschnitten gesammelten Informationen geht es nun darum, diese Informationen in Code für Ihren Connector zu übersetzen.

Sie können diese Übersetzung erreichen, indem Sie ein neues NativeQueryProperties Datensatzfeld zum Publish Datensatz Ihres Connectors hinzufügen, der in diesem Fall der SqlODBC.Publish Datensatz ist. Der NativeQueryProperties Datensatz spielt eine entscheidende Rolle bei der Definition der Interaktion des Connectors mit der Value.NativeQuery-Funktion.

Das neue Datensatzfeld besteht aus zwei Feldern:

  • NavigationSteps: Dieses Feld legt fest, wie die Navigation von Ihrem Connector durchgeführt oder gehandhabt werden soll. Es enthält eine Liste mit Datensätzen, in denen die Schritte beschrieben sind, die Sie ausführen müssen, um zu den spezifischen Daten zu navigieren, die Sie mithilfe der Value.NativeQuery-Funktion abfragen möchten. Es legt in jedem Datensatz fest, welche Parameter erforderlich sind, damit eine solche Navigation das gewünschte Ziel für die Funktion erreicht.
  • DefaultOptions: Dieses Feld beschreibt, wie bestimmte optionale Parameter in den Optionssatz Value.NativeQuery aufgenommen oder diesem hinzugefügt werden sollen. Es stellt eine Reihe von Standardoptionen zur Verfügung, die beim Abfragen der Datenquelle verwendet werden können.

Ihre Navigationsschritte können in zwei Gruppen eingeteilt werden. Der erste enthält die Werte, die vom Endbenutzer eingegeben werden, wie in diesem Fall den Namen des Servers oder der Datenbank. Der zweite enthält die Werte, die von der spezifischen Connector-Implementierung abgeleitet werden, wie z. B. die Namen von Feldern, die dem Benutzer während der Datenabfrage nicht angezeigt werden. Diese Felder können Name, Kind, Data und andere enthalten, je nach der Implementierung Ihres Connectors.

In diesem Fall gab es nur einen Navigationsschritt, der aus zwei Feldern bestand:

  • Name: Dieses Feld ist der Name der Datenbank, die vom Endbenutzer übergeben wurde. In diesem Fall war es AdventureWorks2019, aber dieses Feld sollte immer so übergeben werden, wie es der Endbenutzer während der Datenabfrage eingegeben hat.
  • Freundlich: Bei diesem Feld handelt es sich um Informationen, die für den Endbenutzer nicht sichtbar sind und spezifisch für den Connector oder die Treiberimplementierung sind. In diesem Fall gibt dieser Wert an, auf welche Art von Objekt zugegriffen werden soll. Bei dieser Implementierung wird dieses Feld ein fester Wert sein, der aus der Zeichenfolge Databasebesteht.

Diese Informationen werden in den folgenden Code übersetzt. Dieser Code sollte als neues Feld zu Ihrem SqlODBC.Publish Datensatz hinzugefügt werden.

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

Wichtig

Bei der Bezeichnung der Felder ist die Groß- und Kleinschreibung zu beachten und sie muss wie im obigen Beispiel verwendet werden. Alle Informationen, die an die Felder ConstantValue, IndexName oder FieldDisplayName übergeben werden, müssen aus dem M-Code des Verbinders abgeleitet werden.

Für Werte, die aus den Eingaben des Benutzers übernommen werden, können Sie das Paar FieldDisplayName und IndexNameverwenden. Für Werte, die fest oder vordefiniert sind und nicht vom Endbenutzer übergeben werden können, können Sie das Paar ConstantValue und IndexName verwenden. In diesem Sinne besteht der Datensatz NavigationSteps aus zwei Feldern:

  • Indizes: Definiert, welche Felder und welche Werte verwendet werden sollen, um zu dem Datensatz zu navigieren, der das Ziel für die Funktion Value.NativeQuery enthält.
  • FieldAccess: Legt fest, welches Feld das Ziel enthält, das in der Regel eine Tabelle ist.

DefaultOptions

Mit dem Feld DefaultOptions können Sie optionale Parameter an die Funktion Value.NativeQuery übergeben, wenn Sie die systemeigene Abfragefunktion für Ihren Connector verwenden.

Um das Query Folding nach einer nativen Abfrage beizubehalten, und unter der Annahme, dass Ihr Connector über Query Folding-Funktionen verfügt, können Sie den folgenden Beispielcode für EnableFolding = trueverwenden.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Nach diesen Änderungen erstellen Sie den Connector und laden ihn zum Testen und zur Validierung in Power BI Desktop.

Test und Validierung des Connectors

Starten Sie in Power BI Desktop mit Ihrem neuen benutzerdefinierten Connector den Connector über Get Data. Wenn Sie den Connector starten, werden Sie feststellen, dass das Dialogfeld jetzt ein langes Textfeld mit dem Namen Native query und in Klammern die erforderlichen Felder enthält, damit es funktioniert. Geben Sie die gleichen Werte für den Server, die Datenbank und die SQL-Anweisung ein, die Sie zuvor beim Testen des Connectors eingegeben haben.

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

Nachdem Sie OKgewählt haben, wird eine Tabellenvorschau der ausgeführten nativen Abfrage in einem neuen Dialogfeld angezeigt.

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

Klickan Sie auf OK. Eine neue Abfrage wird nun im Power Query Editor geladen, in dem Sie Ihren Connector bei Bedarf weiter testen können.

Hinweis

Wenn Ihr Connector über Abfrage-Folding-Funktionen verfügt und EnableFolding=true explizit als Teil des optionalen Datensatzes für Value.NativeQuerydefiniert hat, können Sie Ihren Connector im Power Query-Editor weiter testen, indem Sie prüfen, ob weitere Transformationen zur Quelle zurückfalten oder nicht.