Verdichten von historisierten Daten

Ich historisiere Faktentabellen bzw. deren Basis indem ich ein Modifydate und eine Operation hinzugefüge. Das Modifydate gibt an, wann ein Record geändert wurde und die Operation was damit passiert ist.Operation kann folgende Werte annehmen:

  • I: Insert
  • U: Update
  • S: Storno

Beim ersten Einfügen ist ein Datensatz mit Operation „I“ markiert.

Findet eine Änderung an den Daten statt, so wird der zuvor bestehende Datensatz mit einem „U“ versehen, ein Stornodatensatz „S“ mit Modifydate des aktuellen Laufs erstellt (inkl. negativer Faktoren bei Measures) und ein neuer Datensatz mit „I“ eingefügt.

Wurde der Datensatz in der Basistabelle gelöscht, so wird lediglich ein Storno-Datensatz eingefügt.

Somit ist sichergestellt, dass bei Gruppierung der Daten und Summierung der Measures sich immer der aktuelle Stand ergibt.

Nun kann es aber vorkommen, dass die Historie sehr groß wird, weil auch Änderungen von nicht relevanten Felder getrackt werden. Im konkreten Fall enthielt das Vorsystem ein Datumsfeld, dass sich als einziges Feld täglich geändert hat ohne einen Mehrwert zu bringen. Es wurde entschieden diese Änderungen als nicht relevant zu erachten und die Historie wie folgt zu bereinigen.

Vorgehensweise

Es werden zunächst nur „I“ und „U“ Datensätze berücksichtigt und je Datensatz verglichen. Dazu wird zunächst eine ROW_NUMBER auf Basis der Sortierung nach <KEY-COLUMNs> und Modifydate eingefügt. Dann wird diese Tabelle mit sich selbst über ROW_NUMBER und ROW_NUMBER+1 gejoined, als ein Datensatz mit dem jeweils nächsten vergleichen. Wenn alle history-relevanten Felder (FIELD1…n) gleich sind, wird der Datensatz ignoriert, ansonsten weiterverwendet.

Anschließend werden noch Storno-Datensätze für jeden „U“ Datensatz eingefügt und die relevanten Measures (MEASURE1…n) mit -1 gewichtet.

Schritt 1: Vergleich mit dem nächsten Datensatz

— Einfügen einer ID
WITH CTE1 AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY <KEY-COLUMNS>, MODIFYDATE) rwn,
*
FROM
History_Table
WHERE OPERATION <> ‚S‘)
— JOINEN mit dem nächsten Datensatz und vergleichen, ob es Unterschiede gibt
SELECT
a.*
INTO #TmpTable
FROM
CTE1 AS a LEFT JOIN CTE1 AS b ON
a.rwn =b.rwn+1
WHERE
(
(a.FIELD1<>b.FIELD1 OR (a.FIELD1 IS NULL AND b.FIELD1 IS NOT NULL) OR (a.FIELD1 IS NOT NULL AND b.FIELD1 IS NULL)) OR
(a.FIELD2<>b.FIELD2 OR (a.FIELD2 IS NULL AND b.FIELD2 IS NOT NULL) OR (a.FIELD2 IS NOT NULL AND b.FIELD2 IS NULL)) OR …
)

Schritt 2: Generieren von Storno Datensätzen

WITH CTE AS
(
SELECT
LEAD(MODIFYDATE) OVER (Partition by <KEY-COLUMNS> ORDER BY MODIFYDATE ) AS MODI
, *
FROM #TmpTable)
INSERT INTO #TmpTable
SELECT
‚S‘ AS OPERATION
,COALESCE(MODI,MODIFYDATE) AS MODIFYDATE
, <KEY-COLUMNS>
, FIELD1
, FIELD2

, -MEASURE1
, -MEASURE2

FROM CTE
WHERE OPERATION=’U‘

Im Nachgang ist dann noch die History_Table durch die #TmpTable  zu ersetzten.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s