History Preserving updates using all the same Effective_From_Date

[Former Member]

Now I know I'm not the only one having this issue. Two issues actually.

1. When a column changes and is recognized in the History Preserving transform, the records Active flag is set to 0 (which is fine, that's what I want) but it's Effective_End_Date is somehow changed to the same value of its Effective_From_Date.

2. When the row from Issue 1 is updated, the new Inserted row (the new active row for that key) is inserted with an Effective_From_Date of the previous records row. WHAT? Why does it do this? Why doesn't it set the Effective_From_Date to the current system time?

I'm sure that both issues are related and fixing one will fix the other. Buy why is it doing this????

Thanks!!

[Former Member]

Thanks Suneer! Your answer to number 2 sounds like it's assuming that I'm passing in a column that is populated already for EffectiveFromDate and EffectiveToDate, is that correct? How does History Preserving handle type 1 changes...my guess is that it doesn't at all which means a flow split to handle type 1's and type 2's, then merging them down stream...

Thanks again!

[Former Member]

Effective From Date can be passed from the source. A default effective to date will be populated by History preservation as per the value you give inside. In case if the history of a particular key value is preserved, the previous record's effective to date will become the new record's effective from date.

In SCD Type1, you always update the changed records with the new value. Hence, a history preservation transform and key generation is not required as you are not preserving any history here.

Regards,

Suneer

[Former Member]

1) In History Preservation, there is an option to set - "Use Valid From Date of New Record". If you set this, Effective End Date of the old record will be updated with the Effective From Date of the New Record (with same key value). You can also use 'One day before effective end date of new record'

2) For a newly inserted row, Effective From Date will be what you pass as input. It is not the Effective From Date of newly inserted row which is changing, but the Effective End Date of the updated row (the previous row with the same key value), which changes as per the Effective From Date of the new row, due to the above mentioned option.

Regards,

Suneer