Debugging SCD2

Dieser Beitrag ist wieder über Slowly Changing Dimensions Type 2, betrachtet aber eine andere Fragestellung. Wie kann man die Erkennung der Änderungen validieren? Wenn wir mehrere Versionen derselben Daten haben, wie kann man prüfen, welche Felder sich von Version zu Version geändert haben? In Kundeprojekten, wo ich mit Systemen wie Siebel CRM gearbeitet habe, die in einigen Tabellen mehr als 500 Spalten haben, fand ich diese Möglichkeit oft sehr nützlich.
Natürlich kann man mit PL/SQL-Mitteln in einer Schleife über die Spalten ihre Werte vergleichen. Ich habe mich spaßeshalber gefragt, ob es auch in „pure SQL“ ginge – hier ist die Lösung.

Meine Idee ist, erst ein Zwischenergebnis vorzubereiten, das aus einem Datensatz pro zu vergleichendes „Versionspärchen“, Spaltenname und Spaltenwert besteht und dann nach ersten zwei zu gruppieren. HAVING COUNT(DISTINCT value) wird uns helfen, die Änderungen zu sehen. Klingt kompliziert? Probieren wir es mal!

Wir fangen mit der Tabelle an, die im vorherigen Beitrag über SCD2 erstellt wurde:

SQL> select * from t_target;

   DWH_KEY VALID_FROM VALID_TO  C ETL BUS_K FIRST_NAME SECOND_NAM LAST_NAME  HIRE_DATE FIRE_DATE     SALARY
---------- ---------- --------- - --- ----- ---------- ---------- ---------- --------- --------- ----------
         1 01-DEC-16  02-DEC-16 N UPD 123   Roger                 Federer    01-JAN-10               900000
         2 01-DEC-16  02-DEC-16 N UPD 456   Rafael                Nadal      01-MAY-09               720000
         3 01-DEC-16  01-DEC-16 N UPD 789   Serena                Williams   01-JUN-08               650000
         5 02-DEC-16  03-DEC-16 N UPD 789   Serena     Jameka     Williams   01-JUN-08               650000
         6 02-DEC-16  02-DEC-16 N UPD 345   Venus                 Williams   01-NOV-16               500000
        10 03-DEC-16  31-DEC-99 Y INS 345   Venus                 Williams   01-NOV-16 01-DEC-16     500000
        11 03-DEC-16  31-DEC-99 Y INS 123   Roger                 Federer    01-JAN-10               920000
        12 04-DEC-16  31-DEC-99 Y INS 789   Serena     Jameka     Williams   01-JUN-08               700000

 8 rows selected 

Wir sollen die drei gekennzeichneten Datensätze für den Business Key 789 vergleichen und den Feldnamen berichten, wo eine Änderung stattgefunden hat, sowie auch den „alten“ und den „neuen“ Werte. Ich möchte eine Abfrage schreiben, die mehr als zwei Versionen auf einmal vergleichen kann. Aus diesem Grund fangen wir mit dem „Identifizieren“ dieser „Versionspärchen“ an:

select a.dwh_key id_a
,      lead(a.dwh_key) over (order by a.valid_from) id_b
from   t_target a 
where  a.bus_key = 789 

      ID_A       ID_B
---------- ----------
         3          5
         5         12
        12           

Wir nutzen den künstlichen Schlüssel DWH_KEY, um die Datensätze zu identifizieren. Und wir müssen die Änderungen innerhalb zwei Pärchen von Datensätzen untersuchen: 3 <-> 5 and 5 <-> 12. Der dritte Datensatz im Ergebnis ist für uns nicht interessant und wird im nächsten Schritt herausgefiltert.

Jedes Pärchen besteht aus zwei Datensätzen. Daher duplizieren wir jeden Datensatz in diesem Ergebnis durch joinen mit einem „row source generator“, der exakt zwei Datensätze hat (die kleine Subquery aus DUAL mit CONNECT BY). Dabei stellen wir den dwh_key wieder zur Verfügung, der jeden Datensatz identifiziert (Zeile 9) und generieren einen eindeutigen Schlüssel, der das Pärchen indentifiziert (Zeile 10)

with p as ( 
  select a.dwh_key id_a
  ,      lead(a.dwh_key) over (order by a.valid_from) id_b
  from   t_target a 
  where  a.bus_key = 789  )
