IICS:SCD Type2 using MD5 Hashing

#iics #scdt2 #etlguru
IICS:SCD Type2 using MD5 Hashing

Slowly Changing Dimensions enable the historic aspect of data in an analytical system.
It is used in Datawarehouse
There are 6 types of Slowly Changing Dimension that are commonly used.
In reality, only types 0, 1 and 2 are widely used, and others are reserved for very specific requirements.
A Type 2 SCD retains the full history of values. When the value of a chosen attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective date and expiration date to identify the period between which the record was active.

SRC
E_ID,E_NAME,E_SALARY,E_DEPT_ID
100,Naganth,60000,10
101,Vinod,80000,10
102,Antosh,50000,11
103,Krishna,70000,12
104,Mahadev,80000,13
105,Mahesh,40000,15

TGT
CREATE TABLE EMPLOYEES_SCDT2(
emp_key number(6),
emp_id number(6),
emp_name varchar2(20),
emp_salary number(8,2),
emp_dept_id number(4),
start_date date,
end_date date,
checksum varchar2(50));


LKPQuery
SELECT emp_key as LKP_EMP_KEY,
emp_id as LKP_EMP_ID,
checksum AS LKP_CHECKSUM
FROM EMPLOYEES_SCDT2

Expression
o_checksum=MD5(E_ID||E_NAME||E_SALARY||E_DEPT_ID)
o_startdate=SYSDATE
o_enddate = to_date('9999-12-31','YYYY-MM-DD')
o_up_enddate=SYSDATE

RTR
INSERT = ISNULL(LKP_EMP_KEY)
UPSERT = o_checksum!=LKP_CHECKSUM