Processing multiple rows with same Business Key

Topics: Issues 3: Executing the Component (Run-Time)
Aug 28, 2012 at 7:55 PM
Edited Aug 28, 2012 at 10:49 PM

I am setting up my first SCD data flow. Things work fine except for this situation where there are more than one row, with the same business key, in the Source Dimension data source.

In this case I have 3 rows with the same business key. That business key does NOT exist in the Existing Dimension.

The SCD component sends the first row to the New output but the other 2 rows are just ignored and do not end up in any output.

Here is what we are doing. We use CDC to capture changes. The changes are accumulated in the CDC table during the day. At night we need to load the changes to the DW. The CDC table possibly has more than one change for each row (business key) in the OLTP system. I need to be able to push all changes to the DW, each new source row should end date the previous DataWarehouse row and create a new DataWarehouse row.

Not sure what I am doing wrong. Any ideas?

Aug 29, 2012 at 9:59 AM

 

The component works on the principle that each execution will only pass one row for the business key. Otherwise the component would have to cache all the changes till the end of the execution then output. Would cause no end of problems.

I haven't messed with CDC in a while, but can you apply the CDC changes to a staging version of the table, so the 3 changes would be used to update the rows. Then once all the CDC events have ran, feed the staging table into the SCD component?

Aug 31, 2012 at 9:55 PM

My problem is that I need to capture all the 3 changes and push them to the DW.

What I am having to do is setup a loop and select one row at a time to process.