Tips on modeling slowly changing dimensions(SCDs)

There are three common methods to model the slowly changing dimensions. here all the solutions come from Oracle BI.

SCD is dimension that has data that slowly changes, for example: The sales representative assigned to a customer may change over time. as below, the previous salesrep of customer named AA is Peter, and now we transfer AA to salesrep Paul.

for the above scenario, there are at least three solutions. which one to use depends on our business requirment. note: Columns and processing must be added to your source system to track the changes and populate the version information


Method one : overwrite the old one with the new one


It means, we just care the current salesrep Paul, but dont care Peter. so there are the following features
1) New value overwrites the old value ;
2) Single column stores the attribute value;
3) Old value is lost;

Method two : track changes


It means storing all the history versions. such as AA was assigned to Mike before being transfered to Peter, by this method, we will store all the three salesreps.
the common way is using the timestamps to differentiate the records, such as if the transaction appliaction is EBS or Siebel, we always use Effective Start date and Effective End date.






Method Three : track change but only the last change

It means, we just care Paul and Peter, but dont care Mike.
for the above two new salsrep and old salesrep values, we us two columns to store that. current salesrep column stores Paul, and we remove Peter to the prior salesrep column.

so there are the following features for this method


1) Two columns store the attribute values;
2) Old value is moved from current to prior column - Only one prior version is saved;
3) New value overwrites current column;



Later I will give a Implementation which done in RPD and Dashboard.