Oracle Data Integrator 11g Groovy: Add Columns to a Datastore

One of the great new features in Oracle Data Integrator 11.1.1.6 is the integration of the scripting language Groovy right within the ODI user interface. David Allan has written several blog posts on the subject, showing how easy it is to create a project and describing how to add a model to ODI (topology included). I would like to share with you another quick Groovy script, this time adding columns to each Datastore in a given Model.

The scenario I’ll use is one that I went through in a previous post on Extreme BI using GoldenGate and ODI. In a typical data warehouse, replication will be performed from the Source to both the Staging and Foundation layer databases. Both databases hold tables that are duplicates of those in the Source, with the addition of several columns in the Foundation tables for tracking history.

The columns used for history capture in the Foundation database are as follows:

EDW_SCN (System Change Number)
EDW_COMMIT_TIMESTAMP (date/time when the change was committed)
EDW_TRANS_TYPE (type of change made)

The example Foundation layer Model, fittingly named FOUNDATION_EXAMPLE, has 45 Datastores which will need these 3 columns added. Not an overwhelming number of tables, but adding columns to each Datastore would definitely be monotonous and time consuming. Monotony is a part of nearly every job (except maybe an air traffic controller), but time, and remaining budget, is precious to every project. This approach will save both, allowing for the addition of columns to any number of tables within seconds.

To get started, open up the ODI 11g client and connect to a work repository. In the menu bar, click on Tools → Groovy → New Script. This will open a new .groovy extension script within the ODI user interface. When saving, you can rename the file and choose a new file location. The final script, found here (download), can then be pasted into the new script window and executed.

We 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 IOdiModelFinder 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 = new DefaultTransactionDefinition(); 
  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)

  Model m=new Model() 
  d = new java.awt.Dimension(205,20)
  m.toItems = new DefaultComboBoxModel(models as Object[])

  def s = new SwingBuilder()
  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.
  return vars
}

Once the user clicks the OK button, 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 3 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. For this example, I’ll keep it simple since I know the specifics of the columns I want to add.

def addColumns(modCode) { 
  txnDef = new DefaultTransactionDefinition(); 
  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(new OdiDataStore[0]);  
  
  for (int i = 0; i <= dsArray.length - 1; i++) {
    //Get the DataStore.
    OdiDataStore ds = null;
    ds = dsArray[i];

    //Add the columns.
        
    OdiColumn col = new OdiColumn(ds, "EDW_SCN");
    col.setDataTypeCode("NUMBER");
    col.setMandatory(false);
    col.setLength(20);
    col.setScale(0);

    col = new OdiColumn(ds, "EDW_COMMIT_TIMESTAMP");
    col.setDataTypeCode("DATE");
    col.setLength(23);
    col.setMandatory(false);
    
    col = new OdiColumn(ds, "EDW_TRANS_TYPE");
    col.setDataTypeCode("VARCHAR2");
    col.setMandatory(false);
    col.setLength(30);
    
    odiInstance.getTransactionalEntityManager().persist(ds) 
     

  }
  tm.commit(txnStatus)
  return mod 
}

To execute the script, click the green “Play” button on the ODI toolbar. After selecting the appropriate Model from the list, click the OK button. The script should take just a second or two, depending on the number of Datastores in your Model. Once completed, a quick look at the one of the Datastores and we see 3 new EDW columns, ready to capture transactional history.

Enjoy your Groovy scripting!