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

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

5 posters

Go down

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

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

Back to top Go down

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

Post  ngalemmo Wed Oct 29, 2014 12:50 pm

You update all rows for the customer.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  BoxesAndLines Wed Oct 29, 2014 1:43 pm

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

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

Back to top Go down

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

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

Back to top Go down

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

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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

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

Back to top Go down

Should I updated the scd1 attribute of old records while updating the new one ? Empty 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

Back to top

- Similar topics

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