ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

April 1, 2015
by Jale Ozgur
1 Comment

Categories: ODI , Others , Uncategorized

Tags: , , ,

Single Post View


Passing Values to ODI Variables in Packages/Scenarios

Hi Everyone,

Here is my first post, hope you will enjoy this.

This post is about passing values to variables in packages/scenarios and see how the variables behave, the value stays the same in declare step and changes in refresh step and you can override the value during execution.

We need static values during ETL window. Also you can rerun packages and be certain that the sub packages take the same correct variable values. ETL date can be a good example for static. Dynamic values are needed when you have a task changing daily and takes recent value such as exchange rates. Dynamic values can be calculated at run time for example looping through a list.

I created 3 packages to watch variable values:

  • SUB_DECLARE declares the variable
  • SUB_DECLARE_REFRESH declares and refreshes
  • SUB_REFRESH only refreshes the variable.

1_SUBS

And I created MAIN package begins with declares and refreshes the variable. The MAIN package continues to call the scenarios with no variable value and also  passing the given variable value in serial order one by one.

2_MAIN

The MAIN package executed with variable value : 20141217, default value is 1 and when the variable is refreshed value is 20141216 as sysdate-1, as I executed the main package at 17 Dec 2014.

When we execute the MAIN package, we can see the steps:

3_EXC_MAIN

First child session are not taken any variable value from the MAIN package, second scenarios are taken the variable values from the MAIN package.

To see how the variable changes before and after the scenario execution , we wil look variable values from repository tables in total as below:

4_VAR_VAL

At the first row, SUB_DECLARE scenario is not given any variable, as it continues with default value: 1. Second row of SUB_DECLARE scenario takes the MAIN package variable value 20141217.

When we look at SUB_DECLARE_REFRESH scenario values, first two rows does not take any value from main package, and variable begins  with default value and when it is refreshed it takes the etl_date as sysdate-1.  When the MAIN package pass variable value to SUB_DECLARE_REFRESH scenario, the value begins and ends with the same value: 20141217.

In the last scenarios, first row of SUB_REFRESH scenario begins with default value and ends with sysdate-1; 20141216. In the second row, SUB_REFRESH scenario takes only the MAIN package variable value.

In conclusion, passing a variable value to a scenario overrides the scenario value, it is always dominant. Declaring a variable creates a static value and refreshing a variable creates dynamic values, in execution begin and end values of refresh step is changing.

March 31, 2015
by Cezar Santos
0 comments

Categories: Others

Single Post View


New Author Arriving…

Hi Everyone!

We just added a new Author, Jale Ozgur.

For sure, in short time we’ll see new posts!

Welcome Jale!!!!!!

February 12, 2014
by Cezar Santos
10 Comments

Categories: Others

Tags: , , , ,

Single Post View


ODI Certification – Approved

Howdy Readers,

After several year waiting finally Oracle released an ODI Certification… And I’m pretty happy because I was approved in the test…

I invite everyone that got the certification to leave a message in this post!

The fact of exists a certification process, allow the market recognize better the professionals that truly knows the software!

“See” you all soon!

November 13, 2013
by Cezar Santos
5 Comments

Categories: Uncategorized

Tags: , , , ,

Single Post View


Information about the ODI Certification Exam

Hi Everyone,

Our reader, Ramanathan Lakshmanan, make us an huge favor in sent me the following comments about the ODI Certification Exam.

He just had taken the exam and here are a general view about the exam content as he saw:

 

Exam: 1Z1-482-Oracle Data Integrator 11g Certified Implementation Essentials-ENU

The Exam tests you on below Subject Areas and OPN Members can access the relevant Online Courses for these as well from the Oracle OPN Site:

1.      Introduction to ODI

2.      ODI Architecture

3.      ODI Models and Data stores

4.      ODI Project Development

5.      ODI Managing Execution

6.      Oracle Golden Gate with ODI

7.      Doing more with ODI

8.      Enterprise Data Quality with ODI

The Exam has a total of 124 questions and last for a duration of 150 Minutes. The Weightage is as below:

1.      ODI Model, Architecture, KM and Development – 50%

2.      ODI CDC and JKM  – 8%

3.      ODI Load Plans and Execution – 10 %

4.      ODI SDK and ODI Console – 5%

5.      EDQ and ODI– 25%

6.      Golden Gate and ODI – 2%

Also some key areas where repeated questions are coming from are as below:

