Constant "Row is New" for existing BKs

Jul 1, 2009 at 10:59 PM

I'm having a problem where KSCD doesn't seem to "see" existing BKs in the dimension being passed to it. I have an OLE DB source providing the table to it, and joins between the BK in both the source and the dimension return valid results. So every time I add a new set of rows, I get ALL of the new rows being committed to the dimension and the existing rows are sent to the expired handler.

If it makes any difference, my BK is a unicode string, and I'm using this as a SCD2 with an integer surrogate key handled only by KSCD (using an OLE DB destination with Keep Identity to maintain them in my database), defined SCD2 Effective and Expiry dates.  Any help or suggestions would be greatly appreciated!



Jul 3, 2009 at 9:30 PM

When you said "ALL of the new rows being committed to the dimension and the existing rows are sent to the expired" - this indicates to me that the component is correctly matching BKs.  If it were not matching up the BKs of your Source System and Existing Dimension, you would get all Source System rows passed to the "New" output, and all Existing Dimension rows passed to the "Unchanged" output.

Therefore, what it sounds like is that (for some reason) the component thinks that your rows are "SCD2 changed" every time you process them.

Does that make sense?

If it does, then take a peek at the SCD2 expired output with a data viewer - in particular the "Row Change Reason" column.  You should see what column caused the component to think the row was different.  Maybe it is screwing up - but just not the way you think it might be...

Jul 5, 2009 at 5:48 PM

Todd, the Change Reason was listed for all of the rows as a "New Row", hence why I thought it was just not "seeing" the BKs in the existing dimension source. Thanks!

Jul 6, 2009 at 1:00 AM

If the row change reason is "Row Is New" on rows coming out of the "New" output - then the component truly thinks those are brand-new rows that don't match up with an existing dimension row.

You may be right, and I may have been wrong. 

If the row change reason on the New output is "Row is New", and on the SCD2 Expired output rows it's all "Row Deleted" - then you are correct.  That scenario describes the component as being unable to find any matches.  You may want/need to play with the space insensitivity on the BK comparison - your source may have trimmed BK columns, while your dimension may have space-padded columns...