ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Creating Temporary Interface using ODI SDK

In this below example we are creating a temporary interface with HR table REGIONS as the source .

Please go through this post ( http://odiexperts.com/odi-sdk-setup-and-config-in-eclipse ) before proceeding and this post is a continuation of the objects created in the old Post.

The below codes assumes that we already have the HR model with REGIONS datastore.

// Find the Project
// Using IFinder find the project , Folder , Context and Logical Schema ,which
// will be used to create  the Temporary Interface.

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

// Find the folder
// Here among the collection of Folder we are looking for  (FOLDER)
 Collection fold = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiFolder.class)).
findByName("FOLDER");

for (java.util.Iterator it=fold.iterator(); it.hasNext();){
     folder=(OdiFolder)it.next();
}

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

//Find the Oracle Logical Schema
   oracleLogicalSchema = ((IOdiLogicalSchemaFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiLogicalSchema.class)).
findByName("HR");

//Importing the KM
// ImportServiceImpl(OdiInstance pOdiInstance)

IImportService importService = new ImportServiceImpl(odiInstance);

try {

// Specify the path of the XML files ok KM .
String KM_PATH = "C:\Oracle\Middleware\Oracle_ODI1\oracledi\xml-reference";

// Import the KMs using duplication mode

// importObjectFromXml(int pImportMode, java.lang.String pFileName,
//    IImportRoot pObjectParent, boolean pDeclareMissingRepository)
// This method imports an object from an OracleDI export file (XML) under a parent object.

importService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, KM_PATH
+ "\KM_IKM SQL Control Append.xml",proj, false);
} catch (OdiImportNotSupportedException e) {

throw new OdiRuntimeException(e);
} catch (OdiImportException e) {throw new OdiRuntimeException(e);
} catch (IOException e) {throw new OdiRuntimeException(e);
}

// Eclipse can help to create the Exception automatically if it
// detects an Exception, so Exception handling can be easy task.

// Creating a New Interface
// OdiInterface(OdiFolder pFolder, java.lang.String pName, OdiContext pOptimizationContext)

OdiInterface intf = new OdiInterface(fold, "INTF_REGIONS",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)

// A DataSet is a subset of sources that can be combined with other
// DataSets using Set operators (such as UNION, MINUS, etc.). DataSets contain SourceDataStores, Joins, Filters and
// TargetMappings (which are occurrences of mappings executed on Source or Staging Area.

DataSet dataset = intf.getDataSets().iterator().next();

// Reading the Source Data Store
// Find the Data store using the IOdiDataStoreFinder

OdiDataStore sourceDatastore = ((IOdiDataStoreFinder) odiInstance
.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName("REGIONS",
"HR");

 // Creating the Target Data Store
// Helper is to manipulate Odi interfaces in an interactive way

// InteractiveInterfaceHelperWithActions(OdiInterface pInterface, OdiInstance pOdiInstance,
// IOdiEntityManager pOdiEntityManager)

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

helper.performAction(new InterfaceActionAddSourceDataStore(sourceDatastore, dataset,
new AliasComputerDoubleChecker(),new ClauseImporterLazy(),new AutoMappingComputerColumnName()));

// Using the above create Logical Schema in place of Set
// Staging area different from Target

helper.performAction(new InterfaceActionOnStagingAreaSetLogicalSchema(oracleLogicalSchema));

// Creating a Temporary Table Name so using the Format
// Source Data Store + " TEMP"

helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetName(
sourceDatastore.getName().toString() + "_TEMP"));

// Setting the Schema as Work Schema ( Temporary Schema) where Temporary Table will be created

helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetDatabaseSchema(
DatabaseSchema.TEMPORARY_SCHEMA));

// Fetching the Source Columns and adding it to the target
// table and performing the automatic Columns Mapping

Object[] col = sourceDatastore.getColumns().toArray();
for (int i = 0; i < col.length; i++) {
try {
     OdiColumn column = (OdiColumn) col[i];
     helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreAddColumn(
     column, new AutoMappingComputerColumnName()));
// Each Source Column is read and added to target table and
// automatically mapped using AutoMappingComputerColumnName

 } catch (UnknownActionException e1) {e1.printStackTrace();}
     }

// Start mapping the KM
// Since both the source and the Temporary Table is in the
// same Data Server so no LKM only IKM

// IKM
// Find the IKM using the IOdiIKMFinder

Collection<OdiIKM> ikm1 = ((IOdiIKMFinder) odiInstance
.getTransactionalEntityManager().getFinder(
OdiIKM.class)).findByName("IKM SQL Control Append", "XMT");

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

// Fetching each option of the IKM
for (ProcedureOption c : odiIKM.getOptions()) {
// Setting the IKM in the interface
helper.performAction(new InterfaceActionSetKM(
odiIKM, intf.getTargetDataStore(),
KMType.IKM, new KMOptionRetainerLazy()));

// 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,"TRUNCATE", true));

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

}

}

