ODI Variables – General Know-How

If you want to use a ODI variable within a variable simply type variable name in refresh statement. Here is an exampls how you can do. Below SQL statement connects to MSSQL Server and returns if job (named “My_BI_Job”) succesfully finished or not in specific date . Variable #V_SYSDATE is a variable that is refreshed as SYSDATE from DUAL in Oracle database. If this doesn’t work try typing PROJECT_NAME after “#” sign. If you have multiples projects ODI confuses which project to get variable.

select count(1) from msdb.dbo.sysjobhistory
where step_id = 0
and run_status = 1
and job_id = (select job_id from msdb.dbo.sysjobs where name = ‘My_BI_Job’)
and run_date = CAST(SUBSTRING(‘#V_SYSDATE’, 1,8) as integer)

Using ODI functions within a variable

If you need to use ODI reference functions within a variable you need to use below syntax. This SQL statement gives the current session’s explanation if ODI gets error on runtime. Current session’s session number is gathered from ODI reference function.

It is obvious that you may change your infrastructure and migrate to any other database due to business or technical needs. You need to find and replace all the inner select statements and/or variables to replace new schema name. Here is a tooltip to avoid this problem. All you need to do is to use ODI Ref function in order to use Logical Schema instead of writing plain SQL statement. Underlined statement always gives the physical schema name of database “MYDB” and schema “DWH”. If logical schema is chaned somehow, you don’t need to change the code, this code always looks at physical schema in Topology Manager in execution context.