CREATE YOUR OWN DWH WITH EXCEL PART 1: POWER QUERY

MANUEL SUMMERMATTER BI-CONSULTANT TRIVADIS AG 8152 GLATTBRUGG

CREATE YOUR OWN DWH WITH EXCEL

Inhaltsverzeichnis 1

Einleitung ......................................................................................................................... 3

2

Einsatz von Power Query .............................................................................................. 4

2.1

Datengrundlage

4

2.2

Extraction

5

2.3

Transformation

6

2.3.1 Separierung der Daten in Kolonnen

6

2.3.2 Erstellung der Dimensionen

8

2.3.3 Erstellung der Faktentabellen 3

10

Fazit ................................................................................................................................ 12

Manuel Summermatter

Seite 2 von 12

CREATE YOUR OWN DWH WITH EXCEL

1 Einleitung Es wird aufgezeigt, in wie weit Excel bei der Erstellung eines eigenen, kleinen Data Warehouses helfen kann. Dabei werden Statistiken verwendet, die Daten der UEFA Championsleague beinhalten. Der Fokus liegt dabei auf dem ETL Prozess (Extraction, Transformation, Load) und der Datenmodellierung. Weiter werden dann noch verschieden Reportingmöglichkeiten aufgezeigt. Im „Part 1: Power Query“ wird Power Query angeschaut, welches vor allem beim Datenabzug (Extraktion aus Quellen) und bei der Datentransformation helfen soll. Im „Part 2: Power Pivot“ wird das eigentliche Datenmodell erstellt. Die Modellierung lehnt sich dabei an die dimensionale Modellierung (Dimensionstabellen und Faktentabellen). Im „Part 3: Reporting mit Excel“ werden unterschiedliche Reportingtools gezeigt, die Excel zur Verfügung stellt. Anmerkung: Es wird nicht jeder Schritt ausführlich beschrieben. Das Ziel besteht darin, die unterschiedlichen Möglichkeiten und ggf. Grenzen aufzuzeigen.

Manuel Summermatter

Seite 3 von 12

CREATE YOUR OWN DWH WITH EXCEL

2 Einsatz von Power Query Power Query kann als ETL-Werkzeug innerhalb von Excel verwendet werden. Es ist frei zugänglich und kann als Add-in heruntergeladen werden. Hinter jedem Query steckt die Programmiersprache M, welche explizit bearbeitet und erweitert werden kann. Dieses Beispiel beschränkt sich allerdings auf die Transformationen, die die Benutzer ohne Vorwissen dieser Sprache einsetzen können.

2.1

Datengrundlage

In diesem Testszenario werden Fussballstatistiken der UEFA Champions League verwendet. Die Daten stammen aus einem PDF, aus welchem diverse Informationen in unterschiedliche Textfiles transferiert wurden. Anmerkung: Das Einzige was diese Datengrundlage nicht mitliefert, ist ein Zeitbezug. Normalerweise besitzt ein DWH immer eine Datumsdimension. Da es hier aber um das Auzeigen der Möglichkeiten von Power Query, Power Pivot etc geht, wird davon abgesehen. Die eigentlichen Quellen in diesem Beispiel fürs Power Query sind Textfiles, die aus reinem Fliesstext bestehen und keine bestimmten Delimiter wie beispielsweise Semikolon, Komma etc. aufweisen (Siehe Bild unten).

Wie im Auszug oben ersichtlich ist, ist keine eindeutige Aufteilung in Kolonnen möglich, da jeder Record unterschiedlich viele Informationen erhält (bspw. kann ein Spieler für mehrere Clubs Tore geschossen haben). Insgesamt werden in diesem Beispiel drei Quellen verwendet, die folgende Informationen beinhalten: 

Clubstatistiken



Torschützen (dieses Dokument befasst sich vor allem mit der Torschützenstatistik)



Spielereinsätze

Mittels Power Query sollen die Daten so transformiert werden, dass schlussendlich drei Faktentabellen (Clubstatistiken, Torschützenstatistiken und Einsatzstatistiken) und zwei Dimensionen (Dimension Club und Dimension Spieler) im PowerPivot-Modell erstellt werden können.

Manuel Summermatter

Seite 4 von 12

CREATE YOUR OWN DWH WITH EXCEL

2.2

Extraction

Die Quellen liegen wie oben bereits erwähnt als Textfiles vor. Power Query bietet unterschiedliche Möglichkeiten Daten von bestimmten Quellen abzuziehen, u.a. sind dies (siehe Bild unten): 







1

Daten vom Web o

Online Search [1]

o

Angabe der Web-URL [2]

Daten aus unterschiedlichen Files [3] o

Excel

o

XML

o

Textfiles

o

CSV

Daten aus Datenbanken [4] o

SQL Server

o

Azure SQL DB

o

Access DB

o

Oracle DB

o

IBM DB2

o

MySQL DB

o

PostgreSQL DB

o

Sybase DB

o

Terradata DB

Daten aus diversen anderen Quellen [5] o

Sharepoint Listen

o

Azure Marketplace

o

Facebook

o

Hadoop File System

o

Etc.

2