,  pairs as (
  select id_a
  ,      id_b 
  ,      case when lvl = 1 then id_a else id_b end id
  ,      dense_rank() over (order by id_a, id_b) rn 
  from   p
  ,     (select level lvl from dual connect by level<=2) row_source
  where id_b is not null )
select * from pairs;

      ID_A       ID_B         ID         RN
---------- ---------- ---------- ----------
         3          5          5          1
         3          5          3          1
         5         12         12          2
         5         12          5          2
 &#91;/code&#93;

Jetzt sind wir bereit, die Spaltennamen und -Werte zu sammeln. Aber wie? Wir müssen die Spalten "kippen", sodass sie zu Datensätzen im Ergebnis werden - klassische Aufgabe von einem UNPIVOT-Operator. Und INCLUDE NULLS nicht vergessen, schließlich sind die Änderungen von NULL oder auf NULL-Wert auch zu erkennen!

&#91;code language="sql"&#93;
SQL> with prep as ( 
  select a.dwh_key id_a
  ,      lead(a.dwh_key) over (order by a.valid_from) id_b
  from   t_target a 
  where  a.bus_key = 789  )
,  pairs as (
  select id_a
  ,      id_b 
  ,      case when lvl = 1 then id_a else id_b end id
  ,      dense_rank() over (order by id_a, id_b) rn 
  from   prep
  ,     (select level lvl from dual connect by level<=2) row_source
  where id_b is not null )
select * 
from   ( select p.rn, a.DWH_KEY, a.FIRST_NAME, a.SECOND_NAMES, a.LAST_NAME
         from   t_target a, pairs p
         where  a.dwh_key = p.id )
unpivot include nulls (column_value for column_name in (FIRST_NAME, SECOND_NAMES, LAST_NAME )  );

        RN    DWH_KEY COLUMN_NAME                    COLUMN_VALUE                 
---------- ---------- ------------------------------ ------------------------------
         1          3 FIRST_NAME                     Serena                        
         1          3 SECOND_NAMES                                                 
         1          3 LAST_NAME                      Williams                      
         1          5 FIRST_NAME                     Serena                        
         1          5 SECOND_NAMES                   Jameka                        
         1          5 LAST_NAME                      Williams                      
         2          5 FIRST_NAME                     Serena                        
         2          5 SECOND_NAMES                   Jameka                        
         2          5 LAST_NAME                      Williams                      
         2         12 FIRST_NAME                     Serena                        
         2         12 SECOND_NAMES                   Jameka                        
         2         12 LAST_NAME                      Williams                      

 12 rows selected 
&#91;/code&#93;

Sieht erst mal gut aus. Jetzt können wir nach RN und COLUMN_NAME gruppieren und die Änderungen sehen. Aber vorher brauchen noch ein paar Schritte... Zum einen, man kann UNPIVOT und GROUP BY in einer Abfrage nicht kombinieren, also müssen wir noch einmal verschachteln  (Zeilen 14-20). Der andere Punkt zu beachten, ist schon wieder die spezielle Handhabung von NULL-Werten: MIN, MAX and COUNT(distinct ... ) werden NULL-Werte ignorieren. Es sind aber Workarounds für das Problem da: wir können KEEP Klausel mit MIN und MAX einsetzen (Zeilen 22,23) und um auch die NULL-Werte zu zählen - COUNT(distinct dump(column_value)) (Zeile 26) (mehr dazu siehe <a href="http://www.sqlsnippets.com/en/topic-12656.html" target="_blank">Nulls and Aggregate Functions</a>). 


SQL> with prep as ( 
  select a.dwh_key id_a
  ,      lead(a.dwh_key) over (order by a.valid_from) id_b
  from   t_target a 
  where  a.bus_key = 789  )
,  pairs as (
  select id_a
  ,      id_b 
  ,      case when lvl = 1 then id_a else id_b end id
  ,      dense_rank() over (order by id_a, id_b) rn 
  from   prep
  ,     (select level lvl from dual connect by level<=2) row_source
  where id_b is not null )
,  unpivot_query as (  
  select * 
  from   ( select p.rn, a.DWH_KEY, a.FIRST_NAME, a.SECOND_NAMES, a.LAST_NAME
           from   t_target a, pairs p
           where  a.dwh_key = p.id )
  unpivot include nulls (column_value for column_name in (FIRST_NAME, SECOND_NAMES, LAST_NAME )  )
  )
