Planning the journey from Oracle Warehouse Builder to Oracle Data Integrator
By Madhu Nair on Dec 02, 2013
by Julien Testut and David Allan
In this post we are going to discuss how Oracle Warehouse Builder (OWB) customers can easily migrate to Oracle Data Integrator 12c (ODI12c) thanks to two key new features introduced recently.
As mentioned in our statement of direction Oracle Data Integrator is Oracle's strategic product for heterogeneous data integration. recognizing that many Oracle Database customers have a significant investment in OWB, ODI now provides mechanisms to support a phased migration, specifically from OWB 11gR2 (18.104.22.168) to ODI 12c (12.1.2).
The following features are provided to make the transition to ODI easier:
· ODI 12c supports the execution and administration of OWB 11gR2 jobs directly within ODI Studio, ODI Console and Enterprise Manager. This provides a single orchestration and monitoring solution and allows companies to continue running OWB Mappings and perform a phased migration to ODI.
· A new migration utility is provided that will automatically translate many OWB objects and mappings into their ODI equivalents. The migration utility is a command-line tool and requires two patches: one for OWB (17547241) and one for ODI (17053768, both can be found on My Oracle Support.
Phased Migration using OWB and ODI run time Integration
run time integration of Oracle Warehouse Builder and Oracle Data Integrator can
be used a first step towards aligning with Oracle’s data integration strategy.
You can start new developments in ODI 12c while continuing to run side-by-side
the two products and start migrating OWB Mappings into ODI Mappings using the
In Oracle Data Integrator 12c it is now possible to define a connection to an Oracle Warehouse Builder workspace in Topology Navigator. Storing the connection and credential details in the ODI repository allows developers to invoke OWB processes in Packages with the OdiStartOwbJob tool (OdiStartOwbJob documentation). It also allows developers and operators to monitor the execution of OWB processes in ODI Studio, ODI Console or Enterprise Manager along with the rest of the ODI jobs.
The Technical Details
Invoking OWB processes in ODI 12c is simple. You have to first add the OdiStartOwbJob tool to a Package. Then point the tool to the OWB workspace entry in Topology. This then allows you to browse the content of the OWB workspace and select the specific processes you’d like to invoke as you can see in the following screenshot:
Once the Package design is complete, you can execute it and monitor its execution in ODI. The OWB and ODI logs have been integrated allowing end users to centrally monitor their ETL processes.
OWB to ODI Migration Utility
In addition to the integration of OWB jobs there is also a metadata migration utility to migrate metadata from OWB 11gR2 to ODI 12c. A wide range of metadata can now be migrated with this initial release;(you can find a complete list of objects in the documentation). A large selection of core mapping operators are supported – In addition to the patch containing the utility for the OWB migration, there is also a patch for ODI 12c, and new Knowledge Modules to support some of the OWB mapping capabilities. The user documentation explores these considerations in great detail.
1. The migration process itself, a good read to be prepared
2. Details for preparing to migrate
3. Information on using the migration utility
4. Reviewing the migration – what to look for in the logs and so on
reference guide and a list of migrated components
The Technical Details
Below you can see a project in OWB 11gR2; this has a number of modules including Oracle and flat file modules; the Oracle one has mappings for loading a data warehouse from external tables based on flat files.
The migration utility is a command line tool that is executed from the OWB home. The utility uses a driver file to specify which modules and objects to migrate. It also has 3 modes of operation, this lets you discover how much of your project will get migrated. The modes are;
· FAST_CHECK – performs a read only check, which is the fastest way to get an understanding of how much will be migrated.
· DRY_RUN – a sanity check mode which will try creating the objects in-memory using the ODI 12c SDK but will not commit in the ODI repository.
· RUN – performs the migration and commit the newly migrated objects in the 12c repository.
The driver properties file can specify the specific objects to migrate, specify wildcards and lists of objects. The utility produces a report and a log file from its execution. For example, when I migrated the OWB project above, I used the following driver file;
The utility driver parameters are fully document in the user documentation. Firstly I ran the fast check just to see if there were any issues reported. There were none which was good news and bad – I wanted to illustrate an example that wasn’t migrated, so I will create another to show you what happens.
In the report file when I executed there is a convenient summary of what happened, you can quickly see what was migrated, the number of objects and also a column defining if any were not migrated;
You can see the migration utility also has migrated external tables; As part of the patch for ODI there is a new component KM to define an external table on top of the flat file definition. This supports the external table capabilities that were in OWB (both bound and unbound external table). Below you can see the mapping loading the sales data that was shown above, it looks identical to what was in OWB, the datastore representing the external table is still the source, the data is split and written to multiple targets.
The mapping has been created and configured to mimic the OWB behavior. The utility doesn’t configure the multi insert IKMs as this was an optimization within the OWB code generator, in ODI 12c you simply change the assignment of the IKM on each target. So in above my targets were each assigned IKM Oracle Insert, I changed them both to IKM Oracle Multi-Insert after the migration. You can see the physical configuration below;
You can then generate the code and in simulation mode see the Oracle multi table insert generated processing the external table (also created in the mapping) as source.
I mentioned that the report details information on the objects which cannot be migrated. An example is a pure row based PL\SQL mapping (for example where a PL\SQL procedure was used), this kind of mapping would result in a mapping not automatically migrated, you would see errors such as the following in the migration report;
o [ERROR][Migration][MU-5003]Unable to migrate mapping with mapping operator TRANSFORMATION:GEN_DATA_PROC with OUT parameter DATA_NAME.
o [ERROR][Migration][MU-5003]Unable to migrate mapping with mapping operator TRANSFORMATION:GEN_DATA_PROC with OUT parameter DATA_VALUE.
o [ERROR][Migration][MU-5006]Unable to migrate mapping with mapping operator TRANSFORMATION:GEN_DATA_PROC that does not define return attribute.
With these kinds of cases one approach to moving forward to is wrap the PL\SQL procedure in a function if possible. The utility will serve multiple purposes – along with migrating the metadata, for OWB users it will also serve as a way of understanding how concepts map from OWB to ODI.
In this post
we discussed how Oracle Warehouse Builder customers can easily migrate to
Oracle Data Integrator 12c thanks to the run time integration and metadata
migration features introduced recently.
That’s a little taster of the capabilities with the migration, more detailed information on specific components will keep coming. There are now a lot of new exciting capabilities in each. These components are now no longer Oracle only but also can be used for many different systems so there is a lot of fun and exciting possibilities, stay tuned!