Create Interfaces from Excel Mapping Sheets in ODI

Create Interfaces from Excel Mapping Sheets in ODI:

Hey Guys!!

Many of you have requested to provide Groovy codes that will create interfaces by reading mapping information from Excel sheet. Though I have done 95% of homework, I have left rest of work for you to customize as per your need. Check out the post by Julian to know more about building automation scripts. Remember one thing about automation scripts, though it takes few second to create multiple objects, it might be dangerous if you do not understand the effect of your code. These codes must be passed through all the test cases before applying in your development environment. It gives lots of pain when it affects hundreds of artifacts and undoing the changes. Nevertheless sometimes it is convenient to create these scripts as it saves lot of your time in building interfaces, models etc. Business Analysts from different companies follows standard mapping sheet for ODI Development. It would be productive if you can utilize those information to create models, data stores, interfaces, scenarios, projects etc. Here are some major issues that I have faced so far.

1. If you have more than 500 attributes required mapping in target data store, it would be really a tedious task for you as you have to drag each fields and adding expression to individuals.

2. If you have to build 100 interfaces, it is annoying to build one by one.

3. Creating hundreds of models and data store one after other takes lots of time.

If I have thousands of mappings and only few of them needs some changes to add expressions like cases or decodes etc, I would love to create them in excel using formulas and concatenate them to generate the final mappings. It would take 80% less time than doing in ODI Studio. The excel sheet that I am going to show has just two or three mapping as this article represents just a small sample. In my file I have 4 sheets and i expect 4 interface of Table to File, File to File, File to table and Table to Table. Here is the sheet info.

For “source” and “target” second column takes model name and third column takes data store name.

excelsheeets1

Now I will execute scripts in ODI Studio which will create 4 interfaces for me as per the mapping sheet. It will include the lookups, joins, filters, mappings. Also it will set your staging area to different logical schema in case it is different from target.

excelsheeets2

excelsheeets3

excelsheeets4

excelsheeets5

excelsheeets6

Here is the code. Don’t forget to download jxl jar and keep it in odi local client directory.

ODI Version: 11.1.1.7

/*The first row of excel sheet contains info about Project Code, Folder name inside project, Interface name, Context code, Is Staging Different from Target (Y/N), Staging Logical Schema, Interface Type(FF/FT/TT/TF).
This code expects the model,datastore,project,context,logical schema are already exist.
Author: Bhabani Mahapatra
Mail: support@dwteam.in
*/

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 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.project.OdiInterface
import oracle.odi.domain.project.interfaces.DataSet
import oracle.odi.domain.model.OdiDataStore
import oracle.odi.domain.topology.OdiContext
import oracle.odi.interfaces.interactive.support.clauseimporters.ClauseImporterDefault
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerColumnName
import oracle.odi.interfaces.interactive.support.mapping.matchpolicy.MappingMatchPolicyColumnName
import oracle.odi.interfaces.interactive.support.targetkeychoosers.TargetKeyChooserPrimaryKey
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.interfaces.TargetIsTemporaryException
import oracle.odi.interfaces.basic.NoSourceSetException
import oracle.odi.interfaces.basic.support.BasicInterfaceCreationHelperWithActionsHelper
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetDataStoreComputeAutoMapping
import oracle.odi.interfaces.interactive.support.InteractiveInterfaceHelperWithActions
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddLookup
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddJoin
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddFilter
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnTargetMappingSetSql
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddSourceDataStore
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionSetTargetDataStore
import oracle.odi.interfaces.interactive.support.aliascomputers.AliasComputerDoubleChecker
import oracle.odi.interfaces.interactive.support.clauseimporters.ClauseImporterLazy
import oracle.odi.interfaces.interactive.support.mapping.automap.AutoMappingComputerLazy
import oracle.odi.interfaces.interactive.support.mapping.matchpolicy.MappingMatchPolicyLazy
import oracle.odi.domain.topology.finder.IOdiLogicalSchemaFinder
import oracle.odi.interfaces.interactive.support.actions.InterfaceActionOnStagingAreaSetLogicalSchema
import oracle.odi.domain.project.finder.IOdiLKMFinder
import oracle.odi.domain.project.finder.IOdiCKMFinder
import oracle.odi.domain.project.finder.IOdiIKMFinder
import oracle.odi.domain.project.OdiLKM
import oracle.odi.domain.project.OdiCKM
import oracle.odi.interfaces.interactive.support.km.optionretainer.KMOptionRetainerHomonymy
import oracle.odi.domain.topology.OdiLogicalSchema
import oracle.odi.interfaces.basic.MappingDefinition
import oracle.odi.impexp.support.ImportServiceImpl
import oracle.odi.impexp.IImportService
import oracle.odi.domain.project.OdiIKM
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.km.optionretainer.KMOptionRetainerLazy
import oracle.odi.domain.project.interfaces.SourceDataStore
import oracle.odi.domain.project.interfaces.SourceSet
import java.util.Collection
import java.io.*
import jxl.*
import jxl.write.*

// Repository credentials
url = "jdbc:oracle:thin:@localhost:1522:orcl11g"
driver = "oracle.jdbc.OracleDriver"
schema = "ODIMASTER11G"
schemapwd = "odi"
workrep = "WORKREP406"
odiuser= "SUPERVISOR"
odiuserpwd = "SUNOPSIS"
projectcode = "TRAINING_BATCH1"
foldername = "LEVEL 1" //This project folder not model
// Common variables
contextcode = null
stagingLogicalSchemaName=null
isStagingDifferent=null
iname = null
interfaceType =null
OdiContext context=null
OdiInterface odiInterface=null
InteractiveInterfaceHelperWithActions interactiveHelper=null
OdiProject sdkProject=null
Collection<OdiFolder> odiFolders=null
OdiFolder odiFolder=null
SourceSet srcset= null
//For IKM array follow this order to set values if you have multiple sheets. Index 0 for file type km and index 1 for table type km
ikmNameTarget=["IKM SQL to File Append","IKM SQL Control Append"]
ckmName="CKM Oracle"
lkmName="LKM SQL to SQL"
filepath="C:/Bhabani/control/interface.xls"
kmInstallFolder = "C:/Oracle/Middleware/Oracle_BI1/Oracle_ODI1/oracledi/xml-reference/"

try {
Workbook workbook = Workbook.getWorkbook(new File(filepath))
String [] sheetNames = workbook.getSheetNames()
Sheet sheet=null

//Get all sheets and loop in them
try {
for (int sheetNumber =0; sheetNumber < sheetNames.length; sheetNumber++){
sheet=workbook.getSheet(sheetNames[sheetNumber])
int rows=sheet.getRows()
print "Processing "+sheet.getName()+"…..\n"

MasterRepositoryDbInfo masterInfo = new MasterRepositoryDbInfo(url, driver, schema, schemapwd.toCharArray(), new PoolingAttributes())
WorkRepositoryDbInfo workInfo = new WorkRepositoryDbInfo(workrep, new PoolingAttributes())
OdiInstance odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(masterInfo, workInfo))
Authentication auth = odiInstance.getSecurityManager().createAuthentication(odiuser, odiuserpwd.toCharArray())
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth)
ITransactionDefinition txnDef = new DefaultTransactionDefinition()
ITransactionManager tm = odiInstance.getTransactionManager()
ITransactionStatus txnStatus = tm.getTransaction(txnDef)
IImportService impService = new ImportServiceImpl(odiInstance)

projectcode=sheet.getCell(0,0).getContents()
foldername=sheet.getCell(1,0).getContents()
iname=sheet.getCell(2,0).getContents()
contextcode=sheet.getCell(3,0).getContents()
isStagingDifferent=sheet.getCell(4,0).getContents()
stagingLogicalSchemaName=sheet.getCell(5,0).getContents()
interfaceType=sheet.getCell(6,0).getContents()

odiFolders = ((IOdiFolderFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(foldername,projectcode)
sdkProject = ((IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class)).findByCode(projectcode)
Collection<OdiIKM> odiIKM_1 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(ikmNameTarget[0],projectcode)
Collection<OdiIKM> odiIKM_2 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(ikmNameTarget[1],projectcode)
Collection<OdiCKM> odiCKM = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(ckmName,projectcode)
Collection<OdiLKM> odiLKM = ((IOdiLKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLKM.class)).findByName(lkmName,projectcode)
if (odiIKM_1.size()==0){
impService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, kmInstallFolder + "KM_"+ikmNameTarget[0]+".xml", sdkProject, false)
}
if (odiIKM_2.size()==0){
impService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, kmInstallFolder + "KM_"+ikmNameTarget[1]+".xml", sdkProject, false)
}
if (odiCKM.size()==0)
impService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, kmInstallFolder + "KM_"+ckmName+".xml", sdkProject, false)
if (odiLKM.size()==0)
impService.importObjectFromXml(IImportService.IMPORT_MODE_DUPLICATION, kmInstallFolder + "KM_"+lkmName+".xml", sdkProject, false)

if (odiFolders.size() == 0) {
System.err.println("Error: cannot find folder "+foldername+" in project "+projectcode)
}
odiFolder = (OdiFolder) (odiFolders.toArray()[0])
context = ((IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).findByCode(contextcode)
oracleLogicalSchema = ((IOdiLogicalSchemaFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiLogicalSchema.class)).findByName(stagingLogicalSchemaName)
odiInterface = new OdiInterface(odiFolder, iname, context)
interactiveHelper = new InteractiveInterfaceHelperWithActions(odiInterface, odiInstance, odiInstance.getTransactionalEntityManager())
DataSet dataSet = odiInterface.getDataSets().get(0)

//Get all rows present in one sheet and loop in them
for (int i =1; i < rows; i++){
Cell getval=sheet.getCell(0,i)

//Add sources
if(getval.getContents()=="source"){
Cell srcmodel=sheet.getCell(1,i)
Cell srctab = sheet.getCell(2,i)
OdiDataStore odiDatastore1 = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName(srctab.getContents(), srcmodel.getContents())
interactiveHelper.performAction(new InterfaceActionAddSourceDataStore(odiDatastore1, dataSet, new AliasComputerDoubleChecker(), new ClauseImporterLazy(), new AutoMappingComputerLazy()))
}
//Add single target
if(getval.getContents()=="target"){
Cell tgtmodel=sheet.getCell(1,i)
Cell tgttab = sheet.getCell(2,i)
OdiDataStore targetDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName(tgttab.getContents(), tgtmodel.getContents())
interactiveHelper.performAction(new InterfaceActionSetTargetDataStore(targetDatastore,
new MappingMatchPolicyLazy(),
new AutoMappingComputerLazy(),
new AutoMappingComputerLazy(),
new TargetKeyChooserPrimaryKey()))
}
//Add join
if (getval.getContents()=="join") {
Cell join_exp=sheet.getCell(1,i);
interactiveHelper.performAction(new InterfaceActionAddJoin(dataSet, join_exp.getContents(), OdiInterface.ExecutionLocation.SOURCE))
}
//Add filter
if (getval.getContents()=="filter") {
Cell filter_exp=sheet.getCell(1,i)
interactiveHelper.performAction(new InterfaceActionAddFilter(dataSet, filter_exp.getContents(), OdiInterface.ExecutionLocation.SOURCE))
}
//Add lookup
if (getval.getContents()=="lookup") {
Cell src_model=sheet.getCell(1,i)
Cell src_tab=sheet.getCell(2,i)
Cell alias=sheet.getCell(3,i)
Cell lookup_exp=sheet.getCell(4,i)
OdiDataStore lkpDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName(src_tab.getContents(), src_model.getContents())
interactiveHelper.performAction(new InterfaceActionAddLookup(dataSet, lkpDatastore, alias.getContents(), lookup_exp.getContents(), OdiInterface.ExecutionLocation.SOURCE, false))
}
//Add expressions in mapping
if (getval.getContents()=="mapping") {
Cell col=sheet.getCell(1,i)
Cell exp=sheet.getCell(2,i)
interactiveHelper.performAction(new InterfaceActionOnTargetMappingSetSql(col.getContents(), exp.getContents(), dataSet))
}
}

//Set staging area different if the flag is Y and if the logical schema is not null and if the schema actually exist in repository
if (isStagingDifferent == "Y" || interfaceType == "FF"){
if (stagingLogicalSchemaName != null){
if (oracleLogicalSchema != null){
odiInterface.stagingArea.setOnTarget(false)
odiInterface.stagingArea.setLogicalSchema(oracleLogicalSchema)
}
}
}
//Set IKM1
if(interfaceType != null && (interfaceType=="TF" || interfaceType=="FF")){
for (Collection<OdiIKM> odiIKM1 : ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(ikmNameTarget[0],projectcode))
{
interactiveHelper.performAction(new InterfaceActionSetKM(odiIKM1, odiInterface.getTargetDataStore(), KMType.IKM, new KMOptionRetainerLazy()))
interactiveHelper.performAction(new InterfaceActionSetKMOptionValue(odiInterface.getTargetDataStore(), KMType.IKM, "TRUNCATE", true))
}
}
//Set IKM2
if(interfaceType != null && (interfaceType=="TT" || interfaceType=="FT")){
for (Collection<OdiIKM> odiIKM2 : ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(ikmNameTarget[1],projectcode))
{
interactiveHelper.performAction(new InterfaceActionSetKM(odiIKM2, odiInterface.getTargetDataStore(), KMType.IKM, new KMOptionRetainerLazy()))
interactiveHelper.performAction(new InterfaceActionSetKMOptionValue(odiInterface.getTargetDataStore(), KMType.IKM, "TRUNCATE", true))
}
}
//Set CKM
if(interfaceType != null && (interfaceType=="TT" || interfaceType=="FT")){
for (Collection<OdiCKM> odiCKM1 : ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(ckmName,projectcode))
{
interactiveHelper.performAction(new InterfaceActionSetKM(odiCKM1, odiInterface.getTargetDataStore(), KMType.CKM, new KMOptionRetainerLazy()))
}
}

try {
interactiveHelper.computeSourceSets()
}
catch (Exception e) {
println "Exception at first catch: "+e
}
try {
interactiveHelper.preparePersist()
}
catch (Exception e) {
println "Exception at second catch: "+e
}
odiInstance.getTransactionalEntityManager().persist(odiInterface)
tm.commit(txnStatus);
println "Interface successfully created for "+sheet.getName()+"\n"
}

} catch (Exception e) {
println "Exception at third catch: "+e
}
} catch (Exception e) {
println "Exception at final catch: "+e
}

Thats it. Let me know if you face any issues. For creating projects and model see the link I have shared in the beginning of this post. As always dont forget to provide your suggestions and comments for further improvement.

2

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

20 comments

Hi Bhabani,

I was looking the same groovy script for updating the behavioral columns, tried couple of options and ended up changing the SNP_COLUMN.

Can you please help with groovy script for behavioral column update.

Thanks,
Niharikaa

interactiveHelper.performAction(new InterfaceActionOnTargetMappingSetSql(col.getContents(), exp.getContents(), dataSet))

Regarding the above line. It doesn’t apply the mapping expression in the script I’m using. Can you help me with this? Here is my script without the jxl parts.

helper.performAction(new InterfaceActionOnTargetMappingSetSql(“TAB_COUNT”, “COUNT(1)”, dataSet))

Hi Bhabani

Regarding the script:

interactiveHelper.performAction(new InterfaceActionOnTargetMappingSetSql(col.getContents(), exp.getContents(), dataSet))
Regarding the above line. It doesn’t apply the mapping expression in the script I’m using. Can you help me with this? Here is my script without the jxl parts.
helper.performAction(new InterfaceActionOnTargetMappingSetSql(“TAB_COUNT”, “COUNT(1)”, dataSet))

Thanks,
Anthony

Hi Anthony,
I did a check by setting to COUNT(1) and could able to see the mapping in the expression. In this case the “execute on” will be automatically set to target.

It seems like there are some issue with your code. Can you send your artifacts (excel mapping file, groovy script, source table script and target table script) to my mail id?

Anindya Chatterjee / Reply

<>

Dear Bhabani,

Could you please let me know the exact location?
Because I have put the jxl.jar in below location:-
C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\client
And getting the below error-
startup failed:
odi.groovy: 93: unable to resolve class Workbook
@ line 93, column 10.
Workbook workbook = Workbook.getWorkbook(new File(filepath))
^

odi.groovy: 95: unable to resolve class Sheet
@ line 95, column 7.
Sheet sheet=null
^
etc etc
Please help.

Anindya Chatterjee / Reply

Hi Bhabani,
Could you please reply on my query?

Anindya Chatterjee / Reply

Hi Bhabani,
Could you please reply on this ?

Keep that in the client location. Then restart ODI Studio.
C:\Users\your_user_name\AppData\Roaming\odi\oracledi\userlib

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

Can you let me know how we can use Union ,Union all ,Minus and intersect operation using this SDK

Agniva Chatterjee / Reply

Hi Bhabani

Can you please provide the excel file that you have used in this example?

Thanks
Agni

Sent to your mail box.

Agniva Chatterjee / Reply

Hi Bhabani,

I’m getting the below error of ArrayOutOfBoundsException. Please let me know if you have faced similar kind of issue in 3rd catch block.

Thanks,
Agni

Hi Bhabani,

Could you please confirm only the jxl jar is the prerequisite to try this code.I have odi installed in my machine.

Thanks,
kalai.

Thats Correct Kalaivanivishwa.

Hi Bhabani,

However I am still getting the same error.
“Unable to resolve class workbook”

Code which I am trying:

import java.util.Date;

import java.io.File;

import java.util.*;

import java.lang.*;

def project_name=”GRO_PROJECT2″
def project_folder_name=”NEW_FOLDER1″
File file=new File(“D:/Kalaivani/interfaces.xls”)
def s=0

println “Mapping Created SUccessfully”+project+”with the folder”+folder
println “filepath is ” +file + ” verified”

Workbook workbook = new Workbook();

String fileName = workbook.getWorkbook(new File(file))
String [] sheetNames = workbook.getSheetNames()
Sheet sheet=null
println “Sheetname is ” +sheetNames

FYI I am trying this in odi 12.1.3 version

HI Bhabani,
I’m want install JEE Agent on the linux64 system,can you tell me what to do it,I am a Chinese.
Thanks,
dave

Hi Dave,
Nǐ hǎo!!

You didnt say which version ODI you are using. Regardless of version, first and foremost you should have weblogic installed. Then create the domain. Whether you install on windows or linux steps would be same for both cases. Please go through below link and let me know in case you find any difficulties. I will be happy to assist you.

https://docs.oracle.com/middleware/1212/core/ODING/configure_jee.htm#ODING291

Thanks
Bhabani

Thanks!~
My problem is resolved!~
Thanks!Bhabani

Leave a replay to Niharikaa Cancel reply

required*

Are you a human? *