Disclaimer

The content of this article pertains to on premise technology and was last validated in April 2015. It is still made available for its educational value.

Introduction

Oracle now provides a migration utility to convert your existing Oracle Warehouse Builder (OWB) mappings to Oracle Data Integrator (ODI) mappings. As customers started to go through this migration effort, we realized that some post migration scripts could help update the migrated repository. Once we had the scripts, we realized they could also be used by all ODI users, whether they had migrated from OWB, or upgraded from an earlier version of ODI, or simply wanted to automate massive changes to their repositories.

We are describing here a number of such scripts that we have co-developed with our customers, and we provide a link to the source code of these scripts so that you can adapt them to your personal needs.

The Groovy Scripts

The scripts are written in Groovy and leverage the ODI SDK. They all leverage SwingBuilder to create a graphical interface used to prompt the user for the necessary parameters: the SDK retrieves the necessary objects from the repository and lets the user select them as needed. We started from examples provided by David Allan in this blog and quickly had enough for a practical interface. One interesting aspect was the ability to dynamically build lists based on existing selections: only list the relevant Folders or Knowledge Modules for the selected Project for instance.

The scripts that we are presenting here are the following:

We have a conservative approach in these script: we usually limit the scope of changes to a single folder within a given project. It would not take much to alter the scripts to perform the same operations for all folders in a project, or for the entire repository if needed.

Even if all these scripts were originally put together as part of migration or upgrade efforts, they can all be used in any ODI repository where similar changes need to be automated. For instance, a global change of the Knowledge Modules used in the mappings can be performed independently of any migration or upgrade effort.

Using the Groovy scripts in ODI

To make sure that we do not have to write usernames and passwords in the scripts, all scripts must be launched from an ODI Studio that is already connected to the repository: the Groovy code retrieves the Studio connection to the repository. So please make sure that you are indeed connected to the repository when you run these scripts.

Once you have downloaded the Groovy scripts, go to the ODI menu Tools/Groovy/Open Script and select the script that you want to import into the ODI Studio.

To run a script, make sure that the mouse cursor is in the script and click the ODI Run button in the Studio toolbar:

If you create your own scripts, be aware that by default ODI saves the scripts in <user home directory>/odi/oracledi. On Windows7 for instance, that is: C:\Users\<username>\AppData\Roaming\odi\oracledi.

All scripts take advantage of the ODI Studio console to output details about the objects that are parsed in the repository and to keep you informed about the changes that are made to these objects (any print or println command will be displayed in the console). The example below shows the output for the script that renames mappings. In this run, we replace ‘Custs’ with ‘Customers’ for all mappings that contain the string ’LOAD_’:

Merging ODI Models

When would you use this script?

When the migration utility converts an OWB model to ODI, it creates a new ODI model for each OWB project. If you have different projects that use the same tables, this results in unnecessary clutter in your models, with the same tables appearing in multiple models. If you were to try and merge the models manually, you would have to open all the mappings and change the source or target datastores to point to one single model. This can be quite a daunting task. Using this script, you can select an original model name, a new model name, and the script updates the mappings for you.

An example of the result of running this script is illustrated in the screenshots below. The first screenshot shows that, in the original mapping, the target table comes from the model COPY_OF_ORA_SALES

When we run the script we ask that the mapping now use the table from the ORA_SALES model instead of the original COPY_OF_ORA_SALES model (make sure that you close your mappings as you run the script):

When we re-open the mapping, it does indeed point to the ORA_SALES model:

Using the script

The source code for this script is available here.

When you run the script, simply select the project code, original model code and as well as the code of the new model that you want to use. If you only want to impact a subset of your mappings, you can enter a string that helps the script identify the mapping (no wildcard in the current release, though that would be possible with regular expressions).

This script will parse all datasources used in the mappings that match the search criteria, as well as ‘Begin Command’ and ‘End Command’ of these mapping if the corresponding options are selected. All matching datastores will be replaced with the datastores of the model selected for the consolidation.

Code highlight

The replacement of the datastore in the old model with the datastore in the new model is quite trivial: once we have identified the datastore, the binTo() method of the iMapComponent object takes care of everything for us.

if (originalModel.getCode()==originalModelCode){ // Find matching datastore in new model
      newDataStore=datastoref.findByName(datastoreResourceName,newModelCode)
      if (newDataStore==null){
            println("WARNING **** Datastore : " + datastoreResourceName+ " missing in new model " + newModelCode)
      }else{ //replace the component with new binding?
            println("--- replacing with: " + datastoreResourceName + " from " + newModelCode)  
            component.bindTo(newDataStore, false)
      }
}

Renaming Mappings

When could you use this script?

It is not rare for mature projects to see an evolution in naming convention, but often times old objects keep using the old convention. A migration or upgrade can be a great opportunity to review mapping names and make the necessary corrections. This script will allow you to add a prefix and/or a postfix to the mappings names, in addition to replacing string patterns in the mapping names.

Using the script

The code for this script is available here.

In the example below, we are looking for mappings that contain the string ‘Load ’ (including the trailing space) and we change the mapping names to insert ‘Initial ’ at the beginning of the mapping name: 'Load xxx' becomes 'Initial Load xxx'.

