SCD2 : question of date ...

[f6273]

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 ....

[ykud]

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.

[ngalemmo]

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.

[f6273]

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.

[ngalemmo]

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.

[Sponsored content]