Using ODI Variables in Topology: Pushing Contexts to the Extreme
By Christophe Dupupet on May 08, 2009
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!
1. DEFINING THE ENVIRONMENT
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 (
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. SETUP: CREATION OF THE VARIABLE AND TOPOLOGY DEFINITION
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:
select SERVER_NAME from ODI_SERVERS
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
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
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:
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.
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:
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.
3. EXECUTING CODE IN THE DYNAMIC CONTEXT
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.
4. UNDERSTANDING POTENTIAL ERRORS
- 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 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.