Howdy guys!

Keeping the Java series, here is more one Java Code for ODI.

The below codes is used to create an Interface that can create Multiple Dataset depending on the source
datastore and Operator provided.
For example – Here
String[] source_datastore={“REGIONS”,”REGIONS”,”REGIONS”};

we are using three different source tables ,you can change them and provide any number of Source datastore in the array and accordingly n number of the Dataset are created.

String[] operator={“UNION”,”MINUS”};

For n number of the source database , n-1 number of the Operator needs to be provided.
For example if there are 4 different source datastore , then 3 operator need to provided which can be same or different depending on your requirement.

package odi.sdk;

import java.util.Collection;
import java.util.Iterator;

import oracle.odi.core.OdiInstance;
import oracle.odi.core.config.MasterRepositoryDbInfo;
import oracle.odi.core.config.OdiInstanceConfig;
import oracle.odi.core.config.PoolingAttributes;
import oracle.odi.core.config.WorkRepositoryDbInfo;
import oracle.odi.core.exception.OdiRuntimeException;
import oracle.odi.core.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.core.security.Authentication;
import oracle.odi.domain.model.OdiDataStore;
import oracle.odi.domain.model.finder.IOdiDataStoreFinder;
import oracle.odi.domain.project.OdiCKM;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiIKM;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiLKM;
import oracle.odi.domain.project.ProcedureOption;
import oracle.odi.domain.project.finder.IOdiCKMFinder;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiIKMFinder;
import oracle.odi.domain.project.finder.IOdiLKMFinder;
import oracle.odi.domain.project.interfaces.DataSet;
import oracle.odi.domain.project.interfaces.SourceDataStore;
import oracle.odi.domain.project.interfaces.SourceSet;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.interfaces.interactive.support.InteractiveInterfaceHelperWithActions;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetDataStoreComputeAutoMapping;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKM.KMType;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetKMOptionValue;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetTargetDataStore;
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerHomonymy;
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerLazy;
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerLazy;
import oracle.odi.interfaces.interactive.support.mapping.matchpolicy.MappingMatchPolicyLazy;
import oracle.odi.interfaces.interactive.support.sourceset.creators.InexistentMappingException;
import oracle.odi.interfaces.interactive.support.targetkeychoosers.TargetKeyChooserPrimaryKey;

public class PermanentInterfaceWithUnion {

    private static String Project_Code;
    private static String Folder_Name;
    private static OdiFolder folder;
    private static String Context_Code;
    private static OdiContext context;
    private static OdiDataStore sourceDatastore;
    private static String target_model_name;
    private static String source_model_name;
    private static String LKM;
    private static String IKM;
    private static String CKM;
    private static String target_datastore;
    private static SourceSet srcset;
    private static SourceDataStore sd;
    private static DataSet dataset;

