Optimierung von BI/DWH Systemen

Überblick

Ein Business Intelligence (BI)/Datawarehouse (DWH) System ist eine komplexe und oft fragile Anwendung. Sie besteht aus unterschiedlichen Komponenten, wie dem Extract Transform Load (ETL) System, der DWH Datenbank, der BI Plattform und dem BI Frontend bzw. BI Applikationen. Zudem gibt es einige Bausteine, welche im weiteren Sinne auch dem BI/DWH System zugerechnet werden, wie Netzwerk, Speicher oder Scheduler um nur einige zu nennen.

In meiner Laufbahn als BI/DWH Architekt wurde ich oft vom Kunden gerufen, wenn die nächtliche Datenverarbeitung nicht mehr innerhalb des definierten Ladezeitfensters durchgelaufen oder die Performance im BI Frontend extrem schwach geworden war.

In solchen Fällen gibt es grundsätzlich zwei Ansätze, wie das BI/DWH System aus Sicht Performance verbessert werden kann:

• Investitionen in stärkere Hardware und neuere Software, also in die Technologiearchitektur
• Optimierung der individuell entwickelten und konfigurierten Teile des BI/DWH Systems, also der Lösungsarchitektur sowie deren Umsetzung

In der Praxis ist es meist so, dass beide Wege parallel verfolgt werden. Jedoch ist eine Investition in neue Hardware und Software häufig auch mit Migrationskosten und einer mittel- bis langfristigen Umsetzung verbunden. Die Optimierung der Lösungsarchitektur des BI/DWH Systems ist deshalb oft der schnellere Weg zum Erfolg und der Gegenstand dieser Blog Serie zum Thema „Optimierung von BI/DWH Systemen“.

Alle Komponenten des BI/DWH Systems können die Verursacher von Performanceproblemen sein. Folgend beschreibe ich kurz diese Komponenten und deren Subkomponenten. Ich erläutere, wo Performanceprobleme auftreten können und welche Instrumente benötigt werden, um eine Analyse durchzuführen.

Das ETL System

Das ETL System macht in der Regel einen grossen Teil eines BI/DWH Systems aus. Hier ist oft ein sehr grosses Optimierungspotenzial verborgen.

Im Normalfall besteht das ETL System aus verschiedenen Ladeschichten in dieser Reihenfolge:

• 1:1 Datenübernahme aus dem Quellsystem
• eine Transformationsschicht
• eine DWH Schicht
• optional eine Datamart Schicht

Auf dem Weg von der Quelle bis ins DWH bzw. in die Datamarts kann es in all diesen Schichten zu Performanceproblemen kommen. Um diese verschiedenen Schichten zu beladen, wird im Normalfall ein ETL Tool wie z.B. SAP Data Services, MSFT SSIS, WhereScape RED, Oracle Data Integrator oder Informatica eingesetzt.

Häufig wird zuerst im Programmiercode des ETL Tools nach Performanceproblemen gesucht. Das ist sinnvoll, da hier die gesamten Ladestrecken übersichtlich und transparent dargestellt werden und somit ein einfacher Einstieg in das ETL System möglich ist.

Eins vorne weg: Oft macht es auf den ersten Blick den Anschein, der ETL Server sei das Problem, dass die erwartete Performance nicht mehr erreicht wird. Doch in der Praxis habe ich es schon sehr oft erlebt, dass nicht der ETL Server das Problem ist, sondern dass die Datenbank nicht richtig optimiert ist, zu klein für das Datenvolumen ausgelegt ist, das Datenmodell oder das ETL System falsch konzipiert sind oder das ETL Tool nicht richtig eingesetzt wird.

Um eine Analyse eines ETL Jobs durchzuführen, reicht es meist nicht, nur im ETL Code selbst zu suchen. Eine umfassende Analyse kann nur unter folgenden Voraussetzungen seriös durchgeführt werden:

• Zugriff auf das ETL Tool und die Monitor Logs
• Zugriff auf die Datenbank mittels einem SQL Tool
• Zugriff auf eine Entwicklungs- oder Testumgebung mit produktivnahen Daten
• SQL Session-Überwachungstool zur Laufzeit (z.B. Toad Session Browser für Oracle)
• Anzeigen von SQL Ausführungsplänen
• Zugriff auf den DB und ETL Server zur Laufzeit, um CPU, RAM und Netzwerkauslastung zu überwachen

