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

May 8th, 2012 by

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, 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.

The script run prompts you to select a Model

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.

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.

The columns have been added to the datastore

Enjoy your Groovy scripting!

Tags: ,

Comments

  1. David Says:

    Hi Michael

    Thanks for sharing Michael, the SwingBuilder functionality is great for doing this kind of interaction.

    Cheers
    David

  2. Richard Says:

    Comming from a non-java world,
    this opens my eyes fot the skd and groovy.
    Thanks for publishing.

  3. Uli Bethke Says:

    Hi Michael.

    Great example.

    I have the following question. In OWB there was the opion using OWB experts to attach a script to an object and then invoke via right mouse click. Is there some hack in ODI to achieve same?

    Cheers
    uli

  4. Michael Rainey Says:

    Thanks Uli.

    I’m not aware of any features in ODI that allow you to modify the delivered UI as OWB Experts can do in OWB. The closest you could get to this functionality would be to open a dialog via Swingbuilder in the Groovy script. You could then present the user with a list of appropriate ODI objects (datastores, interfaces, etc) and allow the selection of one or more objects to use in the execution of the script. An extra step, but it could achieve similar results.

    Regards,
    Michael Rainey

  5. FUMA Says:

    Hi Michael,

    Using ODI – I want to create and maintain a partition on daily basis in some fact tables. What do you suggest is best way to do so without going in ODI studio ?? Secondly I’m using 11.1.3 in which Groovy was not introduced ? So Is it possible to do so via scripting without Groovy ??

    Thanks – FUMA

  6. Michael Rainey Says:

    Hi FUMA,

    You can use the ODI SDK without Groovy by developing the code in Java. You can either develop the code in an ODI Procedure or external to ODI in a Java IDE, such as Eclipse or JDeveloper.

    Regards,
    Michael Rainey

Website Design & Build: tymedia.co.uk