Thursday, March 1, 2012

Setting up ODI Environments

Content: There’s a common misconception on how to set up environments for Oracle Data Integrator (ODI). While this setup doesn’t prevent you from building integration processes, it causes confusion and is the root cause for serious problems arising. This post will tell you what you need to know, so enjoy the benefit of learning from the experience of many ODI implementations and set yourself up for a successful implementation right from the start (or just contact MindStream Analytics and we’ll give you industry-leading value from inception throughout the entire development and operating life cycle).

Since 2008, I’ve been involved in many ODI implementations as Integration Lead and Architect. Thinking back to my first project always puts me a little bit at unease, although I’ve also built some neat processes there. Today I would have definitely done some things a lot different, but then again it was a great learning experience that a lot me to get very familiar with the underlying principles. (Also the client asked me to come back because the intermediary Consulting firm wasn’t able to perform as expected in this complex environment).

Since Oracle designated ODI as the replacement for Hyperion Application Link (HAL), a lot of clients are starting from scratch with their implementations and environments. This is always very nice, like a fresh breeze to an optimistic start, because it is possible to make sure that all the best practices that I’ve developed over time can be implemented. However, one thing is often not optimal: the initial installation and configuration of the environments. I’m not trying to blame the infrastructure consultants, they are doing a great job getting environments up and running, but I think it’s a matter of understanding the specific requirements of ODI which differ from the other Hyperion products. The main point here is that there are significant differences between a development and a production environment. While these pretty much contain the same artifacts throughout all environments for Hyperion Planning and Financial Management (hierarchies, rules, calc scripts etc.), the objects that ODI uses come in different formats.

Typically ODI is being installed in each individual environment, e.g. DEV, TEST, PROD. While the installation can usually be done pretty much in the same way (install ODI Studio, Standalone and/or J2EE agent, ODI Console), there are some differences in configuring the repositories in the different environments. Let’s take a look at the different repository types:
  • Master Repository: specify available Technologies, stores connectivity information (Topology), set up Work Repositories, define Security
  • Work Repository: generally, a Work Repository contains definitions of the processes and the execution logs. There are two types, though:
    • Development Work Repository (DWR): allows creation and modification of integration processes and components; ODI Designer module is only available for this type of Work Repository
    • Execution Work Repository (EWR): only allows execution of Scenarios (executables), all objects are read-only
What I’ve noticed several times was that all environments were set up the same way: one Master Repository and one Development Work Repository in DEV, TEST and PROD. While this actually does work, I would be concerned that it may be the root cause for future inconsistencies. It causes confusion because it allows a developer to create integration processes in other environments than in DEV. While this is often considered a quick and easy way to develop hot fixes and a workaround to handle incorrectly deemed “migration errors” due to hard coded paths and application names, it’s the beginning of serious problems with ODI – by invitation. If someone really understands ODI, there are actually very elegant and efficient ways to deal with every aspect of these concerns (but this is a different discussion).

EnvironmentDevelopment WRExecution WRMaster Repository
DEVXX (optional; DWR is usually sufficient)X
STAGE (if desired)XX

Note: instead of creating one Master Repository per environment, it is also possible to create one centralized Master Repository which can be accessed from all environments (see below).

A Development Work Repository should only exist in DEV: one place to make changes, all other environments will only execute the processes which have been designed in DEV. This principle is the foundation that every software project should follow unless you want to face the risk of running into serious issues with version inconsistencies.

All other environments should only have an Execution Work Repository, to transfer over and execute the Scenarios (“Executables”) which were designed in DEV. ODI supports this principle by restricting the Designer to be only available in a Development Work Repository. Trying to connect via Designer to an Execution Work Repository leads to an error message. This way, Projects, Models etc. can only be accessed in DEV. Access to EWRs is granted via the Operator, the main purpose being monitoring and debugging processes as well as importing the Scenarios which have been created in DEV.

The setup of the Master Repository is different. It is actually possible to leverage one central Master Repository for all environments. All types of WRs can access the same Master Repository. From an architecture standpoint I like this idea because it simplifies the migration process. However, there are some concerns about this. From my experience, most clients are more comfortable with a separate Master in each environment rather than having one central repository. This is particularly the case because it is one way to prevent users to execute a process in the wrong Context.

I do understand this concern, but on the same token would propose designing a Security Concept which handles these access matters. Once this has been set up, there are several advantages over the environment specific Master Repositories, especially in regards to Migration as well as setting up Disaster Recovery environments.


  1. Hi,

    Nice post.
    I am looking for information regarding License that I need to procure to setup ODI development environment. Will you be able to help me?


    1. Hi Murali,

      I'm not the right person to ask about licensing and would recommend to look on Oracle's website for information instead. Typically, a development environment is pretty easy to set up, but I'm not familiar with your exact situation (e.g. location, export restrictions, non-competes). Try or the Oracle Community at

      Hope this helps.


  2. Hi Matt,

    This was really helpful. Can you guide me through a step by step process of setting up a execution work repository with an existing export of master or work repository from a different environment ?

    I just need the EWR on my production environment.

    Thanks for you suggestions in advance, it was a great post to read through on setting up ODI environments.


  3. Hi Matthias Heilos,
    Post isvery helpful. I have a question here in our development environment do we need to install ODI Studio on each developer's workstation and provide access to a centralized Repository Development Work repository database.

  4. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (even if they're dating somebody else now) you need to watch this video
    right away...

    (VIDEO) Text Your Ex Back?