SCD2 : question of date ...

Hi all !

I have a question about managing dates in scd2 dimension.

The main question is : which date must I take as start/end date in my SCD2 dimension : business date or technical date ?

For example, I made an extraction of my production datas every month an load them in the datawarehouse.
If I have a new record in my production system that was, for example :

inserted in the production systeme on 02 january 2011;
take effect on 15 january 2011 (an insurance contract, for example ...)
extracted for the datawarehouse on 31 january 2011

What will be my start/end date that I will use to manage my historical datas ?

start 02 january, end 31 december 9999
start 15 january, end 31 december 9999
start 31 january, end 31 december 9999

Thanks ....

Business date should go in as SCD end \ start date, since you're capturing business related change of attribute.

But then you should also have a separate technical Audit field in dimension, which is an FK to an Audit table that containes detailed information about your data loads ('procedure_name', 'number_of_errors',...,'start_date_time','end_datetime'). So your load date will be captured in that Audit field.

How about both? One tracks when it changed for the business, the other when it was available in the DW. Both sets of dates are useful for different situations.

OK, thanks for your proposition.

So, will you find it stupid if I build my dimension like that :

..., business_start_date, business_end_date, technical_start_date, technical_end_date, ...

My technical dates will always be my extraction date from the source system with an end date at 31/12/9999.
My business dates will always be the valid dates from the business with end date at 31/12/9999.

If I don't have a business date, I put the same values in the business and technical dates, that means the extraction date ...

And I suppose that if I must do an initial load for a dimension without knowing the business start date, I put the minimum value in this field, that means 01/01/01 ?

Thanks for your support.

Yes, you have the idea.

What to do on the initial load needs to be discussed with the business. If you can get a business effective date I would tend to use that rather than a dummy early date as it provides more information. However, the downside is you would not be able to reliably filter based on a date prior to the initial load. Usually this is not a problem and becomes less of an issue as time passes.