Should I updated the scd1 attribute of old records while updating the new one ?

View previous topic View next topic Go down

Should I updated the scd1 attribute of old records while updating the new one ?

Post  ozisamur on Wed Oct 29, 2014 9:19 am

Consider this table.

CUSTOMER_SK|CUSTOMER_ID|NAME|SURNAME|AGE|JOB|EFF_DATE|EXP_DATE|IS_CURRENT

NAME and SURNAME is SCD1 attribute. When this attributes changes I just updated.
AGE and JOB is SCD2 attribute. When this attributes changes I updated the IS_CURRENT column to 'N' for the existing record then insert new record.

As an example, customer1 has just one record in customer table. When name and surname changes just update the existing record. When the customer's age or job changes I adding new record. So there are 2 customer records for just one customer as it is supposed to be.

Then, customer1 name is changed. So should I update the just existing record ( is_current='Y' ) or all records for that customer should be updated ( no matter is_current = 'y' or 'n' )?

Thanks.


ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Should I updated the scd1 attribute of old records while updating the new one ?

Post  ngalemmo on Wed Oct 29, 2014 12:50 pm

You update all rows for the customer.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Should I updated the scd1 attribute of old records while updating the new one ?

Post  BoxesAndLines on Wed Oct 29, 2014 1:43 pm

I guess we call that SCD Type 6 these days.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re:Should I updated the scd1 attribute of old records while updating the new one ?

Post  hkandpal on Thu Oct 30, 2014 1:01 pm

Hi,

if you want to preserve the customer name (old and new) , then you can have a new column added to the diemnsion which will be like a customer ID which will be the natural key for the table.
This way you can capture if there is a frist name or last name change.



thanks

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Should I updated the scd1 attribute of old records while updating the new one ?

Post  Jeff Smith on Thu Oct 30, 2014 1:04 pm

The problem with keeping history for things like "Name" is how do you differentiate between a name that has been changed and one that has been corrected?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Should I updated the scd1 attribute of old records while updating the new one ?

Post  ngalemmo on Thu Oct 30, 2014 4:14 pm

Guys, the gist of the question was that the dimension is a type 2 (or I guess is it's now called a type 6 I can't count that high) but there is no desire to track changes for all the attributes (name being one of them). So you have a mix of 'type 1' and 'type 2' attributes in the same table. In such situations, type 1 attributes are handled by updating all rows based on the natural 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: Should I updated the scd1 attribute of old records while updating the new one ?

Post  hkandpal on Fri Oct 31, 2014 12:28 pm

If Name is SCD1, then just update the name for all the rows for that Natural key.

thanks

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Should I updated the scd1 attribute of old records while updating the new one ?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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