    /**
     * @param args
     */
    public static void main(String[] args) {

              /****** Please change these Parameters *********/

        String Url = "jdbc:oracle:thin:@localhost:1521:xe";
        String Driver="oracle.jdbc.OracleDriver";
        String Master_User="ODI_MASTER_11G";
        String Master_Pass="ODI_MASTER_11G";
        String WorkRep="WORKREP1";
        String Odi_User="SUPERVISOR";
        String Odi_Pass="SUNOPSIS";

        Project_Code="XMT";
        Context_Code="XMT";
        Folder_Name="FOLDER";
        source_model_name = "SRCE_HR";
        String[] source_datastore={"REGIONS","REGIONS","REGIONS"};
        String[] operator={"UNION","MINUS"};
        target_model_name = "TRGT_HR";
        target_datastore="REGIONS";

        LKM ="LKM SQL to Oracle";
        IKM ="IKM SQL Control Append";
        CKM ="CKM Oracle";

                /*****************************/

        // Connection
MasterRepositoryDbInfo masterInfo = new MasterRepositoryDbInfo(Url, Driver, Master_User,Master_Pass.toCharArray(), new PoolingAttributes());
WorkRepositoryDbInfo workInfo = new WorkRepositoryDbInfo(WorkRep, new PoolingAttributes());
OdiInstance odiInstance=OdiInstance.createInstance(new OdiInstanceConfig(masterInfo,workInfo));
Authentication auth = odiInstance.getSecurityManager().createAuthentication(Odi_User,Odi_Pass.toCharArray());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);
ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());

        // Find the folder

        Collection<OdiFolder> fold = ((IOdiFolderFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(Folder_Name);
        for (Iterator<OdiFolder> it = fold.iterator(); it.hasNext();) {
            folder = (OdiFolder) it.next();
        }

        // Find the Context
        context = ((IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).findByCode(Context_Code);

        OdiDataStore targetDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().
                getFinder(OdiDataStore.class)).findByName(target_datastore, target_model_name);

        System.out.println("Interface Creation Started for ..."+ target_datastore);
        // Creating a New Interface
        OdiInterface intf = new OdiInterface(folder, target_datastore, context);

        // Setting the above Context as the Optimization Context
                intf.setOptimizationContext(context);

        // Reading the Source Data Store
        // Find the Data store using the IOdiDataStoreFinder
        int order=0;
        for (int i=0 ;i<source_datastore.length ;i++) {

            sourceDatastore = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).
                    findByName(source_datastore[i], source_model_name);

            // Creating DataSet to automatically assign different
            // Source Data store
            // DataSet(OdiInterface pInterface, java.lang.String pName)
            if (i == 0) {
                dataset = intf.getDataSets().iterator().next();
                dataset.setName("DATASET"+i);

            } else {
            dataset=new DataSet(intf,"DATASET"+i);
            dataset.setOperator(operator[i-1]);
            dataset.setOrder(order);
            }

            srcset = new SourceSet("SrcSet01",dataset);
            sd=new SourceDataStore(dataset,false,sourceDatastore.getName().toString(),0,sourceDatastore);
            srcset.addSourceDataStore(sd);
            dataset.addSourceSet(srcset);
            order+=10;

        }

        // Helper is to manipulate Odi interfaces in an
        // interactive way

        InteractiveInterfaceHelperWithActions helper = new InteractiveInterfaceHelperWithActions
                (intf, odiInstance, odiInstance.getTransactionalEntityManager());

        helper.performAction(new InterfaceActionSetTargetDataStore(
                targetDatastore, new MappingMatchPolicyLazy(),
                new AutoMappingComputerLazy(),
                new AutoMappingComputerLazy(),
                new TargetKeyChooserPrimaryKey()));

        helper.performAction(new InterfaceActionOnTargetDataStoreComputeAutoMapping());

        // Add the Filter
        //helper.performAction(new InterfaceActionAddFilter(dataset, sd.getName(),ExecutionLocation.WORK));

        // Start mapping the KM
        // LKM
        Collection<OdiLKM> lkm1 = ((IOdiLKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLKM.class)).findByName(
                        LKM,Project_Code);

        for (Iterator<OdiLKM> iterator = lkm1.iterator(); iterator.hasNext();) {
            OdiLKM odiLKM = (OdiLKM) iterator.next();
            helper.performAction(new InterfaceActionSetKM(odiLKM,srcset, KMType.LKM,new KMOptionRetainerHomonymy()));

            // Fetching each option of the LKM
            for (ProcedureOption c : odiLKM.getOptions()) {
                helper.performAction(new InterfaceActionSetKMOptionValue(srcset, KMType.LKM,"DELETE_TEMPORARY_INDEXES", true));
            }
        }

        // IKM
        // Find the IKM using the IOdiIKMFinder

        Collection<OdiIKM> ikm1 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(
                        IKM,Project_Code);

        for (Iterator<OdiIKM> iterator = ikm1.iterator(); iterator.hasNext();) {
            OdiIKM odiIKM = (OdiIKM) iterator.next();

            // Setting the IKM in the interface
            helper.performAction(new InterfaceActionSetKM(odiIKM, intf.getTargetDataStore(),KMType.IKM, new KMOptionRetainerLazy()));

            // Fetching each option of the IKM
            for (ProcedureOption c : odiIKM.getOptions()) {

                // Modifying the Options of the IKM in the
                // Interface
                helper.performAction(new InterfaceActionSetKMOptionValue(intf.getTargetDataStore(), KMType.IKM,
                        "FLOW_CONTROL", false));

                helper.performAction(new InterfaceActionSetKMOptionValue(intf.getTargetDataStore(), KMType.IKM,
                        "STATIC_CONTROL", true));

            }
        }

        // CKM
        Collection<OdiCKM> ckm1 = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(
                CKM,Project_Code);

        for (Iterator<OdiCKM> iterator = ckm1.iterator(); iterator.hasNext();) {
            OdiCKM odiCKM = (OdiCKM) iterator.next();
            helper.performAction(new InterfaceActionSetKM(odiCKM, intf.getTargetDataStore(),KMType.CKM, new KMOptionRetainerLazy()));
        }

        // Compute the Interface sourceset

        try {helper.computeSourceSets();}
        catch (InexistentMappingException e) {throw new OdiRuntimeException(e);}

        // Persisting the Interface
        // Called to inform the ODI persistence layer that this
        // interface will be persisted
        try {helper.preparePersist();}
        catch (oracle.odi.interfaces.interactive.exceptions.OdiInterfaceNotReadyForPersistException e) {e.printStackTrace();}

        odiInstance.getTransactionalEntityManager().persist(intf);

        /*// Generating Scenarios

        System.out.println("Generating Scenario for .."+ target_datastore );
        IOdiScenarioGenerator gene = new OdiScenarioGeneratorImpl(odiInstance);
        OdiScenario newScen = gene.generateScenario(intf,target_datastore, "001");
        odiInstance.getTransactionalEntityManager().persist(newScen);*/

        // Finally close the Instance
        odiInstance.getTransactionManager().commit(trans);
        odiInstance.close();

        System.out.println("Process Completed");
    }

}