3

Manuel Summermatter

4

5

Seite 5 von 12

CREATE YOUR OWN DWH WITH EXCEL

Bei der Extraktion der Daten aus Textfiles muss der Pfad der jeweiligen Datei angeben werden. Nach dem Quelldatenabzug der Texfiles liegen die Daten im Power Query Editor zur Bearbeitung vor (siehe Bild unten). Aus dieser Datenbasis soll schlussendlich eine Faktentabelle der Anzahl erzielten Tore für einen Verein, sowie eine Club- und Spielerdimension entstehen (Analog gilt das auch für die Quellen zu den „Spielereinsätzen“ und „Clubstatistiken“).

Das Problem an den nun vorliegenden Daten, ist das Fehlen von eindeutigen Delimitern. Weiter weisen einige Spieler mehrere Clubs auf, wobei eine eindeutige Zuweisung der Kolonnen erschwert wird.

2.3

Transformation

Power Query gibt dem Anwender die Möglichkeit die einzelnen Transformationsschritte in den Query Settings / Applied Steps einzusehen und ggf. auch zu bearbeiten (siehe Bild unten).

2.3.1 Separierung der Daten in Kolonnen Bei der Transformation der Daten geht es hauptsächlich darum, die Daten in die gewünschte Form und Struktur zu bringen. In einem ersten Schritt müssen die Daten in unterschiedliche

Manuel Summermatter

Seite 6 von 12

CREATE YOUR OWN DWH WITH EXCEL

Kolonnen eingeteilt werden. Hierfür kann die Split Column Funktion für „Delimiter“ oder für „Anzahl Zeichen“ eingesetzt werden (siehe Bild unten)

Da die Datenbasis keine eindeutigen Delimiter aufweisen und eine Aufteilung in Kolonnen anhand der Anzahl Zeichen nicht möglich ist, muss dieser Schritt mehrere Male für unterschiedliche Delimiter vorgenommen werden bis die Daten in der gewünschten Form vorliegen (Siehe Bild unten).

Weitere Transformationsschritte: 

Unerwünschte Zeichen entfernen (siehe gelbe Markierung oben im Bild) o



Power Query Funktion: Replace Values

Leerzeichen am Anfang oder Schluss entfernen (Manchester United FC_ -> Der Underscore signalisiert ein „unnötiges“ Leerzeichen) o



Änderung des Datentyps (bspw. für Tore: Whole Number) o



Power Query Funktion: Format -> Trim Power Query Funktion: Data Type

Kolonnenüberschriften ändern

Im Bild unten ist das Zwischenergebnis nach den diversen Transformationsschritten ersichtlich.

Manuel Summermatter

Seite 7 von 12

CREATE YOUR OWN DWH WITH EXCEL

2.3.2 Erstellung der Dimensionen Für die Erstellung der Dimensionen (Spieler und Club) dienen die entsprechenden Kolonnen, welche u.a. in der oben erstellten Basis (vgl. Kapitel 2.3.1) existieren. Dabei werden unterschiedliche Querys verfasst, die bspw. jeweils die Clubs und Spieler auflisten. Für die Clubs existieren jeweils mehrere Kolonnen (Club 1, Club 2 etc), die dafür in Frage kommen (Step 1). Gewisse Spieler haben nur für einen Club Tore geschossen und andere für mehrere Clubs. Darum ist es möglich, dass bei einigen Spielern in Kolonnen die den Club anzeigen, NULLValues vorkommen. Diese Null-Values können herausgefiltert werden mit der Power Query Funktion Text-Filters: Does not equal null (Step 2). Nachdem alle Null-Values herausgefiltert wurden, können neue Querys erstellt werden, die die einzelnen Kolonnen jeweils untereinander anhängen. Ein Nachteil hier ist es, dass jeweils nur zwei Querys zusammengefügt werden können. Da aber mehre Querys mit den unterschiedlichen Clubs existieren, muss dier Schritt mehrmals durchgeführt werden. Hierfür bietet Power Query die Funktion Append an. Voraussetzung für eine Auflistung der Clubs untereinander ist dieselbe Kolonnenüberschrift. Nun kann es sein, dass ein Club mehrmals vorkommt. Mit der Power Query Funktion Remove Duplicates können die redundanten Einträge entfernt werden. Anschliessend kann bspw. noch sortiert werden (Step 3). Die einzelnen Transformationsschritte sind im Bild unten am Beispiel zur Erstellung der Dimension Clubs veranschaulicht. In ähnlicher Art und Weise kann die Dimension Spieler erstellt werden. Für die Club-Dimension wurde zusätzlich eine Ländertabelle aus dem Web eingelesen, welche die Clubs den entsprechenden Ländern zuweist. Dabei kann eine Web-Url angegeben werden und anschliessend kann die entsprechende Tabelle der Website ausgewählt und in ein Query eingelesen werden. Dies ermöglicht schlussendlich bei der Modellierung des Power Pivot Modells die Erstellung einer Hierarchie. Diese Möglichkeit, die eigenen Daten mit Inhalten aus dem Internet anzureichern, ist eine der grossen Stärken von Power Query.

