Friday May 08, 2009

Using ODI Variables in Topology: Pushing Contexts to the Extreme

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Context, Topology, Logical Architecture, Physical Architecture and Variables are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.

In a previous post, we have discussed the notion of Context and how powerful it can be. There is a limit however. If you remember our discussion, each server, each schema on each server, have to be defined in the ODI Topology. Now imagine that you design processes that have to run on a large number of systems. The exact same code will be executed; only the physical location will be different. How about having thousands of systems where this code has to run. Who wants to maintain the connection information manually through a graphical interface? And maintain thousands of contexts while doing so?

Realistically, I think that contexts are fine up to about a dozen of environments. Beyond that, you need an environment that will be more dynamic. But we want to keep the benefits of having the exact same code on all systems, the flexibility of having a complete separation of the generated code and of the execution location. The solution? Use variables in Topology!


Before jumping heads down in the usage of variables in Topology, I strongly recommend the creation of 2 contexts:
- A development context where all URL in topology point to an actual server, not using the variables. This will ensure that data can be viewed, and interfaces can be tested without any concerns regarding the variables resolution;
- The dynamic context (similar to what will be used in QA and Production) will use the variables in topology to name the servers, port numbers, user names for the connections, etc. The package will assign the appropriate values to the variable and run the interfaces on the appropriate servers. This context will only be used to validate that the processes defined in the Development context work properly when we use the variables.

Independently from ODI, we will need a table to store the values that will be used for the Topology variables. For this example, will be simply use different server names. Keep in mind that other topology parameters can be set using this same technique.

The table for our structure will contain the server names, as well as the name of an ODI agent: with many processes running concurrently, it is better to assign pools of servers to different agents. We will use the following structure:

