I am working on designing a job for loading one of the dimension table that has SCD2 and SCD1 coulmns. I did get the SCD2 column getting updated correctly, not sure how to proceed with SCD1 changes. I need some suggestions.
Currently my flow is
SOURCE – QUERY – TC (SCD2 COLUMNS) – HP(SCD2)— KEYGEN(SCD2) – DIMENSION
I am stuck at the phase where I need update some SCD1 columns. I tried doing a two pass solution like branching of QUERY as QUERY_1 – TC (SCD1 columns) – Map-- MERGE – DIMENSION
If a column is recognized by the Table Comparison as updated but the column is not included in the column compare list of the History Preserving transform then the existing row will be updated. But if there are other columns in the row that were updated and are included in the column compare list of the History Preserving transform then you end up end dating the old row and inserting a new row.
It sounds like what you want is to update columns in the old row and end date the old row in the same operation. I don’t think it will do that.
What I would try is a two phase approach. Phase one does the SCD Type I updates and the phase two does the SCD Type II. Each phase would be a separate Dataflow.
Having said all that I do have to ask: do you really want that level of complexity? If you’re going to update the current row for Type I changes then you probably should be updating ALL the rows for Type I changes.
(BOB member since 2007-09-12)