Pentaho – Das ETL-Strecken Wunderkind für Controller und ITler

Häufig müssen ETL-Strecken entworfen werden, um Daten in ein anderes Format zu überführen. Auch das Kopieren von Daten von einer Datenquelle in eine andere Datenquelle gehört zur Aufgabe einer ETL-Strecke. Bevor wir an dieser Stelle zu sehr abschweifen und ich euch schon erkläre, warum Pentaho ein ETL-Strecken-Wunderkind ist, möchte ich euch die zugrunde liegende Herausforderung anhand eines Kundenbeispiels näherbringen.

Szene 1 – Die Herausforderung

In der Controlling Abteilung eines Kunden wurden monatliche Berichte für Projektleiter erstellt. Diese Berichte werden verwendet, um über KPIs den Fortschritt des Projektes zu messen. Jeder Projektleiter weiß, wie wichtig solche monatlichen Berichte sind und jede Controlling Abteilung weiß, wie viel Arbeit solch ein monatlicher Bericht machen kann. Zur Erstellung des Berichtes werden mehrere Excel-Dateien (Berichte) aus einem ERP-System benötigt. Dabei werden mehrere Excel-Dateien pro Projekt generiert. Das bedeutet, dass bei über 100 Projekten auch über 100 Excel-Dateien erzeugt werden müssen. An dieser Stelle möchte ich betonen, dass die Dateien manuell aus dem ERP-System heraus generiert werden. Nun müssen die entsprechenden Excel-Dateien in eine bestimmte Ordnerstruktur überführt werden, beispielweise in solch eine Struktur (Projektleiter/Projekte/(Excel-Dateien)). Nun wird aus jedem Projektordner heraus ein Makro gestartet das den monatlichen Projektbericht generiert. Der Prozess wiederholt sich von Monat zu Monat. Jetzt kann sich jeder von euch bestimmt ausmalen, wie viel Zeit das den Controller kostet. Ungefähr sprechen wir hier über 30 Tage im Jahr. (Excel-Datei*(Erstelldauer + Kopieren in die Struktur + Makro Durchlaufdauer)) Bestimmt bekommt, dass dem einen oder anderen Controller bekannt vor.

Aber wie können solche Zeiten reduziert werden?

Szene 2 – Die Analyse

Grundsätzlich würde ich behaupten, alles was manuell erledigt werden muss, kann und muss optimiert/ angepasst werden. Durch manuelle Bearbeitung von Berichten steigt die Arbeitszeit vom Controller und die Wahrscheinlichkeit, dass ein Fehler gemacht wird, erhöht sich. Also sollte das ERP-System näher betrachtet werden. Jedes ERP-System basiert auf einer Datenbank (DB), in der alle Informationen hinterlegt sind. Warum sollte der Controller nun die Excel-Daten einzeln aus dem ERP-System heraus generieren, wenn diese doch per einfachem SQL-Befehl automatisch mit entsprechem Filter pro Projekt oder Projektleiter abgefragt werden könnten? Das reduziert die Erstelldauer, denn eine Abfrage gegen eine DB läuft schneller über ein SQL-Statement als über die ERP-Software. Zusätzlich kann ein SQL-Statement flexibel angepasst werden (Welche Daten sind relevant?). Auch das manuelle Anstoßen des Makros ist  zeitaufwendig, das könnte aber durch einen Job ersetzt werden. Damit meine ich, dass wiederholte Durchlaufen des oben genannten SQL-Statements pro Projekt und das Überführen der Daten in eine Excel-Datei mit einem bestimmten Verweis auf den Pfad [PM]/[Projekt]/[Bericht].xlsx. So muss der Controller nur noch einmal im Monat einen Job anstoßen (der auch zeitgesteuert ausgelöst werden kann), um den Projektleitern (PM) die monatlichen Berichte bereitzustellen.

An dieser Stelle wird es nun schwierig. Die Daten sollen in ein bestimmtes Layout kopiert werden. Das Layout umfasst das Corporate Design, Tabellenberechnung und weitere Verweise. Somit würde sich für die Optimierung des Prozesses eine ETL-Strecke anbieten. Aber was ist eine ETL-Strecke?

Szene 3 – Die ETL-Strecke

ETL steht für die Extraktion, die Transformation und das Laden von Daten. Häufig wird der Begriff im Kontext von Business Intelligence verwendet und beschreibt das Ziel bestimmte Daten aus der produktiv Umgebung in Business Intelligence System einzubringen. In unserem Fall haben wir die ETL-Strecke zur Erstellung monatlicher Reports verwendet.

Genauer bedeutet ETL folgendes:

  • Extraktion von Daten aus Produktivanwendungen und Datenbanken (CRM, ERP, MES, SQL, etc.)
  • Transformation der Daten um eine einheitliche Sicht über die Quellsysteme zu erhalten, Berechnungen und Datenbereinigung durchzuführen. Des Weiteren können die Daten durch zusätzliche Informationen angereichert werden.
  • Load der Daten in unterschiedliche Anwendungen, Datenbanken oder Files. Wie zum Beispiel in Data Marts, Data Warehouse und oder in Cubes.

