Did You Know Series -  Dynamic Updates of Time-Based Substitution Variables

Businessman flying super fast with data numbers left behind concept

Substitution variables are an important component to easily streamline modeling and calculation logic in enterprise performance management (EPM) systems and business intelligence (BI) reporting. To ensure that monthly, quarterly, and yearly roll-over processes are updated timely, it is helpful to have a way to uniformly increment time-based substitution variables. Typical variable examples include:

  • CurrYear
  • LastYear
  • NextYear
  • CurrMth
  • LastMth
  • NextMth
  • Yr1
  • Yr2
  • etc

 

 

 

These substitution variables have assignments such as “FY20” and “June”, or numeric year values. When a planning and reporting system is initialized for a new period, these variables all need to be updated. While this is not a complex process, it can be time consuming and often needs to occur precisely at midnight of the reporting switchover date. This can be a challenge at any time but can be especially unrewarding on a day like New Year’s Eve. OneCloud’s variable rollover capabilities help you to automate and schedule this process. This ensures that the variables are always set correctly across all your applications.

In this use case blog, a simple OneCloud Chain will be created to:

1. Fetch the values of the current substitution variables in an Oracle Essbase application

2. Transform the values of those variables in the following ways:

a. If the value is “FY20”, then change to “FY21”
b. If the value is “Q4”, then change to “Q1”
c. If the value is “December”, then change to “January”
d. etc...

To perform this operation, we will use an OneCloud Chain that looks like the following:

img1-onecloud-chain

Image 1: A OneCloud Chain

Now let’s break it down:

Command #1 - List Essbase Vars

In the first step, the Essbase Variables are listed for a specific Essbase application. 

List Essbase Variables

Image 2: List Essbase Variables

The output of this command is a JSON list format. Here is an example of some of the variables that were returned from this particular Essbase application. Each grouping of “application”, “name”, and “value” is referred to as an “element”.

[{
"application": "Vision",
"name": "LastYr",
"value": "FY18"
}, {
"application": "Vision",
"name": "CurYr",
"value": "FY19"
}, {
"application": "Vision",
"name": "NextYear",
"value": "FY20"
}, {
"application": "Vision",
"name": "Yr3",
"value": "FY21"
}, {
"application": "Vision",
"name": "Yr4",
"value": "FY22"
}]

JSON is a very powerful and flexible way to share information and data between systems. To learn more about this format, please visit the JSON Wikipedia article.

 

Command #2 - Transform Vars

The second step converts the Essbase variables that are in a JSON format into a tabular format that then can be subsequently transformed in the third command. 

Transform Variables from JSON to Tablular

Image 3: Transform Variables

The output now looks as we might expect:

application,name,value 
Vision,LastYr,FY18 
Vision,CurYr,FY19
Vision,NextYear,FY20
Vision,Yr3,FY21
Vision,Yr4,FY22 

 

Command #3 - Set Vars

The third step does the real work that transforms the value of the variables into incremental time-based value. What is unique about this solution, it that there is an inline transformation that is being performed with stand SQL! If you are familiar with SQL, you will recognize right away the SELECT and WHERE syntax combined with some other more sophisticated SQL to perform the various functional transformations.

Set Variables

Image 4: Set Variables

Command #4 - Convert to List

The fourth step now converts the tabular data back to a JSON list. This list is then used in the next step for to loop over each Essbase variable and set the appropriate value. 

Convert Tabular Data to a JSON List

Image 5: Convert Tabular Data to a JSON List

Command #5 - Set Essbase Vars

This fifth and final step sets the Essbase variables for each respective value that has been transformed. It is important to note that this Command leverages iteration which loops over a list. This list is the JSON list created in the prior command and is identically structured to the example in the first command but now with incremented variable values. 

Set Essbase Variables

Image 6: Set Essbase Variables

In the animated example below, see how each value for Variable Name, Variable Value, and Application is set by isolating the particular JSON key (“name”, “value”, and “application”) for each element in the JSON list. 

Set Each Arguments

Image7 : Set Each Arguments

The Results

Here are the Essbase variables before the execution of this OneCloud Chain.

Essbase Variables Overview Pre

Image 8: Essbase Variables Overview Pre

Here are the Essbase variables after they were automatically updated by the OneCloud Chain

 

Essbase Variables Overview Post

Image 9: Essbase Variables Overview Post

Try it out yourself!

If you would like to try this out for yourself, then we would like to make it easy for you and you do not even need to have an Essbase application.

1. Sign up

If you do not have access to OneCloud, please sign-up for a full-use OneCloud trial.

2. Configure your environment and add the following Connections 

a. HTTP BizApp

b. JSON BizApp

c. Tabular Transformation BizApp

3. Create a Chain similar to the following:

Image 10: Build your own OneCloud Chain

Image 10: Build Your own Onecloud Chain

Notes:

 

a. For the first command, fetch data from this URL:

https://onecloud-demo.s3.amazonaws.com/sample-json/sample-essbase-vars.json

 

b, For the third command, use this SQL:

https://onecloud-demo.s3.amazonaws.com/sample-json/increment-essbase-vars.txt 

 

4. Publish & Execute

 

Stuck? We are here to help! Contact support or learn more about OneCloud via the OneCloud Quick Start Guides

If you have any questions or need additional information, please feel free to contact us at customersuccess@onecloud.io