Thursday, February 23, 2017

All about Oracle Data Integrator(ODI) Variables

ODI variable is an ODI object that can be used to store values of string, date or number datatypes. Variables can be both static and dynamic, they can be updated at run-time using a query. These variables can be passed to other ODI objects like interfaces or procedures as filters or as column values.
  • Can store String/Date/Number values.
  • Values are updated during run-time.
  • Values can be passed to other ODI objects like Procedures and Interfaces.
  • Can either be Static/Dynamic in nature. 
  • Dynamic ODI variables can be refreshed with a SQL query executed against a source database.
  • Variables are commonly used for filter conditions, case conditions etc in interfaces, procedures and packages.
The scope of an ODI variable is two, 

Global: The variable can be called across projects in ODI repository.

Project: The scope of the ODI variable is limited to the particular project. 

There are three attributes to an ODI variable, 
  1. Name
  2. Datatype
  3. Action
Action determines how values are to be stored in an ODI variable, The action types are 

Persistent: Values are stored for a particular session
Last Value: Stores the last value. 
Historize: Stores current the value and the others in history. Very useful if your variable values change frequently over time. Helps in debugging. 

An ODI variable can be called using the below syntax,

#PROJECT_NAME.Variable_Name

ODI used scenario examples, 
  1. Imagine you are passing a date value dynamically from database to fetch records for ETL load. Instead of using a sub query filter, you can fetch the date value to a variable and use it in all your procedures and interfaces. 
  2. Another scenario is storing JDBC url. You might be using JDBC urls at different places in an ODI instance. If hardcoded at each place, it would be hard to find and map the change wherever the same JDBC url has been used. Instead you can save the JDBC url as an ODI variable and then use the variable name wherever needed in topology. 
Oracle has a pretty neat document on variables. 


No comments:

Post a Comment