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:

1. Create an Interface with an Oracle DB as a target that can handle Inserts and Updates.
2. As part of that Interface I also have to use an ODI Specific Sequence.
3. For the ODI Specific Sequence to work, I have to use ‘IKM SQL to SQL Control Append’, for good reason, per Oracle Knowledge Base Article ID 423872.1 (login required).
4. The IKM mentioned above, ‘IKM SQL to SQL Control Append’ can only handle Inserts.
5. 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:

1. In the ‘Designer’ tab, under ‘Knowledge Modules’, right click ‘IKM SQL to SQL Incremental Update’.
2. Click on ‘Duplicate Selection’ to create a duplicate of this IKM.
3. 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.
4. Click on the Details tab of our new custom IKM.
5. Double click ‘Insert New Rows’ command to edit it.
6. Rename this command to ‘Insert New Rows Custom’ and hit Save.
7. Remove the existing code under ‘Command on Target’ tab and hit Save.
8. In the ‘Designer’ tab, under ‘Knowledge Modules’, open ‘IKM SQL to SQL Control Append’.
9. 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.
10. Paste this code under our ‘Insert New Rows Custom’ ‘Command on Target’ tab, from step 7.
11. I simplified this code a bit.

    insert into <%=snpRef.getTable(“L”,”TARG_NAME”,”A”)%>( <%=snpRef.getColList(“”, “[COL_NAME]”, “,nt”, “”, “((INS and !TRG) and REW)”)%> <%=snpRef.getColList(“,”, “[COL_NAME]”, “,nt”, “”, “((INS and TRG) and REW)”)%>) values ( <%=snpRef.getColList(“”, “:[COL_NAME]”, “,nt”, “”, “((INS and !TRG) and REW)”)%> <%=snpRef.getColList(“,nt”, “[EXPRESSION]”, “,nt”, “”, “((INS and TRG) and REW)”)%>)

12. Hit Save.
13. Click on ‘Command on Source’ tab.
14. Remove the existing code under ‘Command on Source’ tab.
15. Enter the following code in there.select * from <%=snpRef.getTable(“L”,”INT_NAME”,”A”)%> where IND_UPDATE = ‘I’
16. Hit Save and you are done!
17. Use our custom IKM, ‘IKM SQL to SQL Incremental Update Custom’ in our Interface to achieve the scenario mentioned earlier.

I hope the steps mentioned above to create my custom IKM can be used as a sample to create other custom KMs. Go ahead and open up those KMs. They are there to be tinkered with.

Feel free to drop me a line if you have any questions.