Einleitung
Die Konsolidierung, Sicherung und Bereitstellung von aufgearbeiteten Quelldaten ist ein zentraler Aspekt zur Auswertung und Berechnung wichtiger Kennzahlen einer Statistik-Datenbank. Synvert konnte ein bedeutendes Projekt bei einem langjährigen Kunden abschließen, bei dem die wichtigsten statistischen Kennzahlen durch grundlegend neu konzipierte und optimierte Führungskennzahlen abgelöst wurden.
Für die Optimierung der Kennzahlen wurde in einem umfangreichen Entwicklungsprozess eine rückwirkende Versionierung aufgebaut. Diese basiert auf spezifische Bewegungs- und Personenstammdaten aus einem versionierten Core Data Warehouse und einem nicht direkt verknüpfbaren tagesaktuellen Datamart. Mit zahlreichen SQL-Skripten und ‑Funktionen konnte eine Verbesserung der Datenqualität, die Korrektur fehlerhafter Datenerfassung und eine Optimierung der Performance der Datenverarbeitung erzielt werden.
In diesem Blogbeitrag werden die Herausforderungen und Ziele sowie die Umsetzung mit SQL näher beleuchtet.
Herausforderungen und Ziele
Die zugrundeliegende Statistik-Datenbank ist das zentrale Element für die Analyse und Auswertung von Daten, die aus verschiedenen Quellsystemen stammen. Täglich werden große Datenmengen geliefert, die anschließend verarbeitet, aufbereitet und für das BI-Reporting zur Verfügung gestellt werden. Aus dem Quellsystem werden personenbezogene Daten konsolidiert, welche die versionierten Stammdaten bilden.
Ist-Zustand
Bedingt durch fehlerhafte manuelle Eingaben in der Quelle können für eine Person mehrer Vorgänge V1, V2, …, Vn existieren, die wiederum an Maßnahmen Mi geknüpft sind. Für einen Vorgang Vi einer Person existiert jeweils eine Versionierung im Core. Jeder Vorgang Vi wird durch ein Startdatum festgelegt und kann durch ein Enddatum abgeschlossen werden. Im Quellsystem ist es möglich, dieses Start- und Enddatum rückwirkend zu verändern, wodurch sich die Vorgänge V1, V2, …, Vn zeitlich überlappen oder parallel laufen können. Die Maßnahmen M1, M2, …, Mn haben ebenfalls eine zeitliche Gültigkeit wie einen Stichtag, an dem eine Maßnahme Mi durchgeführt wird (Abbildung 1).
Für die Berechnung der Kennzahlen ist es zwingend erforderlich, die Vorgänge einer Person zu einem Vorgang Z zusammenzufassen, um die Kennzahlen nicht zu verfälschen oder verzerren, da sonst möglicherweise mehrere Vorgänge einer Person miteinfließen würden.
Im Datamart existierte bereits vor dem Projekt ein Verfahren mit umfangreicher Logik und Verarbeitungsprozessen in Informatica PowerCenter, um die mit einer Person verknüpften Vorgänge zu einem Vorgang Z zusammenzufassen. Insbesondere das Startdatum spielt eine entscheidende Rolle, welcher Vorgang Vi den Lead für den Vorgang Z übernimmt. Obwohl diese Informationen auf tagesaktueller Basis vorliegen, können sie aber zeitlich nicht so aufgelöst werden, um den Lead-Vorgang abzubilden. Zudem wird bis heute auf die Verknüpfung zwischen Vi und Z mit einem Fremdschlüssel verzichtet, obwohl Vi und Z jeweils durch eindeutige Schlüssel festgelegt werden.
Soll-Zustand
Es lässt sich zum heutigen Stand nicht ohne Weiteres identifizieren, welcher Vorgang Vi und zugehörige Maßnahmen Mi zu einem Vorgang Z gehören. Für die Optimierung der Kennzahlen ist es von entscheidender Bedeutung, zu einem bestimmten Zeitpunkt den richtigen Vorgang Vi zu identifizieren und die zugehörigen Informationen aus der Versionierung des Vorgangs Vi zu erhalten. Darüber hinaus muss für eine Maßnahme Mi gewährleistet sein, dass diese dem richtigen Vorgang Z zugeordnet wird. In Folge dessen ist der Aufbau einer Versionierung im Datamart wünschenswert, welche die versionierten Stammdaten aus dem Core mit den Bewegungsdaten aus dem Datamart verknüpft.
Bei der Umsetzung gilt es, folgende Herausforderungen zu bewältigen:
Fehlerhafte manuelle Eingaben und Datenkonsistenz
Das Problem der zu einer Person existierenden, zeitlich überschneidenden oder parallellaufenden Vorgänge ist zurückzuführen auf die fehlerhafte oder zu Teilen unvollständige manuelle Erfassung im Quellsystem. Diese beeinträchtigt nicht nur den Aufbau der Versionierung, sondern auch die Qualität der neuen Kennzahlen. Hierfür müssen insbesondere aufwändige Bereinigungen erfolgen.
Eine weitere Herausforderung ist die Sicherstellung der Datenkonsistenz. Es ist entscheidend, dass die neuen Kennzahlen korrekt berechnet und strukturelle Inkonsistenzen vermieden werden. Außerdem darf bei der Integration in die Datenbank die Integrität der bestehenden Daten nicht gefährdet werden. Hierfür werden umfassende Testpläne entwickelt und Validierungen durchgeführt, um die Korrektheit sowie Konsistenz der neuen Kennzahlen sicherzustellen.
Aufbau der Versionshistorie und tägliche Bewirtschaftung
Es muss ein Verfahren entwickelt werden, um die zu einem Vorgang Z gehörenden Vorgänge Vi und Maßnahmen Mi zuordnen zu können. Dies wird von speziellen SQL-Skripten erzielt, die unter anderem im nachfolgenden Abschnitt genauer erläutert werden. Neben der initialen Beladung der Versionierung im Datamart muss ebenfalls sichergestellt werden, dass die tägliche Bewirtschaftung der neuen Versionstabelle gewährleistet ist. Hierfür soll ein Mapping in Informatica PowerCenter entwickelt werden, welches die tägliche Versionierung der Daten vornimmt.
Performance-Engpässe
Die Kennzahlen und die neue Versionierung darf die Performance des täglichen Betriebs nicht beeinträchtigen. In einem PoP wurde zunächst entlang des Artikels Performante Dublettenentfernung mit Informatica PowerCenter die initiale Versionierung geprüft. Die Berechnung pro Minute von weniger als 10 der insgesamt 26 Millionen Datensätze zeigte sich als nicht performant. Mit den deutlich größeren Leistungsreserven von 320 CPUs (im Vergleich zu den auf dem ETL-Server bereitstehenden 16 CPUs) und der parallelisierten Verarbeitung der indizierten Daten boten die SQL-basierten Skripte eine vermeintlich bessere Alternative. Die Verlagerung von Berechnungen und Datenverarbeitungsprozessen von Informatica PowerCenter auf den vorhandenen VMware Greenplum® Datenbankcluster stellt eine technische Herausforderung dar.
Umsetzung
Aufgrund der oben erwähnten Ressourcen spielten SQL-Skripte die zentrale Rolle bei dem Aufbau der Dimensionshistorie. Im Entwicklungsprozess haben sich dabei die nachfolgenden Prozessschritte hervorgetan.
Sicherung der Datenqualität
Eingangs wurde bereits auf den hohen Anspruch der Statistik an die Datenqualität hingewiesen. Eine zuverlässige Metrik erfordert daher eine solide Datenbasis. Um dieses Ziel zu erreichen, müssen Redundanzen und strukturelle Inkonsistenzen aufgedeckt und betroffene Datensätze entfernt werden.
Für den Kunden war es daher von besonderer Bedeutung, zunächst alle notwendigen Regeln und Anforderungen an die vorhandenen und auch zukünftigen Daten aus einem historisch gewachsenen Data Warehouse zu isolieren und gegebenenfalls neu zu definieren.
Dieser Vorgang war und ist dahingehend so entscheidend, um in den darauffolgenden Korrekturen der Inkonsistenzen auf eine manuelle Überprüfung verzichtet zu können.
Der Findungsprozess
Self-joins, geknüpft an sinnvolle join-Bedingungen sind ein effektives Mittel, um Redundanzen zu identifizieren. Zumal in diesem use case mehr als vier verschiedene Szenarien möglich waren, die einen Datensatz als ungültig qualifizieren konnten. Window functions wären hier ebenfalls denkbar und erzielen unter Umständen eine effizientere Verarbeitung der Daten und sollen daher an dieser Stelle erwähnt werden.
Angesichts der Les- und Nachvollziehbarkeit der Prozessschritte wurde sich jedoch für eine self-join-Logik entschieden.
Dazu wurde die Quelltabelle mit Informationen aus validierten Bewegungsdaten angereichert und mit sich selbst verbunden (siehe Abbildung 2). Die daraus entstandene Schnittmenge wurde genutzt, um eine verschlankte, geprüfte Datenbasis zu kreieren.
Die Verknüpfung von Core und Datamart
Der betrachtete Datamart fungierte bis dato als Datenbasis für tagesaktuelle Auswertungen von fachlich sinnvollen, nicht abgeschlossenen Vorgängen. Daher sieht das dazugehörige Regelwerk der Verarbeitungslogik eine rückwirkende Änderung abgeschlossener, ungültiger oder gelöschter Vorgänge nicht vor. Die fehlende Verbindung ins Core durch einen Anker- oder eindeutigen Fremdschlüssel ist deshalb nicht unbedingt verwunderlich, birgt wie in diesem Anwendungsfall anschaulich zu erkennen aber das Risiko divergierender Daten.
Um dennoch eine Verbindung zwischen Datamart und Core herzustellen, wurden die Start- sowie Endzeitpunkte eines Vorgangs Z einer Person genutzt, um mögliche Übereinstimmungen auf den versionierten Core-Tabellen zu finden (siehe Abbildung 3 Markierung).
Dabei kann, wie Eingangs beschrieben, ein Vorgang Z mehrere Vorgänge V zeitlich überspannen und zusammenfassen. Die maximalen äußeren Grenzen bleiben dabei aber bestehen, sodass eine Zuordnung wieder möglich ist.
Bei der Erkennung von Dateninkonsistenzen oder Dubletten durch den join, werden die in der Abbildung 3 gezeigten Regeln angewandt. Der Datensatz wird auf den tagesaktuellen Stand gebracht, als ungültig markiert oder gar gelöscht. Dieser Schritt wirkt harsch, jedoch kann nach den angewandten Regeln nur ein einziger Vorgang Z einer Person zu einem bestimmten Zeitpunkt existieren. Zeitliche Überschneidungen in den Vorgängen Z würde im Aufbau einer Versionshistorie zu einer unerwünschten Vervielfältigung von Datensätzen führen.
Die Versionierung
Für die Entwicklung einer initialen Datenhistorie werden die bereinigten täglichen Daten erneut über die versionierten Core-Daten geschickt, über die bestehende Historie vervielfältigt (siehe Abbildung 4 Links) und auf temporäre Tabellen ausgelagert. Als zeitliche Grenzen dienen dabei die Start- und sofern vorhanden die Endzeitpunkte eines Vorgangs Z. Gezielte select-Statements und umfassende join-Bedingungen schränken dabei die abgefragten Informationen der Bewegungsdaten auf das Wesentliche ein und sorgen für einen schlanken Datenfluss.
Der in diesem Schritt noch nichtexistierende eindeutige Versionsschlüssel wird temporär durch einen composite key ersetzt. Das ermöglicht natürlich nicht nur die Identifizierung einzelner Datensätze, sondern auch die Referenzierung zwischen den temporären Tabellen. Zudem beschleunigt es die parallele Qualitätssicherung und Anreicherung der Versionstabelle mit Bewegungsdaten.
Die sich daraus zeitlich noch überlappenden Datenversionen werden im nächsten Schritt durch window functions einem Ranking unterzogen und gefiltert, sodass diese nicht-überlappende Zeitintervalle ergeben. Entscheidend für den Abschluss eines Intervalls ist dabei das Datum der Erfassung des darauffolgenden Vorgangs. Abschließend wird der gesetzte composite key aufgelöst und der Primärschlüssel auf der Tabelle gesetzt. Zudem wird im gleichen Zug eine täglich bewirtschaftete Ankertabelle zwischen Datamart und Core erstellt.
Performance-Optimierung
Meilenstein des Projekts war die schnelle Kennzahlenberechnung durch direkte Ausführung auf dem on-premise Datenbankcluster. Glücklicherweise lag uns keine „pay-as-you-go“-Restriktion vor.
Eine Kombination aus indizierten Daten, durchdacht eingesetzten window functions und die Auslagerung von Berechnungen auf temporäre Tabellen resultieren in einer kundenfreundlichen Performance.
Die Berechnungszeit von mehreren Tagen in Informatica PowerCenter wurde auf wenige Minuten auf dem Cluster reduziert. PowerCenter wird ausschließlich für die tägliche Delta-Kodierung verwendet.
Insgesamt wurden mehr als 2000 Zeilen SQL-Code benötigt, um eine fachlich korrekte initiale Versionshistorie aufzubauen. Im Sinne der Modularität konnte mehr als die Hälfte des Codes für die tägliche Verarbeitung weiterer täglich geladener Tabellen in diesem Datamart wiederverwendet werden. Daraus ergab sich nicht nur ein reduzierter workload der Entwickler, es konnten auch die zeitlichen engen Grenzen der Batchverarbeitung des Kunden eingehalten werden.
Resümee
Die Neukonzipierung wichtigster Führungskennzahlen der vorliegenden Statistik-Datenbank und die Entwicklung einer rückwirkenden Datenhistorie ohne Verlust der Integrität war ein komplexes und herausforderndes Projekt. In diesem use case konnte durch den Einsatz von SQL-Skripten die Datenqualität enorm verbessert, inkonsistente manuelle Nutzereingaben korrigiert und die Performance der Verarbeitungsstrecke optimiert werden. Die Entwicklung der Versionierung im Datamart verbesserte zudem die Transparenz und Nachvollziehbarkeit der Daten erheblich.
Die versionierte Dimension wird in Zukunft auch noch für neue Kennzahlen relevant und von Nutzen sein. Dem Kunden wird die Möglichkeit geboten, präzisere und informationsreichere Analysen und Berichte, die auf verlässlichen Daten basieren, durchzuführen. Die Ergebnisse dieses Projekts sind beeindruckend und zeigen die Wichtigkeit in die Qualität und Performance von Datenbanken zu investieren.
Die Erfolge dieses Projekts sind ein Beweis dafür, dass innovative Lösungen zur Verbesserung von Datenbanken im laufenden Betrieb möglich sind.
