Slowly Changing Dimension 2(SCD2) - Flag Control

Slowly Changing Dimension 2(SCD2) - Flag Control

Process :

Step 1: Create Source Table & Data

1. We can use Scott Schema EMP table
2. Import table using source analyzer

Step 2: Create Target Table

1. Create target table in target database

 

CREATE TABLE EMP_SCD2_FLAG   ( EMPKEY NUMBER(5,0) PRIMARY KEY, EMPNO NUMBER(5,0), ENAME VARCHAR2(20 BYTE), JOB VARCHAR2(20 BYTE), SAL NUMBER(10,2), DEPTNO NUMBER(5,0), FLAG VARCHAR2(1)   )

2. Import table using Target Designer

Step 3: Create mapping with transformations

1. Create a mapping with the name m_SCD2_FLAG
2. Drag and drop source and target into work area

3. Introduce  Lookup transformation and select lookup table as EMP_SCD2_FLAG
4. Drag and drop EMPNO from Source Qualifier to Lookup and rename it as EMPNO_in
5. Click on condition tab of lookup provide join condition as shown below

6. Click on properties tab and select multiple match property as last value

7. Introduce Expression transformation into work area and name it as exp_INSERT_UPDATE_NOCHANGE
8. Drag and drop ports EMPNO,ENAME,JOB,SAL,DEPTNO from source qualifier to expression
9. Drag and drop ports EMPKEY,JOB,SAL  from Lookup to expression and rename job,sal as job_previous ,sal_previous respectively
10. Add one new port in expression and name it as INSERT_UPDATE_NOCHANGE_FLAG
11. Develop expression using below formula

IIF( ISNULL(EMPKEY),'I', IIF( (JOB != JOB_previous) OR       (SAL != SAL_previous),'U','NC' ) )

12. Introduce ROUTER transformation → Drag and drop EMPKEY,EMPNO,ENAME,JOB,SAL,DEPTNO and INSERT_UPDATE_NOCHANGE_FLAG from expression to router

13. Double click on ROUTER header → click on groups tab → add two groups as shown

14. Introduce Expression transformation→ from INSERT section of router drag and drop ports EMPNO1,ENAME1,JOB1,SAL1,DEPTNO1and INSERT_UPDATE_NOCHANGE_FLAG1
15. Create two new ports EMPKEY1 ,FLAG
16. Introduce Sequence generator transformation and connect NEXTVAL with EMPKEY1
17. Provide constant value ‘Y’ in FLAG port
18. Overall it looks like below

19. Introduce update strategy transformation and connect as shown

20. Connect ports from update strategy transformation to target as shown

21. Introduce Expression and update strategy transformations
22. Duplicate target table and connect all as shown

23. Double click on update strategy transformation → provide Update Strategy Expression as 1

24. Save it .

Step 4: Create Workflow and Session

1. Create workflow and session as shown below

2. Double click on session task provide source and target connection accordingly

3. Save the workflow

Step 5: Run workflow and Observe output

1. Observe source data in my case

2. Select Start task → click on run
3. observe target data

4. Observe target table output

5. Update any one existing record in my case iam updating RAM REDDY sal from 10000 to 12000

6. Run Workflow again

Step 6: Download workflow XML Code and observe it

https://drive.google.com/file/d/0B6mKqi53iP-NOWdtaDFvRW1zMEU/edit?usp=sharing