Initial date of effective date column for SCD 2 implementation

View previous topic View next topic Go down

Initial date of effective date column for SCD 2 implementation

Post  ozisamur on 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

View user profile

Back to top Go down

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

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

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

View user profile http://aginity.com

Back to top Go down

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

Post  Dr. Warehouse on 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

View user profile

Back to top Go down

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

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