by Julien Testut and David Allan
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
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
(188.8.131.52) to ODI 12c (12.1.2).
The following features are provided to make the transition to ODI easier:
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.
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
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
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.
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.
migration process itself, a good read to be prepared
for preparing to migrate
on using the migration utility
the migration – what to look for in the logs and so on
reference guide and a list of migrated components
The Technical Details
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.
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;
– performs a read only check, which is the fastest way to get an understanding
of how much will be migrated.
– 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.
– performs the migration and commit the newly migrated objects in the 12c
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;
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.
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;
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.
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;