Here is my first post, hope you will enjoy this.
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.