Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

ODI 12c - Components and LKMs/IKMs

David Allan

Here I'd like to illustrate some nice capabilities of ODI 12c's knowledge module framework in combination with the new component based mapper. Some of this was prompted from recent questions from Holger Friedrich (on migration from OWB..'where is delete'), Stewart Bryson and Mark Rittman (on components and KMs here) and a general nagging thought I had that people were generally unaware of the capabilities. There's a lot more we can illustrate and talk about in this area, so this is a little taster...

Customized loading, integration and error management can be defined using knowledge modules. There is a text based substitution library that makes building such knowledge modules very easy - especially with the abundance of examples - plus the substitution reference documentation is a good reference guide (intro here, reference here). This can be used in harmony with ODI 12c components. In 12c we have modularized a lot and introduced components (components are logical and describe the WHAT of the transformation, component KMs describe the HOW, just like regular KMs), also we have formalized some parts of the framework but it is still as open as ever.

In supporting the odiRef substitution library we have chosen a similar approach to how the sub-select worked in 11g. If you want to build your own integration you can use the odiRef methods to get the target shape, details, connection etc. The source for the target, just like in 11g is provided via the odiRef methods also and may be rolled into the odiRef.getFrom method.

To illustrate, if you want to perform an integration to do a delete operation, then the following snippet is sufficient - in 11g or 12c (the code in red is boiler plate). In ODI 12c you can now have an arbitrary mapping graph leading up to the target;

  1. DELETE FROM <%=odiRef.getTable("L","TARG_NAME","A")%> T
  2. WHERE (<%=odiRef.getTargetColList("", "[COL_NAME]", ", ", "\n", "UK")%> )
  3. IN
  4. (
  5. select * from (
  6.  <%for (int i=odiRef.getDataSetMin(); i <= odiRef.getDataSetMax(); i++){%>
  7.  <%=odiRef.getDataSet(i, "Operator")%>
  8.    SELECT  <%=snpRef.getColList(i,"", "\t[COL_NAME]", ",\n", "", "UK")%>
  9.    FROM <%=odiRef.getFrom(i)%>
  10.    WHERE (1=1) <%=snpRef.getJoin(i)%> <%=snpRef.getFilter(i)%> <%=snpRef.getGrpBy(i)%> <%=snpRef.getHaving(i)%>
  11.  <%}%>
  12. ) S
  13. )

For example in the mapping below, I am using the subquery filter component to identify some rows that I then want to DELETE from the CONTACT_LIST target. The integration type property on target datastores was introduced in 12c and supports a set number of integrations to help filter the IKMs, if you select None, you can pick any IKM applicable for the technology.

If you look at the physical design for the above mapping you can now pick the IKM which performs the delete, the subquery filter also has a component KM which produces code, this code is rolled into the odiRef methods in the template. This then lets arbitrary map designs to be created and still build customized KMs for loading, integrating and error management. The odiRef substitution methods are simple text based APIs for producing text based on simple primitives.

This then produces the following SQL statement to perform the DELETE DML, our IKM has no knowledge of how to construct subquery filter, pivot or any other complex transformation - the code has been modularized. The code produced is below;
  2. IN (
  6.                = SOME  (  
  7. SELECT 
  9. FROM
  12.     )
  13.    )  
  14. )

Another useful illustration of things working in harmony is related to the improved code generation for Oracle connectivity - the basics of database links have been greatly improved (no more requirements for view on source, but still supported). The framework change in order to support this will have more use cases down the line too. Now you can use the 12c database link LKMs to pull data from tables over a database link and leverage custom IKMs on the target. One of the 11g issues was related to the requirements the 11g Oracle to Oracle database link LKM placed on users, this has been greatly improved, you can now take advantage of this, plus use existing IKMs, build new ones etc.

The mapping above uses new 12c LKMs to access remote Oracle datastores and a customized IKM to illustrate the mix and match capabilities. 

Hopefully this gives you some background and insight into the ODI 12c mapping and knowledge module capabilities that you weren't aware of. Looking forward to any questions and additional ideas, insights that you have.

Join the discussion

Comments ( 4 )
  • Jeff Saturday, February 1, 2014

    This component KM looks very cool. I presume that the sub query filter component is a custom built one as opposed to the standard one that come with the core product. If so, are you able to share the sub query filter component over at the java.net oracledi project?

  • David Saturday, February 1, 2014

    Hi Jeff

    The subquery filter, table function, pivot and unpivot are included in the ODI patch I blogged below;

    The ODI patch is for regular ODI developers too (the components were in OWB).



  • Mark Thursday, March 27, 2014

    Hi David,

    I want to use the unpivot component on oracle side and load it into MySQL.

    What LKM and IKM need to be used ?



  • David Thursday, March 27, 2014

    Hi Mark

    You can have the UNPIVOT component in the source (Oracle) execution unit and the KM would be using the Oracle Unpivot KM for this component, see the image below;


    The MySQL target can use any applicable IKM for MySQL, below you can see it is using the 12c insert statement but it could use any applicable one such as IKM SQL Control Append etc.;


    You can drag and drop the unpivot component from execution unit to execution unit if you wanted the transformation to execute on MySQL for example.



Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.