Type -2 - Effective From Date...Audit field(Update_TS) from OLTP or System Time ?

View previous topic View next topic Go down

Type -2 - Effective From Date...Audit field(Update_TS) from OLTP or System Time ?

Post  VTK on Sat Jul 16, 2011 5:58 pm

What is the best field to use as Effective From Date in DW ? Audit field from OLTP or the System time when inserting into DW.

If we use second option we have following two issues...
1. we are not accurately recording the time the record created in our systems
2. If we capture intra day changes from the source system, It will cause issues when we traverse between entities in DW (to establish relationships) as we have to use timestamp to differentiate versions in DW.

If we go for the first option, then there will be a problem if we form a DW record from two source systems.

What is the industry best practice ?


VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Type -2 - Effective From Date...Audit field(Update_TS) from OLTP or System Time ?

Post  ngalemmo on Sun Jul 17, 2011 10:09 am

Either, but if you have a source system time, all the better.

But generally, business requirements would drive the decision if you have a choice, and sometimes you may carry both. In the latter case, it would give you the ability to regenerate a query as of a point in time in relation to the state of the data warehouse, as well as creating reports in terms of business state.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Type -2 - Effective From Date...Audit field(Update_TS) from OLTP or System Time ?

Post  VTK on Mon Jul 18, 2011 7:01 pm

We do have fields called Start_Date and End_Date which carries the business validity wherever it's applicable. I am just talking about the Effective dates which are used for Type-2 versioning. Are you sure that we need to talk to busienss about these fields. If we go for the upadte_ts in the source system it will cause problems when the data is coming from multiple sources...Correct ?

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Type -2 - Effective From Date...Audit field(Update_TS) from OLTP or System Time ?

Post  ngalemmo on Mon Jul 18, 2011 9:31 pm

If you already have start and end timestamps for business validity, then the other thing you need is effective period for the type 2 row, which is the system timestamp on the DW server. If you want to store the time the data was updated in the source system, that's fine, but it would not play a part in locating the effective row in analysis.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Type -2 - Effective From Date...Audit field(Update_TS) from OLTP or System Time ?

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