we are trying to use a view of a table (that delivers just the actual version) as comparison table to compare to an input table. The view shows the latest version of a table and only if data in certain Compare Columns changed to from the input data set to the comparison set then a new row needs to be inserted in the target table, which is the same as the underlying comparison table. As primary key column we defined a non-changeable column with values that should be stable. Does that mean we will not get an insert if comparison columns changed but not the primary key?
Correct. TC will identify that there was a change in the payload columns and flag the row as Update. If TC is connected to the target table directly, it would get an Update flagged row and executes an update statement based on the primary key of the target table.
So you need to put a Transform inbetween, e.g. a Map_Operation. But actually, what you seem to try is a so called Slow Changing Dimension Type 2, isn’t it?
Thank you very much, Werner. That is what we are trying to do. Not in a normal ETL process at the moment, but rather as part of a migration from various existing dimension tables to a different structure. This leaves the question if we send only one version per input PK at the time (e.g. monthly) then how do we tell the table comparison or history preserve component that the comparison table includes all versions, but the input dataset needs to be compared only to the last (actual) version?
That happens automatically. When a generated_key_column is specified in TC, we compare the row with the highest key number - the most recent row obviously - only (order by generated_key_column descending). Hence if on day1 you are living a X, day2 in Y and day3 in X again, it is recognized as yet another change and will get inserted.
(BOB member since 2004-12-17)