Nun stand ich vor der Wahl, welches Programm ich verwenden wollte oder ob ich selber eine ETL-Strecke programmieren würde. Um zukunftssicher agieren zu können, habe ich mich für das Tool Pentaho Data Integration entschieden, weil es viele Funktionen und somit auch viele Möglichkeiten bietet eine ETL-Strecke aufzubauen. Zudem ist das Tool kostenlos und kann hier geladen werden. Mit Hilfe von Pentaho wird eine grafische ETL-Strecke aufgebaut. Per Drag and Drop werden bestimmte Funktionen der ETL-Strecke hinzugefügt.

Szene 4 – Die Lösung

Uns sind jetzt die Herausforderungen bekannt und wir haben ein grobes Konzept davon wie wir die Arbeiten des Controllers erleichtern können. Mit Pentaho haben wir die Möglichkeit schnell und einfach eine ETL-Strecke zu bauen. Zuvor sollte noch zwischen einer Transformation und einem Job unterschieden werden. In Pentaho wird eine Transformation verwendet, um Daten aus Datenquellen zu laden, diese zu transformieren und  diese in eine neue Datenquelle oder Datei zu laden. Somit kann die Transformation mit einer ETL-Strecke gleichgesetzt werden. Mit einem Job stellt Pentaho die Möglichkeit bereit mehrere Transformationen zu verbinden und gleichzeitig oder nacheinander auszuführen. Die Ausführung des Jobs kann manuell angestoßen oder zeitlich eingeplant werden. Zum Beispiel kann der Job auch automatisch jeden Sonntag um 12 Uhr ausgeführt werden. Damit könnte der Traffic, der bei einer Abfrage gegen Datenquellen produziert wird, ins Wochenende ausgelagert werden.

Zunächst habe ich eine Transformation erstellt, die die PMs und Projektnummer aus dem ERP-System liest.

Input Data (Project)

Zudem habe ich eine zweite Transforamtion erstellt, die die benötigten Daten für den monatlichen Projektbericht aus mehreren Datenquellen in eine Excel-Datei schreibt. Basierend auf einer Template-Datei, die das Corporate Design, Berechnungen und Links enthält. Mit Hilfe des Pentaho Excel-Writer konnte ich genau sagen, an welcher Stelle welche Daten eingefügt und wo die Excel-Datei abgespeichert werden soll – mit einem Verweis auf den endsprechenden Projektleiter.

Create Project-Review

Schlussendlich musste ich nur einen Job erstellen, der die beiden Transformationen miteinander verbindet. Die erste Transformation sorgt dafür, dass die zweite Transformation so häufig ausgeführt wird, bis alle aktiven Projekte durchlaufen wurden. Einfach gesagt, definiert die erste Transformation einen Filterwert und gibt diesen Filterwert an die zweite Transformation weiter. Darauf erstellt die zweite Transformation einen monatlichen Bericht, basierend auf dem Filterwert. Der Job sorgt dafür, das alle aktiven Projekte durchlaufen werden und wiederholt die Ausführung der zweiten Transformation solange bis zu jedem Projekt ein monatlicher Projektbericht erstellt wurde.

Job – ausführen der ETL-Strecke

Mit einem Knopfdruck, werden über 100 monatliche Projektberichte erstellt und das in ca. 10 min (je nach Datenmenge). Mit Hilfe dieser ETL-Strecke werden etwa 30 Tage im Jahr eingespart und der Controller kann die 30 Tage für das Controlling nutzen. Zum Aufbau der ETL-Strecke wurden nur 2 Tage benötigt.

Das ist nur ein Beispiel wofür einen ETL-Strecke genutzt werden kann.

Szene 5 – Vorteile

Der Kunde hat nicht nur 30 Tage gespart, sondern auch in die Zukunft investiert. Dieses kann behauptet werden, weil Pentaho es einfach gestaltet, die ETL-Streck anzupassen. Wenn das Design geändert werden muss, wird nur die Template-Datei angepasst. Müssen neue Daten hinzugefügt werden, wird die Abfrage erweitert. Es muss kein großer Programmieraufwand mehr erfolgen.

Mein Fazit ist, das ETL-Strecken viele Prozesse optimieren und vereinfachen können, ohne beim Aufbau dieser  Zeit zu verschwenden. Das sorgt dafür, dass der Mitarbeiter keine „dummen“ Aufgaben mehr erfüllen muss, sondern sich aktiv um das Tagesgeschäft kümmern kann. Erlebe mit mir die ersten Schritte in Pentaho..

Leave a Comment

Your email address will not be published. Required fields are marked *