8/12/2013

Slowly Changing Dimensions

Working with Data Warehousing, one must identify the type of changes over time.

What is referred to as Slowly Changing Dimension?

There are three types.

Type 1: Records gets inserted to Data Warehouse once, data never changes.

Type 2: Record gets inserted to Data Warehouse, if changes to front end system, record gets updated repeatedly.

Type 3: Record gets inserted to Data Warehouse with an active flag set to current record, if changes occur on the front end database, the DW current record is flagged as not current with a date timestamp and a new record gets inserted as set to current record.

Type 3 keeps an audit trail of changes over time, where Type 2 only stores the current value and Type 1 never changes.

Example of Type 1 is "First Visit Date", it occurs once and never changes.
Type 2, if it's a new record, it gets inserted, if the record changed, it does an update.
Type 3, the existing row gets expired, the new row gets inserted.

Depending on your system requirements, you may use one, two or all three types in a given project.

That's Slowly Changing Dimensions to the best of my recollection.

SSIS: Suggested Best Practices and naming conventions

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.