A major Greek Telecommunications Operator needed to upgrade from Oracle Warehouse Builder 11.2.0.4 to Oracle Data Integrator

Challenges

The OWB repositories contained more than 10.000 mappings and more than 2.200 process flows. Oracle provides a migration utility that does not convert all mappings correctly. Furthermore, the Oracle migration utility does not convert process flows. Finally, testing mapping correctness is difficult to verify after the initial conversion

Solutions

For handling the bulk of the problem, Neurocom developed a set of utilities to accelerate the migration from OWB to ODI. Starting from a root process flow, a utility has been created to provide the list of mappings that need to be converted. If the process flow invokes other process flows, the utility retrieves all invoked mappings recursively. After the initial mapping conversion, a utility compares the code of the OWB mapping with the code of the ODI mapping. The comparison involves SQL tests for sources/targets, columns and execution plan equality. Furthermore, the utility tests both codes on the same database against real data to ensure data equality. If all tests pass, the conversion is marked as successful, otherwise a report is created and the developer will investigate further. After the developer makes corrections to the resulting mapping, the tests are repeated using the same utility.

For migrating the OWB Process Flows, Neurocom developed a conversion utility from OWB process flows to ODI Load Plans. The conversion utility converts successfully the most frequent process flow objects (mappings, PL/SQL procedures and functions, variables, AND/OR/FORK operators, e-mail and user defined scripts). The resulting ODI load plan, maintains the original parallelism and dependencies. Some special conditions (e.g. loops) are not supported an a warning is created for a developer to correct the process flow manually.

After all checks in mappings and process flows, a deployment utility developed by Neurocom creates a package of all related objects that can be deployed from the ODI testing repository to production.

Results

The utilities accelerate the testing and deployment phases tremendously, with a single developer being able to test and verify the correctness of at least ten times more mappings that it would take to do it manually. Also, the process flow migration is very helpful in speeding up the migration and a developer only needs to verify the resulting load plan afterwards. Finally, the deployment utility makes it easy to transfer a load plan to production, avoiding the need to manually create the list of objects to be deployed