{"id":283,"date":"2016-11-24T18:39:20","date_gmt":"2016-11-24T16:39:20","guid":{"rendered":"http:\/\/blog.sqlora.com\/de\/?p=283"},"modified":"2018-04-08T23:00:17","modified_gmt":"2018-04-08T21:00:17","slug":"wie-vereinfache-ich-die-historisierung-der-daten","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/de\/wie-vereinfache-ich-die-historisierung-der-daten\/","title":{"rendered":"Wie vereinfache ich die Historisierung der Daten?"},"content":{"rendered":"

Die Historisierung der Daten ist eine typische aber auch rechen- und zeitintensive Aufgabe im Data Warehouse Umfeld. Man hat damit beim Beladen von historisierter Core-Schicht (auch bekannt als Enterprise Data Warehouse or Foundation Layer), Data Vault Datenmodellen, Slowly Changing Dimensions, etc. zu tun. Typische Methoden f\u00fchren einen Outer Join und eine Art der Deltaerkennung aus. Diese Deltaerkennung ist wohl der kniffligste Teil, denn man muss die Null-Werte besonders beachten. Eine sehr gute \u00dcbersicht der verwendeten Techniken hat Dani Schnider in seinem Blog zusammengestellt: Delta Detection in Oracle SQL<\/a><\/p>\n

Auf der anderen Seite bietet die SQL-Standardfunktionalit\u00e4t genau das Verhalten an, das hier gebraucht wird: die Group By Klausel oder Partitioning-Klausel bei analytischen Funktionen. Kann man das ausnutzen? Macht es Sinn? Wie wird dann der ETL Prozess aussehen? K\u00f6nnen wir eventuell das Laden durch Partition Exchange weiter beschleunigen? Ich werde diese Fragen in den n\u00e4chsten Beitr\u00e4gen beleuchten. <\/p>\n

Einf\u00fchrung und die typische Vorgehensweise<\/strong><\/p>\n

Ich nehme an, dass Ihnen die grunds\u00e4tzlichen Konzepte f\u00fcr die Historisierung und temporale G\u00fcltigkeit der Daten bekannt sind. Wir betrachten den Historisierungsprinzip, den man als „Slowly Changing Dimensions Type 2“ kennt. Der Name soll Sie aber nicht irritieren: es geht hier nicht nur um die Dimensionen.<\/p>\n

Schauen wir uns folgendes Beispiel an. Eine Quelltabelle wird t\u00e4glich in eine Zieltabelle ins Datawarehouse System geladen. Die Zieltabelle hat Datumsspalten f\u00fcr die G\u00fcltigkeitsangaben und erlaubt mehrere Versionen pro Business Key. Wenn wir f\u00fcr einen Business Key an einem Ladetag \u00c4nderungen in den gelieferten Daten feststellen, schliessen wir die aktuelle Version ab (setzen das g\u00fcltig_bis Datum) und legen eine neue Version an. Keine Daten werden \u00fcberschrieben und alle \u00c4nderungen sind \u00fcber den gesamten Zeitraum nachvollziehbar.<\/p>\n

\r\nwhenever sqlerror continue\r\ndrop table t_source purge;\r\ndrop table t_target purge;\r\ndrop sequence scd2_seq ;\r\n\r\ncreate sequence scd2_seq ;\r\n\r\n--Source Table \r\n\r\ncreate table t_source\r\n( load_date date\r\n, business_key varchar2(5)\r\n, first_name varchar2(50)\r\n, second_names varchar2(100)\r\n, last_name varchar(50)\r\n, hire_date date\r\n, fire_date date\r\n, salary number(10));\r\n\r\n\r\n-- Target Table\r\n \r\ncreate table t_target\r\n( dwh_key number\r\n, dwh_valid_from date\r\n, dwh_valid_to date\r\n, current_version char(1 byte)\r\n, etl_op varchar2(3 byte)\r\n, business_key varchar2(5)\r\n, first_name varchar2(50)\r\n, second_names varchar2(100)\r\n, last_name varchar(50)\r\n, hire_date date\r\n, fire_date date\r\n, salary number(10));\r\n\r\n\r\n\r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-01', '123', 'Roger', null, 'Federer', DATE '2010-01-01', null, 900000);\r\n               \r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-01', '456', 'Rafael', null, 'Nadal', DATE '2009-05-01', null, 720000);\r\n\r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-01', '789', 'Serena', null, 'Williams', DATE '2008-06-01', null, 650000);               \r\n               \r\n-- We simulate the first ETL run and simply insert our data in the target table\r\n \r\ninsert into t_target (dwh_key, dwh_valid_from, dwh_valid_to, current_version, etl_op, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(scd2_seq.nextval, DATE '2016-12-01', null, 'Y', 'INS', '123', 'Roger', null, 'Federer', DATE '2010-01-01', null, 900000);\r\n               \r\ninsert into t_target (dwh_key, dwh_valid_from, dwh_valid_to, current_version, etl_op, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(scd2_seq.nextval, DATE '2016-12-01', null, 'Y', 'INS', '456', 'Rafael', null, 'Nadal', DATE '2009-05-01', null, 720000);\r\n\r\ninsert into t_target (dwh_key, dwh_valid_from, dwh_valid_to, current_version, etl_op, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(scd2_seq.nextval, DATE '2016-12-01', null, 'Y', 'INS', '789', 'Serena', null, 'Williams', DATE '2008-06-01', null, 650000);  \r\n               \r\ncommit;\r\n<\/pre>\n
 
\nDer meist verbreitete Ansatz, den ich gesehen habe, f\u00e4ngt mit einem Outer Join von der Quelltabelle mit den aktuellen Versionen aus der Zieltabelle an. Der Join wird \u00fcber den Business Key gemacht. Wenn wir im Join-Ergebnis den Business Key nur auf einer Seite des Joins haben, bedeutet das, dass wir mit neuen oder gel\u00f6schten Datens\u00e4tzen zu tun haben. Wenn der Key auf beiden Seiten vorhanden ist, m\u00fcssen wir die tats\u00e4chlichen \u00c4nderungen weiter untersuchen. Damit die alten Versionen geschlossen und die neuen Versionen in einem MERGE Statement angelegt werden k\u00f6nnen, m\u00fcssen wir das Ergebnis vom Join wieder in zwei „T\u00f6pfe“ verteilen (splitten) und anschlie\u00dfend mit einem UNION ALL wieder zusammenf\u00fchren.  Der Ansatz ist im oben erw\u00e4hnten Blog-Beitrag sehr gut beschrieben: Delta Detection in Oracle SQL<\/a><\/p>\n
Das typische Muster vom ETL-Prozess sieht in Oracle Warehouse Builder etwa so aus:<\/p>\n
<\/A><\/p>\n
\"SCD2\"<\/a><\/p>\n
<\/A>
\nZiemlich viel Arbeit! Der weitere Nachteil der OWB-Implementierung liegt darin, dass die Quelle und das Ziel zwei Mal gescnaned und gejoined werden. Also, k\u00f6nnen wir das besser machen?<\/p>\n
Der Ansatz<\/strong><\/p>\n
Stellen wir vor, am n\u00e4chsten Tag kommen neue Daten in der Quelle an:<\/p>\n
\r\n-- Next we get new data in the source \r\ndelete from t_source;\r\n\r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-02', '123', 'Roger', null, 'Federer', DATE '2010-01-01', null, 900000);\r\n               \r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-02', '456', 'Rafael', null, 'Nadal', DATE '2009-05-01', null, 720000);\r\n-- New Second_name \r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-02', '789', 'Serena', 'Jameka', 'Williams', DATE '2008-06-01', null, 650000);     \r\n--new record\r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-02', '345', 'Venus', null, 'Williams', DATE '2016-11-01', null, 500000);     \r\n\r\ncommit;\r\n<\/pre>\n
 
