Multi - Table Insert KM

Данная страница была утеряна на оригинальном сайте http://odiexperts.com/, и я попытался восстановить ее содержание используя http://web.archive.org/.

ODI 11g presents the a new KM called Multi Insert Table which enables to Insert data from one source into one to many Oracle target tables in append mode, using a multi-table insert statement (MTI).

Here is a small example to show its functionality. Here in this we are loading the Source table Employees to Multiple Target table ( employees1, employees2, employees3 ).

Create the First Interface and lets name it – Parallel_load_emp1.



and set the IKM option as following



Create the Second Interface and lets name it – Parallel_load_emp2.
And set the IKM options as follows



Create the Third Interface and lets name it – Parallel_load_emp3.

And set the IKM options as follows



In short the First Interface should have the DEFINE_QUERY – True and the Final Interface EXECUTE – True

We create a package called MULTI_TABLE_INSERT_EMPLOYEES and arrange the Interface as per the required Order.






As we can see In the above image of the operator run , we find that it kept adding the Target table for each interface run and finally ran the MTI condition and load the data into the Multiple target as shown below




This KM can be used to perform Insert only

Comments:

Srinivas
April 26, 2012 at 2:19 pm

Hi Kshitiz,

Can I use different target tables structures and still used the IKM Oracle Multi Table Insert. My problem is that I have one denormalized source table and 3 normalized target tables that have the same PK/AK.

Denormalized Customer (Source) To Normalized Customer (target 1), Customer Address (target 2) and Customer Department (Target 3).
Currently I am using 3 interfaces in sequence in a package and loading them using IKM Oracle Incremental Update
Any inputs will help.

Thanks,
Srinivas

---

Kshitiz Devendra
April 26, 2012 at 4:33 pm

hi Srinivas,

I believe it should still work . Please look into KM description for specific restriction . Also MTI can only be used for Insert only.

Thanks
Kshitiz

---

Pradosh
May 4, 2012 at 7:44 am

Hi , can I put different filters in these 3 interfaces and execute? will it work?

Let’s say in employee1 I want to insert those rows from employee where employee.empid is from 1 to 10
employee2 I want to insert those rows from employee where employee.empid is from 10 to 20
so on…….

---

Kshitiz Devendra
May 4, 2012 at 7:52 am

Hi Pradosh ,
Thanks for visiting us . Personally i have not tried yet. Why don’t you try and let us know too

Thanks
Kshitiz

---

MAK
July 25, 2012 at 4:34 am

Hi,

I want to load 4 source tables data in a single a target flat file using a single interface. Any help on how to do this would be highly appreciated?

For your info, each source tables has different total number of columns, but the target flat file has the total number of all columns in all the source tables.

Thanks,
MAK

---

Kshitiz Devendra
July 25, 2012 at 5:38 am

Mak ,

You can create using interface between different source and Target and use the KM – IKM Sql to File Append and for the first Interface use Truncate – True and for the rest Truncate – False so every time you run the package the first interface will clear the file and load and the rest will append the data .

---

Prerna
September 17, 2012 at 5:07 am

Hi,

When i tried the same thing at my end, I got an invalid identifier error in the insert into table step of the last interface in the package. I have checked the data type of C$ table and the target table, its the same … still i am getting this error. Could you please help me with this.

Regards,
Prerna

---

Kshitiz Devendra
September 17, 2012 at 6:43 am

Prerna,
Invalid Identifier generally come when one of the column does not exists in the target/Created C$ table or the column is misspelled. Please check for that .

---

Prerna
September 18, 2012 at 3:37 am

Hi Kshitiz,

The query works without the column name alias so i modified the km to exclude column name alias in the select query.

Can we customize the MTI KM to update rows as well?

---

Kshitiz Devendra
September 18, 2012 at 6:07 am

No MTI does not allow updates.