Business Intelligence/28. August 2023 -Aktualisiert am 29. Januar 2024

Wie gelangen die Daten ins Reporting

Eine visuelle Darstellung von ELT (Extract, Load, Transform)

Das Vertrauen in die Daten ist das A und O beim Reporting. Die Daten können noch so gut visualisiert sein, doch wenn sie falsch sind, haben sie keinen Nutzen für den Anwender oder die Anwenderin. Daher ist eine ausgereifte und stabile Infrastruktur, die sicherstellt, dass die Daten immer aktuell und sauber sind, einer der wichtigsten Punkte eines BI-Projekts.

Zu einer stabilen Infrastruktur gehören viele Faktoren, doch in diesem Artikel nehmen wir nur einen unter die Lupe: wie die Daten aus verschiedenen Services in einem Report landen. Dieser Prozess wird oft übergreifend als ETL Pipeline bezeichnet. ETL steht hierbei für Extract, Transform and Load, und bezeichnet die Reihenfolge, in der jeder dieser Schritte stattfindet. Traditionell werden die Daten erst aus einer Plattform extrahiert, dann transformiert – in ein einheitlich Format gebracht, damit bspw. jedes Datum das selbe Format hat – und schließlich in eine zentrale Datenbank geladen.

In den letzten Jahren sind jedoch viele cloud-basierte Datenbanken auf den Markt gekommen, die besonders auf die schnelle Bearbeitung großer Datenmengen ausgelegt sind und mit relativ niedrigen Kosten bei der Datenspeicherung punkten. Unter diesen Voraussetzungen wurden viele Business Intelligence Prozesse für die Datenverarbeitung neu durchdacht. Ein dadurch entstandener Prozess nennt sich Extract, Load, Transform oder kurz: ELT. Dieser Prozess kehrt die Reihenfolge der Schritte Load und Transform um. Dies ist der größte Unterschied zu ETL. Die Daten werden aus der Quelle extrahiert und mit wenigen oder gar keinen Transformationen in die Datenbanken geschrieben. Die eigentliche Transformation der Daten geschieht dann innerhalb der Datenbank und nicht mehr während des Imports in die Datenbank. Als Resultat dessen setzen wir ein Schichtmodell in der Datenbank ein, um die Transformation der Daten sicher abzubilden.

ETL und ELT im Vergleich

"ETL und ELT im Vergleich"

Das obere Diagramm dient als schnelle Übersicht der Vor- und Nachteile von jedem Prozess. Zwar haben wir in letzter Zeit mehr und mehr ELT benutzt, jedoch sollte klar sein, beide Prozesse haben ihren Nutzen und die Entscheidung für den einen oder anderen sollte individuell erfolgen, je nach Anforderungen des Kunden und Projektes.

Eine typische ELT Pipeline bei Peaks & Pies

Nach Abwägung der Vor- und Nachteile jeder ETL Methode, muss man sich für eine entscheiden, doch selbst nach der Entscheidung für eine Methode können sich die Pipelines im Detail unterscheiden. Typischerweise sehen sie bei Peaks & Pies folgendermaßen aus:

Eine Pipeline vom Cloud Scheduler, über den Datenimport, bis hin zur Datenbank

"Datenpipeline vom Import bis hin zur Datenbank"

Der Cloud Scheduler ist eine Art Cronjob und dient dazu, die Datenimporte regelmäßig, automatisch zu starten. Den Datenimport selbst übernimmt eine Cloud Function, die vom Cloud Scheduler aufgerufen wird und die Daten aus einer Schnittstelle mithilfe unserer Eigenentwicklung Datahub holt.

datecampaign_idclickscreated_at
202002021252020-02-27T07:10:06+ 00:00
202002011202020-02- 27T07:10:06+00:00
202002011192020-02-26T07: 10:06+00:00
202001311322020-02-26T07:10:06+00:00

Eine Stage-Tabelle, die jeden Tag für die letzten zwei Tage Klicks pro Tag (date) und ID (campaign_id) speichert. Die dritte Zeile ist älter als die dadrüber liegende (zweite), beide enthalten Daten für die selbe Kombination aus Tag und ID. Auf dem Weg zum Rawmart wird die aktuellere Zeile behalten.

Die Cloud function schreibt diese Daten in eine sogenannte Stage-Tabelle in BigQuery. Eine Stage-Tabelle ist nur dazu da, um mit erhöhter Flexibilität Daten laden zu können. Hier ist beispielsweise der Datentyp nicht relevant, sondern wird erst in einem späteren Schritt festgelegt. Auch können Duplikate enthalten sein, da der Datenimport per Append die Daten ablegt.

datecampaign_idclickscreated_at
202002021252020-02-27T07:10:06+ 00:00
202002011202020-02- 27T07:10:06+00:00
202001311322020-02-26T07:10:06+00:00

In der Rawmart-Tabelle werden Duplikate entfernt. In unserem Beispie: eine Zeile pro Tag und ID. Liegen die Daten in BigQuery, können sie schneller verarbeitet und transformiert werden. Im Rawmart liegen diese Daten im Rohformat. Sprich, ohne jegliche Businesslogik, also werden weder Gruppierungen oder Aggregationen ausgeführt, noch werden die Daten aus einer Quelle mit anderen vermischt. Oft werden hier nur die korrekten Datentypen hinterlegt und die Tabellen partitioniert, damit wir in zukünftigen Abfragen Kosten sparen können, da in BigQuery nur Kosten für die abgefragte Datenmenge entsteht.

Erst im letzten Schritt, vom Rawmart in den Datamart, werden die Daten transformiert und in einheitliche Datenformate gebracht. Hier werden die Daten schließlich für die Auswertung aufbereitet und mit Businesslogik versehen. Dies kann zum Beispiel eine Länderzuordnung für Accounts sein, eine Zusammenführung von unterschiedlichen Datenquellen oder diverse andere Aggregationen, die für den Business Case relevant sind.

monthcampaignclicks
2020-02winter-sale45
2020-01winter-sale32

Daten im Datamart werden je Monat aggregiert und die ID durch einen Kampagnenname aus den Metadaten ersetzt.

Dadurch, dass wir die Daten zunächst im Quellformat importieren:

  1. verringern wir die Fehleranfälligkeit während des Datenimports, durch weniger Komplexität im Prozess,
  2. bieten die Möglichkeit, basierend auf den Rohdaten, Fehler bei der Transformation der Daten zu reproduzieren und
  3. nutzen für den Großteil der ELT Pipeline SQL als Sprache, die deutlich einfacher nachzuvollziehen ist als eine Programmiersprache

Auf den Datamart greift schlussendlich unser Reporting zu, um die Daten zu visualisieren.