Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.
The Type 3 method tracks changes using separate columns. Whereas Type 2 had unlimited history preservation, Type 3 has limited history preservation, as its limited to the number of columns we designate for storing historical data.
Following are the steps:
1. Create following tables in database:
CREATE TABLE SCOTT.EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
CREATE TABLE SCOTT.EMP_TYPE3
(
EMP_KEY NUMBER(4),
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
SAL NUMBER(7,2),
ENAME_OLD VARCHAR2(10 BYTE)
)
2. Open SPOON à Fileà New à Transformation
3. Insert Table Input Step
4. Right clickàEdit Step. Set the following properties:
5.Insert DataBase Lookup step
6. Set the following properties:
7. Create a Hop between Table Input step and Database Lookup Step
8. Add a Sequence Step. Set the following properties.
Note: A sequence must be created in database.
9. Add Filter Rows. Set the following properties:
10. Insert Select Value Step and set the following properties:
11.Add Filter Row 2 Step. Set the following properties:
12. Add a Dummy(do nothing) Step.
13. Add Execute SQL Script Step to write a Query.
Set the following properties:
14. Insert a Update Step. Set the following properties:
15. Create appropriate Hop between steps. The overall structure will look like following:
16. Save and execute.
