Posted by rcamarda on 05/18/06 14:39

This wont be much help on how to, but here it goes:
In data warehouse speak, you are talking about a slowly changing
dimension also know as a type 2 dimension. (Ralph Kimbal).
I use this type of data in my datawarehouse. As example, I need to keep
track of a students home campus over time. As we open new campuses, or
students move around the area, students will change their 'home
campus'.
I need to associate student's registrations to a version of their
student record that reflects their home campus at the time of the
registration. Without this, reports would show the students current
campus for a registration from 5 years ago. If the campus opened in
spring 2006, the report would show the student registering for classes
5 years in a campus that didnt exist.

The way I track these changes is with an ETL tool from Cognos: Data
Manager. Once I have built the model, I indicated which fields to
track, and the tool does the rest.
It adds fields to the data.
1. Surrogate key
2. Current Flag indicator
3. Create Date
4. End date
5. effective date
6. update date
I'm glad I dont have to tackle this problem w/o a tool.
I would have a separate table to track the changes. A trigger that
would check for the changes you are looking for. If a change is found:
1. End the current record by putting date in the end_date field.
2. set current_ind field from Y to N
3. get next surrogate key (this is just a int field starting at 1)
4. Insert new record with new surrogate key
5. set current indicator to Y
the tricky bit is how to know when a change happend, after that its
pretty straight forward.
HTH
Rob

[Back to original message]