\nF\u00fcr den Business Key 789 (Serena Williams) bekommen wir den zweiten Namen statt eines Null-Wertes. Der Business Key 345 (Venus Williams) ist neu.
\nNun haben wir folgenden Inhalt in unseren beiden Tabellen:<\/p>\n
\r\nselect * from t_source;\r\n\r\nLOAD_DATE  BUS FIRST_NAME SECOND_NAM LAST_NAME  HIRE_DATE  FIRE_DATE      SALARY\r\n---------- --- ---------- ---------- ---------- ---------- ---------- ----------\r\n2016-12-02 123 Roger                 Federer    2010-01-01                900000\r\n2016-12-02 456 Rafael                Nadal      2009-05-01                720000\r\n2016-12-02 789 Serena     Jameka     Williams   2008-06-01                650000\r\n2016-12-02 345 Venus                 Williams   2016-11-01                500000\r\n\r\n4 rows selected.\r\n\r\nselect * from t_target;\r\n\r\n   DWH_KEY DWH_VALID_ DWH_VALID_ C ETL_OP BUS FIRST_NAME SECOND_NAM LAST_NAME  HIRE_DATE  FIRE_DATE      SALARY\r\n---------- ---------- ---------- - ------ --- ---------- ---------- ---------- ---------- ---------- ----------\r\n         1 2016-12-01            Y INS    123 Roger                 Federer    2010-01-01                900000\r\n         2 2016-12-01            Y INS    456 Rafael                Nadal      2009-05-01                720000\r\n         3 2016-12-01            Y INS    789 Serena                Williams   2008-06-01                650000\r\n\r\n3 rows selected.\r\n\r\n<\/pre>\n
 
