Difference between revisions of "Slowly Changing Dimensions"

Revision as of 11:52, 30 May 2017

External Links

https://en.wikipedia.org/wiki/Temporal_database#Bitemporal_Relations
https://en.wikipedia.org/wiki/Slowly_changing_dimension
http://datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html

Notes:

Additional analysis will be performed on the topic of Bi-Temporal Databases / Relationships

Requirements

To be able to capture historical values of a record as they change over time.

For example, when issue 540 of the Fantastic Four is release, the details for the Title and Publisher should reflect the data that was active during the date that issue 540 was published. If only the current data is maintained, then you would loose the details for the Title record for the Fantastic Four that were in effect when the issue was printed.

Change versus Correction

If we use the address of a customer as an example, we can show the difference between a true change versus a correction of existing data.

When we start doing business with Customer ABC their address is:

Company ABC
145 Maple Street
Centerville, OH, 43111

This data is inserted into the database as the original details for the customer. Now, six months later, Customer ABC moves to the following location.

Company ABC
2122 Lincoln Parkway
Preston, OH, 43111

The original data is updated. This is considered a Change, as the customer's location is different than it was previously. Now, lets say a week goes by and we notice that the zip code was not updated when the customer moved, so we make a correction and update the old zip code of 43111 to new zip cod of 43203.