vorheriger Artikel
Vielleicht bist Du auch schon über folgendes Problem gestolpert? Du hast eine versionierte Tabelle in Deiner Datenbank und bemerkst, dass sich von Version zu Version gar nichts Relevantes ändert und Du einfach viel zu viele Zeilen hast? Wir zeigen Dir, wir Du das Problem lösen kannst!
Um das Thema besser einordnen zu können, lass uns mit einem passenden Beispiel starten, ein paar Produktstammdaten für ein Whisky-Sortiment:
An der Bezeichnung für den Whisky wird häufig herumgeschraubt, was uns aber nicht interessiert. Es sollen nur Änderungen der Kategorie mit ihrer jeweiligen Gültigkeit „herausdestilliert“ werden:
Man sieht es sofort: Die Datenmenge reduziert sich erheblich.
Zeitscheiben zusammenfassen: Wann ist das notwendig?
Grundsätzlich gibt es viele Situationen im Umfeld temporaler Datenbanken, in denen uns dieses Problem begegnen kann, z. B. wenn im klassischen Data Warehouse nur bestimmte Attribute aus einer Quelle benötigt werden.
Im Data-Vault-Umfeld will man vielleicht volatilere Attribute in eigene Satelliten auslagern, doch die Quellsysteme versionieren nicht auf Spaltenebene.
Beim Übergang zum Data Mart können durch Joins viele kleine „Zeitscheiben“ entstehen, die für einige Dimensionen aber nicht gewünscht sind. Besonders viele überflüssige Zeilen erzeugt man übrigens beim Transponieren (Unpivot) von Spalten zu Zeilen.
Echtes Problem oder nur bessere Perfomance?
Um es vorwegzunehmen: Eigentlich ist nichts daran auszusetzen, dass es „zu viele“ Versionen für einen Attributwert gibt. Neben der Einsparung von Speicherplatz ist diese Destillation deshalb eine Maßnahme zur Performance-Steigerung, weil man danach mit weniger Daten weiterarbeiten kann.
Stellt man sich für das Eingangsbeispiel die Frage: „Seit wann ist ein Produkt einer bestimmten Kategorie zugeordnet?“, wird deutlich, dass es auch fachliche Gründe geben kann, solche Versionen zu „destillieren“.
Gruppieren im Produkt-Lifecycle – das allein reicht nicht!
Tatsächlich ist ein Gruppieren mit (GROUP BY) der erste Gedanke, der aufkommt. Doch das würde voraussetzen, dass Attribute nie wieder einen Wert annehmen, den sie bereits hatten. Wenn das in der Praxis so ist (z. B. bei einer festen Reihenfolge von Werten im Lifecycle), ist die Lösung natürlich trivial. Aber meistens sieht es anders aus.
Im Gegenteil, es ist gar nicht so ungewöhnlich, dass Spalten nur zwischen wenigen Werten „hin- und herflackern“. Im Beispiel gibt es zwei Zeiträume, in denen die Kategorie A gültig war.
Viele Namen für viele Lösungen
Im Netz finden sich einige Code-Snippets – oft mit ganz unterschiedlichen Umschreibungen des Problems, wie „redundante“ oder „obsolete“ Versionen, Kondensieren, Verdichten, Densification, Duplikate oder Dubletten in versionierten oder historisierten Daten.
Aber eigentlich geht es nicht nur darum, Daten irgendwie zusammenzudrücken, sondern die „Essenz“ herauszuziehen, die fachlich wirklich relevant ist. Daher rede ich hier jetzt von einer „Destillation“.
Durch die Verwendung von Standard-SQL wird die Lösung sehr generisch, schließlich liegt das Problem ja auch direkt in der Datenbank.
Hochprozentigen brennen – genial einfach mit SQL!
Zunächst werfen wir mal einen Blick auf die Ausgangssituation. Hier gibt es folgende Attribute:
Die Lösung selbst besteht aus nur drei Common Table Expressions (CTE), die jeweils aufeinander aufbauen und durchnummeriert sind. Mit der vierten Abfrage wird das Endergebnis ermittelt und als View „distillery_algo“ zur Verfügung gestellt.
In der CTE1 wird zunächst für jeden Schlüssel ein virtueller Vorgänger erzeugt und mit UNION an die Tabelle angehängt. Dabei handelt es sich um eine „allererste“ Version, die „schon immer“ (hier 01.01.1900) gültig war und bis zum Gültig-ab-Datum der eigentlich ersten Version des Schlüssels gültig ist. Für das relevante Attribut (kategorie) wird ein Dummy-Wert gesetzt, der sonst nicht vorkommen kann.
CTE2 ist nicht kompliziert, aber vielleicht etwas verwirrend: Die CTE1 wird über Schlüssel und Gültigkeitsdatum mit sich selbst gejoint, sodass jede Zeile ihren direkten Nachfolger findet. Jetzt erkennt man, warum sich Gültig-ab- und Gültig-bis-Datum immer exakt entsprechen müssen.
Die Versionen des relevanten Attributs (Kategorie) werden umbenannt: Der aktuelle Wert (this) wird zum Vorgängerwert (prev_val) erklärt, der Nachfolgerwert (next) zum aktuellen (this_val).
Das aktuelle Gültig-bis-Datum wird zum neuen Gültig-ab-Datum gemacht. Dadurch erhält die zeitlich letzte Version einen virtuellen Nachfolger. Da dieser „Ende-Dummy“ beim Left Join keinen Partner gefunden hat, wird dafür das relevante Attribut durch den gleichen Dummy-Wert wie am Anfang ersetzt.
Sinn und Zweck erschließen sich wahrscheinlich erst in den nachfolgenden Schritten:
Nach dieser „Verschiebung“ werden in CTE3 nun einfach nur die Zeilen stehen gelassen, bei denen sich das relevante Attribut geändert hat (this_val <> prev_val) und es sich noch nicht um den Ende-Dummy handelt. Dabei bleibt der Start-Dummy aber bestehen (Dummy-Wert in prev_val).
Bei der Ausgabe des finalen Ergebnisses wird mit der Window-Funktion LEAD() das Gültig-bis-Datum aus dem Gültig-ab-Datum des Nachfolgers ermittelt und this_val bekommt den Namen des relevanten Attributs (kategorie) zurück. Für die letzte Version innerhalb des Schlüssels wird das finale Ende-Datum gesetzt (z. B. 31.12.9999).
Wunderbar, in nur 47 Zeilen Code haben wir aus vormals flatterhaften Daten einen äußerst runden Single Malt Whisky destilliert!
Fazit
Das Problem, besonders im Umfeld temporaler Datenbanken relevante Versionen aus einer Historie „herauszudestillieren“, ist auf jeden Fall mit SQL in einer einzigen Abfrage zu lösen. Der vorliegende Algorithmus stellt aber gewisse Anforderungen an die Daten, die in der Praxis nicht immer erfüllt sein werden. Das muss vom Anwender auf jeden Fall vorher geprüft werden.
Klingt aber irgendwie noch nach Selbstgebranntem? In meinem nächsten Blogbeitrag zeige ich die Lösung für Datenkonstellationen, die diese Anforderungen nicht erfüllen … Das willst Du nicht verpassen?
Dann melde Dich zu unserem Newsletter an!
