Slowly Changing Dimension (SCD2) IN ODI 11G

Slowly Changing Dimension (SCD2)

1. Although dimension tables are typically static lists, most dimension tables do change over time.
2. Since these changes are smaller in magnitude compared to changes in fact tables, these dimensions are known as slowly growing or slowly changing dimensions.  
3. If we load first Time

4. Second Time load

5. Third time load

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SCOTT schema as source
2. Reverse engineer EMP table into SOURCE  MODEL

STEP 2 : Creating and Reverse Engineering Target

1. Create target table in Target Schema using below script

CREATE TABLE TGT_SCD2_EMP   ( SURR_EMPNO NUMBER PRIMARY KEY, EMPNO NUMBER(4,0), ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), SAL NUMBER(7,2), DEPTNO NUMBER(2,0), CURR_FLG NUMBER, EFF_FROM_DT DATE, EFF_TO_DT DATE   )

3. Reverse engineer into TARGET MODEL

STEP 3 : Configuration for SCD

1. Right Click on table TGT_SCD2_EMP2 → Click on OPEN → Select OLAP Type as Slowly Changing Dimension → Save → Close

2. Expand table → Expand columns → right click on one by one column and set as shown below

3. Save and close one by one

STEP 4 : Creating Interface/Mapping

1. Create interface with the name of m_SCD2
2. Click on mapping tab → Drag and drop source (EMP)and target (TGT_SCD2_EMP2)in respective locations
3. Map columns as shown

S NO	Target Column Name	Expression	Comments
1	SURR_EMPNO	Map to existing sequence :NAT_SEQ_SCD_NEXTVAL	Create new sequence or map to existing sequence
2	EMPNO	EMP.EMPNO
3	ENAME	EMP.ENAME
4	JOB	EMP.JOB
5	SAL	EMP.SAL
6	DEPTNO	EMP.DEPTNO
7	CURR_FLG	0
8	EFF_FROM_DT	SYSDATE
9	EFF_TO_DT	SYSDATE

4. Click on Flow tab
5. Select Target and select IKM Oracle Slowly Changing Dimension
6. Select FLOW_CONTROL = False

7. Click on SAVE
8. Click on RUN
9. Change source data sal and run again
10. Again change source data sal run (in my case i changed Ram Reddy sal from 10000 to 30000 and 30000 to 35000)