1.      ODI Model, Architecture, KM and Development

a.       Dependency between physical and logical Entities

b.      Use of Security UI in ODI

c.       How Group By Clause works in Interface

d.      Mapping using Data sets

2.      ODI CDC and JKM 

a.       Naming conventions of Views and Tables created by JKM

3.      ODI Load Plans and Execution

a.       Types of steps

b.      Error Handling

c.       Rerun options

4.      ODI SDK and ODI Console

a.       Can we do Admin tasks with ODI SDK

b.      What can we do from the ODI Console

5.      EDQ and ODI

a.       Types of Parsers

b.      Address Validation related Questions

c.       How does EDQ get invoked from ODI

6.      Golden Gate and ODI

a.       User Parameters to configure OGG in ODI

 

Thank you very much Ramanathan!!!

September 16, 2013
by Cezar Santos
17 Comments

Categories: Others

Tags: , , , , , , ,

Single Post View


Finally!!!! The Oracle Data Integrator 11g Certified Implementation Exam!!!!!!

Hi Everybody,

 

after years and years of waiting, I got the so expected email about  the Oracle Data Integrator 11g Certified Implementation Exam.

The following link can tell you all about it:

http://www.oracle.com/partners/en/most-popular-resources/dataintegrator-11g-ic-exam-2005763.html

However, they included Oracle Data Quality and Golden Gate Integration in the exam…

Anyway… still in BETA.

 

 

 

December 13, 2011
by kdevendr
3 Comments

Categories: How to , Java Codes , ODI , SDK , SQL

Tags: , , , , , ,

Single Post View


Interface with Union, Minus Operator using ODI SDK

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");
    }

}

ScreenClip(2)

ScreenClip(3) 

As you know, comments are always really welcome!

Good to see you here.

November 27, 2011
by kdevendr
7 Comments

Categories: How to , Logic , ODI , Others , SDK , SQL , Tips and Tricks

Tags: , , , , , , ,

Single Post View


Export and Importing Scenarios based on Project (or)Folder

Hi Everyone,

Keeping our “java series” we have one more piece of code to share.

I hope you “enjoy” it!

The below code is used to Export and Import all the Scenario from Development Work Rep to Execution Work Rep .

For Export there are 3 different options either Export all the scenario

  • For a Project (or)
  • For a Folder (or)
  • Scenarios matching a particular Time period .

Note : – Make sure you either delete the folder or delete all the scenario inside the Export Folder once successful import is done .

package odi.sdk;

import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
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.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.core.security.Authentication;
import oracle.odi.domain.project.IOdiScenarioSourceContainer;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.domain.runtime.scenario.OdiScenario;
import oracle.odi.domain.runtime.scenario.finder.IOdiScenarioFinder;
import oracle.odi.impexp.EncodingOptions;
import oracle.odi.impexp.OdiImportException;
import oracle.odi.impexp.support.ExportServiceImpl;
import oracle.odi.impexp.support.ImportServiceImpl;

public class ExportImport {

     private static String Project_Code;
     private static OdiProject project;
     private static String   Folder_Name;
     private static OdiFolder folder;

     /**
     * @param args
     * @throws IOException
     * @throws OdiImportException
     * @throws ParseException
     */

    public static void main(String[] args) throws IOException, OdiImportException, ParseException {

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

    /** Development Repository ****/

     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";

     /*** Execution Repository ***/
     // In case if your Execution rep is linked to a different Master repository please appropriately create new variables
     // The present codes assumes that Development and Execution are linked to the same Master Repository.

     String WorkRep_Execution="WORKREP_EXECUTION";

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

     //Exporting Scenario Options

     String ExportFolderPath="/home/oracle/Documents/ODIExport";

     // While providing path in windows make sure you use \
     Boolean ExportPackageScen      = true;
     Boolean ExportInterfaceScen    = true;
     Boolean ExportProcedureScen    = true;
     Boolean ExportVariableScen     = false;
     Boolean RecursiveExport        = true;
     Boolean OverWriteFile          = true;

     Project_Code   ="XMT";
     Folder_Name    ="FOLDER2";

     // Date based on which the scenario is compared
     SimpleDateFormat df = new SimpleDateFormat("MM-dd-yyyy");
     Date date =df.parse("11-23-2011");

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

MasterRepositoryDbInfo masterInfo = new MasterRepositoryDbInfo(Url, Driver, Master_User,Master_Pass.toCharArray(), new PoolingAttributes());

/// Development Repository
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());

//Execution Repository

WorkRepositoryDbInfo workInfo_exec = new WorkRepositoryDbInfo(WorkRep_Execution, new PoolingAttributes());
OdiInstance odiInstance_exec=OdiInstance.createInstance(new OdiInstanceConfig(masterInfo,workInfo_exec));
Authentication auth_exec = odiInstance_exec.getSecurityManager().createAuthentication(Odi_User,Odi_Pass.toCharArray());
odiInstance_exec.getSecurityManager().setCurrentThreadAuthentication(auth_exec);
ITransactionStatus trans_exec = odiInstance_exec.getTransactionManager().getTransaction(new DefaultTransactionDefinition());