// Compute the Interface sourceset

try {
helper.computeSourceSets();
//Should be called at some point after source data stores are added to or
// removed from the interface, or some mappings/joins/filters have been added
// or had their locations changed, to create the correct source sets.
// Typically called before setting the KMs for the interface source set, or before calling preparePersist

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

After successful execution the interface is created successfully and with the columns added to target and mapped .

Also the IKM is set with the options specified.

image

image

image

The Complete Java Code

package odi.sdk;

import java.io.IOException;
import java.util.Collection;
import java.util.Iterator;

import oracle.odi.core.OdiInstance;
import oracle.odi.core.exception.OdiRuntimeException;
import oracle.odi.core.persistence.transaction.ITransactionStatus;
import oracle.odi.core.persistence.transaction.support.TransactionCallbackWithoutResult;
import oracle.odi.core.persistence.transaction.support.TransactionTemplate;
import oracle.odi.domain.model.OdiColumn;
import oracle.odi.domain.model.OdiDataStore;
import oracle.odi.domain.model.finder.IOdiDataStoreFinder;
import oracle.odi.domain.project.OdiFolder;
import oracle.odi.domain.project.OdiIKM;
import oracle.odi.domain.project.OdiInterface;
import oracle.odi.domain.project.OdiInterface.DatabaseSchema;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.ProcedureOption;
import oracle.odi.domain.project.finder.IOdiIKMFinder;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.domain.project.finder.*;
import oracle.odi.domain.topology.finder.*;
import oracle.odi.domain.project.interfaces.DataSet;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.impexp.IImportService;
import oracle.odi.impexp.OdiImportException;
import oracle.odi.impexp.OdiImportNotSupportedException;
import oracle.odi.impexp.support.ImportServiceImpl;
import oracle.odi.interfaces.interactive.exceptions.UnknownActionException;
import oracle.odi.interfaces.interactive.support.InteractiveInterfaceHelperWithActions;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddSourceDataStore;
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnStagingAreaSetLogicalSchema;
import oracle.odi.interfaces.interactive.support.actions.
InterfaceActionOnTemporaryTargetDataStoreAddColumn;
import oracle.odi.interfaces.interactive.support.actions.
InterfaceActionOnTemporaryTargetDataStoreSetDatabaseSchema;
import oracle.odi.interfaces.interactive.support.actions.
InterfaceActionOnTemporaryTargetDataStoreSetName;
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.aliascomputers.AliasComputerDoubleChecker;
import oracle.odi.interfaces.interactive.support.clauseimporters.ClauseImporterLazy;
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerLazy;
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerColumnName;
import oracle.odi.interfaces.interactive.support.sourceset.creators.InexistentMappingException;
import oracle.odi.publicapi.samples.SimpleOdiInstanceHandle;

public class TempIntf {

	private static OdiProject project;
	private static OdiFolder folder;
	private static OdiLogicalSchema oracleLogicalSchema;
	private static OdiContext context;

	public static void main(String[] args) {

	final SimpleOdiInstanceHandle odiInstanceHandle = SimpleOdiInstanceHandle
	.create("jdbc:oracle:thin:@localhost:1521:orcl",
	"oracle.jdbc.OracleDriver",
	"ODI_MASTER_11G",
	"ODI_MASTER_11G",
	"WORKREP1",
	"SUPERVISOR",
	"SUNOPSIS");

	final OdiInstance odiInstance = odiInstanceHandle.getOdiInstance();
	try {
	TransactionTemplate tx = new TransactionTemplate(odiInstance.getTransactionManager());
	tx.execute(new TransactionCallbackWithoutResult()
	{
	   protected void doInTransactionWithoutResult(ITransactionStatus pStatus)
	   {

		 // Find the Project
		 project = ((IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class)).
findByCode("XMT");

		 // Find the folder
		 Collection fold = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).
findByName("FOLDER");

	        for (java.util.Iterator it=fold.iterator(); it.hasNext();){
	          folder=(OdiFolder)it.next();
	        }
		 // Find the Context
		 context = ((IOdiContextFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).
findByCode("XMT");

		 //Find the Oracle Logical Schema
		 oracleLogicalSchema = ((IOdiLogicalSchemaFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiLogicalSchema.class)).
findByName("HR");

		 //Importing the KM

		// ImportServiceImpl(OdiInstance pOdiInstance)

		IImportService importService = new ImportServiceImpl(odiInstance);

		try {

		// Specify the path of the XML files ok KM .
		String KM_PATH = "H:\oracle\ODI_Middleware\Oracle_ODI1\oracledi\xml-reference";

		// Import the KMs using duplication mode

		// importObjectFromXml(int pImportMode, java.lang.String pFileName,
//		    IImportRoot pObjectParent, boolean pDeclareMissingRepository)
		// This method imports an object from an OracleDI export file (XML) under a parent object.

		importService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, KM_PATH
		+ "\KM_IKM SQL Control Append.xml",project, false);
		} catch (OdiImportNotSupportedException e) {

		throw new OdiRuntimeException(e);
		} catch (OdiImportException e) {throw new OdiRuntimeException(e);
		} catch (IOException e) {throw new OdiRuntimeException(e);
		}

		// Eclipse can help to create the Exception automatically if it
		// detects an Exception, so Exception handling can be easy task.

		// Creating a New Interface
		// OdiInterface(OdiFolder pFolder, java.lang.String pName, OdiContext pOptimizationContext)

		OdiInterface intf = new OdiInterface(folder, "INTF_REGIONS",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)

		// A DataSet is a subset of sources that can be combined with other
		// DataSets using Set operators (such as UNION, MINUS, etc.). DataSets contain SourceDataStores, Joins, Filters and
		// TargetMappings (which are occurrences of mappings executed on Source or Staging Area.

		DataSet dataset = intf.getDataSets().iterator().next();

		// Reading the Source Data Store
		// Find the Data store using the IOdiDataStoreFinder

		OdiDataStore sourceDatastore = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).
findByName("REGIONS","HR");

		 // Creating the Target Data Store
		// Helper is to manipulate Odi interfaces in an interactive way

		// InteractiveInterfaceHelperWithActions(OdiInterface pInterface, OdiInstance pOdiInstance,
		// IOdiEntityManager pOdiEntityManager)

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

		helper.performAction(new InterfaceActionAddSourceDataStore(sourceDatastore, dataset,
		new AliasComputerDoubleChecker(),new ClauseImporterLazy(),new AutoMappingComputerColumnName()));

		// Using the above create Logical Schema in place of Set
		// Staging area different from Target

		helper.performAction(new InterfaceActionOnStagingAreaSetLogicalSchema(oracleLogicalSchema));

		// Creating a Temporary Table Name so using the Format
		// Source Data Store + " TEMP"

		helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetName(
		sourceDatastore.getName().toString() + "_TEMP"));

		// Setting the Schema as Work Schema ( Temporary Schema) where Temporary Table will be created

		helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreSetDatabaseSchema(
		DatabaseSchema.TEMPORARY_SCHEMA));

		// Fetching the Source Columns and adding it to the target
		// table and performing the automatic Columns Mapping

		Object[] col = sourceDatastore.getColumns().toArray();
		for (int i = 0; i < col.length; i++) {
		try {
		     OdiColumn column = (OdiColumn) col[i];
		     helper.performAction(new InterfaceActionOnTemporaryTargetDataStoreAddColumn(
		     column, new AutoMappingComputerColumnName()));
		// Each Source Column is read and added to target table and
		// automatically mapped using AutoMappingComputerColumnName

		 } catch (UnknownActionException e1) {e1.printStackTrace();}
		     }

		// Start mapping the KM
		// Since both the source and the Temporary Table is in the
		// same Data Server so no LKM only IKM

		// IKM
		// Find the IKM using the IOdiIKMFinder

		Collection ikm1 = ((IOdiIKMFinder) odiInstance
		.getTransactionalEntityManager().getFinder(
		OdiIKM.class)).findByName("IKM SQL Control Append", "XMT");

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

		// Fetching each option of the IKM
		for (ProcedureOption c : odiIKM.getOptions()) {
		// Setting the IKM in the interface
		helper.performAction(new InterfaceActionSetKM(
		odiIKM, intf.getTargetDataStore(),
		KMType.IKM, new KMOptionRetainerLazy()));

		// 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,"TRUNCATE", true));

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

		}

		}

		// Compute the Interface sourceset

		try {
		helper.computeSourceSets();
		//Should be called at some point after source data stores are added to or
		// removed from the interface, or some mappings/joins/filters have been added
		// or had their locations changed, to create the correct source sets.
		// Typically called before setting the KMs for the interface source set, or before calling preparePersist

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

		// Persist the Data Server , Physical and Logical Schema

		   //odiInstance.getTransactionalEntityManager().persist(oracleTechnology);

		   System.out.println("Done");

		   }
		   });
		   }

		   finally

		   {
		   odiInstanceHandle.release();
		   }
}

}

One Comment

  1. Hi

    Can u please provide code to add custom columns in temporary target and set its data type.
    also can u explain the implementation of the constructor with example.
    InterfaceActionOnTargetColumnSetDataType(java.lang.String pColumnName, OdiDataType pOdiDataType)

    Thanking you
    SALMAN SHARIFF

Leave a Reply

Required fields are marked *.