SCD start date

Topics: Questions About Best Practices
Sep 7, 2010 at 9:05 PM
Edited Sep 8, 2010 at 8:14 AM

Hello Todd,

i found your SCD Copmponent and its realy nice, thank you for that gift.

I tested around a bit but stuck at one thing. I know this isn't the standard, maybe but in our DW the first record for each customer should start with the date he bought the first time.

So I imported the data from the "test" source system, writing the SCD startDate and left EndDate NULL.

Then Changed some records and started the Kimball SCD task. But the changes where not recognised as SCD2 Changes, the changed records where delivered to the "New Output".

I Searched a while until i noticed the changes will be recognised when I set the startDate for the first records of each customer to NULL.

Is this behaviour wanted? Would it be possible that you build in an option to change that behaviour?

UPDATE

Okay looks like it was only in a special case.

I had set the startDate for testing to the current date. So Startdate for each record was the 7 Sep. 2010.

So today I started the package again and nothing was send to the new output.

This means, if i update the History records at the same day again and the customer record changed already it will ignor the first record because it has the startdate set to todays date. 

Greetings

Michael

 

Coordinator
Sep 10, 2010 at 11:40 PM

Using the date that the customer was added to your source system as the row version's start date is perfectly normal.  Whether that is the same thing as the "first day they bought" something... I don't know.

The default settings for the component do NOT work when you have NULL as the end date for "current" records.  However, you can change the component's behaviour in this respect by going to the SCD2 Date Handling tab, and select a NULL end date as how it recognizes current records.

And yes, good eye to realize that.  The component is NOT idempotent.  Version 1.5 of the component will only function properly if it's only run once per day per dimension.  If you attempt to run it a second time, it's behaviour will be very odd with regards to recognizing changes.  Version 1.6 has new configuration options that can permit multiple runs per day.