Tutorial corner

Informatica,ETL,oracle,sql/plsql

slowly changing dimension type 3 with example | SCD3

Spread the love

As we saw in last tutorial , Slowly Changing Dimension Type 2, tracks historical changes by  keeping multiple records for a given natural key in the dimensional tables. It resulted in the large increase in the size of tables . To overcome these issue , Slowly Changing Dimension Type 3 which keep history of some attribute in the same record itself .For example , we can add extra field “PREVIOUS_LOCATION” to keep track of history of the supplier. In below article we will go through the details of SCD type 3 , example of SCD type 3  in Dataware house , their advantages and disadvantages. Hope you will enjoy this short tutorial.

slowly changing dimension type 3

slowly changing dimension type 3

What is Slowly Changing  Dimension Type 3?

SCD type 3 stores the current as well as previous state of the data in the same record only. To maintain history of some attribute example , location , we can have two fields LAST_LOCATION and CURRENT_LOCATION. Similarly we can add extra fields for other attributes as well.

Example of Slowly Changing  Dimension Type 3

Below example how data of EMP_SCD3 will change when changed data from EMP_INP  is merged .

ORIGINAL :

EMP_INP

EMPNO NAME EFFECTIVE_DATE DEPT

1

JOHNSON 12/17/1990 OPERATIONS

2

HARDING 2/2/1998 ACCOUNTING

3

TAFT 1/2/1996 RESEARCH

4

HOOVER 4/2/1990 SALES

EMP_SCD3

SRG_KEY EMPNO NAME OLD_DEPT DEPT CREATE_DATE UPDATE_DATE

101

1

JOHNSON NULL OPERATIONS 12/17/1990

102

2

HARDING NULL ACCOUNTING 2/2/1998

103

3

TAFT NULL RESEARCH 1/2/1996

104

4

HOOVER NULL SALES 4/2/1990

After Change :

EMP_INP

EMPNO NAME EFFECTIVE_DATE DEPT

1

JOHNSON 4/13/1992 ACCOUNTING

2

HARDING 9/17/1998 SALES

3

TAFT 1/2/1996 3RESEARCH

4

HOOVER 7/11/1993 OPERATIONS

5

LINCOLN 6/23/1994 OPERATIONS

EMP_SCD3

SRG_KEY EMPNO NAME OLD_DEPT DEPT CREATE_DATE UPDATE_DATE

101

1

JOHNSON OPERATIONS ACCOUNTING 12/17/1990 4/13/1992

102

2

HARDING ACCOUNTING SALES 2/2/1998 9/17/1998

103

3

TAFT RESEARCH 1/2/1996

104

4

HOOVER SALES OPERATIONS 4/2/1990 7/11/1993

105

5

LINCOLN OPERATIONS 6/23/1994

Key points of slowly changing dimension type 3

  • SRG_KEY is the Surrogate key to identify record in Dimension table
  • EMPNO  is the natural key to identify EMPLOYEE  records.
  • CREATE_DATE  tell the date when record was initially created in Dimension table. Example : New record was insert for EMP= LINCOLN with create_date  =  effective date from staging table.
  • UPDATE_DATE , tells the date when records was last updated because of change in the mentioned attribute. Example : For EMP =HOOVER , Update_Date = Effective_date from staging table EMP.
  • OLD_DEPT =  contains the previous DEPT for the EMP .Example : For EMP =HOOVER , OLD_DEPT =  Current_DEPT from EMP_SCD3 and then CURRENT_DEPT was updated from staging table EMP.

Check here for SCD type 3 in informatica mapping

Slowly changing dimension type 3 Advantages

  • This helps in summarizing history data along with the current data in the same records
  • Size of Dimension Table is less because of limited history
  • No need to maintain extra table to maintain history

Slowly changing dimension type 3 Disadvantages

  • It maintain the limited history of the records. For example , it maintain the previous DEPT only for the employee , full history.
  • Design is very difficult to maintain , as for history of new attribute , there will be huge change in design.

Must Read :

Source :

So , in above tutorial , we have gone through the detailed explanation of SCD Type 3(Slowly Changing Dimension Type 3) with example and their advantages and disadvantages.

Updated: December 25, 2015 — 3:51 pm

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme