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)

Check Knowledge Modules (CKM)

1.1          Check Knowledge Modules (CKM)

1. Data Quality control is essential in ensuring the overall consistency of the data in your information System's applications.
2. Application data is not always valid for the constraints and declarative rules imposed by the information system. You may, for instance, find orders with no customer, or order lines with no product, etc. In addition, such incorrect data may propagate via integration flows
3. The CKM can be used in 2 ways:
1. Check the consistency of existing data.This can be done on any data store or within interfaces, by setting the STATIC_CONTROL option to "Yes".
2. Check consistency of the incoming data before loading the records to a target data store. This is done by using the FLOW_CONTROL option. In this case, the CKM simulates the constraints of the target data store on the resulting flow prior to writing to the target.
4. In summary: the CKM can check either an existing table or the temporary "I$" table created by an IKM.
5. The CKM accepts a set of constraints and the name of the table to check. It creates an "E$" error table which it writes all the rejected records to. The CKM can also remove the erroneous records from the checked result set.
6. The following figures show how a CKM operates in both STATIC_CONTROL and FLOW_CONTROL modes.

Check Knowledge Module (STATIC_CONTROL)

In STATIC_CONTROL mode, the CKM reads the constraints of the table and checks them against the data of the table. Records that don't match the constraints are written to the "E$" error table in the staging area.

Check Knowledge Module (FLOW_CONTROL)

In FLOW_CONTROL mode, the CKM reads the constraints of the target table of the Interface. It checks these constraints against the data contained in the "I$" flow table of the staging area. Records that violate these constraints are written to the "E$" table of the staging area.

In both cases, a CKM usually performs the following tasks:

1. Create the "E$" error table on the staging area. The error table should contain the same columns as the data store as well as additional columns to trace error messages, check origin, check date  etc.
2. Isolate the erroneous records in the "E$" table for each primary key, alternate key, foreign key, condition, mandatory column that needs to be checked.
3. If required, remove erroneous records from the table that has been checked.

Exercise 1: Static Control on Source table

1. CKM is model level property  hence we need to import ckm and need to assign to model

STEP 1: Importing CKM KM

1. Open project right click on Check(CKM) → Click on Import Knowledge Module

2. Select CKM as CKM ORACLE → Click on ok

STEP 2: Assigning  CKM KM to Model

1. Right click on source model → Click on  open → select control tab → select KM as CKM oracle

2. save → close

STEP 3: Checking COMM column for not null values

1. Create a table using below command
1. create table B107EMP as select * from emp
2. Reverse engineer this table into source model
3. expand the table → expand attributes open comm column select mandatory and static options

4. save → close
5. right click on this table → click on control → click on check select context properly → click on ok → again ok
6. go to database notice that two tables created
1. SNP_CHECK_TAB
2. E$_B107EMP
7. Observe that all comm null records are copied into E$B107EMP table

Exercise 2: Static Control using package on data store

1. Create a package as CKM_STATIC_CONTROL
2. drag and drop B107EMP datastore into package
3. select B107EMP datastore and select type data store check
4. check delete errors from the checked table

5. save → run
6. observe output

Exercise 3: Flow Control Understanding

Step 1: Prepare source data

1. open sql developer → Connect to source connection → double click on table B107EMP name → go to the data tab

2. Insert records as shown → COMMIT

INSERT INTO "SCOTT"."B108EMP" (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('5555', 'RRITEC', 'Training', '7698', TO_DATE('08-SEP-81', 'DD-MON-RR'), '1500', '100', '20'); INSERT INTO "SCOTT"."B108EMP" (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('5555', 'RRITEC', 'Training', '7698', TO_DATE('08-SEP-81', 'DD-MON-RR'), '1500', '100', '20'); INSERT INTO "SCOTT"."B108EMP" (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('12', 'John', 'Manager', '7782', TO_DATE('08-SEP-81', 'DD-MON-RR'), '1500', '100', '20'); INSERT INTO "SCOTT"."B108EMP" (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES ('6666', 'Nancy', 'Manager', '7782', TO_DATE('08-SEP-81', 'DD-MON-RR'), '1500', '100', '60');

Step 2: CREATE TARGET TABLE

1. open target schema B107TDBU execute below script

create table TGT_B107EMP as select * from scott.emp where 1=2

3. Reverse engineer into target model

Step 3: Creating Constraints

1. open the target model navigate to target table TGT_B107EMP
2. right click on constraints → click on newkey

3. In description name it as PK_EMPNO and select primary key option

4. Click on attributes tab → select empno → click on >

5. save → close
6. Right click on constraints → Click on New Reference

7. In definition tab provide below information

8. In columns tab select DEPTNO as shown below

9. Save → close
10. Again right click on constraints → select New Condition

11. Provide below information
12. 
13. Save→ close

Step 4: Creating Mapping

1. Create  a mapping with the name of m_CKM_FLOW_CONTROL
2. drag and drop source B107EMP and target TGT_B107EMP
3. map corresponding columns

4. In flow tab select IKM and mark FLOW_CONTROL as TRUE

5. Select Control tab and observe conditions

6. Click on RUN → observe output .