Manuel Summermatter

Seite 8 von 12

CREATE YOUR OWN DWH WITH EXCEL

Step 1

Step 2

Step 3

Manuel Summermatter

Seite 9 von 12

CREATE YOUR OWN DWH WITH EXCEL

2.3.3 Erstellung der Faktentabellen Für die Erstellung der Faktentabellen wird wie für die Dimensionen auch, die Datenbasis welche im Kapitel 2.3.1 erstellt wurde, verwendet. Hier wird näher auf die Erstellung der Faktentabelle „Torschützenstatistiken“ eingegangen. Die anderen erstellten Faktentabellen (Clubstatistiken und Spieler-Einsatzstatistiken) werden auf ähnliche Art erstellt. In der zugrundeliegenden Datenbasis wird jeder Spieler nur einmal aufgelistet. Innerhalb einer Zeile wiederum sind die erzielten Tore des Spielers für die verschiedenen Clubs aufgelistet (siehe Bild unten).

Beispielsweise hat Christiane Ronaldo für zwei unterschiedliche Clubs (Manchester United FC, Real Madrid CF) Tore geschossen, hingegen Lionel Messi nur für einen Club (FC Barcelona). Das Ziel bei der Erstellung der Faktentabelle ist es nun, die Records untereinander aufzulisten wie in der Tabelle unten veranschaulicht. Spieler

Club

Anzahl Tore

Gesamtanzahl Tore

Christiano Ronaldo

Manchester United FC

15

75

Christiano Ronaldo

Real Madrid CF

60

75

Lionel Messi

Barcelona

null

75

…

Was an dieser Zielstruktur auffällt ist der Null-Value unter Anzahl Tore. Dieser ist von der Quelle so gegeben und kommt jeweils vor, wenn ein Spieler nur für einen Club Tore geschossen hat. Ist das der Fall, sind die Gesamtanzahl Tore des Spielers mit der Kolonne Anzahl Tore identisch. Diese Null-Values werden allerdings erst im Power Pivot-Modell ersetzt und nicht hier mittels Power Query. Im ersten Schritt geht es also darum, Querys zu erzeugen, die die entsprechenden Kolonnen beinhalten. Folgendes Query (siehe Bild unten) zeigt die Auflistung der Spieler für den ersten Club.

Da nächste Query (siehe Bild unten) zeigt die Auflistung der Spieler für den zweiten Club.

Manuel Summermatter

Seite 10 von 12

CREATE YOUR OWN DWH WITH EXCEL

Die oben angezeigten Bilder zeigen das Endresultat eines Querys an. Dabei wurden folgende weiteren Transformationen angewendet: 

Alle Null-Values aussortiert (falls ein Spieler nur für einen Club Tore geschossen hat, weist er bei den weiteren Clubs keinen Eintrag resp. einen Null-Value auf und wird somit aussortiert) o



Änderung des Datentyps o



Power Query Funktion: Text-Filters, Does not equal null Power Query Funktion: Data Type

Kolonnenüberschrift ändern

Zusätzlich wurden für jedes Query zwei Custom Columns eingefügt, welche fixe Werte aufweisen (Power Query Funktion: Add Custom Column), um keine Informationen zu verlieren, welche in der Datengrundlage enthalten sind. 

Anzahl Clubs: Fixer Wert jeweils 1, um schlussendlich analysieren zu können für wie viele Clubs ein Spieler Tore geschossen hat.



Bemerkung: Gibt die Information des „wie vielten Clubs eines Spielers“ zurück

Das Bild unten zeigt die beiden zusätzlich eingefügten Kolonnen (siehe Bild unten):

Analog werden die weiteren Querys erstellt. Anschliessend werden diese über die AppendFunktion zur schlussendlichen Faktentabelle zusammengefügt (siehe auch Kapitel 2.3.2 Append).

Manuel Summermatter

Seite 11 von 12

CREATE YOUR OWN DWH WITH EXCEL

3 Fazit Power Query bietet gute Möglichkeiten Daten auf einfache Weise zu transformieren. Ein grosser Vorteil den Power Query bietet, ist der Datenbezug von unterschiedlichsten Quellen. Weiter lassen sich die Querys mit bestimmten Inhalten aus dem Web anreichern. So kann man beispielsweise die eigenen produktiven Daten mit Informationen aus dem Web anreichern und so noch bessere Möglichkeiten für die spätere Analyse ermöglichen. Andererseits kann man mit Power Query nicht jede erdenkliche Transformation durchführen. So fehlt mir beispielsweise eine Lookup-Funktion (nur Merge möglich), welche ich gut hätte Einsetzen könne. Für diesen Punkt müsste man auf Power Pivot zugreifen (bspw. mit der DAX-Funktion „Related“). Ebenso ist es schade, dass die Appendfunktion auf zwei Querys begrenzt ist. Rundum ist Power Query ein schönes Werkzeug, um Daten in die gewünschte Funktion zu bringen, v.a. in Anbetracht dessen, dass es ein frei verfügbares Add-in ist und Excel sehr verbreitet ist.

Manuel Summermatter

Seite 12 von 12