Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Initial date of effective date column for SCD 2 implementation

3 posters

Go down

Initial date of effective date column for SCD 2 implementation Empty Initial date of effective date column for SCD 2 implementation

Post  ozisamur Sun Nov 16, 2014 4:55 am

Hi guys,

Assume that I have a CUSTOMER table. And also in the OLTP system I have createdDate field in the customer table which specifies the creation of the customer.

In the first initial loading to the CUSTOMER table in DW, which value should I set to the EFFECTIVE_DATE column for the CUSTOMER dimension?
. CreateDate or SystemTimestamp?

ozisamur

Posts : 30
Join date : 2014-10-27

Back to top Go down

Initial date of effective date column for SCD 2 implementation Empty Re: Initial date of effective date column for SCD 2 implementation

Post  ngalemmo Sun Nov 16, 2014 9:02 pm

Both. If you have a source that provides a business timestamp as to when a change occurred, you should capture it. You should also capture the system timestamp when the row was created/updated in the DW itself. This gives you the ability to view data using two different points of view… when the business knew and when it became known in the DW.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Initial date of effective date column for SCD 2 implementation Empty Re: Initial date of effective date column for SCD 2 implementation

Post  Dr. Warehouse Wed Nov 19, 2014 6:34 pm

Aside capturing both, I would set the creation timestamp as the first effective date.

Using the load timestamp could render you unable to find an effective SCD row because the first instance of your customer became effective after the first occurrence in the fact table (if using source system timestamps to resolve the surrogate key during ETL).

Dr. Warehouse

Posts : 5
Join date : 2014-10-28

Back to top Go down

Initial date of effective date column for SCD 2 implementation Empty Re: Initial date of effective date column for SCD 2 implementation

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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