Slowly changing dimensions

Slowly Changing Dimensions (SCD’s)

SCD is a dimension which captures the changes

that occur over a period of time.

Type I – Contains only current data.Change in source is overwritten

Type II-Maintains complete history+Current data

Type III-Partial(recent) history+Current data

In OLTP

Cust Id  C.Name  Loc  StartDate

11  Smith  Hyd  12Jan09

12  James  Che  12Jan09

13  Tom  Mum  12Jan09

14  Peter  Hyd  15Sept10

In DWH

Sl.No     CustId     C.Name        Loc     StartDt              EndDate     ChangeVer.

1  11  Smith       Hyd      12Jan09  1

2  12  James       Che      12Jan09         14Sept09  1

3  13  Tom       Mum      12Jan09              1

4  12  James       Kol      15Sep09  2

5  14  Peter       Hyde      15Sep09  1

•Active record is where end date is null

•History

•Active status(Active record – Max Version)

•Change No (Version)

•Duration (Time stamp)