X

Welcome to All Things Warehouse Builder

ODI 11g – Interface Builder

David Allan
Architect

In the previous blogs such as the one here I illustrated how to use the SDK to perform interface creation using various auto mapping options for generating 1:1 interfaces either using positional based matching, like names ignoring case and so on. Here we will see another example (download OdiInterfaceBuilder.java) showing a different aspect using a control file which describes the interface in simple primitives which drives the creation. The example uses a tab delimited text file to control the interface creation, but it could be easily taken and changed to drive from Excel, XML or whatever you wanted to capture the design of the interface.

The interface 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;


DirectiveColumn 2Column 3Column 4Column 5
source<model><datastore>  
   can add many    
target<model><datastore>  
mapping<column><expression>  
   can add many    
join<expression>   
   can add many    
filter<expression>   
    can repeat many    
lookup<model><datastore><alias><expression>
   can add many    

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

source    SCOTT    EMP
source    SCOTT    DEPT
target    STG_MODEL_CASE    TGTEMP
mapping    ENAME    UPPER(EMP.ENAME)
mapping    DNAME    UPPER(DEPT.DNAME)
mapping    DEPTNO    ABS(EMP.EMPNO)
join    EMP.DEPTNO = DEPT.DEPTNO
lookup    SCOTT    BONUS    BONUS    BONUS.ENAME = EMP.ENAME
filter    EMP.SAL > 1
mapping    COMM    ABS(BONUS.COMM)

When executed, this generates the interface 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 interface design above.

So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceBuilder;

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

The interface 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.

Anyway some useful snippets of code for those learning the SDK, or for those wanting to capture the design outside and generate ODI Interfaces. Have fun!

Join the discussion

Comments ( 10 )
  • David Tuesday, March 6, 2012

    See this posting from Michael who took the java and did a groovy implementation;

    http://mverzijl.wordpress.com/2012/02/27/odi-11-1-1-6-working-with-groovy-editor/

    Cheers

    David


  • guest Friday, May 4, 2012

    Hi,

    I want to set UD1,UD2.. on target column and also want to use two tables join as Ordered join using SDK code.

    Can any one suggest me on this.Any response is much appreciated.

    Aniket


  • David Friday, May 4, 2012

    Hi There are classes for this, for example to set the UD indicators on a target column use this InterfaceActionOnTargetColumnSetUD, for the join use InterfaceActionOnGenericClauseSetOrder.

    Cheers

    David


  • Aniket Friday, May 18, 2012

    Thanks David for your reply.

    Now i am able to create interface with column label property using SDK.

    Can I execute objects using SDK ?I want to execute model(for reverse engineering )using SDK.

    Please suggest on this.

    Thanks,

    Aniket


  • David Friday, May 18, 2012

    Hi Aniket

    You will have to create a package with a model object etc and do the reverse using this - that's the only way.

    Cheers

    David


  • Aniket Friday, May 18, 2012

    Thanks David.

    It means we can execute only scenarios.

    Is there any way to generate scenario for existing package using SDK.I am not able to find solution for this.

    Thanks,

    Aniket


  • David Friday, May 18, 2012

    Hi Aniket

    You can use OdiScenarioGeneratorImpl to generate the scenario;

    http://odiexperts.com/generate-all-scenario-for-a-particular-project-using-odi-sdk/

    Cheers

    David


  • Vasanth Wednesday, March 14, 2018
    Hi,

    Your article is very good.I have same requirement to create interfaces automatically from excel file.
    Could you please give me interacebuilder.java code to read data from file
  • David Wednesday, March 14, 2018
    Hi Vasanth

    There is code linked at the end of this blog post here (its a 12c version);
    https://blogs.oracle.com/dataintegration/odi-12c-mapping-builder

    Cheers
    DAvid
  • Vasanth Monday, March 19, 2018
    Hi,

    Can you please provide me the same code for 11g version.we need 11g version .

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