select rn
,      min (column_value) keep (dense_rank first order by dwh_key) old_value
,      max (column_value) keep (dense_rank last order by dwh_key) new_value
from   unpivot_query
group by rn, column_name
having count(distinct dump(column_value)) > 1
order by rn;

        RN OLD_VALUE                      NEW_VALUE                    
---------- ------------------------------ ------------------------------
         1                                Jameka                        

Fertig? Nicht ganz. Wir haben bis jetzt nur die drei VARCHAR2-Spalten verglichen, es gibt aber auch NUMBER- und DATE-Spalten in der Tabelle. Ist das ein Problem? Ja, ist es! Der Versuch, UNPIVOT mit Spalten von unterschiedlichen Datentypen zu nutzen, resultiert in ORA-01790:

...
unpivot include nulls (column_value for column_name in (FIRST_NAME, SECOND_NAMES, LAST_NAME, SALARY )  )
...
ORA-01790: expression must have same datatype as corresponding expression

Aber auch dieses Problem können wir lösen, wenn auch nicht besonders elegant. Einfach drei UNPIVOT subqueries definieren, die mit eigenen Datentypen umgehen können (VARCHAR2, DATE, NUMBER), dann die Ergebnisse zu Strings konvertieren und mit UNION ALL zusammenführen:

SQL> with prep as ( 
  select a.dwh_key id_a
  ,      lead(a.dwh_key) over (order by a.valid_from) id_b
  from   t_target a 
  where  a.bus_key = 789  )
,  pairs as (
  select id_a
  ,      id_b 
  ,      case when lvl = 1 then id_a else id_b end id
  ,      dense_rank() over (order by id_a, id_b) rn 
  from   prep
  ,     (select level lvl from dual connect by level<=2) row_source
  where id_b is not null )
,  unpivot_query as (  
  -- CHAR fields
  select * from (
  select * 
  from   ( select p.rn, a.DWH_KEY, a.FIRST_NAME, a.SECOND_NAMES, a.LAST_NAME
           from   t_target a, pairs p
           where  a.dwh_key = p.id )
  unpivot include nulls (column_value for column_name in (FIRST_NAME, SECOND_NAMES, LAST_NAME )  )
  )
  union all
  -- DATE fields
  select rn, DWH_KEY, column_name, to_char(column_value,'dd.mm.yyyy hh24:mi:ss')  
  from (select * 
        from   ( select p.rn, a.DWH_KEY, a.HIRE_DATE, FIRE_DATE
                 from   t_target a, pairs p
                 where  a.dwh_key = p.id )
        unpivot include nulls (column_value for column_name in (HIRE_DATE, FIRE_DATE )  )
        )
  union all
  --NUMBER fields
  select rn, DWH_KEY, column_name, to_char(column_value) 
  from (select * 
        from   ( select p.rn, a.DWH_KEY, a.SALARY
                 from   t_target a, pairs p
                 where  a.dwh_key = p.id )
        unpivot include nulls (column_value for column_name in (SALARY )  )
       )
  )
select rn
,      listagg(dwh_key,'-') within group (order by dwh_key) dwh_keys
,      column_name 
,      min (column_value) keep (dense_rank first order by dwh_key) old_value
,      max (column_value) keep (dense_rank last order by dwh_key) new_value
from   unpivot_query
group by rn, column_name
having count(distinct dump(column_value)) > 1
order by rn;

        RN DWH_KEYS   COLUMN_NAME                    OLD_VALUE                      NEW_VALUE                    
---------- ---------- ------------------------------ ------------------------------ ------------------------------
         1 3-5        SECOND_NAMES                                                  Jameka                        
         2 5-12       SALARY                         650000                         700000                        

Ich habe LISTAGG als Pärchen von DWH_KEYs hinzugefügt, um sehen zu können, welche zwei Datensätze verglichen wurden.
Okay, die endgültige Abfrage ist komplizierter, als zuerst gedacht. Es war eine spannende SQL-Aufgabe und kann für ad hoc Abfragen evtl. nützlich sein. Wenn ich eine Lösung entwickeln müsste, die auf regulärer Basis eingesetzt wird, würde ich doch zu PL/SQL greifen, denke ich. Alleine schon, um die Spalten und deren Datentypen aus dem Dictionary zu holen.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert