ODI Variables

Variables are used in ODI to handle ETL parameters. It is a very powerful feature and one needs to understand them to effectively use them in your project. The purpose of this article is to explain ODI variables, different types and its usage.

What is an ODI Variable?

A variable is an object that stores a single value. This value can be a string, a number or a date. The variable value is stored in Oracle Data Integrator. It can be used in several places in your projects, and its value can be updated at run-time.

A variable has the one or more of the following properties:

• A static variable has a default value defined.
• A declared variable can have the value passed as a parameter when running a scenario using that variable.
• A dynamic variable value can be refreshed with the result of a statement executed on one of your data servers.
• An incremental variable is used to increment a counter in a loop inside a package.
• An evaluate variable is used to evaluate an expression and branch the execution sequence in a package.
• Variables are used in the expressions and code of interfaces, procedures, steps etc.

Variablescan be used in any expression (SQL or others), as well as within the metadata of the repository. A variable is resolved when the command containing it is executed by the agent or the graphical interface.

Create a Variable

1. Right click on Variables Node in Project or on Global Variables node in Global Objects tab in Designer navigator and click on Insert Variable.
2. Provide the Name, Action, Datatype and default value for the variable.
3. If the variable value has to be picked from a database table or flat file, select the refreshing tab and enter the query on the corresponding logical schema.
4. Save the variable. The variable appears in the tree view.
5. Right click on the variable and select Test to test the variable. For more information on creating variable, refer Working with Variables

Using Variables

Variables are used in almost all ODI objects.

Packages: In Packages, Variables are used in one or more of the below forms

1. Declare Variable: Variables are declared in the initial steps of Package. Values can be passed to these variables at run time during the execution of Scenario. This is similar to Declaration section of a PL/SQL package
2. Refreshing: When a dynamic variable is used in ODI package steps like Interface, procedure etc, the value should be refreshed just before its usage so that the latest value is used in the Interface. To achieve that, after dragging the variable onto diagram tab of ODI Package, the type of variable is set to Refresh Variable.
3. Assigning: A value can be assigned to a variable inside an ODI package. This is achieved using the Set Variable step of ODI.
4. Incrementing: A Set Variable step in ODI package can be used to either increment or decrement a variable. This functionality is useful in creating loops inside a ODI package.
5. Conditional Evaluation: An Evaluate variable is used to evaluate a condition in ODI package. This functionality is used when there is a need to conditionally change the path of execution of ODI steps.

Variables are further used in Interfaces, Procedures, Functions etc. They are used in Mappings, Joins, filters and in KM options within an interface.

To substitute the value of variable in an expression, precede its name with ‘#’. At run time, the graphical interface of ODI or the ODI agent replaces the variable with its value.

Scope of ODI Variable:

Global variables can be used across projects and used as ‘#GLOBAL.<variable name>.

Project variables are specific to a project to which they belong and used as ‘#<project code>.<variable name>

Swapna Alluri

Add comment