Zudem braucht es eine direkte Kommunikation mit verschiedenen Personen, wie dem DWH Architekt, den ETL Entwicklern und dem Datenbankadministrator. Oft kann man nicht nur innerhalb des abgeschlossenen ETL Systems agieren, sondern muss am Gesamtdesign des BI/DWH Systems ansetzen. In diesem Fall wird weiter Zugriff auf die Quelldatenlieferanten, die BI Entwickler und fallweise den Business Owner des Gesamtsystems benötigt.

Die DWH Datenbank

Auf der DWH Datenbank sind alle Daten des BI/DWH Systems gespeichert. Sie ist somit der Dreh- und Angelpunkt im BI/DWH System. Die DWH Datenbank besteht aus verschiedenen Subkomponenten:

• DB Server
• Physisches Datenmodell (Tabellen, Views, Indexe, Partitionen etc.)
• Tablespaces/Speicher
• I/O System

Um die Analyse der Datenbank durchzuführen, werden grundsätzlich dieselben Tools und Zugriffe benötigt, wie bei der Analyse der ETL Komponente. Für die verschiedenen Datenbanktypen gibt es zudem ausgereifte Datenbankadministrationstools, wie z.B. den Enterprise Manager von Oracle oder den Activity Manager von Microsoft. Diese erlauben es, die Performance des Systems zu überwachen.

Die wichtigsten Bereiche der Analyse der Datenbank sind das physische Datenmodell und die SQL Statements, welche vom ETL System und vom BI Frontend zur Laufzeit auf die Datenbank abgesetzt werden. Beim physischen Datenmodell stellen sich Fragen wie:

• Welcher Modellierungsstandard wird angewendet (3NF, dimensional, Data Vault)?
• Welche Modellierungs-Design-Patterns (z.B. Slowly Changing Dimensions Type 2, etc.) kommen zum Einsatz?
• Welche Constraints (Primärschlüssel, Fremdschlüssel, Muss-Felder etc.) wurden gesetzt?
• Wie ist die Indexorganisation (b-tree, clustered, bimap etc.) aufgebaut?
• Wie wurde partitioniert?
• Wie ist die Tablespace Organisation aufgebaut?
• Etc.

Die detaillierte physische Datenmodellierung erfordert eine sehr enge Zusammenarbeit mit dem Datenbank-, dem Server- und dem Netzwerkadministrator, da Abhängigkeiten zur Backup-/Recorverystrategie, zum DB Logging, zu der Art der physischen Datenablage im Filesystem, der Zuordnung der physischen Harddisk zum Filesystem, sowie zum Transfer der Daten zum Speichermedium (im Falle eines SAN) bestehen.

SQLs und deren Ausführungspläne müssen direkt auf der Datenbank analysiert werden. Dies erfordert tiefe Kenntnisse des jeweiligen Datenbanksystems und Erfahrung in der Interpretation der Ausführungspläne. Häufig kann mittels Indexierung, minimalen Anpassungen am Datenmodell, sowie Anpassungen am ETL Job bereits einiges an Optimierungspotenzial erschlossen werden.

Oft fehlen aus historischen Gründen durchgängig angewendete Standards bei der Datenmodellierung und in der ETL Entwicklung. Dies kann dazu führen, dass das gesamte BI/DWH Systemdesign hinterfragt werden muss und somit grössere Redesign Projekte notwendig sind, um eine Optimierung des BI/DWH System zu erreichen. Solche Massnahmen sind auf der mittel- bis langfristigen Zeitachse angesiedelt.

Es liegt auf der Hand, dass sich auch hier Abhängigkeiten zu anderen Funktionen und Personen ergeben, die über die BI/DWH Systemgrenzen hinausgehen.

Das BI Frontend

Das BI Frontend ist der eigentliche Zugang zum BI/DWH System. Oft manifestieren sich hier die Performance Probleme des Gesamtsystems in Form von langen Laufzeiten der Informationsprodukte wie Dashboards oder Standard- und Ad-hoc-Reports.

Das BI Frontend besteht aus verschiedenen Subkomponenten:

• BI Applikationsserver
• Frontend Applikation
• Entwicklungstools
• Metadatenlayer (z.B. BO Universe)
• Allenfalls OLAP Cubes (wobei diese – technisch betrachtet – an der Schnittstelle zur DWH-Datenbank liegen)

In den meisten BI Entwicklungsumgebungen werden die Reports auf einem BI Server gespeichert und beim Aufruf des Endanwenders wird der Dateninhalt aktualisiert. Das bedeutet, dass zur Laufzeit ein SQL Statement auf der DWH Datenbank ausgeführt wird.

