Willibald-Data: Übernahme der Kunden ins Data Warehouse

In diesem Beitrag möchte ich, wie zuvor bereits angekündigt, ins Detail gehen und zeigen, wie wir die Daten von Willibald in das DWH übernehmen.

Der erste Schritt der Übernahme ins DWH ist das Exportieren der Daten aus dem operativen System von Willibald in die CSV-Dateien. Dieser Schritt wird hier nicht beschrieben. Die CSV-Dateien der Schnittstellen sind der Ausgangspunkt unserer Betrachtung.

Zunächst stellen wir die Webshop-Daten von Willibald für alle drei Lieferungen in einem Übergabeverzeichnis bereit. Dafür haben wir für jede Lieferung einen Timestamp für die Bereitstellung der Daten vergeben:

• Lieferung1: 2022-03-16 23:00:00
• Lieferung2: 2022-03-22 23:00:00
• Lieferung3: 2022-03-26 23:00:00

Für jede Lieferung wird dann ein Skript aufgerufen, dass die Willibald-Daten in das Data Warehouse übernimmt. Hier wird als Beispiel das Skript lieferung_1.sh gezeigt, dass die erste Lieferung ins Data Warehouse übernimmt:

Die Übernahme der 2. und der 3. Lieferung wird mit entsprechenden Skripten ausgeführt.

Überblick über den Ablauf

• Die CSV-Dateien werden mithilfe von SQL-Skripten in die DWH-Datenbank übernommen:
  • 0102_config_load.ps1 und
  • 0102_willibald_load.ps1
• Anschließend werden die Daten im Tool dbt unter Verwendung des AutomateDV-Plugins in die Persistant Staging Area (PSA) übernommen:
  • 0201_psa.ps1
• Dann werden die Daten im Tool dbt unter Verwendung des AutomateDV-Plugins in den Raw Vault übernommen:
  • 0302_load.ps1
    • Bereitstellen der Raw Stage
    • Bereitstellen der Stage
    • Erstellen der Hubs, Links und Satellites im Raw Vault
    • Erstellen der „Last Seen“-Tabellen im Raw Vault
    • Bereitstellen von Views auf die Objekte des Raw Vault
• Zum Abschluss der Lieferung werden die Daten in den „Last Seen“-Tabellen aufgeräumt, was wieder mit einem SQL-Skript außerhalb von dbt geschieht:
  • 0399_delete_from_ls.ps1

Beispiel: Übernahme der Kundendaten

Als Beispiel möchte ich hier die Übernahme der Kundendaten genauer beschreiben. Die Schnittstelle „Kunde“ für die Kundendaten ist deshalb gut geeignet, da sie recht einfach ist. Es gibt darin eine Kunden-ID („KundeID“) und ein paar Informationen zu jedem Kunden. Die einzige Besonderheit ist das Feld VereinsPartnerID, das einen Fremdschlüssel in die Tabelle der VereinsPartner darstellt.

In den folgenden Kapiteln beschreibe ich, wie diese Kundendaten von den csv-Dateien bis in den Raw Vault übertragen werden.

Einlesen der CSV-Datei in das Schema Willibald

Im Skript 0102_willibald_load.ps1 werden für die Schnittstelle „Kunde“ diese beiden SQL-Skripte aufgerufen:

• Drop_Kunde.sql: zum Entfernen der Tabelle Kunde aus dem Schema Willibald der DWH-Datenbank und
• Create_and_bulk_insert_Kunde.sql: zum Erzeugen der Tabelle Kunde im Schema Willibald der DWH-Datenbank und zum Einlesen der Schnittstelle in diese Tabelle

Diese Skripte werden bei jeder Datenübernahme ausgeführt und füllen die Tabelle Willibald.Kunde mit den aktuellen Daten aus der Schnittstelle.

Außerdem wird die Information über die Schnittstelle Willibald.Kunde in der dbt-Datei schema.yml als Source eingetragen, damit sie im dbt-Modell genutzt werden kann.

Erstellen des dbt-Modells für die Kundendaten

Im Skript 0201_psa.ps1 wird das Skript psa_wb_kunde.sql aufgerufen, mit dem die Tabelle

• DATAVAULT_PSA.psa_wb_kunde: die Tabelle für die Kundendaten in der Persistent Staging Area

erstellt wird.

Im Skript 0302_load.ps1 werden die Skripte aufgerufen, mit denen die folgenden Tabellen und Views erstellt werden:

