SCD2 : question of date ...

View previous topic View next topic Go down

SCD2 : question of date ...

Post  f6273 on Sat Jan 21, 2012 10:58 am

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

f6273

Posts : 2
Join date : 2012-01-21

View user profile

Back to top Go down

Re: SCD2 : question of date ...

Post  ykud on Sun Jan 22, 2012 7:03 am

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

Posts : 12
Join date : 2012-01-16

View user profile http://ykud.com

Back to top Go down

Re: SCD2 : question of date ...

Post  ngalemmo on Sun Jan 22, 2012 12:37 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD2 : question of date ...

Post  f6273 on Mon Jan 23, 2012 2:58 am

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.


f6273

Posts : 2
Join date : 2012-01-21

View user profile

Back to top Go down

Re: SCD2 : question of date ...

Post  ngalemmo on Mon Jan 23, 2012 1:43 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD2 : question of date ...

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum