business key keep on changing

[bobby]

Hi frnds,
i am dealing with TYPE 2 dimension however it does not follow standard type 2 dimensional due to fact the business key keeps changing and source system maintains rolling history in 3 columns

orderid
previousorderid
originalorderid

i mean if order id is 1 2 3
previousid - 1 2
originalid - 1 1
curr_flg 0 0 1

we have to set follwing cloumns as appropriate
eff_date
end_dt
curr_flg
dt_added
dt_changed

i am moving from staging to DM i have set current_flg to 1 i want it to become 0
if order id become 2 and i have added all eff_date end_date curr_flg dt_added dt_changed my self so lets say i have only order id,previousid ,
originalid from source rest all coulms are created to take care of order id
can u please give me detail like wht hash file ( i mean wht colum)should i have to take care type 2 .

Thanks,
Bob

[roy]

Hi,
if your need to update more then 2 records foreach row you process you'll probably need a recursive solution that could be done in DS basic or a Stored Procedure.
if you have a max limit of 3 rows, current and 2 more historical rows you need 2 lookups to get 2 update rows besides the new one ( perhaps in 2 consecutive transformers).

IHTH,