1 reply Latest Post - ‏2011-10-31T05:56:49Z by zybjcdl
203 Posts

Pinned topic SCD Operator clarification

‏2011-10-27T07:45:56Z |

after experimenting a bit with the SCD target operator, I should need some more formal clarification about it's features.

1. The first think is about how row versioning occurs in a single execution. I wonder if the operator is able to monitor and handle inserts or updates (based on the SCD Type policy) when several versions of the input rows appear in the source that is connected to the operator's input port in a single execution of the data flow. If this is possible, how SCD start and end dates (in case of SCD Type 2) are internally managed?

2. The second (and last one) is about Carry-over Update Columns. Based on the documentation, these columns will be always updated regardless of whether changes are made to the monitored columns. From this definition, I understand that, for a SCD Type 1 policy, should be no difference in selecting a column that should be updated when a change is detected as monitored column or carry-over update column (besides at least one monitored column must be selected and, also, may be some performance reason behind). In the case of SCD Type 2 policy, things are a bit more confusing for me. Selected carry-over update columns, will be updated for all of the existing versions of a matching record? If this is the case, you could combine SCD Type 1 and Type 2 policies in the same operator (monitored columns for SCD Type 2, and carry-over update columns for SCD Type 1).

Many thanks for your help
Updated on 2011-10-31T05:56:49Z at 2011-10-31T05:56:49Z by zybjcdl
  • zybjcdl
    13 Posts

    Re: SCD Operator clarification

    ‏2011-10-31T05:56:49Z  in response to SystemAdmin
    #1 What do you mean about "several versions of the input rows appear in the source"? Do you mean we have several different rows has the same natual key columns' value? I think this situation is not support, natual key columns should be the primary key of the table, which should be unique. You could try this scenario, if you don't choose
    the primary key as the natual key columns, it may fail to execute.

    #2, For SCD Type 2, the carry-over columns will be updated for all of the existing versions of a matching record.
    But it is different from the monitered columns, the "end date" is determined by the monitored columns, when the monitored columns are changed, the "End date" column will be changed, otherwise, we will only changed the carry-over columns