how to get reason for change information

View previous topic View next topic Go down

how to get reason for change information

Post  abyss on Mon Mar 10, 2014 1:44 am

Hi all:
I have a basic question: our customer table has about 200 columns, some of them are SCD 1 and some are SCD 2. and when customer information is updated, it normally involve a few different fields, they could be scd1 and/or scd2. customer information come from a few different source tables.
so my question is how to get reason for change information? for example customer may update their address (scd2), first name (scd 1) and credit detail (scd 2) at the same time, I know its wrong to compare every field to figure out it is a scd 1 change or scd 2 change and which fields are changed. is it any other solution for that?

thanks
Howard

abyss

Posts : 8
Join date : 2013-05-27

View user profile

Back to top Go down

Re: how to get reason for change information

Post  nick_white on Mon Mar 10, 2014 11:10 am

Hi - if you need to know which columns have changed then the only way is to compare the old and new versions of every column, there's no way round it.
Obviously there are many ways of doing this, some of them dependant on the DB/ETL tool that you are using, but you will probably only find the best way by (informed) trial and error

Regards

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: how to get reason for change information

Post  ngalemmo on Mon Mar 10, 2014 3:33 pm

If this is something you need to provide, as well as support reporting against, your best bet would be to implement a logging fact table that tracks what changes were applied. It would be a by-product of the process that applies the updates. It is real messy to do using most ETL tools, (one row coming in, an indeterminate number of rows going out) but it can be done.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: how to get reason for change information

Post  abyss on Mon Mar 10, 2014 10:14 pm

hi, thanks guys, hmmmm, looks like i need dig into the ETL tool!

abyss

Posts : 8
Join date : 2013-05-27

View user profile

Back to top Go down

Re: how to get reason for change information

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