Best Practices from Oracle Development's A‑Team

Using ODI with a Development Topology that Doesn’t Match Production Topology

Christophe Dupupet
Sr Director, A-Team - Cloud Solutions Architects


The cost of reproducing a production environment for the purpose of developing and testing data integration solutions can be prohibitive. A common approach is to downscale the development environment by grouping databases and applications. The purpose of this post is to explain how to define the ODI Topology when the physical implementation differs between the development and production environments.

Using ODI with a Development Topology that Doesn’t Match Production Topology

Let’s consider a setup where the production environment uses three distinct database installations to host data that needs to be aggregated. By reducing the volume of data, it is possible to group all three databases in one single installation that developers can leverage as they write their data integration code. This simplifies the infrastructure maintenance and greatly reduces the overall cost. We have represented such a setup in figure 1 below.

development doesn't match production

Figure 1: Simplified architecture to reduce the cost of development

The challenge with such a setup will be to make sure that the code designed in the development environment will run properly in the production environment. As a point of reference, an ODI Topology that would match the above infrastructure is represented in figure 2.

ODI view of different development / Production

Figure 2: Different Topology layout for development and production environments

ODI Code Generation

The SQL code that ODI generates is optimized for the infrastructure where the code runs. If we look at the Development environment represented above, best practices recommend that we create a dedicated login for the database, and then use that single login to access data from the two source schemas and then use the same login to write to the target schema.

From an ODI Topology perspective, this means that there is one single data server under which we define 3 physical schemas.

Based on the above Topology declaration, ODI optimizes the data movement when you create your mappings. It makes sure that data flows as quickly as possible from the source schemas to the target schemas. From that perspective, ODI does not use any LKM for the source schemas: all data is already in the database, there is no need to stage the data in a C$ table (or C$ view or C$ external table).

Sources and target in the same database

Figure 3: Sources and targets schemas in the same database

Conversely, if the data happens to be physically located on separate servers, then ODI automatically introduces LKMs to bring the data into the target server.

Source and Target in Separate Databases

Figure 4: Separate databases for two sources and target schema

The challenge with topology discrepancies

If the development environment matches the architecture shown on figure 3, and the production environment matches the architecture represented on 4, the scenarios generated in the development environment cannot run in the production environment as is. But the last thing we want to do is to redesign the developed code as we promote it from environment to environment.

With the challenge now clearly stated, let’s see what ODI provides to solve this conundrum.

Option 1: Optimization contexts

When you are building mappings, you can ask ODI to generate code based on a specific execution environment, as long as all environments are defined in the same Master repository. In this case, ODI Contexts will represent the different environments.

If we look at the use case provided here as an example, we can ask ODI to generate the code explicitly for the production environment even though the development environment is much simpler. In other words, we can force the use of LKMs to better represent the reality of production even if they are not needed to process the data in the development environment.

If you click on the Physical tab or your mappings on ODI 12c, you can see an option called Optimization Context (in earlier versions of ODI, this same option is available in the Overview tab of Interfaces). If there is a discrepancy between the different environments, setting this option properly guarantees that the code always matches the layout of the environment of your choice.

Optimization Context Selection

Figure 5: Optimization context selection

One challenge with this approach though is that you will have to remember to select the proper optimization context for every single mapping that is created. Unless of course you use the production context as your default context for Designer in the User Preferences: Select the Tools menu, then Preferences… Under ODI/System you can set the Default Context for Designer

Set Default Context

Figure 6: ODI preferences to set the default context for Designer

 For users of older releases of ODI you will find this parameter under the menu ODI/User Parameter.

Option 2: Design the development Topology to match the production Topology in ODI

Another approach would be to ignore the specifics of the simplified Development environment and design everything so that it matches the production environment.

The ODI best practice of declaring all schemas in the same database under a single data server in Topology has only one purpose: making sure that ODI generates the most optimized code for the environment. But if the production environment does not have all schemas in the same database, then we can create different data servers in the development environment so that ODI believes that there are different databases.

In a more extreme example we have combined all our source and target tables in the same database schema for our development environment. This does not prevent us from creating 3 separate database and schema definitions. Then we can have 3 separate models that each contain only the relevant source or target tables in order to match the production environment:

Development Simulates Production

Figure 7: Comparing real data organization vs. simulated data organization

If we use a Topology organization that always matches the production environment, then we never have to worry about setting the optimization contexts in any of the mappings.


It is possible for ODI to always generate code that matches your production environment infrastructure even if it differs from your development environment. Just make sure that you are aware of these discrepancies as you lay out your Topology environment so that you can select the approach that best fits the specifics of your projects.

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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha