Managing substitution variables using EPM Automate and REST

I was recently asked if it is possible to use the REST API in EPM cloud to manage substitution variables, at the time there was no documentation advising that it was possible but the clue was that it is available using the EPM Automate utility.

The rule I usually apply is if it is available in the EPM Automate utility, then there should be an equivalent REST resource as the utility is built on top of REST APIs.

In the next release (16.12) there is a documented new feature -
“You can now use REST APIs to get and set substitution variables”

I am assuming this just means the REST API documentation is going to be updated as the resources are already there. (update: this is exactly what has happened)

In this post I am going to explore the options that are currently available with the EPM Automate utility and REST API to manage substitution variables, at the same time I am going to apply this to one of the many scenarios where this might be beneficial.

If you are not already aware there is functionality in both cloud and on-premise to create a rolling forecast in the form designer, there is a possibility you have not seen this before so I will quickly go through setting it up.

Once you drop Year and Period dimension into a column the right click option of “Rolling Forecast Setup” appears.

This will open the forecast setup window where you can define the prefix, start period/year and number of periods.

In my example I am going to create the forecast starting from Jan 2017 for 12 periods with a prefix of Roll

After generating Essbase substitution variables will be created and the form design will be populated with them.

So in my example 12 period and year substitution variables have been created using the defined prefix.

Using the inbuilt functionality, the sub vars are always created at application level.

Opening the form displays twelve periods starting from Jan 2017.

To update the sub vars to say shift a month forward you can right click the period or year in the form and select “Set Rolling Forecast Variables”

This will open a window with the forecast variables and the current values.

If I shift the values by one the variables values will be updated in the window.

Applying this will then update the Essbase sub vars and the form will now reflect this.

This all well and good if you like going into the form and shifting the sub var values or manually updating them through the variables section in the user interface, I prefer to use automation than live in a world of manual repetitiveness so this is where using either the EPM Automate utility or the REST API can help.

Let us start with the EPM Automate utility.

There are two commands currently available for managing sub vars through the utility:

setsubstvars - creates or updates one or more substitution variables at the Planning application or at cube level (added 16.04)

getsubstvar - enables you to view the current value of a substitution variable or the value of all variables at the cube or application level (added 16.11)

The syntax for setsubstvars is:

epmautomate setsubstvars CUBE_NAME|ALL SUBSTVAR=VALUE [SUBSTVAR=VALUE]

CUBE_NAME can be set as an individual cube name or by using ALL to set at application level, a single or multiple sub vars can be created/updated in in one command.

For example:

epmautomate setsubstvars ALL CurYear=FY16 CurPeriod=Dec

or at cube level

epmautomate setsubstvars Plan2 CurForecast=FY17

If I take an example using the rolling forecast variables that were created earlier.

The variables "RollPer1" and "RollPer2" could be shifted forward a month by using the utility to set them to "Mar" and "Apr"

epmautomate setsubstvars ALL RollPer1=Mar RollPer2=Apr

To check the variables have been set then the getsubstvar command can be used.

The syntax for the command is

epmautomate getsubstvar CUBE_NAME|ALL [name=VARIABLE_NAME]

Once again variables can be retrieve either at application level using ALL or by specifying the cube, there is also the option to specify the variable name to return the value for.

To return the value of the "RollPer1" variable which was just updated then you can simply use

epmautomate getsubstvar ALL name=RollPer1

To return all the variables at application level the following syntax can be used.

If you wanted to return variables that have been assigned at cube level, then you would just change ALL to the cube name.

Unlike with the set command it doesn’t look like you can define multiple variables as it returns all variables.

For the majority of situations, the EPM automate utility is perfectly acceptable for managing variables but if you want more flexibility over them or build into a current process then this is where the REST API can help.

The REST resources are available through the following URLs

For application level:

https://epm_cloud_instance/HyperionPlanning/rest/{api_version}/applications/{app_name}/substitutionvariables

For cube level

https://epm_cloud_instance/HyperionPlanning/rest/{api_version}/applications/{app_name}/plantypes/{cube_name}/substitutionvariables 

You can see the URL contains “plantypes” which is the way Essbase databases have been known in the planning world for a long time, there is now a transition to these being called cubes instead of plan types, in reality they the same thing but there will be a crossover period where there be reference to both naming conventions.

If you are retrieving variables, then you would use the GET method and if you are creating/updating then you would use the POST method.

When creating/updating variables the body of the POST requires the sub var details provided in JSON format.

The format for the JSON should be:

{  
   "items":[  
      {  
         "planType":"ALL|CUBE_NAME",
         "name":"SUB_VAR_NAME",
         "value":"SUB_VAR_VALUE"
      }
   ]
}

Basically there is an array called “items” which contains objects holding the name/value pairs for the sub var definition.

Using the REST resource, the value of the sub var “RollPer1” can be updated to “Apr” which I will demonstrate with a REST client.

If after a posting a success response is returned then this indicates there were no problems with the create/update of the variable.

To verify this, REST can be used again using a GET method to retrieve the variable details in JSON format, to return a single variable then the name of the variable can be added to the end of the URL.

To return all the variable at application level then you just wouldn’t include the variable name:

The same principal goes for cube level and the only difference is a slight change to the URL.

To create/update multiple variables the JSON just requires additional objects and sub var definitions adding to the “items” array.

To verify this a GET request can be made including the cube name in the URL.

Going back to where I started with this post and the rolling forecast functionality in the UI with the ability to shift periods, I thought I would put together a script to replicate this functionality and provide a fully automated solution.

This is just an example using PowerShell and no doubt it could be simplified further or written using different methods or scripting languages, the beauty is there are so many ways to achieve the same end result and it places the power in your own hands.

I have reset the sub vars back to the way they were so a start period of Jan and a start year of FY17, the example is based on a financial year starting in January.

The objective is to read in the current sub vars based on the prefix and then shift the periods/year depending on the value defined in a variable.

I am going to break the script into chunks and give an overview of what is happening, I am not including the authorisation header information as I have included that in previous posts and if you want to understand how it is done just revert back to them.

The above section defines the prefix of the sub vars which is “Roll” and they are set at application level, the number of periods to shift will be forward by one.

The sub vars REST resource is invoked using a GET method and the returned items are then stored.

The current value of all the rolling forecast variables are then displayed.

Next the number of months, start period and year are stored in variables.

I have displayed the variables to highlight they have been retrieved and stored correctly.

The next section loops through all the months in the forecast so in this example that will be twelve times.

During the first loop the start period and year are converted into date format and stored.

The date is then shifted by x number of months so in my case that will be one.

The date is then converted back in the correct format for the sub vars e.g. period=Feb and year=FY17

The converted dates are then stored and the loop continues until completed.

At this point if we take a look at what has been stored we can see the variable values have now been shifted forward by one.

The final section of the script creates an object with all the updated sub var information and converts that into JSON format.

A post is then made to the REST resource with the body containing the formatted JSON.

I have outputted the body of the post to show that the JSON is in the correct format.

Running another GET request to the REST resource verifies all the sub vars have been shifted forward by one month.

Opening the rolling forecast form also confirms that the all the variables have been correctly set.

Now I have an automated solution to manage the rolling forecast without the hassle.

So there we go, hopefully this post has provided a valuable insight into some of the options available to simplify the management of substitution variables.