Friday Jan 03, 2014

ODI 12c - Components and LKMs/IKMs

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;
  1. DELETE FROM OE.CONTACT_LIST T WHERE (CUSTOMER_ID)
  2. IN (
  3. SELECT   CUSTOMER_ID
  4. FROM OE.ADDRESSES ADDRESSES
  5. WHERE (1=1)   and ( ( ADDRESSES.CUSTOMER_ID ,  ADDRESSES.PHONE_NUMBER )
  6.                = SOME  (  
  7. SELECT 
  8.   CUSTOMERS.CUSTOMER_ID  CUSTOMER_ID ,   CUSTOMERS.CUST_FIRST_NAME  PHONE_NUMBER  
  9. FROM
  10.  OE.CUSTOMERS CUSTOMERS  
  11.  WHERE  CUSTOMERS.MARITAL_STATUS = 'married' AND CUSTOMERS.CREDIT_LIMIT > 1000  AND  ADDRESSES.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID 
  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.

Monday Jan 14, 2013

ODI - Integrating PDF using iText

Integrating PDF form data with ODI is as easy as any other data source,  see the code template from Suraj Bang a few years back with OWB for what I followed and converted to ODI - a few changes for the newer version of iText and to work with ODI. The LKM PDF to SQL uses ODI's File technology as a source and loads to any ANSI SQL store. The LKM reads all of the form fields from a PDF form and supports the File technology's String and Numeric datatype. The fields have to be defined by position, so your field name in the file can be MYCOL_1, MYCOL_2, MYCOL_3 etc. or whatever prefix you desire. The LKM as mentioned uses the iText java api, so the JAR should be downloaded and copied into the normal ODI userlib directory, I tested with version 5.3.5 of iText.

The LKM PDF to SQL is here. I manually defined a File datastore with column names (remembering to use the position as the suffix) and types, the delimiter and such information are not used since its PDF, but I set them anyway. The example I used was to process W4 PDF data, the same as in Suraj's post and was able to process all of the PDFs in the same manner. Kudos to Suraj for the jython, with a few little changes from me to use the latest iText.

About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today