This post is about passing values to variables in packages/scenarios and see how the variables behave, the value stays the same in declare step and changes in refresh step and you can override the value during execution.

We need static values during ETL window. Also you can rerun packages and be certain that the sub packages take the same correct variable values. ETL date can be a good example for static. Dynamic values are needed when you have a task changing daily and takes recent value such as exchange rates. Dynamic values can be calculated at run time for example looping through a list.

I created 3 packages to watch variable values:

SUB_DECLARE declares the variable

SUB_DECLARE_REFRESH declares and refreshes

SUB_REFRESH only refreshes the variable.

And I created MAIN package begins with declares and refreshes the variable. The MAIN package continues to call the scenarios with no variable value and also passing the given variable value in serial order one by one.

The MAIN package executed with variable value : 20141217, default value is 1 and when the variable is refreshed value is 20141216 as sysdate-1, as I executed the main package at 17 Dec 2014.

When we execute the MAIN package, we can see the steps:

First child session are not taken any variable value from the MAIN package, second scenarios are taken the variable values from the MAIN package.

To see how the variable changes before and after the scenario execution , we wil look variable values from repository tables in total as below:

At the first row, SUB_DECLARE scenario is not given any variable, as it continues with default value: 1. Second row of SUB_DECLARE scenario takes the MAIN package variable value 20141217.

When we look at SUB_DECLARE_REFRESH scenario values, first two rows does not take any value from main package, and variable begins with default value and when it is refreshed it takes the etl_date as sysdate-1. When the MAIN package pass variable value to SUB_DECLARE_REFRESH scenario, the value begins and ends with the same value: 20141217.

In the last scenarios, first row of SUB_REFRESH scenario begins with default value and ends with sysdate-1; 20141216. In the second row, SUB_REFRESH scenario takes only the MAIN package variable value.

In conclusion, passing a variable value to a scenario overrides the scenario value, it is always dominant. Declaring a variable creates a static value and refreshing a variable creates dynamic values, in execution begin and end values of refresh step is changing.

The below codes is used to create an Interface that can create Multiple Dataset depending on the source
datastore and Operator provided.
For example – Here
String[] source_datastore={“REGIONS”,”REGIONS”,”REGIONS”};

we are using three different source tables ,you can change them and provide any number of Source datastore in the array and accordingly n number of the Dataset are created.

String[] operator={“UNION”,”MINUS”};

For n number of the source database , n-1 number of the Operator needs to be provided.
For example if there are 4 different source datastore , then 3 operator need to provided which can be same or different depending on your requirement.

This particular ODI SDK codes creates one source to one target interface and accordingly generate Scenario, based on condition where the Source table is same as Target Table name , under two different Model.

By default the target datastore name is used as the Interface Name.

For this example , Source Model is HR schema and also the Target Model is HR schema.