Updating Substitution variables using EPM Automate

EPM Automate

EPM Automate is always my one of the favourite topic when it comes to blogging. I don’t see EPM Automate as just a bunch of scripts which reduces some manual task done by users/admins but it also helps in troubleshooting, running reports and much more. This is my 4th blog in EPM Automate and I still feel that we can achieve a lot with this Simple and Innovative tool of Oracle Hyperion. I have also shared some of my other blogs below in EPM Automate.

This blog is more focused on Updating Substitution variables using command setsubstvars. EPBCS/PBCS admins one of the responsibility is to update the substitution variables when the forecast cycle begins. We had instances where we had lot of sub variables which were supporting 3 plan types and admins had difficulty in understanding the sub variables introduced by developers. Below scenario is explained for PBCS application but same is applicable to any Oracle Hyperion Cloud instance.

Scenario

PBCS instance we are discussing had 3 plan types Finance, Workforce and Capex. All 3 plan types had common sub variables and some specific to the plan type. Admin has to update the sub variables before the Forecast cycle begins, If any sub variable is provided with a wrong value then it will cause issues as the business rules and forms are based on the Sub variables. Admin may not notice the issue but later user may find when they see wrong data showing up on the Data forms. After user finds the issue related to Sub Variables then its kind of re-doing all the tasks again and that may add some waiting time to users to start the forecast.

Considering the above scenarios we provided a solution using EPM Automate with setsubstvars command. We created 4 csv files which had list of all the substitution variables for next upcoming 4 forecast cycles.

Approach

1. Prepare a csv file for each Forecast.
2. Admin can view the csv file in excel and update if needed before running the EPM Automate script.
3. Script creates/updates all the required Sub Variables for forecast.

Benefits of this approach

1. Admin need not worry about manually setting up the variables for every Forecast in the application.
2. No human error.
3. New variables can be introduced in the forecast files.

Detailed Steps

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

This blog will shows to create 1 file but the same method can be repeated for other forecast files.

Step 1. Open a workbook in excel and update the file as shown below.

Step 2. Sheet should have 3 columns
Col1 – Application or plan type name
Col2 – Sub Variable name
Col3 – Value of the variable
Step 3. After updating all the sub variables save the file in csv format.
Step 4. Create a EPM Automate script to read the csv file and update the Sub Vars.
a) Enter the login and URL Details.

b) Set the variable for input file location.
c) Login to the application

d) Read the csv file to create/update the sub vars.
e) Call the setsubstvars command.

delim specifies the delimiter
token specifies the column number

f) logout from the application.
Step 5. Run the script to update the sub variables.

Step 6. Validate the sub variables in Application.

Below is the complete script which can be modified based on the client admins requirement.

*******************EPM Automate Script to update Sub Var *************************************

REM PBCS Url Details

SET url=https://fastforward-test-perficient.pbcs.us2.oraclecloud.com
SET domain=perficient
SET user= xxx.yyyy@perficient.com
SET password=xxxxx

REM Input file location.

SET Inputfile=C:\Projects\Blog\setvar.csv

REM Login to PBCS Cloud

CALL epmautomate login %user% %password% %url% %domain%

for /f “delims=, tokens=1,2,3” %%A in (‘type “%InputFile%”‘) do (

epmautomate setsubstvars %%A %%B=%%C
)

CALL epmautomate logout

*******************************************************************************************

Below are some of my other blogs where I have shared some sparkling ideas around EPM Automate.

Other Blogs on EPM Automate

How to Download FDMEE Report Using runDMReport in EPMAutomate
How EPM Automate Can Help in Troubleshooting Issues
Integrate EPM Automate with Hyperion Smartview in Cloud