When ever we required tracking the changes of dimension, usually slowly changing dimension comes in the picture. There are three basic techniques to implement them.
Type 1 – It’s the simplest one, Update the existing dimensional value with the latest value. So it is an overwriting of data.
Type 3 – In this Add one more field for storing the old value of dimension. So here one level of history is maintained. In this case when a change occurred then existing value is moved to this new field and new value is overwritten in the existing (current) dimension.
Type 2- Widely used to maintain the history. In this case when a change occurred then a new row is added with a new surrogate key (Primary key). But based on implementation, there could be few columns added in the existing table for tracking the history.
-Only most-recent-row Flag field is added to table – here in this case the latest row flag is equal to ‘Y’. So while retrieving the most current data we only have to use filter flag equal to ‘Y’
-Another way to add row start and end date – Here in this way when ever there is a change happened then a new row is inserted in the table and row start date is the current date and row end date is NULL or some big end date. While the existing row’s end date will be updated by current date – 1.
-The last one is combination of both the above in which all the three columns is added in the table.
No comments:
Post a Comment