how to do change capture in server jobs?

battleboy1 · Post by **battleboy1** » Fri Nov 25, 2005 5:10 pm

could some one help me with the job design in sever edition where i want to capture changes in database.

for example the source master has columns

c#(pk), cname, address.

c1 , abc , usa
c2 , xyz , uk

and the target dimension should look like

c#, cname, address, start_date, end_date, current_flag.

c1, abc , usa , 12/09/2000, 01/01/2050 , y

c2 , xyz ,uk , 12/09/2000, 31/10/2001, n

c2 , xyz , canada , 31/10/2001, 01/01/2050, y

so c2 changed address on 31/10/2001 so that should be the end_date for his uk address and start date for canada address. also the curren flag for previous address should become n and the present address should become y. i am new to datastage. i want to have the job design and if possible and logic behind it.

battleboy1 · Post by **battleboy1** » Fri Nov 25, 2005 6:09 pm

i was asking about implementing slowly changing dimensions ?

someone please let me know how to proceed with the job design. this is really urgent for me as i have to start from here without which i cannot proceed.

thanks in advance

kcbland · Post by **kcbland** » Fri Nov 25, 2005 8:23 pm

Search the forum for "SCD", this has been talked about a lot. You're talking about a type-2.

The basic summary is:

1. Derive source row
2. Lookup using natural key against the warehouse for the surrogate key that has the max begin timestamp (or even better, and end timestamp forecast into the future like 2099-12-31).
3. If the lookup failed, row is first for this natural key and set the end timestamp to 2099-12-31, assign a surrogate key, and insert.
4. If lookup succeeded, compare that rows attributes with new source row, and if materially different, update the found row with an end timestamp of yesterday, set new row with start timestamp of today and assign surrogate key and insert

vmcburney · Post by **vmcburney** » Sat Nov 26, 2005 12:40 am

Also do a search for threads on the CRC32 function and look at the example uploaded to Ascential developernet. It can be a faster way to compare a lot of attributes when doing change detection.