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

Slowly Changing Dimension 2(SCD2) -Version Control

What is a Slowly Changing Dimension?

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. Slowly changing dimensions are classified into three different types
1. TYPE I
2. TYPE II
1. TYPE 11 -Version Control
2. TYPE 11- Flag Control
3. TYPE 11 - Date Control
3. TYPE III

Slowly Changing Dimension 2(SCD2) - Version 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_VERSION   ( 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), VERSION NUMBER(5)   )

2. Import table using Target Designer

Step 3: Create mapping with transformations

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

3. Introduce  Lookup transformation and select lookup table as EMP_SCD2_VERSION
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,VERSION from Lookup to expression and rename job,sal,version as job_previous ,sal_previous,version_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 EMPNO,ENAME,JOB,SAl,DEPTNO,INSERT_UPDATE_NOCHANGE_FLAG  and VERSION_previous from expression to router

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

14. Introduce Expression transformation→ from INSERT section of router drag and drop ports EMPNO1,ENAME1,JOB1,SAL1,DEPTNO1,VERSION_previous1 and INSERT_UPDATE_NOCHANGE_FLAG1
15. Create two new ports EMPKEY ,VERSION
16. Introduce Sequence generator transformation and connect NEXTVAL with EMPKEY
17. develop below expression in version 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. 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 8000 to 10000

6. Run Workflow again

Step 6: Download workflow XML Code and observe it

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