        //Export All Scenario by Project
        // Get Project
        project = ((IOdiProjectFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class)).findByCode(Project_Code);
        ExportServiceImpl export=new ExportServiceImpl(odiInstance);
        EncodingOptions EncdOption = new EncodingOptions();
        System.out.println( " Exporting all Scenario for the Project " +Project_Code);
        export.exportAllScenarii(project, ExportFolderPath, ExportPackageScen, ExportInterfaceScen, ExportProcedureScen, ExportVariableScen, EncdOption, RecursiveExport, OverWriteFile);

        // ( or )

        // Export All Scenario by Folder
        // Find the folder
        Collection<OdiFolder> fold = ((IOdiFolderFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(Folder_Name, Project_Code);
        for (Iterator<OdiFolder> it = fold.iterator(); it.hasNext();) {
             folder = (OdiFolder) it.next();
        }

        ExportServiceImpl export=new ExportServiceImpl(odiInstance);
        EncodingOptions EncdOption = new EncodingOptions();
        System.out.println( " Exporting all Scenario for the Project " +Project_Code+ " and Folder "+Folder_Name);
        export.exportAllScenarii(folder, ExportFolderPath, ExportPackageScen, ExportInterfaceScen, ExportProcedureScen, ExportVariableScen, EncdOption, RecursiveExport, OverWriteFile);

        // ( or )

        // Export Selected Scenario based on date

        ExportServiceImpl export=new ExportServiceImpl(odiInstance);
        EncodingOptions EncdOption = new EncodingOptions();

        //Reading all scenario

        Collection odiscen= ((IOdiScenarioFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiScenario.class)).findAll();
        for (Object scen : odiscen) {
            OdiScenario odiscenario =(OdiScenario)scen ;
            if (odiscenario.getLastDate().before(date)) {
                System.out.println(" Exporting scenario  "+odiscenario.getName());
                export.exportToXml(odiscenario, ExportFolderPath, OverWriteFile, RecursiveExport, EncdOption);
            }
        }

        //Importing Scenarios in INSERT_UPDATE Mode
        // The below code import all the scenarios present in the Exported Folder path , so make sure older files are deleted
        ImportServiceImpl importsrvc=new ImportServiceImpl(odiInstance_exec);
        String[] XMLFiles=getXMLFiles(ExportFolderPath).split("n");
        for (String xmlfile : XMLFiles) {
            System.out.println(" Importing scenario  XML "+xmlfile);
            importsrvc.importObjectFromXml(importsrvc.IMPORT_MODE_SYNONYM_INSERT_UPDATE, xmlfile, true);
        }

       // Commit and Close Transaction
       odiInstance.close();
       odiInstance_exec.getTransactionManager().commit(trans_exec);
       odiInstance_exec.close();

    }

    //Reading all the XML Files
    public static String getXMLFiles(String DirectoryName){

        String xmlfiles="";
        String files;
        File folder = new File(DirectoryName);
        File[] listOfFiles = folder.listFiles();
          for (int i = 0; i < listOfFiles.length; i++)
          {
           if (listOfFiles[i].isFile())
           {
           files = listOfFiles[i].getName();
               if (files.endsWith(".xml") || files.endsWith(".XML"))
               {
                   xmlfiles+=DirectoryName+"/"+files+"n";
                }
           }}
        return xmlfiles;
  }

}

Good to see you around and please, keep visiting us!

November 16, 2011
by kdevendr
0 comments

Categories: How to , ODI , SDK , Tips and Tricks

Single Post View


Creating Interface for Single Source and Target

This particular Code generate Interface and Scenario for one single Source and Target. By default the target datastore name is used as the Interface Name

package odi_sdk;

import java.util.Collection;
import java.util.HashSet;
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.runtime.scenario.OdiScenario;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.generation.IOdiScenarioGenerator;
import oracle.odi.generation.support.OdiScenarioGeneratorImpl;
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 PermanentInterface {

	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 OdiInterface odiInterface;
	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 source_datastore;
	private static String target_datastore;

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

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

		String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
		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";
		source_datastore="REGIONS";
		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 fold = ((IOdiFolderFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(Folder_Name);
		for (Iterator 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);
		// Reading the Source Data Store
		// Find the Data store using the IOdiDataStoreFinder
		sourceDatastore = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).
                findByName(source_datastore, source_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);

		// Creating DataSet to automatically assign different
		// Source Data store
		// DataSet(OdiInterface pInterface, java.lang.String pName)

		DataSet dataset = intf.getDataSets().iterator().next();
		SourceSet srcset = new SourceSet("srcset0",dataset);
		SourceDataStore sd=new SourceDataStore(dataset,false,sourceDatastore.getName().toString(),0,sourceDatastore);
		srcset.addSourceDataStore(sd);

		// 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 lkm1 = ((IOdiLKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLKM.class)).findByName(
						LKM,Project_Code);

		for (Iterator 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 ikm1 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(
						IKM,Project_Code);

		for (Iterator 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 ckm1 = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(
				CKM,Project_Code);

		for (Iterator 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");
	}

}

November 16, 2011
by kdevendr
1 Comment

Categories: How to , ODI , SDK , Tips and Tricks

Single Post View


Creating Permanent Interface based on Model Level

This particular ODI SDK codes creates one source to one target   interface and accordingly generate Scenario,  based on condition where the Source table is same as Target Table name , under two different Model.

By default the target datastore name is used as the Interface Name.

For this example , Source Model is HR schema and also the Target Model is HR schema.

package odi_sdk;

import java.util.Collection;
import java.util.HashSet;
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.runtime.scenario.OdiScenario;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.generation.IOdiScenarioGenerator;
import oracle.odi.generation.support.OdiScenarioGeneratorImpl;
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 PermanentInterface {

	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 OdiInterface odiInterface;
	private static String target_model_name;
	private static String source_model_name;
	private static String LKM;
	private static String IKM;
	private static String CKM;

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

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

		String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
		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";
		target_model_name = "TRGT_HR";
		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 fold = ((IOdiFolderFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(Folder_Name);
		for (Iterator it = fold.iterator(); it.hasNext();) {
			folder = (OdiFolder) it.next();
		}

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

		// Target Data Store
		Object[] trgt_ds = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findAll().toArray();

		// This part of the code is to remove the
		// duplicate datastores present in different Models
		HashSet hs = new HashSet();
		for (Object dt_str : trgt_ds) {
			OdiDataStore ds = (OdiDataStore) dt_str;
			hs.add(ds.getName().toString());
		}

		for (Object ds : hs) {
			// Target Data Store
			OdiDataStore targetDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().
                        getFinder(OdiDataStore.class)).
                        findByName(ds.toString(), target_model_name);

			if (targetDatastore != null) {
			// Reading the Source Data Store
			// Find the Data store using the IOdiDataStoreFinder
			sourceDatastore = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).
                        findByName(ds.toString(), source_model_name);

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

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

			// Creating DataSet to automatically assign different
			// Source Data store
			// DataSet(OdiInterface pInterface, java.lang.String pName)

			DataSet dataset = intf.getDataSets().iterator().next();
			SourceSet srcset = new SourceSet("srcset0",dataset);
			SourceDataStore sd=new SourceDataStore(dataset,false,sourceDatastore.getName().
                        toString(),0,sourceDatastore);
			srcset.addSourceDataStore(sd);

			// 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 lkm1 = ((IOdiLKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLKM.class)).
                        findByName(LKM,Project_Code);

			for (Iterator 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", false));
				}
			}

			// IKM
			// Find the IKM using the IOdiIKMFinder

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

			for (Iterator 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 ckm1 = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(
					CKM,Project_Code);

			for (Iterator 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 .."+ ds.toString() );
			IOdiScenarioGenerator gene = new OdiScenarioGeneratorImpl(odiInstance);
			OdiScenario newScen = gene.generateScenario(intf,ds.toString(), "001");
			odiInstance.getTransactionalEntityManager().persist(newScen);
			} }

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

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

}

Sample Output