Tutorial corner

Informatica,ETL,oracle,sql/plsql

slowly changing dimension type 2 with example | SCD2

Spread the love

Concept of slowly changing dimension type 2 : Contrary to  Slowly Changing Dimension Type-1 ,  Slowly Changing  Dimension Type 2  (also known as SCD Type 2), also tracks historical changes  by  keeping multiple records for a given natural key in the dimensional tables .For example , we may need to track the current location of a supplier along with its previous location just to track his sales in different region . In below article we will go through the details of SCD type 2 , example of SCD Type 2  in Data warehouse , their advantages and disadvantages.

slowly changing dimension type 2

slowly changing dimension type 2

What is Slowly Changing  Dimension Type 2 ?

SCD type 2 stores the current as well as history data in the dimension table.With type 2 we can store unlimited history in the dimension table. We can implement SCD Type  2  via :-

  • Effective Date
  • Flagging
  • Version

Example of Slowly Changing  Dimension Type 2

Slowly Changing  Dimension Type 2 (Effective Date)

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

EMP_INP

EMPNO NAME EFFECTIVE_DATE DEPT

1

JOHNSON 4/13/1992 ACCOUNTING

2

HARDING 9/17/1998 SALES

3

TAFT 1/2/1996 RESEARCH

4

HOOVER 7/11/1993 OPERATIONS

5

LINCOLN 6/23/1994 OPERATIONS

EMP_SCD2

SRG_KEY EMPNO NAME DEPT START_DATE END_DATE

101

1

JOHNSON OPERATIONS 12/17/1990

102

2

HARDING ACCOUNTING 2/2/1998

103

3

TAFT RESEARCH 1/2/1996

104

4

HOOVER SALES 4/2/1990

Will result into

EMP_SCD2

SRG_KEY EMPNO NAME DEPT START_DATE END_DATE

101

1

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

102

2

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

103

3

TAFT RESEARCH 1/2/1996

104

4

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

105

1

JOHNSON ACCOUNTING 4/13/1992

106

2

HARDING SALES 9/17/1998

107

4

HOOVER OPERATIONS 7/11/1993

108

5

LINCOLN OPERATIONS 6/23/1994

Key points :

  • SRG_KEY is the Surrogate key to identify record in Dimension table
  • EMPNO  is the natural key to identify EMPLOYEE  records.
  • End_date is added to show the status of records.
  • if END_DATE is populated with NON null value for a record,it means it is a historical record (example for Johnson , a new records (SRG_KEY =105) was inserted due to change in DEPT , hence , previous record (SRG_KEY=101) was updated with END_DATE=4/13/1992)
  • if END_DATE is populated with NON null value for a record,it means it is a historical record

Check here for SCD Type 2 in informatica mapping

Slowly Changing  Dimension Type 2 (Flagging)

A flag is added in the dimensional table to mark is as Historical/Current record.

SRG_KEY EMPNO NAME DEPT START_DATE CURRENT_FLAG

101

1

JOHNSON OPERATIONS 12/17/1990  N

102

2

HARDING ACCOUNTING 2/2/1998  N

103

3

TAFT RESEARCH 1/2/1996  Y

104

4

HOOVER SALES 4/2/1990  N

05

1

JOHNSON ACCOUNTING 4/13/1992  Y

106

2

HARDING SALES 9/17/1998  Y

107

4

HOOVER OPERATIONS 7/11/1993  Y

108

5

LINCOLN OPERATIONS 6/23/1994  Y

Key points :

  • SRG_KEY is the Surrogate key to identify record in Dimension table
  • EMPNO  is the natural key to identify EMPLOYEE  records.
  • Record with CURRENT_FLAG=’Y’ are the current one , rest are historical records.

Check here for SCD type 3

Slowly Changing  Dimension Type 2  (Version)

In version method , a sequence is added to represent the history of records. Record with highest version will represent the latest record.

SRG_KEY EMPNO NAME DEPT START_DATE VERSION

101

1

JOHNSON OPERATIONS 12/17/1990  0

102

2

HARDING ACCOUNTING 2/2/1998  0

103

3

TAFT RESEARCH 1/2/1996  1

104

4

HOOVER SALES 4/2/1990  0

105

1

JOHNSON ACCOUNTING 4/13/1992  1

106

2

HARDING SALES 9/17/1998  1

107

4

HOOVER OPERATIONS 7/11/1993  1

108

5

LINCOLN OPERATIONS 6/23/1994  0

Key points of Slowly Changing  Dimension Type 2

  • SRG_KEY is the Surrogate key to identify record in Dimension table
  • EMPNO  is the natural key to identify EMPLOYEE  records.
  • For every changed record , a new version in created (for example Records for “Johnson” )

Slowly Changing  Dimension Type 2 Advantages

  • This helps in summarizing history data along with the current data
  • No need to maintain extra table to maintain history

Slowly Changing  Dimension Type 2 Disadvantages

  • Because of history , size of table increases fast , which is difficult to maintain.
  • It is also difficult to implement in ETL process and also cause performance issue .

Must Read :

Source :

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

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