Friday, April 16, 2010

ODI variables from Work Repository

Ever thought if there was an SQL way of accessing the values of the variables that were evaluated or refreshed by ODI sessions ?

ODI stores the information about the variables in the following tables in a Development WR:

SNP_VAR
SNP_VAR_DATA (historical values of variable)
SNP_VAR_SCEN
SNP_VAR_SESS


The following query will extract the variable value for a particular session:

SELECT svd.var_name,
       svd.var_n AS numval,
       svd.var_d AS dateval,
       svd.var_v AS strval
FROM snp_var_data svd,
     (SELECT sp.project_code || '.' || sv.var_name AS fullvarname
      FROM snp_project sp, snp_var sv
      WHERE sv.i_project = sp.i_project) svp,
     snp_var_sess svs
WHERE     svd.var_name = svp.fullvarname
      AND svs.var_name = svd.var_name
      AND svs.sess_no = :sess_no

The Execution WR would be a bit different