X

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

ODI 12c - Mapping SDK the ins and outs

By: David Allan | Architect

The ODI 12c SDK provides a mechanism to accelerate data integration development using patterns and the APIs in the SDK. With OWB many customers automated mundane tasks - tasks that were repeated and consistent. I've uploaded a bunch of examples covering many characteristics of the 12c mapping SDK, the examples are primarily slanted towards the logical side right now. You can find examples of the components and images for each on the java.net site (download ODI_12c_Mappings_SDK Examples here unzip and inspect the groovy). This is an open community area where you too can contribute.

The mappings I have covered include (click on hyperlink for an image of each);

 

  1. basic datastore to datastore mapping with configuration of the LKM and IKM on the deployment spec which options set (datastore component)
  2. simple filter example writing to a target (filter component).
  3. datastores being joined, with a left outer join property set then written to a target (join component).
  4. data being looked up from a reference table using the (lookup component).
  5. data being aggregated, SUM aggregation used and data automatically grouped (aggregate component).
  6. defining expressions which may be reused across many map parts, also useful for explicitly seeing expressions in canvas (expression component).
  7. unifying data using the relational set component to UNION/MINUS data (set component).
  8. if then/else capabilities using the split component to define branches (split component).
  9. defining a dataset to source data from, defines joins and filters (dataset component)
  10. deduplicating data with distinct (distinct component).

These examples all use the mapping SDK to build data integration flows, the existing ODI interface SDK is still supported for now. I plan on more examples, plenty more to show including some of the nitty gritty details on the physical deployment specification side. I'd also like to show the mapping accelerators, I posted some while back this kind of capability for 11g (see here).

If you are brand new to the ODI SDK, I tend to break down the labyrinth of classes into the following sections (I blogged about this for 11g here);

Entry to the Platform

Object Finder SDK
odiInstance odiInstance (groovy variable for console) OdiInstance

Topology Objects

Object Finder SDK
Technology IOdiTechnologyFinder OdiTechnology
Context IOdiContextFinder OdiContext
Logical Schema IOdiLogicalSchemaFinder OdiLogicalSchema
Data Server IOdiDataServerFinder OdiDataServer
Physical Schema IOdiPhysicalSchemaFinder OdiPhysicalSchema
Logical Schema to Physical Mapping IOdiContextualSchemaMappingFinder OdiContextualSchemaMapping
Logical Agent IOdiLogicalAgentFinder OdiLogicalAgent
Physical Agent IOdiPhysicalAgentFinder OdiPhysicalAgent
Logical Agent to Physical Mapping IOdiContextualAgentMappingFinder OdiContextualAgentMapping
Master Repository IOdiMasterRepositoryInfoFinder OdiMasterRepositoryInfo
Work Repository IOdiWorkRepositoryInfoFinder OdiWorkRepositoryInfo

Project Objects

Object Finder SDK
Project IOdiProjectFinder OdiProject
Folder IOdiFolderFinder OdiFolder
Mapping IMappingFinder Mapping
Package IOdiPackageFinder OdiPackage
Procedure IOdiUserProcedureFinder OdiUserProcedure
User Function IOdiUserFunctionFinder OdiUserFunction
Variable IOdiVariableFinder OdiVariable
Sequence IOdiSequenceFinder OdiSequence
KM IOdiKMFinder OdiKM

Load Plans and Scenarios

 

Object Finder SDK
Load Plan IOdiLoadPlanFinder OdiLoadPlan
Load Plan and Scenario Folder IOdiScenarioFolderFinder OdiScenarioFolder

Model Objects

Object Finder SDK
Model IOdiModelFinder OdiModel
Sub Model IOdiSubModel OdiSubModel
DataStore IOdiDataStoreFinder OdiDataStore
Column IOdiColumnFinder OdiColumn
Key IOdiKeyFinder OdiKey
Condition IOdiConditionFinder OdiCondition

Operator Objects

 

Object Finder SDK
Session Folder IOdiSessionFolderFinder OdiSessionFolder
Session IOdiSessionFinder OdiSession
Schedule   OdiSchedule

 

How to Create an Object?

Here is a simple example to create a project, it uses IOdiEntityManager.persist to persist the object.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

project = new OdiProject("Project For Demo", "PROJECT_DEMO")
odiInstance.getTransactionalEntityManager().persist(project)
tm.commit(txnStatus)

How to Update an Object?

This update example uses the methods on the OdiProject object to change the project’s name that was created above, it is then persisted.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

prjFinder = (IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class);
project = prjFinder.findByCode("PROJECT_DEMO");