\nSchritt 1<\/strong><\/p>\n
Wir m\u00fcssen nun die Daten in der Quelltabelle mit den aktuellen Versionen aus der Zieltabelle vergleichen, pro Business Key vertsteht sich. Vielleicht ist es auf den ersten Blick nicht nachvollziehbar, aber statt diese zu joinen, f\u00fchren wir diese Daten einmal mit einem UNION ALL Operator zusammen:<\/p>\n
\r\n-- The current versions in the target table\r\nselect  'T' source_target  -- to distinguish these records later\r\n,       business_key\r\n,       dwh_valid_from\r\n,       first_name\r\n,       second_names\r\n,       last_name\r\n,       hire_date\r\n,       fire_date\r\n,       salary\r\nfrom    t_target\r\nwhere   current_version = 'Y'\r\n-- Now the new data\r\n-- put them together with UNION ALL\r\nunion all\r\nselect  'S' source_target\r\n,       business_key\r\n,       load_date dwh_valid_from  -- we use load_date as the new validity start date\r\n,       first_name\r\n,       second_names\r\n,       last_name\r\n,       hire_date\r\n,       fire_date\r\n,       salary\r\nfrom    t_source;\r\n\r\nS BUS DWH_VALID_ FIRST_NAME SECOND_NAM LAST_NAME  HIRE_DATE  FIRE_DATE      SALARY\r\n- --- ---------- ---------- ---------- ---------- ---------- ---------- ----------\r\nT 123 2016-12-01 Roger                 Federer    2010-01-01                900000\r\nT 456 2016-12-01 Rafael                Nadal      2009-05-01                720000\r\nT 789 2016-12-01 Serena                Williams   2008-06-01                650000\r\nS 123 2016-12-02 Roger                 Federer    2010-01-01                900000\r\nS 456 2016-12-02 Rafael                Nadal      2009-05-01                720000\r\nS 789 2016-12-02 Serena     Jameka     Williams   2008-06-01                650000\r\nS 345 2016-12-02 Venus                 Williams   2016-11-01                500000\r\n\r\n7 rows selected.\r\n\r\n<\/pre>\n
 