Falls zwischen dem DWH und dem BI Frontend noch ein OLAP Cube Layer platziert ist, werden zur Laufzeit Statements in MDX, DAX oder einer ähnlichen Sprache auf den OLAP Cube abgesetzt und die Daten im Frontend aktualisiert.

Bei Standardreports ist es üblich, dass die Dateninhalte nach einem Ladelauf auf der BI Plattform neu gerechnet und dem Benutzer ein aktueller Report zur Verfügung gestellt wird.

Um eine Analyse im Frontend durchführen zu können benötigt man folgende Tools und Zugriffe:

• Zugriff auf die BI Frontend Entwicklertools
• Zugriff auf den BI Plattform Server und zugehörige Systemlogs
• Evtl. Zugriff auf separate Web (Application) Server und zugehörige Systemlogs
• Zugriff auf die DWH Datenbank analog ETL System und DB System

In vielen Fällen wird die Datenabfrage nicht durch den Entwickler geschrieben sondern durch das Frontend Tool bzw. den Metadatenlayer generiert. Das ist hilfreich und beschleunigt die Entwicklung. Jedoch gilt es auch hier kritisch zu prüfen, ob die generierten SQL (oder MDX, DAX etc.) Statements performance-mässig optimal sind. So gilt es z.B. darauf zu achten, dass möglichst viele Aggregationen auf der Datenbankebene und nicht im Frontend gerechnet werden. Ebenso gilt es das optimale Zusammenspiel zwischen dem jeweiligen Frontend und der verwendeten Datenbank zu finden. In der Regel gilt: Je mehr man auf der Datenbank lösen kann, desto schlanker und performanter ist das Frontend. Gleichzeitig muss man aber auch sagen: Kein Frontend ist schneller als die ihm zugrundeliegende Datenbank. Daher ist auch hier die Zusammenarbeit der verschiedenen Spezialisten gefragt, um gewisse Probleme analysieren und das System optimieren zu können.

BI Plattform

Auf der BI Plattform werden alle BI Frontend Reports, Metadatenlayers, Security Objekte und je nach Technologie Daten-Caches (z.B. Mini Cubes von SAP WebIntelligence) gespeichert. Auch diese Komponenten können zur Verlangsamung des Gesamtsystems beitragen. Da eine BI Plattform immer als Applikationsserver ausgestaltet ist, ist dem Datentransfer zwischen DWH Datenbank, dem BI Plattform Server und dem Endbenutzer hohe Beachtung zu schenken. Je nach BI Lösung werden Daten zwischen dem BI Plattform Server und der DWH Datenbank transferiert. So wird z.B. bei Lösungen wie SAP WebIntelligence die für einen Report benötigte Datenmenge in einem Mini Cube auf dem BI Server gehalten bzw. zur Laufzeit ab der DWH Datenbank aktualisiert und als Webseite dem Endbenutzer zur Verfügung gestellt. Wo hingegen QlikView ein in-memory-Cube auf dem Server vorgängig (meist nächtlich) vorberechnet und somit alle Daten in-memory auf dem Server gehalten und dem Enduser zur Laufzeit in Form einer Webseite angezeigt werden.

Den meisten BI Plattformen ist heute gemeinsam, dass sie den Benutzern eine Weboberfläche zur Verfügung stellen. Damit einhergeht die Bedeutung des Web (Application) Servers für die Performance und das entsprechende Tuning. Hier gilt es im konkreten Fall die besonderen Spezifika der verschiedenen BI Lösungen im Detail zu kennen und zu verstehen.

Um die BI Plattform im Detail zu analysieren, benötigt man dieselben Zugriffe und Tools, wie bei einer BI Frontend Analyse.

Zusammenfassung

In diesem ersten Blogartikel habe ich Ihnen die verschiedenen Komponenten eines BI/DWH Systems, die wichtigsten Subkomponenten, sowie die Anforderungen an die Problemanalyse nähergebracht. Wie Sie bemerkt haben, sind die verschiedenen Komponenten sehr eng miteinander verzahnt und keine davon kann in sich geschlossen betrachtet werden. Eine Optimierung des Systems ist selten durch eine einzelne Person möglich. Vielmehr braucht es eine Zusammenarbeit aller beteiligten Experten, um eine optimale Performance des Gesamtsystems zu erreichen.

In weiteren Artikeln werden meine Kollegen und ich Ihnen beispielhafte Vorgehen schildern, wie man die verschiedenen Komponenten konkret analysieren und optimieren kann.

Ich würde mich freuen mit Ihnen Ihre Meinungen und Erfahrungen zum Thema BI/DWH Optimierung zu diskutieren. Bitte nutzen Sie dafür die Kommentar Funktion unten im Blog.