Data Integrator -- history preserve/table comparison ...

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 ?? :confused:

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)

Hi Amit,

i hope i understand your problem.

First of all i would change the LAST_UPD_DATETIME to a timestamp format(database). This would always store date/time-informations when the
record was changed (insert/update). A trigger would be possible to.

I think that your MODEL_KEY is a surrogated key and if a record changed the new one should have the same MODEL_KEY? VALID_TO and CURRENT_FLAG should become SYSDATE from the new record and N for the CURRENT_FLAG?
In this case you should change your transformation to:

source–query–table_comparsion–key_generation-history_preserving–target

The Tasks:

  1. Query
  • SCHEMA_IN
    MAKE_NAME
    MODEL_SERIES
    SERIES_NAME
    MODEL_YEAR
    PLATFORM
    TYPE
    ORG_CODE
    COUNTRY

  • SCHEMA_OUT
    MODEL_KEY = is a lookup query from your target-table over the needed keys
    MAKE_NAME
    MODEL_SERIES
    SERIES_NAME
    MODEL_YEAR
    PLATFORM
    TYPE
    ORG_CODE
    COUNTRY
    CUREN_FLAG = always Y because it is new one ore it has changed
    VALID_FROM = SYSDATE() if it is new one or it has changed, than we
    need the date

  1. Table_Comparsion
    Table name: Dim_Model
    Input primary key columns:
    MODEL_KEY
    MAKE_NAME = i assume that is your unique key, otherwise put the other relevant fields inside this
    CURRENT_FLAG = we only whant to change the active records inside the target
    Compare columns:
    The columns which could change, like COUNTRY, ORG_CODE, etc.
    Generated key column: EMPTY

  2. Key_Generation
    Table name: Dim_Model
    Generated key column: MODEL_KEY
    Increment value: 1

  3. History_Preserving
    Valid from: VALID_FROM
    Valid to: VALID_TO
    Valid to date value: 2099.01.01
    Column: CURRENT_FLAG
    Set value: Y
    Reset value: N

I hope this helps?
cu.
Martin

P.S.: The documentation of 6.5.1 is the same documetation as 6.1. BO changed only the version and put some new functions inside. The only recommendation for your project is try and play.


ml1 (BOB member since 2003-09-09)