Creating a Custom Knowledge Module (KM) in ODI 11g

In my previous blog, I showed how to do an ELT from Oracle DB to XML in ODI. Every ELT uses a Loading Knowledge Module (LKM), which describes how the data is loaded from one system to another, and an Integration Knowledge Module (IKM), which describes how the data is integrated in the target system. These Knowledge Modules are fully extensible and can be edited from within ODI Studio. In most cases, these Knowledge Modules do not need to be edited, but there are situations where you may have to, or want to, customize a Knowledge Module to achieve a specific scenario. A good example would be to remove certain commands or a piece of code from a KM to gain higher performance. Another example would be to combine commands or pieces of code from multiple KMs into one KM to achieve a particular functionality. In my example below, we will create a custom IKM using a combination to two IKMs to accomplish a specific scenario.

Here is my specific scenario:

Create an Interface with an Oracle DB as a target that can handle Inserts and Updates.

As part of that Interface I also have to use an ODI Specific Sequence.

The IKM mentioned above, ‘IKM SQL to SQL Control Append’ can only handle Inserts.

But, another IKM, ‘IKM SQL to SQL Incremental Update’ can handle Inserts and Updates, but not ODI Specific Sequences.

Given the above scenario, we need an IKM that can handle ODI Specific Sequences in an Interface and allow Inserts and Updates to happen in the same Interface. This is where our custom IKM comes into play. We will combine commands from the 2 the IKMs, ‘IKM SQL to SQL Control Append’ and ‘IKM SQL to SQL Incremental Update’ to create our own custom IKM.

Here are the steps to create this custom IKM:

In the ‘Designer’ tab, under ‘Knowledge Modules’, right click ‘IKM SQL to SQL Incremental Update’.

Click on ‘Duplicate Selection’ to create a duplicate of this IKM.

Rename our new duplicate IKM, to say, ‘IKM SQL to SQL Incremental Update Custom’ and hit Save. This duplicate IKM will be our new custom IKM.

Click on the Details tab of our new custom IKM.

Double click ‘Insert New Rows’ command to edit it.

Rename this command to ‘Insert New Rows Custom’ and hit Save.

Remove the existing code under ‘Command on Target’ tab and hit Save.

In the ‘Designer’ tab, under ‘Knowledge Modules’, open ‘IKM SQL to SQL Control Append’.

Double click ‘Insert New Rows’ command and copy the code from ‘Command on Target’ tab. We want to use this code in our new custom IKM because we want our ODI Specific Sequence to work.

Paste this code under our ‘Insert New Rows Custom’ ‘Command on Target’ tab, from step 7.

AVIO specializes in assisting our clients in achieving their strategic goals as soon as possible. What tomorrow looks like has never been so uncertain and that is why AVIO Consulting has built our firm around quickly understanding and delivering our client's critical projects.