Tag: SCD Type 1

ODI 12C – Slowly Changing Dimensions

In this blog we will explore how we can setup Slowly Changing Dimension SCD2 in ODI 12c.

Slowly Changing Dimensions: Attributes that changes slowly over the period of time, rather than changing frequent. In order to generate the historical reports in DW we need to track the history of dimension.

Type 1: When there is no need to store historical data in the dimension table. So process overwrites the old data with the new data in the dimension table. This is normal incremental update.

Type 2: When there is need to stores the entire history of the attribute in the dimension table in the form of separate rows. Using dates & current flag you can identify which records is currently active. See below example.

scd2

Type 3: When there is need to store current as well as previous value of the attribute. In this scenario two separate columns required in table to store current as well as previous value. See below example.

scd3

Import the IKM: Oracle Slowly Changing Dimension

IKM

Step 1: Create the tables & reverse engineered the same in your model. I have created below tables in localhost database.

 

  CREATE TABLE "SRC_EMP_ASSIGNMENT"
   (	EMP_ID VARCHAR2(10 CHAR),
	ASSIGNMENT_ID VARCHAR2(150 CHAR)
   );
  CREATE TABLE "TRG_EMP_ASSIGNMENT"
   (  ROW_WID NUMBER,
      EMP_ID VARCHAR2(10 CHAR),
      ASSIGNMENT_ID VARCHAR2(150 CHAR),
      ASSIGNMENT_TYPE VARCHAR2(5 CHAR),
      CURRENT_FLAG CHAR(1 CHAR),
      EFF_START_DATE DATE,
      EFF_END_DATE DATE
   );
 CREATE SEQUENCE SEQ_TRG_EMP_ASSIGNMENT START WITH 1;

 

Step 2: Open the target data-store and change the OLAP type to “Slowly Changing Dimension“.

1

Step 3: Now you have to set the SCD behavior of columns for target SCD2 data-store.

  • Surrogate Key – Unique key for table which does not have any business meaning.
  • Natural Key – Unique Key from the source data.
  • Current Record Flag – Indicate whether the record is active or old.
  • Starting Timestamp – Indicate the starting date of that entry
  • Ending Timestamp – Indicate the ending date of that entry
  • Overwrite on Change – If you don’t want to enable on SCD2 on any column.
  • Add Row on Change – If you want to enable on SCD2 on any column.

In this example, for target table : TRG_EMP_ASSIGNMENT

  • Surrogate Key – ROW_WID
  • Natural Key – EMP_ID
  • Current Record Flag – CURRENT_FLAG
  • Starting Timestamp – EFF_START_DATE DATE
  • Ending Timestamp – EFF_END_DATE DATE
  • Overwrite on Change – ASSIGNMENT_TYPE (Don’t want to track history for this)
  • Add Row on Change – ASSIGNMENT_ID

Open the data-store, attributes & select the SCD behavior for every column.

8

Step 4: Create Mapping: See below execute on hints for columns.

mappig

Executing Mapping 1st Time: Initial Run

Source Date:

src

Target: See the end dates of all the records, default 01-01-2400 EFF_END_DATE.

trg

Executing Mapping 1st Time: Incremental Run

Source Data:

src2

Target: 

trg2

In above screenshot you can notice that previous assignment for EMP_ID 100 has been updated with CURRENT_FLAG=0 and EFF_END_DATE updated. New assignment for EMP_ID 100 has been created with CURRENT_FLAG=1 and default 01-01-2400 EFF_END_DATE.

odiopr

Thanks!

Happy Learning! Your feedback would be appreciated!