Tutorial corner

Informatica,ETL,oracle,sql/plsql

slowly changing dimension type 6 with example | SCD6

Spread the love

slowly changing dimension type 6 : 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. Slowly Changing Dimension Type 3 which keep history of some attribute in the same record itself .Slowly Changing Dimension is combination of SCD 1 + SCD 2 +SCD 3, hence the name given SCD 6.For example , we can add extra field “PREVIOUS_LOCATION” to keep track of history of the supplier and for new history a new record is also inserted (just like SCD2) . In below tutorial , we will go through the details of SCD type 6 , example of SCD type 6  in Dataware house , their advantages and disadvantages.

slowly changing dimension type 6

slowly changing dimension type 6

What is slowly changing dimension type 6 ?

SCD type 6 stores the current as well as previous state of the data in the same record only. It also create history records for every change with indicator like FLAG to indicate whether it is Current or history.

Example of Slowly Changing  Dimension Type-6

Below example how data of EMP_SCD6 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_SCD6

SRG_KEY EMPNO NAME OLD_DEPT CURRENT_DEPT CREATE_DATE END_DATE CURRENT_FLAG

99

1

JOHNSON RESEARCH OPERATIONS 11/20/1989 11/25/1989 N

100

1

JOHNSON SALES OPERATIONS 11/25/1989 12/17/1990 N

101

1

JOHNSON OPERATIONS OPERATIONS 12/17/1990   Y

102

2

HARDING ACCOUNTING ACCOUNTING 2/2/1998   Y

103

3

TAFT RESEARCH RESEARCH 1/2/1996   Y

104

4

HOOVER SALES SALES 4/2/1990   Y

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_SCD6

SRG_KEY EMPNO NAME OLD_DEPT CURRENT_DEPT CREATE_DATE END_DATE FLAG

99

1

JOHNSON RESEARCH ACCOUNTING 11/20/1989 11/25/1989 N

100

1

JOHNSON SALES ACCOUNTING 11/25/1989 12/17/1990 N

101

1

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

105

1

JOHNSON ACCOUNTING ACCOUNTING 4/13/1992   Y

102

2

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

106

2

HARDING SALES SALES 9/17/1998   Y

103

3

TAFT RESEARCH RESEARCH 1/2/1996   Y

104

4

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

107

4

HOOVER OPERATIONS OPERATIONS 7/11/1993   Y

108

5

LINCOLN OPERATIONS OPERATIONS 6/23/1994   Y

Key points of Slowly Changing  Dimension Type 6

  • 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.
  • END_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_SCD6 and then CURRENT_DEPT was updated from staging table EMP.
  • CURRENT_DEPT  =  Contains the current DEPT for the EMP .It is also updated in History record as well
  • FLAG  is the indicator which indicate whether it is current record or not.

Check here for SCD type 6 in informatica mapping

Slowly Changing  Dimension Type 6 Advantages

  • This helps in summarizing history data along with the current data in the same records

Slowly Changing  Dimension Type 6 Disadvantages

  • Size of table grow faster because of history records
  • 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 -6(Slowly Changing Dimension Type -6) 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