Data Warehouse in BigQuery  —  Tracking Changes In Dimensions

Verfolgung von Änderungen in Dimensionen - Definitionen

Wenn Sie mit den im Data Warehousing verwendeten Begriffen bereits vertraut sind, können Sie diesen Teil überspringen.

• Dimension: "Eine Dimension ist eine Struktur, die Fakten und Kennzahlen kategorisiert, um den Benutzern die Beantwortung von Geschäftsfragen zu ermöglichen." (Wikipedia) Dimensionen sind Daten, nach denen Sie normalerweise filtern und gruppieren, wenn Sie Ihre Abfragen erstellen.
• Langsam wechselnde Dimension (SCD): Einige Dimensionen bleiben konstant (wie z. B. die Zeit), während sich andere im Laufe der Zeit ändern. Letztere werden als SCDs bezeichnet, auch wenn sie sich täglich ändern können. Ein Beispiel für eine Änderung: Ein Vertriebsmitarbeiter wird einem anderen Gebiet zugewiesen. Wenn Sie in diesem Fall einen Bericht über die Verkaufsleistung einer Region erstellen möchten, müssen Sie diese Person für einen Teil des Zeitraums berücksichtigen, während Sie die Daten, die erstellt wurden, als sie einer anderen Region zugewiesen wurde, ausschließen.
• Änderungsmanagement (CM): Es gibt viele Möglichkeiten, eine Änderung in einer Dimension darzustellen: eine neue Zeile, eine neue Spalte, Überschreiben, usw.
• CM-Typ 2: Bei dieser Art von CM wird für jede Version der Dimension ein Datensatz erstellt, der entweder durch eine Versionsspalte oder durch Spalten mit Start- und Enddatum identifiziert wird.
• CM-Typ 4: Dieser CM-Typ wird auch als "Historientabellen-CM" bezeichnet. In diesem Szenario gibt es zwei Tabellen: eine "aktuelle" Tabelle, die die neuesten Daten zu jeder Dimension enthält, und eine Historientabelle, in der alle Versionen gespeichert sind. Diese Historientabelle entspricht der Tabelle vom Typ 2, mit dem Unterschied, dass es nur eine Datumsspalte (Startdatum) gibt. Wenn Ihre Dimensionswerte gelöscht werden können, fügen Sie auch eine Löschspalte hinzu.

Verfolgung von Dimensionsänderungen - Änderungsmanagement Typ 2 in einer typischen SQL-Umgebung

In einem typischen SQL-Szenario ist die Änderung von Dimensionsdaten ziemlich einfach. Wenn sich ein Dimensionswert ändert, starten Sie eine Transaktion, aktualisieren die Spalte end_date des aktuell gültigen Datensatzes, speichern den neuen Datensatz mit demselben start_date und bestätigen die Transaktion. Wenn die Dimension gelöscht wird, aktualisieren Sie einfach das Enddatum des aktuellen Datensatzes, was bedeutet, dass es danach keine Dimension mehr gibt.

Um dies anhand eines Beispiels zu veranschaulichen, stellen wir uns eine Dimension für Verkäufer vor. Jede Zeile stellt eine Person dar: Sie hat einen synthetischen Schlüssel (Primärschlüssel), einen eindeutigen Code (natürlicher Schlüssel), einen Namen und eine Region.

Data Warehouse in BigQuer- Probleme mit BigQuery

Während die Versionsspalte problemlos implementiert werden kann, sind die Spalten mit dem Gültigkeitsdatum problematisch:

• In BigQuery gibt es keine Transaktionen. Wir können einen Datensatz nicht ändern und automatisch einen neuen einfügen - wenn der zweite Vorgang fehlschlägt, gibt es keine automatische Rollback-Funktion.
• Datenmanipulation wird nicht unterstützt. DML wurde im August 2016 in die Funktionsliste von BigQuery aufgenommen.

Außerdem gibt es spezielle Anforderungen, damit sie funktioniert.

• Sie funktioniert nur mit Standardabfragen.
• Die Tabelle darf kein REQUIRED-Feld haben.
• Die Tabelle darf keinen Streaming-Puffer haben.
• Es kann jeweils nur ein DML-Auftrag ausgeführt werden.

Wie Sie sehen können, ist es ziemlich restriktiv, ganz zu schweigen von der Tatsache, dass es sich noch in der Beta-Phase befindet, was bedeutet, dass es kein SLA gibt und keine Garantie, dass es nicht geändert oder ganz abgeschafft wird.

Data Warehouse in BigQuer - Abhilfe

Trotz des Fehlens einer DML-Funktion ist BigQuery ein wirklich großartiges Tool. Anstatt also zu einem traditionellen SQL-System zurückzukehren, beschloss ich, eine Versionierung zu implementieren, die sich auf die Stärken von BigQuery stützt.

Anstelle einer Typ-2-Tabelle basiert diese Lösung auf der Typ-4-Historientabelle (mit einer Löschspalte). Dies ist die einzige Tabelle, die wir benötigen; in diesem Szenario gibt es keine aktuelle Tabelle. Die Regeln für das Füllen der Tabelle sind:

• Neue Datenversion: Fügen Sie die neuen Daten in die Tabelle ein, mit dem korrekten start_date und deleted = false.
• Gelöschte Daten: Fügen Sie eine Zeile mit dem Startdatum als Löschdatum und deleted = true ein. Jedes erforderliche Feld kann aus dem vorherigen Zustand kopiert werden.

In dieser Tabelle würde eine Abfrage zum Abrufen von Verkäuferdaten zum 01.01.2010 wie folgt aussehen:

Zunächst suchen wir nach den zum angegebenen Zeitpunkt gültigen Datensätzen (start_date ist der letzte Wert vor dem angegebenen Zeitpunkt, Unterabfrage A), die wir dann mit allen Datensätzen in der Tabelle verbinden, die keinen gelöschten Datensatz darstellen, um alle nicht gelöschten Datensätze zu erhalten, die zum angegebenen Zeitpunkt gültig sind.

Verfolgung von Änderungen in Dimensionen - Ansicht vom Typ 2 mit Gültigkeitsdatum

Wir können unsere Verlaufstabelle mithilfe einer BigQuery-Ansicht in eine richtige Tabelle mit Gültigkeitsdatum umwandeln:

Diese Abfrage verknüpft die Tabelle links mit einer Abfrage von sich selbst, die das Startdatum in Enddatum umbenennt. Die Verknüpfung lässt nur Zeilen zu, bei denen das Startdatum vor dem Enddatum liegt. Von all diesen Zeilen wird diejenige mit dem frühesten Enddatum ausgewählt. Dadurch wird sichergestellt, dass es keine Überschneidungen gibt. Anschließend werden die als gelöscht markierten Zeilen entfernt. Die resultierende Ansicht ist eine richtige Typ-2-Tabelle mit Gültigkeitsdaten.

Ein Problem bei dieser Ansicht ist, dass die aktuell gültige Zeile als Enddatum null hat, und der BigQuery BETWEEN-Operator mag keine Nullwerte. Um dieses Problem zu lösen, umhüllen wir die Spalte end_date mit einer IFNULL(expr, null_result)-Funktion und einem Datum in der fernen Zukunft:

Zusammenfassung

Ich habe Ihnen gezeigt, wie Sie die Versionierung von Typ-2-Effektivdaten für Dimensionen mithilfe von Self-Joins in BigQuery implementieren. Im nächsten Beitrag dieser Serie zeige ich Ihnen eine weitere Möglichkeit, dies zu tun, und anschließend gehen wir auf die Leistung dieser Lösungen ein.