Blog

Getting Groovy with ODI: Mass Expression Setting

Tim Seyfried, Principal Consultant, Data Analytics Practice

June 2019

I have now had the opportunity to work extensively with SAP data services (SDS) previously known as BusinessObjects Data Integrator (BODI) and Oracle Data Integrator (ODI).  Both ETL tools are powerful and do an excellent job at ETL or ELT work.

In this post, I am going to look at one functionality in SDS that ODI doesn’t have in its GUI Then I will show you how we can use the built-in Groovy scripting to accomplish the same thing and more.

In SDS, there is the concept of manipulating the values in multiple columns.  You can highlight various columns right click and select properties and then change what is already in the column.

You can do things like:

nvl({original expression},’N/A’)  à which wraps whatever is already in the expression with an nvl function.

nvl({schema_name}.{column_name},’N/A’) à which replaces whatever is in the expression with an nvl wrapping the mapped column.

The capability to manipulate multiple expressions in mappings this way is a compelling feature that accelerates the building of mappings, especially in a data warehouse type environment where every column should have a default value instead of a null value.

Here is a screenshot of the Multiple Columns Properties popup:

In ODI, we can select multiple column mappings, but we don’t have the same capability to make mass changes.  We can select multiple columns, but we still have to select and manipulate each expression to make a change.

But in ODI, Oracle has provided a powerful scripting language, Groovy, that allows us to automate actions that would typically be done using the GUI interface.  All activities accomplished through the GUI can be done using Groovy scripting.

Apache Groovy is a Java-syntax-compatible object-oriented programming language for the Java platform. It is both a static and dynamic language with features similar to those of PythonRubyPerl, and Smalltalk. It can be used as both a programming language and a scripting language for the Java Platform, is compiled to Java virtual machine (JVM) bytecode, and interoperates seamlessly with other Java code and libraries. Groovy uses a curly-bracket syntax similar to Java’s. Groovy supports closures, multiline strings, and expressions embedded in strings. Much of Groovy’s power lies in its AST transformations, triggered through annotations.”

https://en.wikipedia.org/wiki/Apache_Groovy

Let us walk through how to accomplish a mass apply of expressions using Groovy.

First, let us look at the mapping we want to modify.

Mapping MP_JDE_ODSSTG_F4211_SOTRANSACTIONALDETAIL above is a basic mapping, taking table F4211 from JDE and mapping it to F4211_SOTRANSACTIONALDETAIL in our ODS/STG though has the following challenges.

  1. First two fields are not in the source and the column names in the target don’t match the source so automapping doesn’t help.
  2. All character fields longer then 3 characters need to have the default for null values of ‘N/D’ representing not defined.
  3. All character fields less then 3 characters need to have a default of ‘0’.
  4. All number fields need a default of 0.
  5. All number fields that represent a date need to be converted to an ANSI standard database date.

Let’s dive into the components on Groovy inside ODI.

To open the groovy editor, go to Tools > Groovy > New Script.

This gives us an empty Groovy editor.

 

The first thing we need to do is include the libraries we need for this exercise.  You can find all the oracle documentation for these libraries at:

https://docs.oracle.com/middleware/1221/odi/reference-java-api/index.html

Here are the libraries we need to import:

Set up the ODI transaction to work within.

Set short cuts to the different finder commands to make the code more readable

Set variables so that the code is easily modified for different mappings

Attach to the correct objects in the ODI work repository by walking down the project hierarchy


Define a function to change the expressions in the target.  This function makes the definition of multiple assignments a lot easier to define.


Code the set expressions.  Here is a sample.


Commit the transaction.


Running this groovy script, we now see the expressions in the target updated.

With this code you can modify one or more expressions with simple or complex logic.

This is a very simple example of using Groovy to make a tedious task in development easier.

I hope to post more complex scripts in the future that do other cool things in ODI.

Click here to access the script in its entirety.