Incremental Updates -- Latest Flag

dataservices job design
1

I have a target table in which i am inserting a new record even if the existing record is modified but i want to mark the latest version with a flag ‘Y’ indicating the latest record. How do i achieve this?

Ex:
In the initial load, lets assume the following records are inserted:

PK DB_ID Name Age Current_Flag
1 1 Emanuel 24 Y
2 2 Kunz 21 Y
3 3 Teri 21 Y
4 4 James 37 Y
5 5 Chris 38 Y

Now, lets assume Age corresponding to DB_ID 4 in the source transaction system changed from 37 to 38. I want to insert a new record with the corresponding changes and mark the latest record as Y for the Current_Flag field.

PK DB_ID Name Age Current_Flag
1 1 Emanuel 24 Y
2 2 Kunz 21 Y
3 3 Teri 21 Y
4 4 James 37 N
5 5 Chris 38 Y
6 4 James 37 Y

I want to update the Current Flag to N for PK 4 and insert a new record PK6 with current flag Y.

Any suggestions are appreciated.

shalini (BOB member since 2008-03-26)

2

Sorry, in the above example, i forgot to change the age to 38.
Initial Load Results

PK DB_ID Name Age Current_Flag
1 1 Emanuel 24 Y
2 2 Kunz 21 Y
3 3 Teri 21 Y
4 4 James 37 Y
5 5 Chris 38 Y

Incremental Load Results:

PK DB_ID Name Age Current_Flag
1 1 Emanuel 24 Y
2 2 Kunz 21 Y
3 3 Teri 21 Y
4 4 James 37 N
5 5 Chris 38 Y
6 4 James 38 Y

shalini (BOB member since 2008-03-26)

3

640×323 73.7 KB

shows exactly that plus an optional valid from/to date column.

https://wiki.sdn.sap.com/wiki/display/BOBJ/Slow+Changing+Dimension+Type+2

Werner Daehn :de: (BOB member since 2004-12-17)