Freigeben über

Native Abfrageunterstützung in benutzerdefinierten Power Query-Connectors

Hinweis

In diesem Artikel werden erweiterte Themen zur Implementierung der Native Query-Unterstützung für benutzerdefinierte Connectors sowie die Abfragefaltung darauf behandelt. In diesem Artikel wird davon ausgegangen, dass Sie bereits über ein funktionierendes Wissen über diese Konzepte verfügen.

Weitere Informationen zu benutzerdefinierten Power Query-Connectors finden Sie in der Power Query SDK-Übersicht.

In Power Query können Sie benutzerdefinierte systemeigene Abfragen für Ihre Datenquelle ausführen, um die gesuchten Daten abzurufen. Sie können auch die Fähigkeit aktivieren, die Abfragefaltung während dieses Prozesses und bei nachfolgenden Transformationsprozessen innerhalb von Power Query aufrechtzuerhalten.

Ziel dieses Artikels ist es, zu zeigen, wie Sie diese Funktion für Ihren benutzerdefinierten Connector implementieren können.

Voraussetzungen

In diesem Artikel wird ein Beispiel als Ausgangspunkt verwendet, das den SQL ODBC-Treiber für seine Datenquelle nutzt. Die Implementierung der systemeigenen Abfragefunktion wird derzeit nur für ODBC-Connectors unterstützt, die dem SQL-92-Standard entsprechen.

Der Beispielconnector verwendet den SQL Server Native Client 11.0-Treiber . Stellen Sie sicher, dass dieser Treiber zusammen mit diesem Lernprogramm installiert ist.

Sie können auch die fertige Version des Beispiel-Connectors aus dem Finish-Ordner im GitHub-Repository anzeigen.

Ändern Sie die SQLCapabilities Ihres Connectors

Im SqlCapabilities Datensatz des Beispielconnectors finden Sie ein Datensatzfeld mit dem Namen Sql92Translation und dem Wert "PassThrough". Dieses neue Feld ist erforderlich, damit die systemeigene Abfrage ohne Überprüfung mithilfe von Power Query ü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"
]),

Stellen Sie sicher, dass dieses Feld in Ihrem Verbinder angezeigt wird, bevor Sie fortfahren. Andernfalls werden Warnungen und Fehler später angezeigt, wenn es um die Verwendung einer Funktion geht, die nicht unterstützt wird, da sie nicht vom Connector deklariert wird.

Erstellen Sie die Connectordatei (als MEZ oder PQX), und laden Sie sie in Power BI Desktop, um manuelle Tests durchzuführen und das Ziel für Ihre systemeigene Abfrage zu definieren.

Manuelles Testen der systemeigenen Abfragefunktionen ihres Connectors

Hinweis

In diesem Artikel verwenden wir die AdventureWorks2019-Beispieldatenbank. Sie können jedoch mit einer beliebigen SQL Server-Datenbank Ihrer Wahl arbeiten und bei Bedarf die erforderlichen Änderungen vornehmen, wenn es sich um die Besonderheiten der gewählten Datenbank handelt.

Die Art und Weise, wie native Abfrageunterstützung in diesem Artikel implementiert wird, besteht darin, dass der Benutzer aufgefordert wird, drei Werte einzugeben:

  • Servername
  • Datenbankname
  • Native Query auf Datenbankebene

Wechseln Sie nun in Power BI Desktop zur Benutzeroberfläche " Daten abrufen ", und suchen Sie den Connector mit dem Namen "SqlODBC-Beispiel".

Screenshot des Connectors, der sich im Datenabrufbereich von Power BI Desktop befindet.

Geben Sie für das Dialogfeld "Connector" die Parameter für Den Server und den Datenbanknamen ein. Wählen Sie dann OK aus.

Screenshot des Connectordialogfelds mit Server und Datenbank als Parameter.

