Post on 02-Feb-2019
1. Zielsetzung und Nutzen 4
2. Management Summary 5
3. Aufbau des Data Warehouse-Systems 6
4. Datenquellen 7
5. Datenerfassung 8
5.1 Aufbau des ETL-Prozesses 9
5.1.1 Best Practices-Ansatz zum Umgang mit Lookup- und Join-Operationen 9
5.2 Praktische Umsetzung 12
5.3 Datenhaltung 18
5.3.1 Optimierung der Performance 19
5.4 Datenanalyse 21
5.5 Datenpräsentation 22
6. Resümee und Ausblick 23
7. Anhang 24
Inhalt
2
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
1. Zielsetzung und NutzenDiese Best Practice zeigt, wie sich mit Open Source Produkten ein leistungsfähiges Data
Warehouse aufbauen lässt. Die Best Practice ist interessant für Sie, wenn Sie
и über die Einführung eines Data Warehouse im Unternehmen nachdenken und auf der Suche
nach einer geeigneten Lösung sind.
и lieber in Software-Anpassungen investieren als in Software-Lizenzen.
и eine offene und anpassbare Lösung haben wollen, die schnell aufzusetzen ist und mit Ihren
Anforderungen mitwächst.
и bereits Open Source-Lösungen ins Auge gefasst haben, aber noch nicht wissen, wie Sie das
Projekt angehen sollen.
и ein begrenztes Projektbudget haben.
Sie profitieren vom Lesen dieses Best Practice, weil es Ihnen
и eine Beispielarchitektur für ein Open Source-basiertes Data Warehouse aufzeigt und nach-
vollziehbar beschreibt,
и Tipps für die Konzeption und Implementierung gibt,
и ein Konzept zur Verfügung stellt, dass Sie an Ihre eigenen Bedürfnisse anpassen können,
и Empfehlungen für Software-Produkte beinhaltet, die sich im Praxiseinsatz bewährt haben.
3
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
2. Management SummaryUnternehmen und ihr Umfeld produzieren immer schneller, immer größere Datenmengen. Die
anfallenden Daten bergen ein riesiges wirtschaftliches Potenzial – wenn sie richtig verarbei-
tet und analysiert werden. Ein Data Warehouse bietet die optimale Grundlage dafür. Mit der
Einrichtung und dem Aufbau eines Data Warehouse sind jedoch häufig hohe Lizenz- und Hard-
warekosten verbunden. Das schreckt vor allem kleine und mittelständische Unternehmen ab.
Kostengünstige Open Source-Lösungen gibt es im Business Intelligence-Bereich schon lange.
Doch eignen sie sich auch für den Aufbau eines performanten Data Warehouse? Oder führt an
kommerziellen Softwareprodukten kein Weg vorbei, wenn nicht auf Funktionalität und Perfor-
mance verzichtet werden soll?
In dieser Best Practice geht es darum, ob und wie mit Open Source-Systemen ein leistungsfähi-
ges Data Warehouse aufgebaut werden kann.
Dazu wird eine prototypische Beispielarchitektur konzipiert und aufgebaut. Zum Einsatz kom-
men die Lösungen Pentaho und Infobright. Die Architektur haben wir in ihren Grundzügen in
mehreren Projekten umgesetzt, sie sind teils seit Jahren erfolgreich im Einsatz.
Für eine bessere Verständlichkeit ist die Best Practice folgendermaßen gegliedert:
и allgemeiner Aufbau eines Data Warehouse
и Anbindung der Datenquellen
и Aufbau des ETL-Prozesses
и Datenhaltung
и Datenanalyse
и Datenpräsentation
и Resümee
Dadurch werden alle wichtigen Punkte bei der Konzeption und Implementierung eines Data-
Warehouse abgedeckt.
4
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
3. Aufbau des Data Warehouse-SystemsEin Data Warehouse System besteht aus fünf Ebenen: Datenquelle, Datenerfassung, Daten-
haltung, Datenanalyse und Datenpräsentation. Die einzelnen Schichten werden dabei durch
verschiedene Konzepte und Werkzeuge realisiert. Ihre jeweilige Umsetzung ist abhängig von den
Anforderungen des entsprechenden Unternehmens.
Das Data Warehouse-System unseres Prototyps besitzt die in der untenstehenden Abbildung
dargestellte Struktur. Alle Komponenten sind Open Source. Als Datenquelle kommt die Mi-
crosoft-Datenbank AdventureWorks zum Einsatz. Der ETL-Prozess auf der Ebene der Datenerfas-
sung wird mit Pentaho Data Integration realisiert. Das Werkzeug ist Teil der Open Source Lösung
Pentaho Business Analytics Suite, die in diesem Beispiel auch für die Datenanalyse und Daten-
präsentation verwendet wird.
Das eigentliche Data Warehouse innerhalb der Datenhaltung wird mit dem analytischen Da-
tenbankmanagementsystem Infobright abgebildet. Als OLAP-Server kommt Pentaho Mondrian
zum Einsatz. Das notwendige XML-Schema wird mit der Pentaho Schema Workbench erzeugt.
Sind diese Komponenten umgesetzt, können die Daten auf der Datenpräsentationsebene durch
verschiedene Tools von Pentaho in Form von Analysen, Dashboards und Reports aufbereitet wer-
den. In den nachfolgenden Kapiteln werden die einzelnen Schichten und die dabei zum Einsatz
kommenden Tools näher beschrieben.
Aufbau der Beispielarchitektur
5
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
4. DatenquellenBei unserem Prototyp kommen als Datengrundlage die Beispieldaten
AdventureWorks2008R2(AWR2) zum Einsatz. Diese von Microsoft bereitgestellte OLTP-Datenbank
kann kostenlos als Backup-Datei unter http://msftdbprodsamples.codeplex.com/ herunter-
geladen werden. Die Daten repräsentieren ein fiktives, multinationales Unternehmen aus dem
Produktionsbereich, das sich auf die Fertigung und den Vertrieb von Fahrrädern und Zubehör
spezialisiert hat. Das Datenmodell besteht aus mehr als 70 Tabellen, die sich in fünf Bereiche
unterteilen: Human Resources, Person, Production, Purchasing und Sales.
Das Datenmodell ähnelt hinsichtlich des Szenarios und der Komplexität den Datenbankstruk-
turen realer Unternehmen und ist daher gut für Demonstrations- und Testzwecke geeignet. Die
zur Verfügung gestellte Backup-Datei(.bak) kann problemlos im Microsoft SQL Server integriert
werden. Dazu ist lediglich innerhalb des SQL Server Management Studios über das Datenbank-
symbol das Interface zur Datenbankwiederherstellung zu öffnen (siehe Abbildung). Danach wird
die neue Datenbank im Objekt-Explorer angezeigt.
Der Microsoft SQL Server stellt natürlich keine Open Source Software dar. Wir haben ihn aber für
dieses Beispiel herangezogen, um die Testdaten von Microsoft nutzen zu können. Alternativ kann
das Backup auch in CSV-Dateien umgewandelt werden. Die Dateien lassen sich im Anschluss in
jede Open Source Datenbank per Bulk Load laden.
Import der Backup-Datei
6
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
5. DatenerfassungDie Datenerfassung beschäftigt sich mit dem Extrahieren, Transformieren und Laden (ETL) der operativen Daten in
das Data Warehouse. Ziel ist es, eine homogene, qualitativ hochwertige Datenbasis im Data Warehouse zu schaffen,
auf der Analysen ausgeführt werden können. Zu den Aufgaben der Datenerfassung gehört auch die Planung des
Datenmodells.
Das Datenmodell ist die Grundlage für die späteren Transformationsprozesse der Daten. Dabei sind grundsätzliche
Entscheidungen zu treffen, die den zu analysierenden Geschäftsprozess und den dabei angestrebten Detailgrad
sowie mögliche Dimensionen und Kennzahlen betreffen.
Im Rahmen des vorliegenden Prototyps wollen wir Vertriebsdaten auswerten. Am Detailgrad
der Daten wird nichts verändert, so bleiben alle Hierarchiestufen erhalten. Insgesamt sollen
fünf Dimensionen in Bezug auf die OLTP-Datenbank erstellt werden, und zwar zu Datum, Kunde,
Produkt, Vertriebsgebiet und Währung. Was die Kennzahlen betrifft, soll es möglich sein, Bestell-
menge, Frachtkosten, gewährte Rabatte, Steuern, Stückkosten sowie Umsatz zu analysieren. Die
Daten werden in Form eines Star-Schemata aufbereitet, weil es die beste Modellierungsvariante
für unsere Ziele darstellt (siehe Abbildung). Es ist aber auch möglich, ein Snowflake- oder Flat-
Schema zu verwenden.
Star-Schema auf Basis der verwendeten AWR2-Datenbank
7
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
Database Lookup
5.1 Aufbau des ETL-ProzessesFür die Umsetzung des ETL-Prozesses verwenden wir das Open Source Tool Pentaho Data Integ-
ration (PDI). Das Werkzeug bietet vordefinierte Schritte zum Extrahieren, Transformieren und La-
den der Daten. Diese können über eine graphische Drag-and-Drop-Oberfläche, genannt Spoon,
erstellt werden. Auf diese Weise lassen sich mit geringem Aufwand professionelle ETL-Routinen
in Form von Transformationen und übergeordneten Jobs aufbauen. Der manuelle Programmier-
aufwand ist dabei sehr gering, weshalb sich das Tool auch für unerfahrene Nutzer eignet.
5.1.1 Best Practices-Ansatz zum Umgang mit Lookup- und Join-Operationen
Die Dimensionen des Star-Schemas benötigen Informationen aus verschiedenen Tabellen der
OLTP-Datenbank. Um die Daten vergleichen und kombinieren zu können, bietet Pentaho Data
Integration Lookup- und Join-Operationen. Jeder Schritt kann dabei angepasst werden, was im
falschen Kontext zu Performanceproblemen führen kann. Da in der Regel innerhalb des ETL-
Prozesses meist viele Tabellen kombiniert werden müssen, erhöht die Auswahl der richtigen
Schritte signifikant die Verarbeitungsgeschwindigkeit. Wir erklären daher im Folgenden die
wichtigsten Schritte.
Database LookupDatabase Lookup ermöglicht den Abgleich von Daten verschiedener Tabellen einer Datenbank.
Damit lassen sich neue Attribute in die bestehende Datenbanktabelle integrieren. Der Database
Lookup wurde für kleine bis mittlere Data Sets konzipiert. Besonderes Augenmerk gilt dabei der
Cache-Konfiguration: bei aktivem Cache lässt sich die Verarbeitungszeit deutlich verkürzen. Eine
weitere Performance-Steigerung wird erreicht, indem die Checkbox „Load All Data From Table“
aktiviert wird. Gerade bei größeren Data Sets kann durch das Caching der Java Stack Space
überlastet werden, was die Transformation zum Zusammenbruch bringt.
8
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
Stream LookupStream Lookup ähnelt Database Lookup mit aktivem Cache. Allerdings können die zu verglei-
chenden Daten direkt aus dem Stream extrahiert werden. Durch das dauerhafte Caching ist der
Stream Lookup sehr ressourcenintensiv und sollte daher nur bei kleineren Data Sets eingesetzt
werden, die nicht direkt aus einer Datenbank geladen werden können. Mit der Checkbox „Pre-
serve Memory (Costs CPU)“ gibt es die Möglichkeit, den Speicherbedarf auf Kosten der CPU zu
verringern. Bei Aktivierung dieser Funktion werden die geladenen Daten beim Sortieren kodiert
(Hashing). Nachteil dieser Prozedur sind längere Laufzeiten.
Merge JoinMerge Join setzt die klassische Join-Operation innerhalb der Data Integration-Komponente um.
Unterschieden wird zwischen Inner, Full Outer, Left Outer sowie Right Outer-Joins. Merge Join
eignet sich vor allem für große Data Sets mit hoher Kardinalität, also vielen unterschiedlichen
Attributwerten innerhalb einer Spalte. Die Daten müssen zu Beginn nach den zu vergleichenden
Attributen sortiert werden. Das sollte nach Möglichkeit direkt auf der Datenbank über „Order By”
realisiert werden. Alternativ lässt sich auch der Schritt Sort Rows verwenden, was allerdings die
Performance schmälert.
Stream Lookup
Stream Lookup
9
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
Database JoinMit Database Join lassen sich ebenfalls Datenbankentabellen verknüpfen. Dabei lassen sich na-
tive Abfragen erstellen und ausführen. Die Parameter der bisherigen Transformation können in
die native Abfrage integriert werden. Als Platzhalter dient ein Fragezeichen, das beim Ausführen
durch den entsprechenden Parameter ersetzt wird. Bei der Integration mehrerer Parameter, ist
die richtige Reihenfolge im Bereich „The parameters to use“ entscheidend.
Dimension Lookup / UpdateDer Schritt Dimension Lookup / Update kombiniert die Funktionalitäten von Insert / Update und
Database Lookup. Dabei kann man zwischen den beiden Funktionen durch Aktivieren bzw. Deak-
tivieren der Checkbox „Update The Dimension“ wechseln. Zusätzlich ermöglicht der Schritt, auf
effiziente Weise Slowly Caching Dimension zu integrieren. Dimension Lookup / Update wurde für
spezielle Anwendungsfälle entwickelt und ist daher sehr rechenintensiv. Wir erwähnen ihn hier
daher nur der Vollständigkeit halber.
Database Join
Dimension Lookup / Update
10
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
PerformancetestUm die Performanceunterschiede zu zeigen, haben wir die verschiedenen Schritte in Verbindung
mit zwei Data Sets geprüft, die den zu erwartenden Datenmengen der Demo entsprechen.
Wie der Tabelle zu entnehmen ist, weist gerade der Database Join große Performanceprobleme
bei steigendem Datenvolumen. Bei den restlichen Schritten steigen die Verarbeitungszeiten
trotz der deutlich größeren Datenmenge kaum an.
Operation 40.000 Datensätze 152.500 Datensätze
Database Lookup 0,9 sek. 1,5 sek.
Stream Lookup 1,6 sek. 2,3 sek.
Merge Join 1,3 sek. 2,6 sek.
Database Join 8,2 sek. 3 min.
5.2 Praktische UmsetzungDie ETL-Prozesse richten sich nach den im Vorfeld festgelegten Spezifikationen des multidimen-
sionalen Datenmodells. Pro Dimension bzw. Faktentabelle wird eine Transformation erstellt.
Dadurch besteht ein besserer Überblick und Fehler lassen sich einfacher lokalisieren. Außerdem
verringert sich so die Gefahr, den Server zu überlasten. Die verschiedenen Transformationen
werden über einen zentralen Job verbunden und gesteuert:
Transformationen des Performancetest
Ergebnisse des Performancetest
11
it-novum Best Practice | Open Data Warehouse. Aufbau eines Data Warehouse mit Pentaho
Bevor mit den eigentlichen Transformationsprozessen zur Aufbereitung und Befüllung des Data
Warehouse begonnen werden kann, müssen die Datenbankverbindungen zur Datenquelle und
dem Data Warehouse definiert werden. Neue Datenbankverbindungen können in Pentaho Data
Integration im Reiter View>Database Connections angelegt werden. Entscheidend ist die Auswahl
der richtigen Datenbank, des richtigen Treibers sowie benutzerspezifische Einstellungen wie
Datenbankname oder Nutzer.
Wichtig ist, dass die JDBC-Treiber der beiden Datenbanken im Verzeichnis lib des Data Integrati-
on Tools hinterlegt sind. Standardmäßig kann auf MySQL über Port 1433 und auf die Infobright-
Datenbank über Port 5029 zugegriffen werden.
Übergeordneter Job
Details der Datenbankverbindungen
12