\nDamit wir dann im Ergebnis zwischen den Datens\u00e4tzen aus der Quell- und aus der Zieltabelle unterscheiden k\u00f6nnen, f\u00fchren wir eine Spalte SOURCE_TARGET ein, die die Werte ‚T‘ wie Target und ‚S‘ wie Source annehmen kann.<\/p>\n
Im Union-Ergebnis k\u00f6nnen wir maximal zwei Datens\u00e4tze pro Business Key haben. Die m\u00f6glichen Konstellationen sind:<\/p>\n
    \n
  • Die untersuchten Spalten in beiden Datens\u00e4tzen sind exakt gleich – keine Aktion notwendig<\/li>\n
  • Die untersuchten Spalten wurden ge\u00e4ndert – das m\u00fcssen wir erkenne, eine Version abschliessen und eine andere neu anlegen<\/li>\n
  • Es gibt nur einen Datensatz pro Business Key, und zwar wenn ein neuer Datensatz kommt, f\u00fcr den es noch gar keine Version in der Zieltabelle existiert oder wenn ein Datensatz aus der Quelle gel\u00f6scht wird<\/li>\n<\/ul>\n
     
    \nSchritt 2<\/strong><\/p>\n
    Dann z\u00e4hlen wir einfach die Datens\u00e4tze pro Business Key und Spalten, die wir „beobachten“, mit Hilfe der analytischen Funktion count(). Das Interessante passiert in der Zeile 32. Die Partioning-Klausel ist hier der Business_key und alle Spalten, die wir auf \u00c4nderungen untersuchen. Diese Klausel macht die ganze Arbeit f\u00fcr die Deltarerkennung f\u00fcr uns. Ihr Verhalten ist genau das, was wir brauchen: NULL=NULL und NULL!=WERT. Wenn etwas ge\u00e4ndert wurde (auch von NULL auf einen Wert oder von einem Wert auf NULL), bekommen wir eine neue Partition innerhalb der analytischen Funktion. Gab es keine \u00c4nderung, sind der neue und der alte Datensatz in derselben Partition.<\/p>\n
    \r\nwith union_source_target as \r\n(\r\n-- What are the current versions in the target table\r\nselect  'T' source_target  -- to distinguish these records later\r\n,       business_key\r\n,       dwh_valid_from\r\n,       first_name\r\n,       second_names\r\n,       last_name\r\n,       hire_date\r\n,       fire_date\r\n,       salary\r\nfrom    t_target\r\nwhere   current_version = 'Y'\r\n-- Now the new data\r\n-- put them together with UNION ALL\r\nunion all\r\nselect  'S' source_target\r\n,       business_key\r\n,       load_date dwh_valid_from  -- we use load_date as the new validity start date\r\n,       first_name\r\n,       second_names\r\n,       last_name\r\n,       hire_date\r\n,       fire_date\r\n,       salary\r\nfrom    t_source\r\n)\r\nselect un.*\r\n--     we need NVL for the records which were deleted in source in case we must \"close\" them. The constant means for example load date and can be returned from PL\/SQL function \r\n,      nvl(lead (dwh_valid_from ) over(partition by business_key order by dwh_valid_from) - 1,  DATE '2016-12-02' ) new_dwh_valid_to    \r\n,      count(*) over (partition by business_key, first_name, second_names, last_name, hire_date, fire_date, salary)  cnt --  cnt = 2 - two versions are the same, otherwise INS\/UPD\/DEL\r\n,      count(*) over (partition by business_key) cnt_key  -- the count of versions per business_key\r\nfrom   union_source_target un;\r\n\r\n<\/pre>\n
     <\/p>\n
    Schritt 3<\/strong><\/p>\n
    Wie k\u00f6nnen wir die im Schritt 2 gewonnenen Informationen weiter nutzen? Wenn die Anzahl gleich zwei ist, haben wir zwei gleiche Datens\u00e4tze und m\u00fcssen nichts machen. Ansonsten m\u00fcssen wir Insert, Update or Delete verarbeiten. Wir k\u00f6nnen nun die erwartetetn Aktionen mit Hilfe folgender Abfrage anzeigen, nur damit man sieht, was passieren wird.<\/p>\n
    \r\n-- Filter only the records where some action is needed and show what kind of action \r\n\r\nwith union_source_target as \r\n(\r\n-- What are the current versions in the target table\r\nselect  'T' source_target  -- to distinguish these records later\r\n,       business_key\r\n,       dwh_valid_from\r\n,       first_name\r\n,       second_names\r\n,       last_name\r\n,       hire_date\r\n,       fire_date\r\n,       salary\r\nfrom    t_target\r\nwhere   current_version = 'Y'\r\n-- Now the new data\r\n-- put them together with UNION ALL\r\nunion all\r\nselect  'S' source_target\r\n,       business_key\r\n,       load_date dwh_valid_from  -- we use load_date as the new validity start date\r\n,       first_name\r\n,       second_names\r\n,       last_name\r\n,       hire_date\r\n,       fire_date\r\n,       salary\r\nfrom    t_source\r\n)\r\n, action_needed as\r\n(\r\nselect un.*\r\n--     we need NVL for the records which were deleted in source in case we must "close" them. The constant means for example load date and can be returned from PL\/SQL function \r\n,      nvl(lead (dwh_valid_from ) over(partition by business_key order by dwh_valid_from) - 1,  DATE '2016-12-02' ) new_dwh_valid_to    \r\n,      count(*) over (partition by business_key, first_name, second_names, last_name, hire_date, fire_date, salary)  cnt --  cnt = 2 - two versions are the same, otherwise INS\/UPD\/DEL\r\n,      count(*) over (partition by business_key) cnt_key  -- the count of versions per business_key (in this result set only, not in the target table)  \r\nfrom   union_source_target un\r\n)\r\nselect an.*\r\n,      case \r\n          when source_target = 'T' and cnt_key > 1 then 'Close this version (new version will be added)' \r\n          when source_target = 'T' and cnt_key = 1 then 'Close this version (no new version will be added, the record was deleted in source)'\r\n          when source_target = 'S' then 'Insert new version'                    \r\n       end action\r\nfrom   action_needed an\r\nwhere  cnt != 2 \r\n\r\nS BUS DWH_VALID_ FIRST_NAME SECOND_NAM LAST_NAME  HIRE_DATE  FIRE_DATE      SALARY NEW_DWH_VALID_TO CNT CNT_KEY ACTION                                            \r\n- --- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --- ------- --------------------------------------------------\r\nS 345 2016-12-02 Venus                 Williams   2016-11-01                500000 2016-12-02         1       1 Insert new version                                \r\nT 789 2016-12-01 Serena                Williams   2008-06-01                650000 2016-12-01         1       2 Close this version (new version will be added)    \r\nS 789 2016-12-02 Serena     Jameka     Williams   2008-06-01                650000 2016-12-02         1       2 Insert new version                                \r\n\r\n3 rows selected.\r\n\r\n<\/pre>\n
     
    \nSchritt 4<\/strong><\/p>\n
    Alle Aktionen sind wie erwartet und wir k\u00f6nnen dieses Zwischenergebnis mit einem MERGE-Befehl mit der Zieltabelle zusammenf\u00fchren:<\/p>\n
    \r\nmerge into  t_target t\r\nusing (  \r\nwith union_source_target as \r\n(\r\n-- What are the current versions in the target table\r\nselect  'T' source_target  -- to distinguish these records later\r\n,       business_key\r\n,       dwh_valid_from\r\n,       first_name\r\n,       second_names\r\n,       last_name\r\n,       hire_date\r\n,       fire_date\r\n,       salary\r\nfrom    t_target\r\nwhere   current_version = 'Y'\r\n-- Now the new data\r\n-- put them together with UNION ALL\r\nunion all\r\nselect  'S' source_target\r\n,       business_key\r\n,       load_date dwh_valid_from  -- we use load_date as the new validity start date\r\n,       first_name\r\n,       second_names\r\n,       last_name\r\n,       hire_date\r\n,       fire_date\r\n,       salary\r\nfrom    t_source\r\n)\r\n, action_needed as\r\n(\r\nselect un.*\r\n--     we need NVL for the records which were deleted in source in case we must "close" them. The constant means for example load date and can be returned from PL\/SQL function \r\n,      nvl(lead (dwh_valid_from ) over(partition by business_key order by dwh_valid_from) - 1,  DATE '2016-12-02' ) new_dwh_valid_to    \r\n,      count(*) over (partition by business_key, first_name, second_names, last_name, hire_date, fire_date, salary)  cnt --  cnt = 2 - two versions are the same, otherwise INS\/UPD\/DEL\r\n,      count(*) over (partition by business_key) cnt_key  -- the count of versions per business_key (in this result set only, not in the target table)  \r\nfrom   union_source_target un\r\n)\r\nselect an.*\r\n,      case \r\n          when source_target = 'T' and cnt_key > 1 then 'Close this version (new version will be added)' \r\n          when source_target = 'T' and cnt_key = 1 then 'Close this version (no new version will be added, the record was deleted in source)'\r\n          when source_target = 'S' then 'Insert new version'                    \r\n       end action\r\nfrom   action_needed an\r\nwhere  cnt != 2 \r\n)  q\r\non ( t.business_key  = q.business_key and t.dwh_valid_from = q.dwh_valid_from  )\r\nwhen not matched then insert (    dwh_key\r\n                                , dwh_valid_from\r\n                                , dwh_valid_to\r\n                                , current_version\r\n                                , etl_op\r\n                                , business_key\r\n                                , first_name\r\n                                , second_names\r\n                                , last_name\r\n                                , hire_date\r\n                                , fire_date\r\n                                , salary )\r\n    values (  scd2_seq.nextval\r\n            , q.dwh_valid_from \r\n            , NULL  -- NULL means no end date \r\n            , 'Y'\r\n            , 'INS'\r\n            , q.business_key\r\n            , q.first_name\r\n            , q.second_names\r\n            , q.last_name\r\n            , q.hire_date\r\n            , q.fire_date\r\n            , q.salary )\r\nwhen matched then update set \r\n             t.dwh_valid_to = q.new_dwh_valid_to\r\n           , t.current_version = 'N'\r\n           , t.etl_op = 'UPD';\r\n\r\n<\/pre>\n
     
    \nFertig! Was ist in der Zieltabelle nach dem MERGE? Genau das, was wir erwarten: eine neue Version f\u00fcr Venus Williams und auch f\u00fcr Serena Williams. Beide sind ab dem 2016-12-02, unserem Ladedatum, g\u00fcltig. Die alte Version f\u00fcr Serena Williams wurde geschlossen mit „g\u00fcltig bis“ Datum von 2016-12-01.<\/p>\n
    \r\n\r\nselect   * \r\nfrom     t_target\r\norder by business_key, dwh_valid_from;\r\n\r\n   DWH_KEY DWH_VALID_ DWH_VALID_ C ETL_OP BUS FIRST_NAME SECOND_NAM LAST_NAME  HIRE_DATE  FIRE_DATE      SALARY\r\n---------- ---------- ---------- - ------ --- ---------- ---------- ---------- ---------- ---------- ----------\r\n         1 2016-12-01            Y INS    123 Roger                 Federer    2010-01-01                900000\r\n         6 2016-12-02            Y INS    345 Venus                 Williams   2016-11-01                500000\r\n         2 2016-12-01            Y INS    456 Rafael                Nadal      2009-05-01                720000\r\n         3 2016-12-01 2016-12-01 N UPD    789 Serena                Williams   2008-06-01                650000\r\n         5 2016-12-02            Y INS    789 Serena     Jameka     Williams   2008-06-01                650000\r\n\r\n5 rows selected.\r\n<\/pre>\n
     <\/p>\n
    Noch ein Test, das n\u00e4chste Ladedatum ist 2016-12-03. We \u00e4ndern die numerische Spalte SALARY, Datumsspalte FIRE_DATE und l\u00f6schen den Datensatz von Rafael Nadal aus der Quelle:<\/p>\n
    \r\n\r\ndelete from t_source;\r\n\r\n-- Salary changed\r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-03', '123', 'Roger', null, 'Federer', DATE '2010-01-01', null, 920000);\r\n            \r\n-- Business_key = 456 is not there anymore\r\n   \r\n-- No change \r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-03', '789', 'Serena', 'Jameka', 'Williams', DATE '2008-06-01', null, 650000);     \r\n-- Change in fire_date\r\ninsert into t_source (load_date, business_key, first_name, second_names, last_name, hire_date, fire_date, salary)\r\n               values(DATE '2016-12-03', '345', 'Venus', null, 'Williams', DATE '2016-11-01', DATE '2016-12-01', 500000);     \r\n\r\ncommit;  \r\n\r\nMERGE\r\n... \r\n\r\nselect   * \r\nfrom     t_target\r\norder by business_key, dwh_valid_from;\r\n\r\n   DWH_KEY DWH_VALID_ DWH_VALID_ C ETL_OP BUS FIRST_NAME SECOND_NAM LAST_NAME  HIRE_DATE  FIRE_DATE      SALARY\r\n---------- ---------- ---------- - ------ --- ---------- ---------- ---------- ---------- ---------- ----------\r\n         1 2016-12-01 2016-12-02 N UPD    123 Roger                 Federer    2010-01-01                900000\r\n        11 2016-12-03            Y INS    123 Roger                 Federer    2010-01-01                920000\r\n         6 2016-12-02 2016-12-02 N UPD    345 Venus                 Williams   2016-11-01                500000\r\n        10 2016-12-03            Y INS    345 Venus                 Williams   2016-11-01 2016-12-01     500000\r\n         2 2016-12-01 2016-12-02 N UPD    456 Rafael                Nadal      2009-05-01                720000\r\n         3 2016-12-01 2016-12-01 N UPD    789 Serena                Williams   2008-06-01                650000\r\n         5 2016-12-02            Y INS    789 Serena     Jameka     Williams   2008-06-01                650000\r\n\r\n7 rows selected.\r\n\r\n<\/pre>\n
     
    \nWie man sieht, hat Roger Federer eine Gehaltserh\u00f6hung sowie eine neue Version bekommen, Venus Willaims wurde gefeuert und hat auch nun eine neue Version. Der Datensatz f\u00fcr Rafael Nadal wurde in der Quelle gel\u00f6scht. Das haben wir erkannt und in der Zieltabelle entsprechend das „g\u00fcltig bis“ Datum gesetzt.<\/p>\n
    Man kann nat\u00fcrlich dar\u00fcber diskutieren, was die richtige Strategie im Umgang mit zeitlicher G\u00fcltigkeit ist. Wie definiere ich Unendlichkeit, mit Null-Werten, wie im Beispiel, oder mit speziellen Werten wie 31. Dezember 9999? Macht es Sinn, die aktuellen Versionen mit einem Kennzeichen zu versehen, z.B. current_version (Yes\/No)? Ist es besser, wenn G\u00fcltig_bis Datum und G\u00fcltig_von vom der darauffolgender Version gleich sind oder wenn dazwischen ein Tag liegt? Was ist die beste Strategie im Umgang mit gel\u00f6schten Datens\u00e4tzen? Nat\u00fcrlich sind diese Fragen alle berechtigt, sie stehen aber hier nicht im Mittelpunkt. Mit diesem Ansatz haben wir ausreichend Informationen, um beliebige Strategie implementieren zu k\u00f6nnen.<\/p>\n
    Fazit <\/strong>
    \nDas ganze sieht ziemlich einfach aus, macht aber was wir brauchen! Wie  vermeiden den (Outer) Join. Wir vermeiden die komplexe Deltaerkennung mit NVL, Hash-Werten o.\u00e4. Die Quelle wird nur einmal gescannt, was insbesondere dann wichtig ist, wenn als Quelle komplexe Views agieren. Noch besser, weil wir anstelle des Joins einen UNION ALL haben – in 12c werden beide Zweige eines UNION Operators nicht nacheinander, sondern gleichzeitig ausgef\u00fchrt. Das alles klingt f\u00fcr mich vielversprechend. <\/p>\n
    Ich bleibe dran und forsche weiter, vergleiche die Perormance, wie l\u00e4uft die Abfrage in parallel usw. Werde die Ergebnisse hier pr\u00e4sentieren. Ist es m\u00f6glich, das Statement so anzupassen, dass auch die SCD1-Spalten direkt mitber\u00fccksichtigt werden k\u00f6nnen? Eine andere intersessante M\u00f6glichkeit, die ich bereits getestet habe und die auch auf derselben Grundidee basiert, w\u00e4re ein Multi-Table Insert statt MERGE mit anschliessendem Partition Exchange. Ich beschreibe das in einem der n\u00e4chsten Beitr\u00e4ge.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Die Historisierung der Daten ist eine typische aber auch rechen- und zeitintensive Aufgabe im Data Warehouse Umfeld. Man hat damit beim Beladen von historisierter Core-Schicht (auch bekannt als Enterprise Data Warehouse or Foundation Layer), Data Vault Datenmodellen, Slowly Changing Dimensions, etc. zu tun. Typische Methoden f\u00fchren einen Outer Join und eine Art der Deltaerkennung aus. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,39,3,4,22],"tags":[44,43,40,41,42],"class_list":["post-283","post","type-post","status-publish","format-standard","hentry","category-allgemein","category-data-warehouse","category-oracle","category-sql","category-trivadis","tag-deltaerkennung","tag-historisierung","tag-owb","tag-scd2","tag-slowly-changing-dimensions"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/283","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/comments?post=283"}],"version-history":[{"count":12,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/283\/revisions"}],"predecessor-version":[{"id":441,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/283\/revisions\/441"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/media?parent=283"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/categories?post=283"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/tags?post=283"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}