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

Laden aus SQL-Datenbank

Um zu verstehen, wie Power Query Datenbankanfragen optimiert, müssen wir zunächst Daten 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 in Power Query sind die Navigationsschritte:

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

Würde man das direkt in SQL formulieren, lautet der Befehl:

Und das macht Power Query für uns. Die generierte Abfrage finden Sie, wenn Sie den letzten Navigationsschritt markieren und via Kontextmenü den Eintrag Systemeigene Abfrage anzeigen wählen:

Systemeigene Abfrage anzeigen

Spalten löschen, Spalten umbenennen

Power Query lädt also mit dem einem SQL-Befehl die Daten. Wenn Sie jetzt nur ein paar Spalten haben möchten, wählen Sie 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 jetzt alle Spalten aus der Datenbank und verwirft anschließend nicht mehr benötigte oder ist Power Query intelligenter?

Öffnen Sie 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. Der SQL-Befehl schränkt die Felder korrekt ein. Ohne dass der Benutzer eine Ahnung von SQL haben muss.

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

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.

Probieren Sie es: Filtern Sie in der SalesOrderHeader einfach das Datum. Beispielsweise alles nach dem 1. Januar eines beliebigen Jahres. Schauen Sie sich die generierte Abfrage an. Ich glaube, Sie haben das WHERE im SQL schon erwartet

Und bei Verweisen?

Im Tipps & Tricks Artikel können Sie 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. Öffnen Sie erneut die Systemeigene Abfrage. Power Query generiert folgenden SQL:

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

Und 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:

Muss ich das Wissen?

In erster Linie bringt das Wissen natürlich 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. Lange Ladezeiten lassen sich beheben, wenn man weiß, wie Query Folding funktioniert.

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 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?

Jetzt heißt es aufgepasst! Query Folding klappt nicht immer!

Wählen Sie 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 Sie sich jetzt die generierte Abfrage anschauen möchten, werden Sie 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. Filter, die die Datenmenge einschränken, müssen immer ganz am Anfang stehen, damit sie auf der Datenbank ausgeführt werden.

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 Microsoft 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.