create table ODI_SERVERS (
SERVER_NAME varchar(50),
AGENT_NAME varchar(50)

Loading this table is not part of the description we have here. If you have a list of servers available somewhere, ODI would be the perfect tool to load your table though…


2.1 Creation of the Variable
Details on the creation of a variable can be found in this post. We will simply review the main steps here.

Create a new variable called ServerName.
The data type is Alphanumeric.
You can set the default value to localhost.
You can use the Description field to describe the variable.
In the Refreshing tab, select the logical schema that hosts your ODI_SERVERS table and enter the following query:


Note: for more flexibility in the execution of your code, you should never hard-code a schema name – nor assume that the login used to connect to a server defaults to your schema name. Best practice in the example above would be to let ODI complete the table name with the schema name by using the following syntax:

select SERVER_NAME from <%=odiRef.getObjectName(“L”, “ODI_SERVERS”, “D”) %>


2.2 Definition of the Topology

2.2.1 Contexts

To get more details on the creation of contexts, please check out this example. If you already know your way around Topology, create the following two contexts: Development and Dynamic

Servers Context

2.2.2 Development Server: Hardcoded Values

In topology, define one of the servers that you want to access. We will use this server for our development work, and ignore the variables for now: enter the parameters as usual for the definition if this server:

Development Server

Make sure to test the connectivity to validate your parameters. Define a physical schemas as usual under this data server. Map it to the logical schema ORA_DYNAMIC_SERVER for the Development context.

Note: you will have to create the logical schema to perform this mapping.

Servers: Development Schema

2.2.3 Dynamic Server: Using the Variables

For the dynamic contexts, we will use the variable as part of the connection string (aka the JDBC URL). Instead of typing the actual hostname, type the variable name, including the CODE of the project where the variable was created (or GLOBAL for a global variable).

URL example for Oracle:

Servers: Dynamic

This physical server will be considered as the dynamic server. Map its physical schema to the logical schema defined in the previous step, in the Dynamic context this time.

Dynamic Server Schema


Your code will work as usual in the Development environment: you can validate your transformations, your processing logic and processing time. When you will want to use the Dynamic context though, a few more steps will be required.

3.1 Connection to the Databases

The ODI agents are in charge of the connection to the databases, sources and targets. At the beginning of the execution of a process, the agent will connect to the Master Repository to retrieve the Topology information. Then it will connect to the databases, generate the code, and send that code to the databases. This represents a challenge in our case, since we need to set a value for our variables before the agent connects to the databases: we cannot set the value of our variables in the package that will be executed...

3.2 Setting the Values for Topology Variables

The solution will be to pass these values as parameters to the process, so that the values are known to the agent before it establishes the connection. You can set the values no matter how you start the scenario: from a command line interface, from another scenario or from an ODI procedure, from a web service or from a Java API. Remember to declare your variables at the beginning of your package to make sure that the value of the parameters gets properly stored though!

For more details on how to pass parameters to an ODI process, you can check out this other post.


- Make sure that the variable name is properly spelled in Topology (Variable names are case sensitive
- Make sure that the variable is declared at the beginning of your package
- Make sure that the variable is referenced with its project CODE (not the project name) in Topology

For more information on ODI contexts, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "What is the Topology? "

All Screenshots were taken using version of ODI. Actual icons and graphical representations may vary with other versions of ODI.

Monday Apr 27, 2009

Executing the Same Code in All Environments: ODI Contexts.

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Context, Topology, Logical Architecture and Physical Architecture are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

The concept of Contexts in ODI is both very powerful and very convenient. Over the years, I have seen too many people ignore this feature, or not use it properly – hence not fully taking advantage of the flexibility offered by the tool. Hopefully, this brief discussion on the subject will help with a better overall utilization of this feature.


A typical challenge for any code that accesses remote systems is to update the connection parameters to access these without disrupting the code that has been developed. In particular in data integration solutions, when developers promote their code from development to QA and then to production, any modification of the code could alter its quality.

Many software solutions will provide parameter files that can be edited, or variables and parameters than can be changed to reflect the change of environment. The downside of these solutions is that maintenance is performed outside of the tool’s control, and leaves the door open for mis-configuration.

The architecture of ODI has been defined with this issue in mind. To solve this problem, developers will never have to know where they are physically connecting: the administrator will give them an alias (say ORACLE_SALES) and that will give them the connection they require. The details of the connection information is entered and managed by the administrator, through the GUI. This will allow the administrator to validate the connection strings and to check the validity of all environments: missing connections can be easily identified, different environments can be compared, etc.

As developers use the metadata to design the transformations, they have a limited need to access the actual systems. They will need to access the actual systems in two cases: to retrieve the Metadata (the reverse engineering operation) and to run the data integration processes. In the QA and production environments, we will need to access the systems as well to run the data integration jobs.

When metadata is imported from actual system, or when code is about to be executed, ODI will ask for the selection of a Context to define the execution environment.

The following screenshots show where ODI will prompt you for a context, and how you can switch from one execution context to the next.

Execution Context

Execution Context Selection

This of course assumes that you have enough security privileges to do so. Different users may have privileges to run the code in one environment and not in the other. For instance, QA engineers may not have the right to execute the processes in the Development environment or in the Production environment. Or if contexts define different company branches, employees from one branch would not be allowed to run code in a different branch.


The contexts in ODI are basically labels that you can create as needed. ODI comes with only one context called Global. Typical implementations would actually have three contexts: Development, QA, Production. As mentioned earlier, other implementations could define contexts for different branch or anything that identifies different execution locations.

Contexts will be defined in the Topology interface, along with 2 other types of objects: Logical Schemas and Physical Schemas. In the Topology GUI, you will find three corresponding tabs: Physical Architecture, Contexts, Logical Architecture.


The physical and logical architectures are organized by technologies.


The Context will simply list the contexts that you have defined, independently of the technologies.



Let us now take a databases for a data integration project. Let us say this is a sales database. The structure of the data will have to be consistent from Development to QA, and from QA to Production (Over time, the structures will evolve in the different environments. But for the QA to be meaningful, we need to be consistent from Development to QA and from QA to Production).

3.1 Creation of a Logical Schema

To represent the connectivity to the data structures, we will define an alias. Let’s call this alias ORA_SALES. This is our Logical Schema. The Logical Schema is created in the Logical Architecture tree under a given technology.

Logical Schema Creation.PNG

When you create a logical schema, unless you associate it with a context, it is just an alias that points nowhere. Create a logical Schema under the Oracle Technology and call it ORA_SALES. We will revisit this entry shortly after creating contexts, and again after defining the connectivity to the actual physical servers.

3.2 Contexts

Switch now to the Context tab in Topology, and create 3 contexts: Development, Test and Production. Simply give a name to each – no passwords are required.

Development Context.PNG

Once the contexts are created, we can revisit the definition of the logical schema we had created in the first step, and see that there is no physical connection defined for either context. Basically, we have said that we have a data model called ORA_SALES that will exist in three separate environments, but we have not defined the connectivity for either one yet: this will be done in the physical architecture.

3.3 Physical Architecture

The Physical Architecture is where we will define the actual servers that host the data. We will provide the credentials to connect to the servers: user name and password. We will also provide the JDBC connection strings, which usually contain the IP address or host name, the port number for the database, and any additional information as required by the technology: SID or service for Oracle, database name for SQL server, etc.

For this example to work in our environment, you will have to adapt it to point to your own databases and schemas.

Under the Oracle Technology, create a new Data Server (be careful when you make your selection from the menus: a common mistake is to create a new Technology instead of a new server!). Name this server, and then enter the appropriate user name and password for that server.

Physical Credentials

Once you have entered these parameters, you can click on the JDBC tab and enter the database JDBC connection information. Here, connecting to Oracle, we select the Oracle Driver name from the drop-down menu and update the JDBC URL with our server name, port number and SID.

Physical JDBC

Note: if you are not using an Oracle database, you will have to copy the JDBC driver files for your database in the appropriate ODI directory. Check out the ODI documentation for more details on this process.

You can validate the parameters you have entered with the “test” button: at this point the connection should be successful.

Succesfull Connexion

When you will save this entry (click Ok or Apply) ODI will open another window to select a Physical Schema on that server. If the schema window does not appear, you can right-click on the physical server you have just created in the Physical Architecture tree and select Insert Physical Schema.

Note: Different Technologies will have different terminologies and ODI will display the proper terminology as needed. From an ODI perspective though, we will always talk about a “Physical Schema”.

Select the Physical schema name, as well as a “work schema” from the drop-down menus.

Physical Schema Definition

Then click on the Context tab and click on the blue grid button to add a line, where you can select a context and the logical schema name. For instance, Development and ORA_SALES.

Physical Logical Mapping

What you are saying here is that ODI will connect to the physical schema that you have just defined when the generated code will refer to the ORA_SALES object and when the context selected at runtime is set to Development.

You can then define the appropriate servers and/or schemas for your QA and production environments, and associate them with the appropriate Context for your Logical Schema.


Once all physical schemas have been defined and mapped properly, you can edit your logical schema and review your mappings. You can make sure that the schema is actually pointing to a physical schema for all contexts. If a context displays Undefined for the physical schema, then your code cannot run in this context: ODI would not know where to connect.

Logical Schema Review


You can also edit your contexts and select the Schema tab to review the mapping of all logical schemas for this context: all you file directories, source systems and target systems must be properly mapped for your code to run in this environment.

Context Review

For more information on ODI contexts, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "What is the Topology? "

All Screenshots were taken using version of ODI. Actual icons and graphical representations may vary with other versions of ODI.

Monday Apr 20, 2009

Using Variables in ODI: Creating a Loop in a Package

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

In our last post in this series, we looked into passing parameters to dynamically set the name of the file that we want to process. An alternative may be to parse a list of file names that would be stored in a table in your database and to loop over this list to process all files.

To perform this, we will need to perform the following operations:
- Create a variable to store the file name, and a second variable that we will use as a cursor to point in our table
- Define a package and use the variables to loop over the different values in the table

If you need help with the creation of variables please refer to our post on the usage of variables in ODI Interfaces and Packages.


1.1 The Counter Variable

We will start here with the definition of a variable that we will use to loop through your list of files. Let’s call it Counter. I usually use an alphanumeric variable and define the SQL query as being

select #Counter+1 from dual

(Note: the code would vary for non Oracle databases). I find alphanumeric variables easier to process than using numerics as numerics come with decimal values and may require conversions based on where and how you use them. One advantage of numeric variables though is that you can use ODI to increment their values when you select the Assign action on the variable.

Create Counter Variable Counter Variable Query

1.2 The FileName Variable

For this example, we assume that the file names are stored into a table called ODI_FILES_TABLE. This table has only one column with the file names, called FILE_NAME. And we store the table in an Oracle database. You can easily adapt the code for different table structures or databases. Here we will parse the table taking advantage of the ROWNUM returned by the Oracle database.

Create a Variable in the same project, and call it FileName. Define the following query for the FileName variable:

select FILE_NAME
where COUNTER=#Counter

Note that we are using our fist variable to retrieve one record only. To run this statement, remember that the Counter variable MUST have a value – otherwise the where clause would not be valid.

Create FileName Variable FileName Variable Query


We will now create a package to design our loop. In the package, drag and drop the Counter variable and set the action to Set Variable. Assign the value 1 to the variable (select Assign in the properties window and enter the value 1 in the text box below the Assign selection).

Drag and drop the FileName variable in the package after the Counter variable. Set the action on the variable to Refresh Variable. In case of success after the refresh, execute any procedures or interfaces where the FileName is used. In case of failure of the refresh, execute a last step to indicate you are done: a failure here would indicate that you have exhausted all files listed in the table. In our example we are sending an email, but you could execute another branch of your process.

After the execution of the procedures and interfaces, add the Counter variable again, but this time set the action to Refresh Variable: this will increment the value of the variable. (If you are using a numeric variable, you can replace this with a Set Variable action and set the operation to Increment).

Then loop back to the refresh step of the FileName variable: this will either select the next file in the list… or fail and exit the loop.

Loop Package


3.1 A Cleaner Exit

For a cleaner exit than the one described here, you may want to use a third variable where keep track of the number of files in your table with the following query:

select count(*) from ODI_FILES_TABLE

Refresh this new variable at the beginning of the package. You can then compare your Counter variable (using the action Evaluate Variable) to that value and exit the loop when you reach this value.

3.2 Guarantying that the tables are found in all Contexts

Instead of hard-coding schema names in your variable refresh queries, remember to use the ODI Substitution Method that will automatically rebuild the schema name in all contexts: odiRef.getObjectName. For instance:

select FILE_NAME
from (select FILE_NAME, ROWNUM COUNTER from < % = odiRef.getObjectName("ODI_FILES_TABLE") % >)
where COUNTER=#Counter


For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version of ODI. Actual icons and graphical representations may vary with other versions of ODI.


Thursday Apr 09, 2009

Using Parameters in ODI: The Dynamic File Name Example

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

In a previous post we have seen how to use variables in the ODI interfaces and packages.
We used two methods to assign a value these variables: a direct assignment of a value, or a "refresh" statement that was running a SQL query and storing the resulting value in the variable.
Today, we will see how to set the value dynamically as we start our process. The value to be stored in our variable will be passed as a parameter to our process.

The example we will work on is a fairly common one. When dealing with flat files, it is not rare for the actual name of the file to vary over time. An external process may know the file name and pass that name over to ODI for further processing.

For our example to work, we will need to perform two actions:
- Reference the file name dynamically (In the ODI Metadata, we cannot hard-code the file name anymore)
- Pass the file name as a parameter to our process.


1.1 Creation of a Variable

Our first step is to create a variable to store the file name. Variables are defined in projects, even for variables that will be used in the metadata. Select the project where you will use the variable (typically the project that will contain the Interface where the file is used as a source or as a target).
We will create a variable called FileName.

Filename Variable Creation

Defining a default value for the variable is not required, though it is good practice. We do not need an associated query in the refresh tab of the variable object, as we will receive this value as a parameter.
Click Ok to save the definition of the variable.

1.2 Using the variable in the metadata

Now that we have created a variable, we can use it in place of the file name.
Note: If you are not familiar with the definition of Files in the ODI Models (where the metadata are defined) please refer to the ODI Tutorial where you will find a couple of examples.

I usually recommend starting with a hard-coded version of your file name. Copy a version of the file locally, and use it to define the file structure. This will allow you to validate the file format, make sure that the data can be viewed with the ODI GUI, validate alignment of the columns, etc. Pay particularly attention to date columns (unless you define them as strings – after all, this being a file, you can choose the most convenient format for you): dates require the definition of the date format. The ODI documentation gives you the necessary details on how to describe this format (See the Appendix on Date Formats in the Reference Manual).

In our example, we will use the file SRC_SALES_PERSON.txt that is provided as an example with ODI (look under your ODI installation directory under oracledi/demo/file).

Once we have validated that the file format is correct, we will edit the file definition. Double click on the file name in the Model tree, so that we can change the value of the Resource Name. We can keep the Name as is, to have a meaningful description of the nature of the file, and replace the Resource Name with our variable. Whenever using a variable outside of a project, keep in mind that you always have to prefix the variable name with the project CODE. In case of doubt, double click on your project Name in the Project tree: the definition window of the project will show you both the name of the project and the code of the project.

Project Code

In the example above, the project code is SALES.

Back to the Definition of our file, we now change the Resource Name from SRC_SALES_PERSON.txt to #SALES.FileName.

Dynamic File Name

When you click Ok to save this new definition, you can see in the Model tree that ODI will show you both the Name (SRC_SALES_PERSON.txt) and the Resource Name (#SALES.FileName) to indicate that this value will be set dynamically.

File Name In Tree

One important note is that once you use the variable as part of the resource name, you cannot see the data from your file from the GUI as the variable will only be resolved at run-time. From that perspective, it may be useful to have two definitions for the same file: one using the variable, and one that points to a specific file so that debugging is made easier.


2.1 Create a Package and Declare the Variable

You will have to create an interface where you use the file (as you would do for any other file) so that you can really see the operations in action. Then create a package where you drag and drop the FileName variable and the interface. Make sure that the variable is your first step (right-click on the variable icon in the package and select First Step if it is not the case. The first step of your package is identified by a little green triangle over the step name).

Parameters Package

Click on the icon representing the FileName variable and set the action on this variable to Declare Variable. This will authorize the passage of the value as a parameter at run time.

Parameter Declare Variable

2.2 Create a Scenario

You will only be able to pass parameters to a "production ready" object: the scenario. The creation of a scenario is trivial: right-click on the package name, select "generate scenario", edit the scenario name and version if needed and click ok.

Generate Scenario

Set Scenario Name and Version

If you have "declare variable" steps though, you will be prompted to define whether these will be parameters or not.

Scenario Parameters

When this window prompts you, if you do not change anything, all declared variables become placeholders for potential parameter values. Note that in the screenshot above, we have changed the selection from "Use All" to "Selective Use" simply to highlight the variable name. Either selection would have had the same value in our example.

At this point, we now have a scenario that is ready to receive a value for our FileName variable.

2.3 Different ways to start the scenario and pass the parameter

There are many ways to start a scenario. Each one of those will allow you to set the values for your parameters.

2.3.1 Starting the Scenario from Designer or Operator

In both cases, the GUI will prompt you for the value of the parameters after you select in which context and with which agent to run the scenario:

Set Scenario Params Value

ODI will by default select the last value assigned to the variable. You can uncheck the "Last Value" check box and change the value for the parameter. Hit the "Enter" key or the "Tab" key to validate your entry and run the scenario.

2.3.2 Starting the Scenario from a Command Line

You can also start a scenario from a command line interface. The script startscen (.bat for Windows or .sh for Unix-like environments) will take the scenario name as a parameter... along with the name of the variables that you are setting along with their values. Enclose the variable name and its value in double quotes, and prefix the project code with a minus sign (-). Remember to prefix the variable name with the project CODE as usual:

starscen DYNAMICFILENAME 001 "-SALES.FileName=c:/Incoming/Sales09092009.dat"

2.3.3 Starting the Scenario through a Web Service

If you start the scenario from a web service, you are invoking the service OdiInvoke. When you look at the WSDL for this Web Service, you will notice that beyond the scenario name, version and the execution context, you can specify pairs of variable names and values. Again, remember to specify the project code

WSDL to Start Scenario

2.3.4 Starting the Scenario from Another Scenario

If you start a scenario from another ODI package, you can either drag and drop the scenario in the parent package or use the ODI tool OdiStartScen and enter the information manually. When you look at the property window for that step, you will notice an Additional Variables tab. In this tab, you will be able to select your project, variable and to set the value for the variable. Remember to use the Enter or the Tab key to validate your choices as you make them.

Scenario Call From Package


For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version of ODI. Actual icons and graphical representations may vary with other versions of ODI.


Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« July 2016