Die Vermeidung von Schlüsselverletzungen und Dubletten ist bei der Bewirtschaftung konsolidierter Data Warehouses alltägliche Grundanforderung. Es existiert eine ganze Reihe von Software-Tools, wie beispielsweise DWautomatic oder IBM Change Data Capture, die diese Filterungen unterstützen. Im Fall der alleinigen Verwendung eines ETL-Tools bleibt es jedoch der Mapping-Logik überlassen, die Dublettenfilterung zu übernehmen.
In diesem Artikel beleuchten wir eine performante Umsetzungsmöglichkeit am Beispiel des erfolgreichen ETL-Tools Informatica PowerCenter. Im Folgenden wollen wir immer von im Data Warehouse üblichen Staging-Tabellen als Datenquelle ausgehen.
Dubletten
Die Entstehungsmöglichkeiten von Dubletten sind zahlreich. Gehen wir hier davon aus, dass die Daten-Quelle die Aktualität ihrer Sätze mit einem Zeitstempel QUELL_TS markiert. Jede Aktualisierung eines Datensatzes erzeugt so einen neuen Satz mit gleichem Schlüssel aber neuem, höherem QUELL_TS. Durch Nachlieferungen nach Verbindungsproblemen o.ä. ist nicht ausgeschlossen, dass neuere QUELL_TS ältere „überholen“ und vor ihnen in der Anlieferungstabelle ankommen.
Wird die Anlieferung derselben fachlichen Daten – beispielsweise wegen Verbindungsproblemen – wiederholt, so entstehen fachlich identische Sätze. Allen weiteren Dublettenentstehungsmöglichkeiten – vor allem durch schlechte Qualität der angelieferten Daten – begegnen wir mit der Annahme bzw. Definition, dass die neueste Zeile die beste Information enthält. Gehen wir hierbei davon aus, dass ein fortlaufender Zeilenidentifikator STAGE_ROW_ID vorhanden ist. Pro fachlichem Schlüssel – nennen wir ihn KEY1 – wollen wir also nur die Zeile mit der höchsten STAGE_ROW_ID von denen mit dem höchsten QUELL_TS.
Performance-Randbedingungen
In ETL-Tools sind Aggregatoren und Joiner kostenintensive Transformationen, da sie Caches für Ihre Berechnungen anlegen und vorhalten müssen. Man kann sich jedoch die zeilenweise Verarbeitung von Datenzeilen zunutze machen und Informationen von einer Vorgängerzeile an einen Nachfolger weiterreichen. Dies ermöglicht einerseits die performanteste Art zur Wechselwirkung von Datenzeilen miteinander, ist andererseits aber auf eine definierte Sortierung angewiesen und so auf einen einseitigen Datenfluss beschränkt. Die passende Zeilen-Reihenfolge und damit Sortierung ist also die Grundvoraussetzung für performante Dublettenentfernung.
Zeilen-Reihenfolge
Da in PowerCenter eine Zeile immer nur Informationen von ihrem Vorgänger (und damit ggfs. auch weiteren Vorgängern) erhalten kann, ist es nötig, die zu erhaltende Zeile innerhalb einer Dubletten-Gruppe an die erste Stelle zu sortieren. Die nachfolgenden Zeilen können dann erkennen, dass ihr Vorgänger einen identischen Schlüssel hat und sich selbst so als zu entfernende Dublette erkennen.
Die Sortierreihenfolge der Schlüsselattribute ist unerheblich, da es auf die Reihenfolge der Elemente innerhalb einer Schlüsselgruppe ankommt, aber nicht auf die Reihenfolge der Gruppen zueinander. Es muss nur sichergestellt sein, dass die Zeilen eines Schlüsselattributs immer in einer einzigen Gruppe zusammenstehen. Anders ist es bei QUELL_TS und STAGE_ROW_ID: Hier ist jeweils eine absteigende (descending) Sortierung nötig, um den neuesten Satz mit jeweils höchstem Wert zu erhalten.
Sortierung in Informatica PowerCenter
Relationale Datenbanken sind grundsätzlich gut für Sortierungen ausgelegt und so ist es möglich, die Daten schon bei der Selektion sortiert abzurufen. In PowerCenter geschieht diese Einstellung im Source Qualifier Objekt im Reiter „Properties“. Hier kann in das Feld „Number of Sorted Ports“ die Anzahl der Attribute eingetragen werden über die sortiert werden soll. Über Auswahl und Sortierreihenfolge der Attribute entscheidet dabei ihre Reihenfolge von oben nach unten im Source Qualifier. Das Attribut über das als erstes sortiert werden soll, wird also als oberster Port durch den Source Qualifier geführt.
Die Umsetzung dieser Einstellungen von PowerCenter ist dann denkbar einfach: es wird ein „order by …“ gefolgt von einer Liste der betreffenden Attributnamen an das Selektions-Statement angehängt. Ohne weitere Spezifizierung erfolgt die Sortierung auf der Datenbank dann nach der dortigen Standard-Einstellung – im Normalfall also aufsteigend. Die Mitgabe einer aufsteigend (ascending) oder absteigend (descending) Angabe ist nicht möglich, was das ganze für uns unbrauchbar macht: Wir sind ja gerade auf eine absteigende Sortierung angewiesen, um unsere zu erhaltenden Sätze an den Anfang einer Dubletten-Gruppe zu platzieren.
Mit einem Trick können wir aber doch die von uns gewünschte, passende „order by“-Formulierung in das Statement einschleusen. Dazu nutzen wir den „Source Filter“ im selben Reiter, in den sonst „where“-Bedingungen des Statements eingetragen werden. Hier machen wir uns zu Nutze, das PowerCenter genau das tut: es schreibt, was im Source Filter steht im SQL-Statement hinter das „where“. Auf diesen eventuellen Filter folgt im SQL dann ja aber auch direkt das „order by“. So kann man hier nach dem Filter direkt mit der selbstgeschriebenen „order by“-Formulierung fortfahren. Wie auch in unserem einfachen Beispiel braucht man ja nicht immer einen wirklichen Filter. Damit das Statement valide wird, benötigt man also ggfs. eine Pseudo-Filter-Formulierung. Hier genügt z.B. ein „1 = 1“. In unserem Beispiel würden wir also folgendes in den Source Qualifier schreiben:
1=1
order by
KEY1,
QUELL_TS desc,
STAGE_ROW_ID descDie anfangs genannte „Number of Sorted Ports“ muss dann natürlich auf 0 stehen, damit PowerCenter nicht wieder ein weiteres „order by“ anfügt.
Abhängig von der verwendeten Quell-Datenbank und der Komplexität der Quelldatenselektion kann es günstiger sein, auf die Sortierung auf der Datenbank zu verzichten und die Daten erst im Mapping von einem Sorter-Objekt sortieren zu lassen. Dies kann bei notwendigen fachlichen Filtern oder komplexeren Delta-Daten-Selektionen passieren, wenn die Datenbank dabei nicht in der Lage ist, vorhandene Indices sinnvoll zu nutzen. Beim Sorter-Objekt in PowerCenter wird die Sortierreihenfolge auch über die Reihenfolge der Ports von oben nach unten bestimmt. „Ascending“ oder „Descending“ kann hierbei dann ganz einfach ausgewählt werden.
Mit diesen zwei Vorgehensweisen können wir nur also jede von uns gewünscht Sortierung erzeugen. In den folgenden Bild-Beispielen sind PowerCenter-Sorter verwendet, weil so schneller ersichtlich ist, wie genau sortiert wird.
Dublettenerkennung von einzelnen Schlüsseln
Zur Erkennung von Dubletten eines Schlüssels wird nach diesem sortiert. Eine Zeile kann sich so als Dublette erkennen, indem sie ihr Schlüsselattribut – in unserem Beispiel KEY1 – mit dem ihrer Vorgängerzeile vergleicht. Sind diese gleich, kann sie sich als Dublette markieren und ausgefiltert werden.
Die erste Zeile einer Dublettengruppe erkennt sich selbst also nicht als Dublette und ist diejenige einer solchen Gruppe, die bestehen bleibt. Anhand der passenden Sortierung haben wir dafür gesorgt, dass diejenige Zeile als erste verarbeitet wird, die wir im Falle von Dubletten behalten wollen: diejenige mit höchstem QUELL_TS und unter denen ggfs. die mit der höchsten Zeilennummer STAGE_ROW_ID.
Für den Vergleich mit der Vorgängerzeile machen wir uns zu Nutze, dass in PowerCenter-Expressions die Variablen-Felder von oben nach unten berechnet werden. Wenn man bei einer Berechnung also eine andere Variable verwendet, die weiter unten in der Expression steht, dann hat diese zu dem Zeitpunkt noch den „alten“ Wert der Vorgängerzeile.
In unserem Beispiel verwenden wir eine Variable V_FLAG_DUBLETTE, die das Schlüsselattribut KEY1 mit dem Wert einer Variable V_KEY1_PREV vergleicht, in der der Vorgängerzeilenwert gespeichert ist:
IIF( KEY1 = V_KEY1_PREV, 1, 0 )Die Variable V_KEY1_PREV nimmt einfach nur den Wert des durchgeführten Ports KEY1 auf. Entscheidend ist nur, dass V_FLAG_DUBLETTE in der Expression über V_KEY1_PREV steht, damit zum Berechnungszeitpunkt von V_FLAG_DUBLETTE in V_KEY1_PREV noch der Wert von KEY1 der Vorgängerzeile steht. Erst nach Berechnung von V_FLAG_DUBLETTE wird in V_KEY1_PREV der Wert der aktuellen Zeile hinterlegt, um ihn erst in der nachfolgenden Zeile dann zu benutzen. In der Abbildung „Dublettenerkennung von einzelnen Schlüsseln“ ist das Expression-Objekt mit den passenden Feld-Reihenfolgen und ‑Inhalten gezeigt.
In unserem Beispiel führen wir die Dubletten-Information aus V_FLAG_DUBLETTE in einem Output-Port FLAG_DUBLETTE aus der Expression hinaus. In einem nachfolgenden Filter werden dann nur Zeilen durchgelassen, die sich nicht als Dubletten markiert haben, für die also FLAG_DUBLETTE = 0 gilt.
Dublettenerkennung in Gesamtdaten-Lieferungen
In Fällen, in denen nur die neuste Gesamtlieferung von Datensätzen herangezogen werden soll, muss es einen eindeutigen, über die Lieferung konstanten Identifikator geben. Gehen wir also davon aus, dass eine Gesamtlieferung mit einheitlichem QUELL_TS geliefert wird. Hierbei sortieren wir dann zunächst absteigend nach diesem QUELL_TS, sodass die zu berücksichtigenden Sätze als erstes in das Mapping geführt werden. Sobald ein QUELL_TS nicht dem seines Vorgängers entspricht, ist dieser Satz und alle folgenden veraltet und werden verworfen.
In unserem Beispiel lassen wir in V_FLAG_QTS_KLEINER darauf horchen und wenn der Übergang einmal gefunden wurde, die Information in alle folgenden Zeilen weitertragen:
IIF( QUELL_TS < V_QUELL_TS_PREV or V_FLAG_QTS_KLEINER = 1, 1, 0 )Die Ausgabe zum Wegfiltern FLAG_DUBLETTE setzt sich dann aus der Dublettenerkennung einzelner Schlüssel V_FLAG_DUBLETTE und der Gruppenerkennung V_FLAG_QDZ_KLEINER zusammen:
IIF( V_FLAG_DUBLETTE = 1 or V_FLAG_QTS_KLEINER = 1, 1, 0 )In der Abbildung „Dublettenerkennung in Gesamtdaten-Lieferungen“ wird das Vorgehen bildlich zusammengefasst.
Dublettenerkennung bei Gruppen-Schlüsseln
Wenn für einen Gruppenschlüssel – wir nennen ihn hier GRUPPEN_KEY – nur die neusten Elemente berücksichtigt werden sollen, verwenden wir eine Sortierung nach dem GRUPPEN_KEY, dann dem QUELL_TS absteigend und danach den Einzelschlüssel-Attributen – hier KEY1.
Die Dublettenerkennung für einzelne Sätze geht über den gesamten Schlüssel, wird also zu:
IIF( GRUPPEN_KEY = V_GRUPPEN_KEY_PREV and
KEY1 = V_KEY1_PREV, 1, 0 )Die Prüfung in V_FLAG_QTS_KLEINER findet jetzt nur noch innerhalb eines GRUPPEN_KEY statt:
IIF( GRUPPEN_KEY = V_GRUPPEN_KEY_PREV and
( QUELL_TS < V_QUELL_TS_PREV
or
V_FLAG_QTS_KLEINER = 1
)
1, 0 )Die Abbildung „Dublettenerkennung bei Gruppen-Schlüsseln“ fasst das Vorgehen zusammen.
Fazit
Durch die zeilenweise Verarbeitung ist in Informatica PowerCenter eine Dubletten-Erkennung und Filterung bei Sortierung nach dem entsprechenden Schlüssel performant möglich. Wenn es weitere sortierbare Felder zur Identifikation des zu erhaltenden Satzes einer Dubletten-Gruppe gibt, kann er durch die passende Sortierung als der Satz ausgewählt werden, der nicht ausgefiltert wird. Zudem ist das Prinzip auch auf komplexere Gruppen-Auswahl-Regeln erweiterbar.
