Slowly changing dimension in ODI

Question

I'm quite new to ODI. I'm trying to do SCD on my table .

My source table contains id, name and address, the target table contain id, name, address and a strt_date

I chose an OLAP type as SCD on my target table. The following SCD behaviour also applies for each column:

• Id: natural key
• Name: overwrite on change
• Address: Add row on change
• Strt_date: Starting time stamp

I choose IKM as IKM oracle slowly changing dimension

I'm getting an error while executing:

Caused By: java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification

Am I missing some important step?

Answer 1

I assume you are looking for SCD type 2. Your target table needs also the following columns

1. New_id: Surrogate key
2. End_date: Ending time stamp
3. Current_record_flag: Current record flag

The mapping of the columns in the interface must be

1. New_id: your_sequence.nextval Execute on : Target
2. End_date: to_date("01-01-2400",'DD-MM-YYYY') Execute on : Target
3. Current_record_flag: 1 Execute on : Target

Also make sure that you haven't disable any step in the KM during previous experiments. It happen to me a couple of times!!

I am also new in ODI but this is how I did it and it works.

References

• IKM Oracle Slowly changing dimension description
• http://odiexperts.com/scd-type-2/