Data Engineering

A leading telecommunications service provider was seeking a trusted partner to help with upgrade from Oracle Warehouse Builder 11.2.0.4 to Oracle Data Integrator.

Challenge

Our client faced a daunting challenge. Their existing Oracle Warehouse Builder repositories contained over 10,000 mappings and more than 2,200 complex process flows. While Oracle provided a migration utility, it fell short in accurately converting all mappings and process flows. Additionally, verifying the correctness of the converted mappings proved to be a formidable task.

Solution

To address those obstacles, Neurocom data engineering team developed a suite of powerful utilities specifically designed to expedite the migration from Oracle Warehouse Builder (OWB) to Oracle Data Integrator (ODI). Our approach focused on tackling the core flows head-on, ensuring a smooth transition.

The first utility analyzes each root process flow and intelligently identify the mappings that require conversion. Even if the process flow invokes other process flows, our utility automatically retrieves all invoked mappings recursively.

After the initial mapping conversion, another utility performs a meticulous code comparison between the OWB and ODI mappings. This comprehensive analysis includes SQL tests for sources, targets, columns, and execution plan equality. Moreover, we validate the accuracy of both codes using real data on the same database. Any disparities are promptly flagged for further investigation and resolution. Our process ensures that the resulting mapping is error-free and aligned with the initial version.

We’ve also developed a specialized conversion utility that seamlessly migrates OWB process flows to ODI Load Plans. This tool adeptly handles the conversion of various process flow objects, including mappings, PL/SQL procedures and functions, variables, AND/OR/FORK operators, e-mail and user-defined scripts. Our conversion utility preserves the original parallelism and dependencies in the resulting ODI load plan, streamlining the migration process. While certain exceptional conditions such as loops require manual adjustment, our utility provides clear warnings for developers to address them swiftly.

The final utility developed is a deployment utility that simplifies the transfer of the migrated load plan to production. It creates a comprehensive package encompassing all related objects, enabling seamless and hassle-free deployment from the ODI testing repository to production.

Results

The results speak for themselves. Our meticulously crafted utilities have revolutionized the testing and deployment phases, empowering developers to accomplish more in less time. With our solution, a single developer can now effortlessly test and verify the correctness of at least ten times more mappings compared to manual methods. The streamlined process flow migration significantly accelerates the overall migration journey, requiring only a simple verification of the resulting load plan. Our deployment utility adds the final touch of simplicity by eliminating the need for laborious manual object listing.