Falls du dich gefragt hast was besser ist: Spalten löschen oder via SQL erst gar nicht aus der Datenbank holen, dann ist der heutige Artikel für dich. Wir erklären Query folding und was man darunter versteht.

Laden aus SQL-Datenbank

Um zu verstehen, wie Power Query Datenbankanfragen optimiert, müssen die Daten zunächst aus einer SQL-Datenbank laden. Für das Beispiel verwende ich die Tabelle SalesOrderDetail aus der AdventureWorks Datenbank.

SalesOrderDetail Tabelle in Power Query

Auf der rechten Seite sind die Navigationsschritte:

  • Power Query verbindet sich erst mit der Datenbank.
  • Über den Schritt Navigation hast du den Datenbankkatalog und die Tabelle gewählt.

Der SQL-Befehl muss also irgendwie lauten

SELECT SalesOrderID, SalesOrderDetailID,…. FROM SalesOrderHeader

Und genau diese generierte Abfrage findest du, wenn du den letzten Navigationsschritt markierst und via Kontextmenü den Eintrag Systemeigene Abfrage anzeigen wählst:

Systemeigene Abfrage anzeigen

Spalten löschen, Spalten umbenennen

Power Query lädt also mit dem einem SQL-Befehl die Daten. Wenn du jetzt aber nur ein paar Spalten haben möchtest, wähle die Funktion Spalten wählen aus:

Spalten auswählen

Dadurch legt Power Query einen neuen Schritt in den Angewendeten Schritten an: Andere entfernte Spalten.

Quizfrage

Lädt Power Query alle Spalten aus der Datenbank und verwirft anschließend nicht mehr benötigte oder ist Power Query intelligenter?

Öffne erneut die Systemeigene Abfrage im Kontextmenü des letzten Angewendeten Schritts.

Geänderte Systemeigene Abfrage anzeigen

Power Query ist so optimiert, dass möglichst nur Daten geladen werden, die auch wirklich benötigt werden.

Jetzt benennen wir die Spalte CarrierTrackingNumber in CTN um. Power Query erzeugt folgenden SQL-Befehl:

SELECT
[SalesOrderID] as [SalesOrderID],
[SalesOrderDetailID] as [SalesOrderDetailID],
[CarrierTrackingNumber] as [CTN],
[OrderQty] as [OrderQty],
[ProductID] as [ProductID]
FROM
[Sales].[SalesOrderDetail] as [$Table]

Diese Vorgehensweise, Transformationsschritte zusammenzufassen und direkt an die Datenquelle weiterzugeben, nennt man Query folding.

Filtern?

Wenn ich mir vorstelle, dass Power Query aus einer produktiven DB2-Datenbank zehn Jahre Daten lädt und dann im Hauptspeicher davon sieben Jahre wieder verwirft......dann fühle ich mich zumindest mal nicht wohl.

Probiere es aus: Filtere in der SalesOrderHeader einfach das Datum. Beispielsweise alles nach dem 1. Januar eines beliebigen Jahres. Schau dir die generierte Abfrage an. Ich glaube, du hast das WHERE im SQL schon erwartet ;-)

Und bei Verweisen?

Im Tipps & Tricks Artikel kannst du nachlesen, wie man Abfragen deaktiviert und Verweise erstellt. Verweise sind nützlich, wenn man beispielsweise eine Dimensionstabelle aus einer Faktentabelle erzeugen möchte. Dazu wählt man nur die Dimensionsspalten aus (hier nur die Spalte ProductId) und verwirft dann alle Dubletten. In unserem Fall also einen Verweis auf SalesOrderDetail. Dann  benennen wir die Tabelle um in DimProduct und anschließend führen wir folgende Schritte durch:

  • Spalten wählen (nur ProductId bleibt übrig)
  • Spalte ProductId markieren und Zeilen löschen/Dubletten löschen auswählen

In SQL würde man das über eine sogenannte Distinct-Abfrage lösen. Öffne erneut die Systemeigene Abfrage. Power Query generiert folgenden SQL:

SELECT
distinct [ProductID]
FROM
[Sales].[SalesOrderDetail] as [$Table]

Power Query verwendet also alle Transformationsschritte. Auch über Verweise hinweg und fasst diese zusammen.

Gruppierungen?

Wir erstellen noch einen Verweis auf die SalesOrderDetail Tabelle und gruppieren die Verweis-Tabelle nach ProductId und lassen uns in einer zweiten Spalte die Anzahl der Positionen anzeigen. Die Systemeigene Abfrage lautet jetzt:

SELECT 
[rows].[ProductID] as [ProductID],
count(1) as [Anzahl]
FROM
(
SELECT
[ProductID]
FROM
[Sales].[SalesOrderDetail] as [$Table]
) AS [rows]
GROUP BY
[ProductID]

Was bringt dir das?

In erster Linie Verständnis für die Arbeitsweise von Power Query. Große Datenmengen aus einer Datenbank zu laden und dann lokal zu filtern, also zu verwerfen, ergibt keinen Sinn. Datenbanken sind außerordentlich gut optimiert, um auch komplexe Abfragen performant auszuführen. Vor dem Benutzer wird das aber so weit wie möglich verborgen. Werden Daten aus einer Textdatei geladen kann Power Query gar nicht anders als die Daten zu laden und dann zu verwerfen. Textdateien haben keine Abfragesprache oder gar ein System, das die Filterung beim Laden schon vornehmen könnte.

Die Vorgehensweise macht deutlich, was Abfragen eigentlich sind: Abfragen sind abstrakt formulierte Algorithmen, die definieren, was man als Ergebnis haben möchte. WIE Power Query intern zu dem Ergebnis kommt, bleibt internes KnowHow des Data-Connector-Entwicklers. Im Fall des SQL-Servers nimmt der Data-Connector alle Umwandlungsschritte und versucht so gut es geht SQL-Abfragen daraus zu generieren.

Und wenn SQL nicht mehr geht?

Wähle in der Abfrage DimProduct die Funktion Zeilen entfernen/Erste Zeilen entfernen und dort als Anzahl 15 Zeilen, die entfernt werden sollen. Für diese Transformation gibt es keine Entsprechung in SQL. Power Query ist nicht mehr in der Lage die Anforderung in SQL zu übersetzen. Wenn du dir jetzt die generierte Abfrage anschauen möchtest, wirst du sehen, dass der Eintrag im Kontextmenü deaktiviert ist. Im vorherigen Transformationsschritt ist dieser noch aktiv.

Das bedeutet bis zum vorherigen Transformationsschritt übersetzt Power Query alle Transformationen in einen einzigen SQL-Befehl. Alle restlichen Schritte muss Power Query dann im Hauptspeicher durchführen.

Performance Optimierung

Bei Datenbank Verbindungen gilt immer, dass Abfragen auf der Datenbank billiger sind als in Power Query. Manche Transformationsschritte lassen sich nicht in SQL überführen. Ab dem ersten Transformationsschritt, der nicht mehr in SQL übersetzbar ist, versucht Power Query nicht mehr die Schritte in SQL umzusetzen. Auch wenn alle nachfolgenden Schritte eventuell übersetzbar gewesen wären. Aus Performance-Sicht ist es für den Abfrage-Designer daher wichtig zu wissen, welche Transformationen auf Datenbankseite durchgeführt werden und welche nicht. Dadurch kann man versuchen die Transformationen so anzuordnen, dass möglichst viel in SQL übersetzbar ist und erst ganz am Schluss Transformationen zum Einsatz kommen, die eben nur lokal ausgeführt werde können.

Query Folding für andere Connectoren

Query folding funktioniert nicht nur für SQL. OData-Datenquellen haben eine Definition für Filter, die von Power Query genutzt wird. Auch Anfragen auf Exchange oder das Dateisystem werden optimiert. Die Liste ist damit sicher nicht vollständig – in den meisten Fällen wird ohnehin die Fähigkeit für SQL interessant sein.

Hinweis

Bei meinen eigenen Projekten stolpere ich gelegentlich über DB2-Datenbanken. Hier gibt es eine gewisse Variabilität was Treiber angeht. Manchmal kommt man mit dem Microsoft eigenen, manchmal mit dem IBM Treiber auf die Datenbank. Und manchmal nur mit einer ODBC-Verbindung. Ich bemerke häufig, dass Query Folding nicht funktioniert und erstmal zehn  Jahre Bewegungsdaten geladen werden. Und viel schlimmer: bei jeder Änderung erneut geladen werden. Dann sitze ich mit meinen Kunden jedes mal zehn Minuten wartend herum.

In einem solchen Fall bleibt immer noch der Weg, dass ich in der Verbindung den SQL-Befehl selbst schreibe. Zumindest zur Abgrenzung der Datenmenge und Auswahl der Spalten ist das nicht schwierig. Da ich das Heft dann selbst in die Hand nehme, wird kein query folding mehr ausgeführt.

Du willst mehr wissen?

Du findest Power Pivot, Power BI und Power Query super und du brauchst Starthilfe, Unterstützung in Workshops oder die Möglichkeit dich bei komplexen Fragen an jemanden zu wenden? Dann kontaktiere uns per E-Mail oder rufe direkt an!