• DATAVAULT.raw_wb_kunde: der Raw Stage View für die Kundendaten
• DATAVAULT.stg_wb_kunde: die Stage Tabelle für die Kundendaten
• DATAVAULT.hub_wb_kunde: der Hub Kunde
• DATAVAULT.sat_wb_kunde: der Satellit der Kundendaten am Hub Kunde
• DATAVAULT.ls_wb_kunde_hk: die „Last Seen“-Tabelle, die anzeigt, wann ein Kunde in der Stage für die Kundendaten zuletzt gesehen worden ist
• DATAVAULT.hub_wb_vereinspartner: der Hub Vereinspartner, der auch aus der Stage für Kundendaten befüllt wird
• DATAVAULT.ls_wb_kunde_hvp: die „Last Seen“-Tabelle, die anzeigt, wann ein Vereinspartner in der Stage für die Kundendaten zuletzt gesehen worden ist
• DATAVAULT.link_wb_kunde_vereinspartner: der Link zwischen Kunde und VereinsPartner, der aus der Stage für Kundendaten befüllt wird
• DATAVAULT.sat_wb_kunde_vereinspartner: der Satellite der Kundendaten am Link zwischen Kunde und VereinsPartner, der aus der Stage für Kundendaten befüllt wird
• DATAVAULT.ls_wb_kunde_lkvp: die „Last Seen“-Tabelle, die anzeigt, wann eine Kombiation von Kunde und VereinsPartner in der Stage für die Kundendaten zuletzt gesehen worden ist
• DATAVAULT.vlink_wb_kunde_vereinspartner: der View auf den Link zwischen Kunde und VereinsPartner
• DATAVAULT.vsat_wb_kunde_last: der View auf den letzten Stand im Satellit der Kundendaten am Hub Kunde
• DATAVAULT.vsat_wb_kunde_seen: der View auf alle Stände im Satellit der Kundendaten am Hub Kunde
• DATAVAULT.vsat_wb_kunde_vereinspartner_last: der View auf den letzten Stand im Satellit der Kundendaten am Link zwischen Kunde und VereinsPartner
• DATAVAULT.vsat_wb_kunde_vereinspartner_seen: der View auf alle Stände im Satellit der Kundendaten am Link zwischen Kunde und VereinsPartner

Was ist die Bedeutung der „Last Seen“-Tabellen?

Für jemanden, der die Prinzipien von Data Vault 2.0 kennt, ist es klar, welche Bedeutung die Hubs, Links und Satellites im Raw Vault haben. Und dass es sinnvoll sein kann, für den einfacheren Zugriff auf die Daten Views auf die Links und Satellites zu definieren, ist sicher auch verständlich.

Was aber sind die oben erwähnten „Last Seen“-Tabellen?

Satellites enthalten einen Record für jeden neuen Zustand der Daten. Was Satellites nicht leisten können, ist Auskunft darüber zu geben, wann ein Datensatz einfach komplett aus der Datenlieferung verschwunden ist. Wenn ein Datensatz für einen Satelliten ab einem bestimmten Zeitpunkt in den gelieferten Daten nicht mehr vorkommt, dann bleibt der zuletzt übernommene Datensatz weiterhin aktiv. Die „Last Seen“-Tabellen sind unsere Lösung, um dieses Problem zu lösen. Fü jede Übergabedatei in der Stage und für jeden darin definierten Hub und Link wird ein Satellite erstellt, der als einziges Feld den Timestamp des letzten Ladens als „LAST_SEEN“ enthält. Dieser Satellite schreibt demnach zu jedem Ladezeitpunkt für jeden gelesenen Key einen neuen Datensatz.

In einem nach der Übernahme ausgeführten SQL-Skript werden für jeden Key alle Datensätze außer dem letzten aus der „Last Seen“-Tabelle gelöscht, so dass diese für jeden Key die Information enthält, wann dieser Key zuletzt in der Stage gesehen worden ist.

Die Verwendung des AutomateDV-Makros für Satelliten dient zusammen mit dem SQL-Skript dazu, diese Lösung einfach umsetzen zu können. Ein eigenes Makro für solche LS-Tabellen wäre sicher eine interessante Weiterentwicklung des Konzepts.

Zusammenfassung

Insgesamt werden für die Kundendaten 19 Dateien benötigt, in denen Informationen zur Übertragung der Kundendaten in den Raw Vault des Data Warehouse bereitgestellt werden.

Auch wenn in diesen Dateien bereits Makros aus dem dbt-Plugin AutomateDV verwendet werden, so ist der Aufwand, diese Dateien zu erstellen, doch immer noch viel zu groß.

Wir haben deshalb eine einfache Steuersprache definiert, mit deren Hilfe die Informationen für alle diese Dateien in einer einzigen Steuerdatei bereitgestellt werden können. Die benötigten Tabellen werden dann aus dieser Steuerdatei generiert und in das dbt-Modell übernommen. Wie das vor sich geht, ist der Inhalt eines der nächsten Beiträge.