Unterstützung nativer Abfragen in benutzerdefinierten Power Query-Connectors
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.
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.
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.
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.
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.
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.
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).
Nachdem Sie das Ziel identifiziert haben, erstellen Sie einen benutzerdefinierten Schritt nach dem Navigationsschritt, indem Sie das Symbol fx in der Formelleiste auswählen.
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.
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.
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.
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
Database
besteht.
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 IndexName
verwenden. 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.
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 = true
verwenden.
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.
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.
Nachdem Sie OKgewählt haben, wird eine Tabellenvorschau der ausgeführten nativen Abfrage in einem neuen Dialogfeld angezeigt.
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.NativeQuery
definiert 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.