As you know, comments are always really welcome!

Good to see you here.

Hello Friends ,

We have shows you few important features of the new ODI 11g ,today we are going to show you how to implement Union , Minus etc logic in ODI 11g which was lacking in older version and thus maturing ODI in the ETL world.

After the initial step of the Interface name ,Click on the Add/Remove Dataset

Rename the Dataset Name to Source 1, Source 2 for more meaningful or else you can provide the Table name too and in the Second Dataset name select the Operator as Union.

Drag in the Source tables and Target table and map the columns accordingly.

Go the Flow tab and select the Staging Area and select the appropriate LKM and IKM.

[ since my Source are in the Same database so no LKM ) and Execute

ODI 11g provide a new feature called Simulation as that generates all the required codes without running the Interface , useful functionality when you trying carry some complete logic where you want to test in SQL Developer or Toad because carrying out in ODI .

It worked and we got 107 inserts

Lets see the codes and as we see it has carried out the required UNION operation between two tables.

Let’s try the same interface for Minus with truncate table option yes in Target.

To do so, we have just changed the operator for Source 2 to Minus and execute

Similarly it goes for UNION_ALL and INTERSECT

What if we want to do UNION between three tables? Just select the right operator and the ODI will create the logic as defined.

This functionality give an extra edge for ODI on ETL side.

Keep looking odiexperts for more ODI 11g  functionalities .

This post is about how to do a simple Minus query in ODI without creating any view or other process .

Join the two source  and apply a LEFT OUTER JOIN  and in the join query box delete the condition and write the query in this form namely.

NOT EXISTS ( SELECT ‘X’ FROM  SCHEMA_NAME.SOURCE_1 ( TABLE TO BE MINUS WITH ) WHERE JOINING CONDITION.

So for my Example it is 

NOT EXISTS ( SELECT ‘X’ FROM ODI_TEMP.SOURCE_1 WHERE SOURCE_2.EMP_ID=SOURCE_1.EMP_ID)

Also enable Distinct Rows option as there a cross join occurs and thus getting the right record .

comparing the Data between the minus query and odi interface result reveals that they are similar.

Lets look at another example where the table columns are mismatched.

Similarly LEFT OUTER JOIN  on the Source with to be compared and writing the condition and also enabling the distinct records option.

NOT EXITS( SELECT ‘X’ FROM ODI_TEMP.DUP_CHANNELS ( TO BE MINUS WITH) WHERE CHANNELS.CHANNEL_ID=DUP_CHANNELS.CHANNEL_ID( JOINING CONDITION)