Automating Data Loads in Oracle Cloud EPM: A Step-by-Step Guide Using REST APIs

by | Jul 31, 2023 | EPM Tools

Automating Data Loads in Oracle Cloud EPM cover art

Typically, our clients opt to utilize the robust automation options that Oracle’s EPM Automate offer. However, utilizing EPM Automate and its script solutions requires access to a client server for housing automation scripts and installing EPM Automate. If the client does not have access to the Groovy scripting available in the Enterprise Cloud, there is another solution available. Oracle Cloud EPM can utilize REST APIs for automating a variety of EPM processes. You can find a complete list of use cases for REST APIs in the Oracle Cloud EPM, however, this blog post will detail the use of REST APIs to automate data loads from Data Management.

REST API Compatibility

Getting Started

The use of REST APIs will require an API Platform to use as a REST Client. Postman, for example, is a good option and has a relatively user-friendly interface. Within this REST Client, “CurrentMonth”, “CurrentYear”, and “EPMURL” must be pre-defined as global variables. Additionally, the data load rule and import format for the integration you wish to automate must already be set up in either Data Management or Data Exchange. Lastly, the period mappings, as well as substitution variables referencing the period you wish to load must be configured as well.

The Three Key REST API Requests

The automation process utilizes three REST API requests to run the data load rule and complete the integration. The first two are “GET” requests, which essentially pull the month and year substitution variables that are configured in the cloud. These month and year substitution variables will be used to define the load period. The last REST API call is a “POST” request, that will execute the needed data load rule for the defined load period.

The first GET request is used to define the current month for the load period and references the “Current Period” substitution variable configured in the application. The request itself, essentially, reaches into the substitution variables defined in the application and sets a “global variable” in the REST client to the appropriate current month for the period mapping. This REST resource is as follows:

GET {URL}/HyperionPlanning/rest/{api_version}/applications/{application}/substitutionvariables/OEP_CurMnth

The application URL, API version, and application must be entered into the REST resource for this call to work properly. The following script must be introduced in the test tab of the REST client to properly set the current month as the global variable:
pm.globals.set(“CurrentMonth”, pm.response.json().value);
The next GET request is very similar and uses a very similar REST resource to define the current year as a global variable. The REST resource is as follows:

GET {URL}/rest/{api_version}/applications/{application}/substitutionvariables/OEP_CurYr

The following script must be introduced in the test tab of the REST client to properly set the current year as the global variable. The purpose of this script is to extract the two-digit year from the longer OEP_CurYr string.
let messageString = pm.response.json().value
pm.globals.set(“CurrentYear”, messageString.substring(2,4)
The final request is a POST request. This request utilizes the period variables retrieved from the GET requests to execute the data load rule. The POST request is as follows:

POST {{EPMURL}}/aif/rest/{api_version}/jobs

Note that the {{EPMURL}} is a global variable that should be set as the application URL. This request also requires the body to contain the following payload:

 
Notice that the job name should reference the name of the data load rule that is defined in Data Management or Data Exchange. The global variables that were defined in the GET requests are referenced as well for the period name. Also, note that this is for an integration that uses a direct connection. A file-based integration would have an additional “fileName” field added to the payload.

{
“jobType”:”INTEGRATION”,
“jobName”:”{job name}”,
“periodName”:”{{{CurrentMonth}}-{{CurrentYear}}}”,
“importMode”:”REPLACE”,
“exportMode”:”REPLACE_DATA”
}

Running the REST API Requests as a Package

Fortunately, it is relatively easy to run these REST API requests in a sequence. Within your REST client, the three requests should be saved in a “Collection” and the order of the requests should match the order that they were introduced above. From here, the collection can be run as a whole, and can typically be run automatically at a set time.

Automating data loads in Oracle Cloud EPM using REST APIs provides a viable alternative for clients who do not have access to EPM Automate or prefer a different approach. By leveraging REST API requests, clients can define the load period, execute data load rules, and complete integrations seamlessly. For more questions on automating data management processes and improving overall efficiency in Oracle Cloud EPM, please contact our team.

Read more posts