In this tutorial we will learn how to implement the concept of Slowly Changing Dimensions (SCD) using Oracle Data Integrator. As the name suggests, SCD in data warehousing refers to the slow change
in data rather than the change on a regular basis. It is applicable in
those cases where the attribute for a record varies over time.
There are three basic types of SCD’s:
1.Type 1 – Overwrite old values
In this case, new record replaces the original record. No copy of the original record exists.
2.Type 2 – Create a new record
In this case, a new record is added to the dimension table.
3.Type 3 – Create new fields
In this case, the latest update to the changed values can be seen. The original record is modified to reflect the change. So now let’s begin implementing SCD using Oracle Data Integrator. Open ODI Studio and follow the below steps!
Pre-requisites: Oracle 10g Express Edition with *SQL Plus, Oracle Data Integrator 11g (version 11.1.1.7)
Step 1: Create source, target tables for SCD using *SQL Plus
Source table
create table scd_test(EmpId int, EmpName varchar2(30), DeptName varchar2(30), salary number(6,2)); |
Insert few rows of dummy data inside the source table.
insert into scd_test values(101,'Karan','Computer',2200.23);insert into scd_test values(102,'Mahesh','Computer',3200.53);insert into scd_test values(103,'Prasad','Mechanical',5300.13);
Target table
|
create table scd_target(EmpId int, EmpName varchar2(30), DeptName varchar2(30), salary number(6,2), joining_date date, flag number); |
Step 2: Create models for source and target tables
Under Designer tab, create a new Model folder and then right click it, select Models–>Create new model. Reverse engineer both the source and the target tables under the same model. Provide any existing Oracle logical schema while re-factoring.
Step 3: Modify target table model and it’s columns for SCD
Open the target table datastore and change OLAP type to slowly changing dimension.
Then expand your target datastore to get all columns. Open required columns one by one and do the changes as follows!
Step 4: Create new interface
While creating new interface select IKM as Oracle Slowly Changing Dimension. Map the source and the target tables. For columns joining_date and flag specify the implementation as shown below!
Creating interface
Run the interface without any errors. View the target table data.
Running the interface for the firsttime
Now, update few records of the source table. In this
case, I will update name and salary of employee. Hence as per SCD Types 1
and 3, first a new record will be inserted since we had selected Add row on change for Employee name and then salary will be overwritten as we had selected Overwrite on change for Employee salary.
update scd_test set empname='nitesh' where empid=101;update scd_test set salary=5634.43 where empid=102; |
Run the interface again and then view the target table data. It now reflects SCD.
Output data
That completes this tutorial. Keep visiting for more!