Ein neues Navigatorfenster wird angezeigt. In Navigator können Sie das systemeigene Navigationsverhalten vom SQL-Treiber anzeigen, der die hierarchische Ansicht des Servers und der darin enthaltenen Datenbanken anzeigt. Klicken Sie mit der rechten Maustaste auf die AdventureWorks2019-Datenbank , und wählen Sie dann "Daten transformieren" aus.

Screenshot der Option

Diese Auswahl bringt Sie in den Power Query-Editor und eine Vorschau dessen, was effektiv das Ziel Ihrer nativen Abfrage ist, da alle systemeigenen Abfragen auf Datenbankebene ausgeführt werden sollen. Überprüfen Sie die Bearbeitungsleiste des letzten Schritts, um besser zu verstehen, wie Ihr Connector zum Ziel Ihrer systemeigenen Abfragen navigieren soll, bevor sie ausgeführt werden. In diesem Fall zeigt die Bearbeitungsleiste die folgenden Informationen an:

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

Die 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 helfen lediglich, in einer Tabelle zu einer bestimmten Zeile zu navigieren. Die Zeile wird durch die Kriterien des Datensatzes definiert, in dem der Feldname mit AdventureWorks2019 identisch sein muss, und das Feld "Kind " muss " Datenbank" entsprechen. Sobald die Zeile gefunden wird, erlaubt [Data] außerhalb der Liste {} Power Query den Zugriff auf den Wert innerhalb des Felds „Daten“, das in diesem Fall eine Tabelle ist. Sie können zum vorherigen Schritt (Quelle) zurückkehren, um diese Navigation besser zu verstehen.

Screenshot einer Tabelle mit den Werten und Feldern, die für den Navigationsschritt verwendet wurden.

Testen der systemeigenen Abfrage

Nachdem das Ziel jetzt identifiziert wurde, erstellen Sie nach dem Navigationsschritt einen benutzerdefinierten Schritt, indem Sie das Fx-Symbol in der Bearbeitungsleiste auswählen.

Screenshot der Fx-Schaltfläche in der Formel, die zum Erstellen eines benutzerdefinierten Schritts verwendet wird.

Ersetzen Sie die Formel in der Bearbeitungsleiste durch die folgende Formel, und drücken Sie dann die EINGABETASTE.

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

Nachdem Sie diese Änderung angewendet haben, sollte unter der Bearbeitungsleiste eine Warnung angezeigt werden, die die Berechtigung zum Ausführen der systemeigenen Abfrage für Ihre Datenquelle anfordert.

Ein Screenshot der Berechtigung ist erforderlich, um diese Warnmeldung für native Datenbankabfragen anzuzeigen.

Wählen Sie Berechtigung bearbeiten aus. Es wird ein neues Dialogfeld für systemeigene Datenbankabfragen angezeigt, in dem Versucht wird, Sie über die Möglichkeiten der Ausführung systemeigener Abfragen zu warnen. In diesem Fall wissen wir, dass diese SQL-Anweisung sicher ist. Wählen Sie daher "Ausführen" aus, um den Befehl auszuführen.

Screenshot, der zeigt, wie ein natives Datenbankabfrage-Dialogfeld genehmigt wird.

Nachdem Sie Die Abfrage ausgeführt haben, wird eine Vorschau Ihrer Abfrage im Power Query-Editor angezeigt. Diese Vorschau überprüft, ob Ihr Connector systemeigene Abfragen ausführen kann.

Screenshot der nativen Abfrage, die in der ersten Connectorentwicklung und -tests ausgeführt wird.

Implementieren Sie native Abfragelogik in Ihrem Connector

Mit den informationen, die aus den vorherigen Abschnitten gesammelt wurden, besteht das Ziel jetzt darin, diese Informationen in Code für Ihren Connector zu übersetzen.

Wie Sie diese Übersetzung erreichen können, fügen Sie dem Veröffentlichungsdatensatz Ihres Connectors ein neues NativeQueryProperties-Datensatzfeld hinzu, das in diesem Fall der SqlODBC.Publish Datensatz ist. Der NativeQueryProperties Datensatz spielt eine entscheidende Rolle bei der Definition der Interaktion des Verbinders mit der Value.NativeQuery Funktion.

