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

[VTK]

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 ?

[ngalemmo]

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.

[VTK]

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 ?

[ngalemmo]

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.

[Sponsored content]