It always bugged me the process of manually editing the start and end period for the data load script. Thus, I tried to link the substitution variables with the data load scripts, and it worked! So first, let’s go back to the basic concept of data loading in PBCS to explain further about data automation problem that I had. then we are going to fix it by automating a dynamic Oracle PBCS data load process.
Data Loading in PBCS
Data Management is used to load data into PBCS application. Now the process of data loading is done manually or automatically using EPM Automate Utility.
Note: For more details about EPM Automate Utility, please refer to
Manually, the user can mention to which period this data belongs by using the Data Management tool as per the below screenshot; you can load the specific data file and define the start and the end periods while the execute rule.
Automatically, we can create a script for data loading to trigger EPM automate to upload data to PBCS. But then, how the period can be selected while loading the file to PBCS?
You can just include the start and the end of each month in the script, which I need to update it manually each month …. (Which is the problem!!)
To fix the problem mentioned above, you can just include the start and the end of each month as a parameter in the script.
As a parameter, we can use Essbase substitution variables to define the start and the end period of the data load rule file in the script.
Here is the method:
We can use the EPM Automate Utility command to retrieve the Essbase substitution variables by using the Getsubstvar Command:
epmautomate getsubstvar CUBE_NAME|ALL [name=VARIABLE_NAME]
GetSubstvar Parameters are:
CUBE_NAME is the cube (for example, Plan1, Plan2) from which you want to retrieve the substitution variable. Use ALL to retrieve substitution variables at the application level.
Name=VARIABLE_NAME optionally identifies the substitution variable for which you want to retrieve the value. If you do not specify a variable name, the command retrieves the value of all substitution variables.
For example, if we run the above command to get the substitution variable for the current year and current month we will get the return as an array of three lines.
After retrieving the period parameters using “GetSubstvar” Command, we need now to run data load rule by executing the below EPM Automate command:
epmautomate rundatarule RULE_NAME START_PERIOD END_PERIOD IMPORT_MODE EXPORT_MODE [FILE_NAME]
As per the Figure (1), the default output from running the “GetSubstvar” command three highlighted. However, I want to change the output to include the following three lines:
The Current Year value (17), the current Month Value (Nov), and the third line is: Nov-17.
We achieved that by executing the below script:
Starting from line 3, we defined a loop to only keep the second line from the default output of the “GetSubstvar” Command. Then in line 8, the script prints out the current year value Figure (3).
Same steps are included to retrieve the value of the current month (Please check line 12).
In line 9, the variable year is defined in the script, and its value will be equal to the substring of the last two characters from the output in the first line Figure (3). And, again the same method has been used to get the value of Nov. Then in the last line, the script prints out the value of the month concatenated with the year value.
So now, you will be able to run a fully automated data load cycle that can be entirely controlled by the substitution variables in PBCS application.