{"id":301,"date":"2016-11-24T12:14:47","date_gmt":"2016-11-24T10:14:47","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=301"},"modified":"2018-04-08T23:00:17","modified_gmt":"2018-04-08T21:00:17","slug":"how-to-simplify-the-data-historization","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/how-to-simplify-the-data-historization\/","title":{"rendered":"How to simplify the data historization?"},"content":{"rendered":"

Maintaining a data historization is a very common but time consuming task in a data warehouse environment. You face it while loading historized Core-Layer (also known as Enterprise Data Warehouse or Foundation Layer), Data Vault Models, Slowly Changing Dimensions, etc. The common techniques used involve outer joins and some kind of change detection. This change detection must be done with respect of Null-values and is possibly the most trickiest part. A very good overview by Dani Schnider can be found in his blog: Delta Detection in Oracle SQL<\/a><\/p>\n

But, on the other hand, SQL offers standard functionality with exactly desired behaviour: Group By or Partitioning with analytic functions. Can it be used for this task? Does it make sense? And how would the ETL process look like? Can we further speed up the task using partition exchange and when does it make sense? I’ll look at this in the next few posts. <\/p>\n

Introduction and common approach<\/strong><\/p>\n

First of all, I suppose you are familiar with the historization and temporal validity concepts to some extent. We consider so called Slowly Changing Dimensions Type 2. But this historization schema does not apply only for dimensions.<\/p>\n

Consider following example. We have a source table which must be loaded into the target table on a daily basis. The target table has validity date columns and allows multiple versions for one business key. If we detect changes in the data for some business key, we close (set the valid_to date) the current version and open a new one with the new data. No meaningful data will be replaced, all changes will be completely trackable over time.<\/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
 
\nThe most common approach I have seen involves outer joining the source table with the current versions in the target table based on the business key. If we have the business key only on one side of the join, we are dealing with the new or deleted records. If the key is\u00a0 on both sides, we\u00a0 must check for data changes. To be able to close the old versions and insert the new ones in one MERGE Statement we use some split technique\u00a0 and then combine the old and new records with a UNION ALL.\u00a0 Again, the whole approach is very well described here: Delta Detection in Oracle SQL<\/a><\/p>\n
The typical pattren of the ETL process looks in Oracle Warehouse Builder something like this:<\/p>\n
<\/A><\/p>\n
\"SCD2\"<\/a><\/p>\n
Pretty much work. The drawback of the Oracle Warehouse Builder implementation is that the source and target tables will be scanned an joined twice. So how can we do it better?<\/p>\n
Another approach<\/strong><\/p>\n
Imagine, next day we’ve got the new data in the source:<\/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
 
\nFor Business Key 789 (Serena Williams) we get the second name instead of an empty (NULL) value. The Business Key 345 (Venus Williams) is new.
\nNow we have following content in our tables:<\/p>\n
\r\nselect * from t_source;\r\n\r\nLOAD_DATE\u00a0\u00a0BUS\u00a0FIRST_NAME\u00a0SECOND_NAM\u00a0LAST_NAME\u00a0\u00a0HIRE_DATE\u00a0\u00a0FIRE_DATE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SALARY\r\n----------\u00a0---\u00a0----------\u00a0----------\u00a0----------\u00a0----------\u00a0----------\u00a0----------\r\n2016-12-02\u00a0123\u00a0Roger\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Federer\u00a0\u00a0\u00a0\u00a02010-01-01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0900000\r\n2016-12-02\u00a0456\u00a0Rafael\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Nadal\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02009-05-01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0720000\r\n2016-12-02\u00a0789\u00a0Serena\u00a0\u00a0\u00a0\u00a0\u00a0Jameka\u00a0\u00a0\u00a0\u00a0\u00a0Williams\u00a0\u00a0\u00a02008-06-01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0650000\r\n2016-12-02\u00a0345\u00a0Venus\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Williams\u00a0\u00a0\u00a02016-11-01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0500000\r\n\r\n4\u00a0rows\u00a0selected.\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
 
\nStep 1<\/strong><\/p>\n
We must compare the source data with the current versions in the target table. It might be not so straightforward, but let combine them with a UNION operator at first step:<\/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
 
\nWe add the column SOURCE_TARGET which can be ‘T’ or ‘S’ to distinguish between the records coming from target and source tables.<\/p>\n
Note that we can have at most two records per business key in this result set. Possible cases are:<\/p>\n