project.setName("A Demo Project");

odiInstance.getTransactionalEntityManager().persist(project)
tm.commit(txnStatus)

How to Delete an Object?

Here is a simple example to delete all of the sessions, it uses IOdiEntityManager.remove to delete the object.

import oracle.odi.domain.runtime.session.finder.IOdiSessionFinder;
import oracle.odi.domain.runtime.session.OdiSession;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

sessFinder = (IOdiSessionFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiSession.class);
sessc = sessFinder.findAll();
sessItr = sessc.iterator()
while (sessItr.hasNext()) {
  sess = (OdiSession) sessItr.next()
  odiInstance.getTransactionalEntityManager().remove(sess)
}
tm.commit(txnStatus)

Hopefully these examples will get you on your way. This isn't an all encompassing summary of the SDK, but covers a lot of the content to give you a good handle on the objects and how they work. The mappings examples are a good start, more to come on those, remember and check out the ODI area on OTN for the examples (here). Have fun, happy coding

 

 

 

 

 

 

Join the discussion

Comments ( 16 )
  • Dan Jackson Wednesday, February 5, 2014

    Thanks I was resigned to trawling through the SDK guide, and attempting to figure this out myself. This has saved the day. Stupidly assumed my old 11g interface code would work.

    Cheers Dan


  • David Wednesday, February 5, 2014

    Hi Dan

    I'm glad it was useful, but....I'm interested to know which parts were not working. Could you send me some info?

    Cheers

    David


  • Bhabani Wednesday, April 9, 2014

    Hi David,

    How can we get the steps under childrenstep in a loadplan.

    odilplan.getRootStep().getChildrenSteps();

    For example: root_step-->Serial_Step--->Scenario1

    Thanks


  • David Wednesday, April 9, 2014

    Hi Bhabani

    There is a special load plan step which is a container (OdiLoadPlanStepContainer), you would have to check if the step was of that type and then get the children from that and then recurse. ....if I understand your question that is.

    Cheers

    David


  • Bhabani Thursday, April 10, 2014

    Hi David,

    Thanks for your time and as usual you are correct. I had seen the getChildren method under OdiLoadPlanStepContainer but couldnot make it to find the steps under children steps. Finally i did a typecast and it worked.

    http://dwteam.in/disableenable-odi-loadplan-step-using-sdk/


  • Daniel Tuesday, September 30, 2014

    Hi David,

    Very useful article.

    I am looking for a way to get the status of the agent whether it is running or not.

    Is there a Java API or a script in ODI 12c that can be used?

    Thank you

    Daniel


  • Ramunaidu Friday, December 5, 2014

    HI,

    I want 2 know how 2 create mapping in odi 12c through java code.

    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


  • guest Monday, December 28, 2015

    What are the alternative ?

    for e.g:- In Odi 11g, we use InterfaceActionAddJoin to create a join between two or more sources in an ODI interface data set.

    What can be used to create a join between two or more sources in an ODI interface data set in odi 12c?

    Please answer as early as possible


  • guest Monday, January 4, 2016

    Check the example here for examples creating components;

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

    You can create a join without a dataset using the helper method joinSources in the JoinComponent class;

    https://docs.oracle.com/middleware/1213/odi/reference-java-api/oracle/odi/domain/mapping/component/JoinComponent.html#joinSources(oracle.odi.domain.mapping.IMapComponent, oracle.odi.domain.mapping.IMapComponent, java.lang.String)

    Cheers

    David


  • Kalai Tuesday, September 20, 2016

    Hi David,

    I am trying to create an ODI interface in ODI 12.1.3 by controlling the source table name and the target table name in a control file(text file each field seperated with ;) with the below code.I am able to create an interface ,also the source table gets mapped into the mapping.

    But the issue is target table not getting mapped in the interface as one of the datastore component.

    Here is the code which i used:

    ********************

    import java.util.List;

    import java.io.BufferedReader;

    import oracle.odi.core.OdiInstance;

    import oracle.odi.core.config.OdiInstanceConfig;

    import oracle.odi.core.config.MasterRepositoryDbInfo;

    import oracle.odi.core.config.WorkRepositoryDbInfo;

    import oracle.odi.core.security.Authentication;

    import oracle.odi.core.config.PoolingAttributes;

    import groovy.io.FileType

    import oracle.odi.domain.project.OdiProject;

    import oracle.odi.domain.project.finder.IOdiProjectFinder;

    import oracle.odi.domain.project.finder.IOdiFolderFinder;

    import oracle.odi.domain.model.finder.IOdiDataStoreFinder;

    import oracle.odi.domain.topology.finder.IOdiContextFinder;

    import oracle.odi.domain.project.OdiFolder;

    import oracle.odi.domain.model.OdiDataStore;

    import oracle.odi.domain.topology.OdiContext;

    import oracle.odi.core.persistence.transaction.ITransactionStatus;

    import oracle.odi.core.persistence.transaction.ITransactionManager;

    import oracle.odi.core.persistence.transaction.ITransactionDefinition;

    import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

    import oracle.odi.domain.project.finder.IOdiKMFinder

    import oracle.odi.domain.mapping.finder.IMappingFinder;

    import oracle.odi.domain.mapping.Mapping;

    import oracle.odi.domain.mapping.MapComponent;

    import oracle.odi.domain.mapping.MapAttribute;

    import oracle.odi.domain.mapping.MapConnectorPoint;

    import oracle.odi.domain.mapping.component.DatastoreComponent;

    import oracle.odi.domain.mapping.component.JoinComponent;

    import oracle.odi.domain.mapping.component.FilterComponent;

    import oracle.odi.domain.mapping.component.LookupComponent;

    import oracle.odi.domain.mapping.component.SetComponent;

    import oracle.odi.domain.mapping.component.Dataset;

    import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

    import oracle.odi.domain.project.OdiProject

    import oracle.odi.domain.project.finder.IOdiProjectFinder

    import oracle.odi.domain.model.finder.IOdiDataStoreFinder

    import oracle.odi.domain.project.finder.IOdiFolderFinder

    import oracle.odi.domain.project.finder.IOdiKMFinder

    import oracle.odi.domain.mapping.finder.IMappingFinder

    import oracle.odi.domain.adapter.project.IKnowledgeModule.ProcessingType

    import oracle.odi.domain.model.OdiDataStore

    import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition

    import oracle.odi.domain.adapter.relational.IDataStore

    import java.io.BufferedReader;

    import java.io.FileInputStream;

    import java.io.FileNotFoundException;

    import java.io.IOException;

    import java.io.InputStreamReader;

    import java.util.logging.Level;

    import java.util.logging.Logger;

    def project_name="GRO_PROJECT3"

    def project_folder_name="NEW_FOLDER1"

    txnDef = new DefaultTransactionDefinition()

    tm = odiInstance.getTransactionManager()

    tm1 = odiInstance.getTransactionManager()

    tme = odiInstance.getTransactionalEntityManager()

    /*println "Mapping Created SUccessfully"+project+"with the folder"+folder*/

    // Get default context:

    OdiContext context = ((IOdiContextFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).findDefaultContext();

    Collection odiFolders = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(project_folder_name);

    if (odiFolders.size() == 1)

    {

    println("Suuccess: find folder "+project_folder_name+" in project "+project_name)

    }

    OdiFolder odiFolder = (OdiFolder) (odiFolders.toArray()[0]);

    println ("Folder for interface = " + odiFolder.getName())

    println ("Folder for context = " + context.getName())

    def list = []

    def dir = new File("D:/New folder (2)")

    dir.eachFileRecurse (FileType.FILES) {

    file1 ->

    filename=file1.getName().split("\\.")[0]

    println filename

    }

    println filename

    txnStatus = tm.getTransaction(txnDef)

    map = new Mapping(filename, odiFolder)

    odiInstance.getTransactionalEntityManager().persist(map);

    tme.persist(map)

    println "Mapping Created Successfully"

    def FileInputStream fis;

    def BufferedReader reader ;

    fis = new FileInputStream("D:/New folder (2)/new.txt");

    reader = new BufferedReader(new InputStreamReader(fis));

    println("Reading File line by line using BufferedReader");

    String line;

    while((line = reader.readLine())!=null)

    {

    if (line.length() == 0) continue;

    try {

    String[] toks = line.split(";");

    String cmd = toks[0];

    println("toks[0]" +cmd);

    println("line"+line);

    if (cmd.compareTo("source") == 0)

    {

    String srcmodel = toks[1];

    String srctab = toks[2];

    println "srctab"+ srctab

    println "mapping name"+ map

    dsf = (IOdiDataStoreFinder)tme.getFinder(OdiDataStore.class)

    ds_source = dsf.findByName(srctab, srcmodel)

    println "odiDatastore1 name"+ ds_source

    ds_src_comp = new DatastoreComponent(map, ds_source)

    println "odiDatastore1 name"+ ds_src_comp

    tme.persist(map)

    tm.commit(txnStatus);

    }

    if (cmd.compareTo("target") == 0) {

    String tgtmodel = toks[1];

    String tgttab = toks[2];

    println "tgtmodel"+ tgtmodel

    println "mapping name"+ map

    println "target name -"+"'"+tgttab+"'"

    dsf = (IOdiDataStoreFinder)tme.getFinder(OdiDataStore.class)

    println "odiDatastore2 name"+ dsf

    ds_target = dsf.findByName(tgttab, tgtmodel)

    println "odiDatastore2 name"+ ds_target

    println "mapping name"+ map

    ds_tgt_comp = new DatastoreComponent(map,ds_target)

    println "source name"+ ds_src_comp

    println "target name"+ ds_tgt_comp

    ds_src_comp.connectTo(ds_tgt_comp)

    println "target name"+ ds_tgt_comp

    tme.persist(map)

    tm.commit(txnStatus);

    println "odiDatastore2 name"+ ds_tgt_comp

    println "odiDatastore2 name"+ ds_tgt_comp

    }

    }

    catch (Exception e) {

    e.printStackTrace();

    }

    println "end line name"

    }

    ***********************

    Control file

    ***************

    source;USERSRC;DEPT

    target;USERDEST;DEPT_TARGET

    ***************

    Println

    *****************

    Suuccess: find folder NEW_FOLDER1 in project GRO_PROJECT3

    Folder for interface = NEW_FOLDER1

    Folder for context = Development

    NEW

    NEW

    Mapping Created Successfully

    Reading File line by line using BufferedReader

    toks[0]source

    linesource;USERSRC;DEPT

    srctabDEPT

    mapping nameMapping NEW[6158] owning folder=GRO_PROJECT3.NEW_FOLDER1

    odiDatastore1 nameoracle.odi.domain.model.OdiDataStore DEPT

    odiDatastore1 nameMapComponent (DATASTORE) DEPT[null]

    end line name

    toks[0]target

    linetarget;USERDEST;DEPT_TARGET

    tgtmodelUSERDEST

    mapping nameMapping NEW[6158] owning folder=GRO_PROJECT3.NEW_FOLDER1

    target name -'DEPT_TARGET'

    odiDatastore2 nameoracle.odi.domain.model.finder.toplink.OdiDataStoreFinderImpl@18e367c

    odiDatastore2 nameoracle.odi.domain.model.OdiDataStore DEPT_TARGET

    mapping nameMapping NEW[6158] owning folder=GRO_PROJECT3.NEW_FOLDER1

    source nameMapComponent (DATASTORE) DEPT[22947]

    target nameMapComponent (DATASTORE) DEPT_TARGET[null]

    target nameMapComponent (DATASTORE) DEPT_TARGET[null]

    end line name

    Script exited.

    ****************

    Note:All the println are printed.Project and the folder are created already.Model folders are present with the correct name.

    I am using teh control file name as the interface name.I have tried by changing multiple options for the past 2 days,but no luck.Could you please help me to figure out this issue .Thanks in Advance.


  • David Tuesday, September 20, 2016

    Hi Kalai

    Not sure what you mean by not getting target mapped exactly.

    This current blog has details in the groovy examples to map explicitly column for column in the target

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

    This one below has examples for auto mapping attributes which is useful;

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

    Cheers

    David


  • MK Wednesday, September 21, 2016

    Hi David,

    Actually i am trying to automate the mapping creation in odi 12.1.3 using groovy ,by controlling the source table name and target table name in a text file.

    The script which i provided in my last post ran successfully,It created a mapping with the mentioned source datastore in the text file but the target datastore is not brought in automatically once the groovy script runs successfully.

    Thanks,

    MK


  • Sunil Thursday, August 17, 2017
    Hi David, I see that the java.net site is closed and the url to download the codes is no longer active
    (https://java.net/projects/oracledi/downloads/download/ODI/SDK%20Samples/ODI%2012c/odi12c_mapping_sdk.zip).
    Can you please point me to the latest path where the sdk code is present for download.
  • Onur Tuesday, October 17, 2017
    Hi David,

    highly appreciate your effort on sharing your knowledge with the community. As I'm a beginner in this topic, I hoped I could learn more from the uploaded groovy samples. However, since the java.net site has been migrated recently, the uploaded file is no longer available. Would it be possible to upload it on a different platform?

    Thank you, Br
  • David Tuesday, January 16, 2018
    I've updated the blog, I'm hoping you found the links in the meantime.

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

Integrated Cloud Applications & Platform Services