Das neue Datenfeld besteht aus zwei Feldern:

  • NavigationSteps: Dieses Feld definiert, wie die Navigation von Ihrem Connector ausgeführt oder behandelt werden soll. Sie enthält eine Liste von Datensätzen, die die Schritte zum Navigieren zu den spezifischen Daten beschreiben, die Sie mithilfe der Value.NativeQuery Funktion abfragen möchten. Innerhalb jedes Datensatzes wird festgelegt, welche Parameter erforderlich sind, damit eine solche Navigation das gewünschte Ziel erreichen kann.
  • DefaultOptions: Dieses Feld hilft zu identifizieren, wie bestimmte optionale Parameter einbezogen oder dem Value.NativeQuery Optionsdatensatz hinzugefügt werden sollen. Sie stellt eine Reihe von Standardoptionen bereit, die beim Abfragen der Datenquelle verwendet werden können.

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

Für diesen 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 stets unverändert übermittelt werden, basierend auf der Eingabe des Endbenutzers während der Datenerfassung.
  • Art: Dieses Feld enthält Informationen, die für den Endbenutzer nicht sichtbar sind und spezifisch für die Anschluss- oder Treiberimplementierung sind. In diesem Fall gibt dieser Wert an, auf welchen Objekttyp zugegriffen werden soll. Bei dieser Implementierung ist dieses Feld ein fester Wert, der aus der Zeichenfolge Databasebesteht.

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

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

Von Bedeutung

Die Namen der Felder sind case-sensitiv und müssen wie im obigen Beispiel gezeigt verwendet werden. Alle Informationen, die an die Felder übergeben werden, entweder ConstantValue, IndexNameoder FieldDisplayName müssen vom M-Code des Connectors abgeleitet werden.

Für Werte, die vom Benutzer eingegeben wurden, können Sie das Paar FieldDisplayName und IndexName verwenden. Für Werte, die fest oder vordefiniert sind und die nicht durch den Endbenutzer übergeben werden können, können Sie das Paar ConstantValue und IndexName verwenden. In diesem Sinne besteht der NavigationSteps-Datensatz aus zwei Feldern:

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

DefaultOptions

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

Um die Abfragefaltung nach einer nativen Abfrage beizubehalten und angenommen, dass Ihr Connector Abfragefaltungsfunktionen unterstützt, können Sie den folgenden Beispielcode für EnableFolding = true verwenden.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Erstellen Sie mit diesen Änderungen den Connector, und laden Sie ihn zum Testen und Überprüfen in Power BI Desktop.

Testen und Überprüfen des Connectors

Starten Sie in Power BI Desktop mit Ihrem neuen benutzerdefinierten Connector die Funktion über die "Daten abrufen"-Option. Beim Starten des Connectors werden Sie feststellen, dass das Dialogfeld jetzt über ein langes Textfeld mit dem Namen Native Abfrage verfügt und in Klammern die erforderlichen Felder enthält, damit es funktioniert. Geben Sie dieselben Werte für den Server, die Datenbank und die SQL-Anweisung ein, die Sie beim Testen des Connectors zuvor eingegeben haben.

Screenshot des Connector-Dialogfelds mit angezeigtem Langtextfeld der nativen Abfrage.

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

Screenshot des Dialogfelds mit einer Tabellenvorschau der ausgeführten nativen Abfrage.

Wählen Sie OK aus. Eine neue Abfrage wird nun im Power Query-Editor geladen, in dem Sie bei Bedarf weitere Tests des Connectors durchführen können.

Hinweis

Wenn Ihr Connector über Abfragefaltfunktionen verfügt und EnableFolding=true explizit als Teil des optionalen Datensatzes von Value.NativeQuery definiert ist, können Sie den Connector im Power Query-Editor weiter testen, indem Sie überprüfen, ob weitere Transformationen zurück zur Quelle zurückgefaltet werden oder nicht.

  • Last updated on