No Records in Deleted Ouput

Feb 16, 2010 at 4:09 PM

Howdy everyone.  First of all, thanks for the great SCD component.  I originally started using this because the built-in component does not support the DT_TEXT datatype which I neeeded to process in several of my packages.  I now use this exclusively because it is more robust and intuitive.  I am using v1.5 with BIDS 2005.

One issue I am encountering is that I have several records in my existing dimension input that no longer have a corresponding business key in the data source.  I would expect these records to be directed to the "Deleted" output, where I could then expire them.  Unfortunately, these records are instead being sent to the "Unchanged" output.

A bit about my data:

My data source is a SharePoint list, which I am accessing with a SharePoint List Adapter.  The datatype of the business key from here is a 255 character unicode string, which I am converting to a four byte signed integer and then sorting before sending to the Kimball SCD component.

Within the Kimball SCD component, I have several columns of type SCD2, a Surrogate Key, a Business Key and a column for the SCD2 Current Record.  All of the SCD2 columns are unicode strings.

I've tested the New and New SCD2 outputs and both work as expected.  I have even tried routing Deleted output to the Expired SCD2 output with the same results.

I would greatly appreciate any help on this.  I can implement a workaround by comparing staging tables to the dimension table but I would prefer to utilize the functionality of the Kimball SCD component.

Thank you!

Monty

Coordinator
Feb 17, 2010 at 1:35 PM

Hi Monty,

Glad to hear it's (mostly) working for you.  I have a couple questions to help find where the problem is.  The only "SCD2 Housekeeping" column you have is one that indicates the "current" record?  You aren't using any "effective" or "expiry" date columns?

Todd

Feb 17, 2010 at 4:24 PM
Edited Feb 17, 2010 at 4:47 PM

Hi Todd-

Thanks for getting back to me.

Within the dimension, I also have columns for SCD2_Start_Date and SCD2_End_Date but do not have them identified within the Kimball SCD Component.  The reason I did this is that this system is still in development and we need to run several test loads per day.  I sometimes need to assign start and end dates that fall on the same day but at different times.  I've found that the Kimball SCD Component doesn't recognize active records with the start date of the current day and will try to insert new records from the source system that are identical to what is already in the dimension.

When I did not identify an SCD2 Effective Date and SCD2 Expiry Date and only identified an SCD2 Current Record column and set the Start Date and End Date columns manually, I avoided this problem for New and Changed records.  Not until recently have I tried to use the Deleted output.  After reading your post, I went ahead and set the SCD2_Start_Date and SCD2_End_Date to ColumnTypes SCD2 Effective Date and SCD2 Expiry Date in the Kimball SCD Component.  The latest active records in the dimension table were from yesterday so everything worked as expected, including the missing data from the source being routed to the Deleted output where I expired the old records.  When I ran the package again, the same issue I encountered before happened, the active records with today's date as the start date were not recognized by the Kimball component and they were all inserted again from the source.

I'm guessing that I'm not configuring something correctly here but am at a loss for what that might be.  Does what I am describing make sense?

Thank you,

Monty

Coordinator
Feb 22, 2010 at 9:18 PM

Let me run a few tests.  Yes, you're on the money when you see problems with running it more than once on a dimension for the same "day" - that isn't (yet) supported, but now seems to have a LOT of votes :)

Good to know it operated properly when you specified the start and end dates - but it should also work with just a current indicator, I think.