Does a SCD Type 1 Change Response Always Update All Historical Records?

View previous topic View next topic Go down

Does a SCD Type 1 Change Response Always Update All Historical Records?

Post  JoeSalvatore on Fri Jun 19, 2009 2:21 pm

Often when implementing slowly changing dimensions, a particular dimension will have only certain columns that are interesting to view changes over time (Type 2) while the remainder simply need to be updated (Type 1). Given this combination of Type 2 and Type 1 change responses how does one handle historical records? More simply put, anytime a Type 1 change is made, even one that occurs simultaneously with any other SCD Type, are all historical rows updated or is only the current row updated?

Change Scenarios for a particular row in an ETL load:
1. Row has both Type 2 and Type 1 changes
2. Row has only Type 2 changes
3. Row has only Type 1 changes

Example:
Key BusKey FirstName(Type1) LastName(Type2) Position(Type2) Birthday(Type1) Current
5 482 Doug Smith Manager 06/18/1962 Y
4 482 Doug Smith Tech 06/18/1962 N
1 482 Dug Smith Tech 06/18/1962 N
6 862 Sally Jones Sr Admin 02/26/1968 Y
3 862 Sally Jones Admin 02/26/1968 N
2 862 Sally Roberts Admin 02/26/1969 N

Scenarios:
1. Sally got married and changed her name from Roberts to Jones while also providing a corrected birth date
2. Sally changed positions from Admin to Sr Admin
3. Doug/Dug corrected first name
4. Doug/Dug changed position from Tech to Manager

When processing changes for Sally in scenario 1 (Keys 2 and 3), should the original incorrect birth date be changed?

Does the decision depend only on the fact that a Type 1 change is made or should additional business rules apply?
In other words, circling back to the original question, do Type 1 changes, made alone or with other changes, require updating all of history?
If Type 1 should always update history, are you aware of business scenarios where even if incorrect original values should be retained and thus Type 1 would only impact the current row?

Your always insightful ideas appreciated.
THANKS!
avatar
JoeSalvatore

Posts : 4
Join date : 2009-06-19

View user profile

Back to top Go down

Re: Does a SCD Type 1 Change Response Always Update All Historical Records?

Post  ngalemmo on Fri Jun 19, 2009 3:30 pm

Yes, if you have a mix of type 1 and type 2 columns in a type 2 table, a change to a type 1 column is applied to all rows for a particular business key.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Does a SCD Type 1 Change Response Always Update All Historical Records?

Post  ngalemmo on Fri Jun 19, 2009 3:37 pm

To answer your other questions, if there is a need to sometimes retain history for those columns, they are no longer type 1 columns... right? They either become type 2 or 3. In the case of type 3, you would still update all rows for a given business key.

But, if you are in a situation where maybe they want to see history and other times maybe they don't, the best solution is to maintain both a type 1 and type 2 table for the dimension and carry both foreign keys in all facts.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Does a SCD Type 1 Change Response Always Update All Historical Records?

Post  amrit on Thu Feb 11, 2010 2:55 pm

Hi,

I can see you have mentioned the following:

"Yes, if you have a mix of type 1 and type 2 columns in a type 2 table, a change to a type 1 column is applied to all rows for a particular business key."

We are currently applying this logic - ie updating all SCD1 columns - my concern here is performance with millions of rows and as the data builds up, what is the best practice?

thanks

amrit

Posts : 1
Join date : 2010-02-11

View user profile

Back to top Go down

Updating millions of rows

Post  Jeff Smith on Tue Feb 16, 2010 7:19 pm

One way to handle updating millions of rows is to rename the dimension table, recreate the dimension under the old name, and insert from the old table, joining to the source of the updates. Some databases handle Type 2 updates better than others.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Does a SCD Type 1 Change Response Always Update All Historical Records?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum