Slowly Changing Dimensions

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.

Temporal Database

definitions

• Valid time is the time period during which a fact is true in the real world (A valid time period may be in the past, span the current time, or occur in the future.)
• to record valid time, two fields are use: Valid-From and Valid-To.
• Transaction time is the time period during which a fact stored in the database was known (Transaction time records the time period during which a database entry is accepted as correct. This enables queries that show the state of the database at a given time. Transaction time periods can only occur in the past or up to the current time. In a transaction time table, records are never deleted. Only new records can be inserted, and existing ones updated by setting their transaction end time to show that they are no longer current.)
• Transaction-From is the time a transaction was made.
• Transaction-To is the time that the transaction was superseded (which may be infinity if it has not yet been superseded).
• Bitemporal data combines both Valid and Transaction Time
• By using all 4 dates we can enable auditing like features: Transaction time allows capturing the changing details in the database, since entries are never directly modified or deleted. Instead, each entry records when it was entered and when it was superseded.