Retrieving Substitution Variables from Oracle EPM Planning into OAC using REST API

Since a few releases ago, Oracle has introduced native connectors that connect Oracle Analytics to Oracle EPM Application. That way, planning data from Oracle EPM Cloud can be retrieved into Oracle Analytics for reporting and analysis.

However, this was just a first step in to EPM/Essbase world. There are several others required as well, for example:

• Support for EPM Cloud/Essbase data sources in Semantic Modeler: this hasn't been provided yet, however we can expect it in January 2025 release of Oracle Analytics Cloud.
• Substitution Variables can not be imported directly with the data. But there is an option to use REST API to get all of these variables.
• Selection Steps is a functionality known from "classic" Oracle Analytics (Dashboards and Answers) where users can select specific member of the hierarchy and display only its descendants based on hierarchy or similar relationship (ie. leaves of hierarchy).

This post is about retrieving Substitution Variables using REST API.

And here is our initial workbook:

As we can see, there is Vision.Vis1ASO dataset. It is using direct native connection to the application/cube used in this example.

Vision.Vis1ASO dataset is using connection called EPM direct connection, from which 5 plan types or cubes can be read. In our example we are using Vis1ASO plan type.

EPM direct connection is defined as follows:

URL is EPM Cloud Service base URL, for example, if service URL is https://xxx-xxx-xxx.eu-frankfurt-1.ocs.oraclecloud.com/epmcloud then required URL is `https://xxx-xxx-xxx.eu-frankfurt-1.ocs.oraclecloud.com

EPM Connector allows three different options how users are authenticated:

1. Always use credentials that are used in connection definition.
2. Require users to enter their own credentials. This means every time user wants to use this connection he or she would be required to manually provide his or her credentials.
3. Use active user credentials. In this scenario, credentials used to connect into Oracle Analytics would be automatically used to connect to EPM Cloud.

What are Substitution Variables?

Substitution variables are variables that are defined at the EPM Cloud or Essbase data source that serves as a placeholder for specific members that change regularly.

Substitution variables are variables that are defined at the EPM Cloud or Essbase data source that serves as a placeholder for specific members that change regularly.

The idea is to bring Substitution Variables in Oracle Analytics to maintain consistency in reporting and analysis.

The Workflow

The workflow to start using Substitution Variables in Oracle Analytics is the following:

1. Create a new connection that is using REST API to connect to Oracle EPM Cloud service
2. Create a dataset in Oracle Analytics with retrieved Substitution Variables that is using REST API connection
3. Use Substitution Variables dataset in a workbook to create Parameters
4. Bind parameters to filter or use them independently in analyses (ie. Expression Filters, Calculations, etc.)

Create a new connection using REST API

The first step is to create a new connection to Oracle EPM Cloud using REST API. How to access and use Oracle EPM Cloud REST API interface is documented in full in Oracle's documentation, so I'm not going into details of that. More can be found here.

Begin creating a new connection by clicking Create and choosing Connection.

When Create Connection window pops up, scroll down till you get to REST API (Preview).

Click on it and continue with connection definition. Give your connect a name, ie. EPM REST API Connection.

Then specify REST base URL.

When working with Oracle EPM Planning as a data source the following is required to understand regarding URLs that need to be specified.

The URL syntax is the following:

https://<BASE-URL>/HyperionPlanning/rest/{api_version}/{path}

where:

• BASE-URL is the first part of EPM service URL. If service URL is https://xxx-xxx-xxx.eu-frankfurt-1.ocs.oraclecloud.com/epmcloud then BASE_URL is https://xxx-xxx-xxx.eu-frankfurt-1.ocs.oraclecloud.com.

• api_version: API version you are developing with. The current REST API version for Planning is v3.

• path: Identifies the resource. In case of Substitution Variables path is /HyperionPlanning/rest/{api_version}/applications/ {application}/substitutionvariables. For example, I am using planning application called Vision.

In my case, let's say that I am using an instance with the following BASE_URL:

https://epm-1234567890-plan.demoservices.oraclepdemos.com

The next fields are REST Endpoints, as there may be more than one restpoint. In our case we have only one REST Endpoint which is URL that combines BASE_URL and path:

https://epm-1234567890-plan.demoservices.oraclepdemos.com/HyperionPlanning/rest/v3/applications/Vision/substitutionvariables

