Table comparison with Map Op transform vs Table comparison with hist preserve

[Former Member]

What is the right approach:

my source has last_Updated   Date field.

Scenario 1.) source > tbl comparison > Map oper > target

V/S

scenario 2.) Source > tbl comparison > history preservation > target

Both are doing the same thing, what is the benefit of using one over other.

I use source based cdc approach, which is much faster, but sometimes i see the above methods used.

Thank you all for the helpful info.

[Former Member]

Raj,

What does History Preserving do is given here - (taken from SAP Wiki)

The History Preserving Transform requires rows with the OP codes insert/update/delete - typically generated by a Table Comparison Transform upfront - and cannot deal with normal rows at all. It takes the input columns and in case of an insert passes it to the output as is. In case of an update, it compares the before image value with the after image value and if there is a change in one of the columns in the "Compare Columns" list, it changes the OP code from update to insert so that a new version of this row will get inserted instead of updating the old one. Optionally, a current indicator (which version is the current one, all others are not) and a begin-end date can be set. If these features are used, each update row will actually cause two output rows, the insert row with the after image values as the new version and an update row with the before image values to modify the oldest record's currentflag and/or end-date.

Thanks,

Arun

[Former Member]

Hi Raj,

Only difference between 1 & 2 is history preservation.

Scenario 1: It will do the table comparison on selected columns and gives the data sets flagged as UPDATE and INSERT. And Map Op will do the actual insert/update based on the resulted flags from Table comparison.

Scenario 2: It does an additional step which is...it create an extra row for a UPDATE row. This is needed in case if you want to maintain history of changes done to that data (selected columns).

If CDC is available with your source database, i will suggest to go with CDC approach as its better compare to both of these scenario's on a performance level.

Regards,

Naresh

[Former Member]

Naresh,

Even with scenario 1, it creates extra row - with map op code "Update" to "Insert", will create extra rows, in the history table.

Regards,

Raj