Für das Arbeiten mit Excel lassen sich von jedem Anwender individuelle Einstellungen zur Einrichtung der Excel-Umgebung vornehmen. So kann man die Standardschriftart für die Excel-Mappen ändern, die Anzahl der Tabellenblätter bei neuen Mappen, ob Zahlenformate auf die Berechnungsgenauigkeit Einfluss haben sollen, ob Sie mit und ohne Gitternetzlinien arbeiten wollen. Natürlich können Sie auch Einfluss auf die Menüs, seit Excel 2007 Ribbon, Register oder Menüband genannt nehmen. Wenn Sie ein Dashboard erstellen möchten, kann es interessant sein, Register, Bearbeitungsleiste und Bildlaufleisten auszuschalten. Benötigen Sie zusätzliche Add-Ins, wie Solver, Power Query, PowerPivot, Analyse-Funktionen usw., so werden die über die Excel-Optionen aktiviert und deaktiviert.
Daher sollen hier in einer Serie einige Möglichkeiten vorgestellt werden.
Egal, was Sie verändern, in den Excel-Optionen sind diese Einstellungen als „neuer“ Standard in Ihrem Excel gespeichert. Es gibt keine „Reset“-Möglichkeit. Sie sollten sich als Ihre Änderungen sehr gut merken, oder aber besser noch, notieren, damit sie ggf. Ihre Änderungen wieder rückgängig machen können.
Wer seine ersten Versuche mit Power Query unternimmt, kommt oft an den Punkt, an dem Power Query mit unerklärlichen Meldungen aufwartet, für die es noch dazu in den einschlägigen Foren keine Lösungen gibt. Das geht meist einher mit einer ungewöhnlichen Dauer bei den Aktualisierungen einher. Und dass, obwohl die 4GB bei der Dateigröße noch nicht erreicht wurden!
Die Ursachen dafür sind nicht immer eindeutig auszumachen, liegen unter Umständen in einer Kombination verschiedener Ursachen begründet:
Problem 1:
Nach dem Einstieg in die Nutzung von Power Query neigen die meisten Anwender dazu, mit zu vielen Einzelschritte Daten aus den Datenquellen zu verbinden und zu transformieren. So gibt es häufig redundante Schritte (z.B. Ändern des Datentyps für eine Spalte) oder zu viele einzelne Datenselektionsschritte. Mit der Kaffeetasse in der einen und der Maustaste in der anderen Hand, geht das ganz schnell! 😊 Allerdings werden diese Schritte bei einer Aktualisierung alle erneut durchgeführt. Das kann dann in der Summe dazu führen, dass Aktualisierungen außergewöhnlich lang dauern oder sich das Modell dabei „aufhängt“. Hier hilft nur „Ausmisten“ und optimieren von Transformationsschritten.
Problem 2:
Power Query bietet die fantastische Möglichkeit, verschiedene Datenquellen in einem Datenmodell zu verbinden und die Daten aus diesen Quellen zu transformieren. Dabei kann ein ähnliches Problem entstehen, wie in unter 1 geschildert, auch wenn man alle Schritte optimiert. Hier hilft das sogenannte Staging-Kozept .
Abbildung: Staging-Konzeption in Anlehnung an Ken Pulse
Eine mögliche Methode, Abfragen aufzuteilen und zu ordnen, ist die Aufteilung in zwei Phasen bzw. Gruppen. Dafür sind die Begriffe Staging Queries und Loading Queries gebräuchlich.
Bei Staging Queries wird für jede Datenquelle eine Abfrage erstellt, die nur diese eine Datenquelle einliest. In dieser Abfrage finden alle Transformationen statt, es werden aber keine weiteren Datenquellen oder Abfrageergebnisse eingebunden. Bei Loading Queries werden dagegen Ergebnisse von einer oder mehreren Staging Queries weiterverarbeitet. Sie greifen nie direkt auf eine Datenquelle zu, sondern beziehen sich stattdessen auf die jeweilige Staging Query.
Problem 3:
Sind viele „Filter“-Befehle notwendig, um im Power Query-Modell Datenselektionen vornehmen zu können, leidet bei großen Datenmengen die Performance. In diesem Fall (und nicht nur in diesem) ist es vorteilhaft, als Datenquelle ein Datenbank zu verwenden. Denn technisch gesehen ist es ein großer Unterschied, ob Sie als Datenquelle eine Datei (Excel, TXT, CSV) oder eine Datenbank nutzen. Während die Dateien beispielsweise keine Möglichkeit zur Filterung von Daten bieten, gibt es in einer Datenbank viele Möglichkeiten, Daten zu filtern, bzw. zu selektieren. Power Query setzt beim Zugriff auf Datenbanken eine Technik namens Query Folding ein. Query Folding ermöglicht es Power Query, möglichst viel Verarbeitungsschritte auf die Datenquelle auszulagern. Dazu werden Transformationsschritte in die Sprache der Datenbank übersetzt (z.B. SQL-Select, Distinct) und gesendet. Enthält eine Datenbank bspw. 25 Millionen Datensätze und sollen davon nur 2.000 verwendet werden, so geschieht die Selektion in der Datenbank. Danach werden nur diese 2.000 Datensätze in das Power-Query-Modell geladen und bearbeitet.
Fazit:
Power Query ist fantastisch im spontanen, flexiblen Zugriff auf Daten und in deren Verarbeitung (Transformation). Möchten Sie Power Query für ein permanentes (Reporting-)Modell einsetzen, so sollten Sie auf jeden Fall eine Datenbank als Datenquelle verwenden. Dies ist besonders dann notwendig, wenn Sie die Historie Ihrer Daten sichern und verwenden wollen. Haben Sie Office Pro im Einsatz, dann verfügen Sie auch über eine Access-Lizenz, die Open-Source Datenbank MySQL böte sich ebenfalls an, bzw. die bei Ihnen bereits im Zusammenhang mit Ihrem ERP-System (z.B. SAP) genutzte Datenbank (SQL Server, Oracle, DB2 usw.) . Einen möglichen Workaround dazu finden Sie in diesem BLOG-Beitrag.
Wenn Ihnen dieser Beitrag gefallen hat, dann teilen Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.
