X

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

ODI 12c - Mapping Builder

David Allan
Architect

A few years ago I posted a utility (see interface builder post here) to build interfaces from driver files, here I have updated it for 12c to build mappings from driver files. The example uses a tab delimited text file to control the mapping creation, but it could be easily taken and changed to drive from whatever you wanted to capture the design of the mapping.

The mapping can be as complete or incomplete as you’d like, so could just contain the objects or could be concise and semantically complete.

The control file is VERY simple and just like ODI requests the minimal amount of information required. The basic format is as follows;So for example the control file below can define the sources, target, joins, mapping expressions etc;

Directive Column2 Column3 Column4 Column5 Column6
 source  <model>  <datastore>  <alias>
 .....can add many
 target  <model>  <datastore>
 filter  <filter_condition>  <filter_datastore_alias>  <alias>
 lookup  <model> <datastore>  <driver_alias> <lookup_condition>  <alias>
 join  <join_condition>  <alias>
 ....can add many of the components above.
 mapping  <column>  <expression>

So for example the control file below can define the sources, target, joins, mapping expressions etc;

  • source SOURCE EMP EMP
  • source SOURCE DEPT DEPT
  • target TARGET_MODEL TGTEMP
  • join EMP.DEPTNO = DEPT.DEPTNO AJOIN
  • filter EMP.SAL > 1 EMP AFILTER
  • lookup SOURCE BONUS EMP BONUS.ENAME = EMP.ENAME ALOOKUP
  • mapping ENAME UPPER(EMP.ENAME) 
  • mapping DEPTNO ABS(DEPT.DEPTNO) 
  • mapping COMM ABS(BONUS.COMM)

When executed, this generates the mapping below with the join, filter, lookup and target expressions from the file;


You should be able to join the dots between the control file sample and the mapping design above. You will need to compile and execute the code in OdiMappingBuilder;

java –classpath <cp> OdinterfaceBuilder jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd DEMOS SDK DEMO1 < mymappingcontrolfile.tab

The mapping to be created is passed from the command line. You can intersperse other documentation lines between the control lines so long as the control keywords in first column don’t clash. See the driver file below viewed from within Excel;

Anyway some useful snippets of code for those learning the SDK (download OdiMappingBuilder here), or for those wanting to capture the design outside and generate ODI mappings. Have fun!

Join the discussion

Comments ( 15 )
  • Venkat Gali Thursday, April 10, 2014

    Thanks David,

    Is it possible to develop all types of transformations of ODI mappings using ODI SDK and Groovy at this moment in ODI 11 and 12C, including assigning LKM, IKM, CKM etc.?

    Also is it possible to write a summary blog on this topic of Accelerated ODI Development with the tools and technologies available as of today?

    Thanks

    Venkat Gali


  • Ramunaidu Friday, December 5, 2014

    hi,

    how 2 write odi java sdk code for creating mapping in odi 12c..

    thanks,


  • David Friday, December 5, 2014

    There are examples linked from the following blog;

    https://blogs.oracle.com/dataintegration/entry/odi_12c_pivoting_data_and

    Cheers

    David


  • Ramunaidu Thursday, December 11, 2014

    Hi david,

    could you explain how 2 run this above example(Odi 12c Mapping builder)

    thanks


  • David Thursday, December 11, 2014

    This is how you can run it, specify the JARs needed in the classpath, the following are the ODI related ones. I had installed my software in C:\Oracle\ODI_12C

    java -classpath C:\Oracle\ODI_12C\oracle_common\modules\oracle.idm_12.1.3\identitystore.jar;C:\Oracle\ODI_12C\oracle_common\modules\oracle.jdbc_12.1.0\ojdbc6.jar;C:\Oracle\ODI_12C\oracle_common\modules\oracle.jps_12.1.3\jps-api.jar;lib\oracle.odi-sdk-jse_12.1.3.jar;. OdiMappingBuilder ...parameters....

    Cheers

    David


  • David Thursday, December 18, 2014
  • Ramunaidu Friday, December 19, 2014

    Hi David,

    Now it's working fine,Thank you so much for sharing your information..

    This Blog is very helpful for me.

    Thanks


  • Ramunaidu Friday, January 9, 2015

    hi,

    could you please tell me what are the new classes introduced instead of Sourceset and Sourcedatastore classes in odi 12c, these classes are deprecated in odi 12c .


  • David Allan Friday, January 9, 2015

    In 12c, the class Execution Unit is as equivalent a class as the 11g SourceSet. It's on execution units that we define execution location, we can assign KMs and get detailed information about the physical nodes with further refinement possible. The source datastore is replaced by MappingComponent and Dataset for equivalent dataset capability as 12c.

    Cheers

    David


  • David Monday, January 12, 2015

    There is a blog giving an overview of the SDK below showing some of the high level conceptual classes in the logical and physical design areas, worth having a read;

    https://blogs.oracle.com/dataintegration/entry/odi_12c_mapping_sdk_overview

    Also the Eclipse configuration is blogged below;

    https://blogs.oracle.com/dataintegration/entry/odi_12c_eclipse_and_updated

    Cheers

    David


  • guest Monday, July 6, 2015

    Hi David,

    I read all the SDK code, i am building the mapping from SDK, can you please let me know which java class and java method to be use for automapping, i am using ODI12c. there are some code for auto mapping on the net but i wanted to know is there any class and method availble.

    Please let me know

    thanks


  • guest Monday, May 30, 2016

    Hi Dave,

    Great post and I'm using Eclipse to run this also, does .tab config file need to reside on the DB server as it can't be seen using the client side path I am using.

    Paul


  • David Tuesday, May 31, 2016

    Hi Paul

    If you are running just from eclipse client then no, the file should not be on the server. It uses java's FileInputStream and passes the control file as a parameter, so you should be able to give it a fully qualified path name.

    Cheers

    David


  • guest Sunday, September 11, 2016

    Hi David,

    How do I do automapping from source to target in odi 12c using api references in groovy script.Can you please help me with some examples for this please.

    Thanks,

    MK


  • David Monday, September 12, 2016
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.