Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

4 posters

Go down

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

Post  JoeSalvatore 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!
JoeSalvatore
JoeSalvatore

Posts : 4
Join date : 2009-06-19

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  amrit 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

Back to top Go down

Does a SCD Type 1 Change Response Always Update All Historical Records? Empty Updating millions of rows

Post  Jeff Smith 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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