Groovy Script that adds columns to multiple datastores in ODI

Suppose you want to add some same additional columns to all datastores. For this, time consuming monotonous repetitive task we can use power of Groovy scripting in ODI. In the designer on the menu bar, click on Tools → Groovy → New Script. This will open a new .groovy extension script. Begin by adding code to allow for the display of all available Models in the repository. With Groovy syntax we create a function named captureInput. This code will use the OdiModelFinder Interface to get a list of all Models in the repository, adding the list to an array. The array is then displayed in a combobox for user selection.

def captureInput(){
 txnDef=newDefaultTransactionDefinition();
 tm=odiInstance.getTransactionManager()
 txnStatus=tm.getTransaction(txnDef)
 models=[]
 modelFinder=(IOdiModelFinder)odiInstance.getTransactionalEntityManager().
 getFinder(OdiModel.class);
 modelM=modelFinder.findAll();
 conItr=modelM.iterator()
//add each model to the models array.
 while(conItr.hasNext()){
  mod=(OdiModel)conItr.next()
  models.add(mod.getCode())
}
 tm.commit(txnStatus)
 Modelm=newModel()
 d=newjava.awt.Dimension(205,20)
 m.toItems=newDefaultComboBoxModel(models asObject[])
 defs=newSwingBuilder()
 s.setVariable('myDialog-properties',[:])
 def vars=s.variables
def dial=s.dialog(title:'Add Columns to ODI Model',id:'myDialog',modal:true){
 panel(){
 boxLayout(axis:BXL.Y_AXIS)
 //display the models in a combobox.
 panel(alignmentX:0f){
 flowLayout(alignment:FL.RIGHT)
 label('Model Code:')
 comboBox(id:'modelCode','model':bind{m.toItems},null,preferredSize:d)
}
 panel(alignmentX:0f){
  flowLayout(alignment:FL.LEFT)
  button('OK',preferredSize:[80,24],
 actionPerformed:{
 vars.dialogResult='OK'
  dispose()
})
 button('Cancel',preferredSize:[80,24],
 actionPerformed:{
  vars.dialogResult='cancel'
  dispose()
   })
  }
 }
}
 dial.pack()
 dial.show()
 //return the selected model.
 returnvars
}

Once the user clicks OK, the selected Model Code is passed as a parameter to the the addColumns function. We then find the Model object, loop through the Datastores associated with that Model, and add the additional EDW_* columns. A future enhancement to this code might be to capture user input on each column to be added, including name, datatype, and length. To take it a step further you could first select from a list of technologies, then choose the technology appropriate datatype for each column.

def addColumns(modCode){
 txnDef=newDefaultTransactionDefinition();
 tm=odiInstance.getTransactionManager()
 txnStatus=tm.getTransaction(txnDef)
 modFinder=(IOdiModelFinder)odiInstance.getTransactionalEntityManager().
 getFinder(OdiModel.class);
 mod=modFinder.findByCode(modCode);
 Collection dataStores=mod.getGlobalSubModel().getDataStores();
 dsArray=dataStores.toArray(newOdiDataStore[0]);
for(inti=0;i<=dsArray.length-1;i++){
 //Get the DataStore.
 OdiDataStore ds=null;
 ds=dsArray[i];
 //Add the columns.
 OdiColumn col=newOdiColumn(ds,"EDW_SCN");
 col.setDataTypeCode("NUMBER");
 col.setMandatory(false);
 col.setLength(20);
 col.setScale(0);
 col=newOdiColumn(ds,"EDW_COMMIT_TIMESTAMP");
 col.setDataTypeCode("DATE");
 col.setLength(23);
 col.setMandatory(false);
 col=newOdiColumn(ds,"EDW_TRANS_TYPE");
 col.setDataTypeCode("VARCHAR2");
 col.setMandatory(false);
 col.setLength(30);
 odiInstance.getTransactionalEntityManager().persist(ds)
}
 tm.commit(txnStatus)
 returnmod
}

To execute the script, click the play button on the ODI toolbar. After selecting the appropriate Model from the list, click ok.

Advertisements