Click Add Endpoint and replace suggested REST Endpoint with URL described above.

For Authentication select Basic and provide your username and password.

Once entered, click Save. If all done properly, message that connection was defined successfully appears for a bit.

Create a new dataset

Creating a new dataset that will capture Substitution Variables using REST API Connection is the next step.

From the list of available connections, select connection you've just created and open Actions menu on the right side. Choose Create Dataset.

In the New Dataset page, on the left panel, expand Schemas and then AUTOREST.

Double-click or drag and drop object ITEM to the canvas.

What you would get is the list of all Substitution Variables read from Oracle EPM Planning application (Vision) using REST API.

We can see there are four columns:

• POSITION: Substitution Variable id
• NAME: Substitution Variable name, ie. ActVersion
• VALUE: Substitution Variable value, ie. Working
• PLANTYPE: plan type or cube for which this Substitution Variable is being used with.

Make sure your dataset is properly named and saved.

And now, we have yet another dataset which can be used as any other dataset in OAC.

Use Substitution Variables in Analyses

Let's open workbook created in the beginning of this exercise:

The workbook above is based on data that is residing in Oracle EPM Planning application Vision (Vis1ASO plan type) and is accessed by using direct EPM Connector. With this type of connection, Substitution Variables are not retrieved, therefore we created a dataset that does.

Add this dataset, created above, as additional dataset into our workbook. Navigate to Data tab and Add Data ....

In the dialog box, search for REST API dataset just created and complete this action with Add to Workbook.

As a result, Substitution Variables REST dataset has been added in the list of datasets in the left panel.

Added dataset is not related to our Vision data, and there is no need to make and relations. Substitution Variables REST dataset will be only needed to populate Parameters, ie. to filter data.

Navigate back to Visualize tab.

Use case for Substitution Variables is to use their values for filtering data. In our example, we will populate parameter Year Parameter based on the value of LastYr substitution variable. Then we will use this new parameter to filter the analysis. The value for LastYr is FY22.

But before that, let's just list all substitution variables:

It is now confirmed that LastYr has value FY22.

Navigate to our initial analysis.

Create a new filter based on Year Name column.

Open new filter, just as you would select one or more available values, but instead of doing that, click on (x) Bind Parameter icon:

Clicking on Bind Parameter open a menu from which select Create Parameter.

New parameter has been created. Temporary name is set to Year Name. Let's find out what it does.

Right-mouse click on it and select Edit Parameter. We have to define this parameter.

To start with, let' rename it to Year Parameter. Then, change Allow Multi Select to off. Leave other definitions as they are, just replace Initial value with Logical SQL Query. Use the following SQL query (you can help yourself by creating a new visualisation and then use Developer option to retrieve logical SQL query):

SELECT XSA('ziga.vaupot@version1.com'.'Substitution Variables REST')."ITEMS"."VALUE"
FROM XSA('ziga.vaupot@version1.com'.'Substitution Variables REST')
WHERE XSA('ziga.vaupot@version1.com'.'Substitution Variables REST')."ITEMS"."NAME" = 'LastYr'

Binding parameter to the filter results in applying filter based on the predefined substitution variable:

And not only that, parameter has been defined and can be used "freely". For example, we can disable workbook filter just defined and use Expression Filter with parameter to achieve the same result:

... and the result:

Conclusion

In this little exercise, we have seen that Oracle EPM Cloud can be used as a data source in Oracle Analytics. We have used direct connection to retrieve data (not Semantic model!!!) and REST API to get substitution variables. So it does work.

Oracle has announced that Oracle EPM Cloud and Essbase will be supported as data sources in Semantic Modeler in January 2025 release which is major news for those using these two data sources. My expectation is that by using Semantic Modeler, one could retrieve Substitution Variables in Semantic Model directly, without additional REST API connection.

The other major feature that I am missing is Selection Steps. At the moment, it is not possible to use, for example, Account dimension and display only one sub-hierarchy (ie. P&L or Balance Sheet). Currently, hierarchical column would be shown with all of its members. Yes, it is true, you can remove some of the members (ie. other sub-hierarchies) on the same level as P&L member is located, however all upper levels would remain. Which is not very useful. But there is light ... Selection Steps are announced in March 2025 release. So, Marry Christmas & Happy New Year 2025!