Hi all Gurus out there,
am trying out stuff with Data Integrator. Started with trying to implement a simple type 2/3 SCD
here is the scenario …
source table : say called Model_Master
MAKE_NAME, MODEL_SERIES, SERIES_NAME, MODEL_YEAR, PLATFORM, TYPE, ORG_CODE, COUNTRY
target Dim table : say called Dim_Model
MODEL_KEY, MAKE_NAME, MODEL_SERIES, SERIES_NAME, MODEL_YEAR, PLATFORM, TYPE, ORG_CODE, COUNTRY, CURRENT_FLAG, VALID_FROM, VALID_TO, LAST_UPD_DATETIME
my transform looks like this : source–query–table_comparison–history_preserving–key_generation–target
Now, after the initial bulk load, I change some fields in the source table & insert some new ones.
So, I run the mapping & get what is expected – New Inserts for both the updates as well as inserts.
but, in addition to that, I want the ‘valid_from’, ‘valid_to’ & ‘current_flag’ functionality implemented.
i.e., I have a column called as last_upd_datetime in my target. Which is being populated from sysdate() func in the query in the transform.
During Initial run, I keep the ‘Valid_to’ column NULL & all ‘current_flag’ = ‘Y’
During subsequent runs with the changed input data, I want the ‘valid_to’ column of the earlier row to be updated with sysdate() (or ‘valid_from’ column of the new row). Plus I want the last_upd_datetime of both the rows to be updated to sysdate() (so that I can track later on when was each row was last updated)
Also, I want the ‘current_flag’ field of the earlier row to be reset to ‘N’.
& lastly, I want the last_upd_datetime column of all the rows (either updated or inserted) to be changed to sysdate() for tracking purposes later on.
I know that History Preserve will change the op code to Insert for all the rows & hence asking for updation of earlier rows is uncalled for … but this is my requirement.
Any ideas out there ??
Amit
Infogain Corp.
P.S. : I feel DI 6.1’s documentation is pretty inadequate. Is it any better in 6.5 . We are planning to use DI as ETL for a full scale dev project & with this kind of docs, it looks hard :mg:
capreal26 (BOB member since 2004-07-27)