Note that in this case, a mapping named ‘Final Load’ would not be renamed since we included a trailing space in the string used to identify the mappings.

Code highlight

Once we have identified a mapping that we want to rename, the new name is set with the Mapping.setName() method:

// Make sure that the mapping matches the names we are looking for
if (map.getName().contains(MappingNamePattern)) {
      MappingName=(map.getName()=~ReplaceString).replaceAll(ReplaceWith)
      MappingName=Prefix + MappingName + Postfix
      println("New name: " + MappingName)
      map.setName(MappingName)
      bNameChanged=true
}

Replacing Knowledge Modules

When would you use this script?

Replacing a KM in a number of mappings is a task that can be needed after a migration from OWB to ODI, as well as independently of any migration effort: you want to change from a project specific KM to a global KM, or you may want to change from an original ODI 11g KM to a new ODI 12c component KM.

In the case of the migrations from OWB, the migration tool will select the default KM for the type of operation that needs to be performed (insert, update, SCD). In one instance of an OWB to ODI migration, we needed a KM that would delete all records that matched the select statement created on the source. We had a custom KM for this, but short of a script, we would have had to manually edit all mappings.

Even if changing a KM requires only a few clicks, the task can become tedious if you have to do this for hundreds of mappings. This script will automate the operation.

Using the script

The code for this script is available here.

This script is pretty straightforward. All project and global KMs are be available for selection in the graphical interface: pick the KM that you want replaced as well as the new KM, and click OK to perform the substitution. The console lists the mappings where the KMs are substituted.

Note that in the example above, any mapping with a name that includes the substring _INCR_ has its IKM replaced (if the KM matches the other search criteria of course).

Code highlight

We parse the physical nodes in search for KMs. IKM nodes can include both IKM and CKMs. The nodes are part of a MapPhysicalDesign object.

if (map.getName().contains(MappingNamePattern)){ // get the list of physical designs:
      PhysicalDesignList=map.getExistingPhysicalDesigns()
      for (pd in PhysicalDesignList){ // get the list of physical nodes:
            PhysicalNodesList=pd.getPhysicalNodes()
            for (pn in PhysicalNodesList){
                  if (pn.isLKMNode()){ // LKM only
                        CurrentLKMName=pn.getLKMName()
                        if ((CurrentLKMName!=null) && (KMType=='LKM') && (CurrentLKMName==OriginalKMName)){
                              LKMs = LKMf.findByName(NewKMName);
                              myLKM = LKMs.iterator().next();
                              pn.setLKM(myLKM) bKMChanged=true
                        }
                  }else if (pn.isIKMNode()){
                        (…)

Replacing Optimization Context

The optimization context is something that you do not usually want to modify as a rule, as it impacts the code generation. However, as you migrate existing projects from OWB, you may have to change this value.

When would you use this script?

The use for this script is relatively limited. The importance of the optimization context is that if you have different topology layout between your development and production contexts, you can force ODI to behave as if all environments were the same (typically the same as the production context). For more on optimization contexts, you can look at this earlier post.

In ODI 12c, the optimization context is part of the parameters of the Deployment Specifications, available in the Physical tab of the mappings:

Using the script

The code for this script is available here.

Again a pretty straightforward script, simply select the original Optimization Context and the new one to use as a replacement.

Code highlight

This sample is pretty basic: it is only a matter of comparing the existing Optimization Context to the one we searching for, and to perform the replacement when we find a match. This operation is done at the MapPhysicalDesign level:

for (pd in PhysicalDesignList){
      currentOptimizationContext=pd.getOptimizationContext()
      print("OptimizationContext: " + currentOptimizationContext.getName())
      if (currentOptimizationContext.getName()==OriginalOptimizationContext){
            pd.setOptimizationContextByName(NewOptimizationContext)
            println(" replaced with " + NewOptimizationContext)
      }else println(" not replaced")
} // Physical Design

Conclusion

Groovy is a very convenient way to automate massive changes that would be extremely tedious if we were to perform them manually. By leveraging the ODI SDK, we also ensure that the changes that we are making in the ODI repository are valid from an application perspective and from a referential integrity perspective.

For more ODI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for ODI.

References

• The OWB-ODI migration documentation can be found here: http://docs.oracle.com/middleware/1212/odi/ODIMG/toc.htm. Note that the utility comes as patches that you have to apply to OWB and ODI. Details for the patch numbers are available on the Oracle Support web site. After logging in, look for doc ID 1503877.1: How To Migrate From OWB To ODI

• The documentation for the ODI SDK (version 12.1.3) can be found here: http://docs.oracle.com/middleware/1213/odi/reference-java-api/toc.html

• The documentation for Swingbuilder (used for the user interface) can be found here:

• http://groovy.codehaus.org/Swing+Builder (scroll to the bottom of the article for a list of available widgets)

• David Allan’s introduction on Groovy Swingbuilder can be found here:

• https://blogs.oracle.com/dataintegration/entry/odi_accelerator_launchpad_getting_groovy

• David Allan’s reference blog on the SDK for ODI 12c (one you really want to bookmark!):

• https://blogs.oracle.com/dataintegration/entry/odi_12c_mapping_sdk_the