An Introduction to Better Integrations for Oracle EPM (Hyperion) Cloud using REST API
For most people working with Oracle EPM Cloud, the “EPM Automate” tool may be a familiar entity. Rightly so, as it is something that is easy to get started with to communicate with server utilities for automation or repetitive manual jobs. A few things that the EPM Automate tool has going for it:
Easy to get started
Provides a basic syntax that is expandable with a bunch of command switches and flags
Allows in-place upgrades whenever Oracle releases updates, with no more uninstall/install to update
New functionality is added as command switches and flags as new functionality is added to the Oracle EPM Cloud (this sometimes lags a month or so behind the release of the feature to the service, but not always…and let’s face it, unless this is something that you’ve been waiting a long time for…you aren’t going to rush to code new functionality into your process!)
In cases where more is required or desired, Representational State Transfer (REST) API comes into play as an interface that has become increasingly popular for accessing public web services, and that provides a user or administrator a secure way of submitting queries to a web service that can transcend firewalls.
When we talk about using this API, people are often intimidated thinking that it is something akin to rocket science and not something that they are likely to use. Most are surprised to know that there are good reasons for why REST API is a good fit for integrations with Oracle EPM Cloud:
Allows authoring of code once, with reuse in multiple places due to its flexibility
Reduces the necessity of upgrades (if not made negligible) when compared to EPM Automate
If you have written a process automation script, even a simple one, you can learn how to script using popular tools like Groovy, cURL or even VBScript!
What does it look like to use the REST API? If you’ve written SQL queries, or MS Excel “lookup”-based formulas, you will understand the concept. Specifically, when talking about the Oracle EPM Cloud, the primary differences between these and REST API include:
Queries are sent over a HTTP/HTTPS URL, which is simpler than it sounds, once you know how to write and figure out where to submit the content
Provides more granular transactions, including status lookup and log detail
Let’s start with a simple example. You’ll need access to an Oracle Planning and Budgeting Cloud (EPBCS or PBCS) instance. Open up a Web browser, such as Google Chrome, or Mozilla Firefox (or whatever else you choose for that matter). In the address bar, type in this:
Depending on whether you were already logged in to the environment or not, you may be prompted to login to the environment. Once you do, you will see a response in the browser page that looks something like this:
Looks complicated, right? As mentioned before, the response is in the JSON format, meaning that everything is written out as a “key”:“value” pair. The “Key” indicates the label of the data point, and “Value” indicates the data.
Let’s look at this in a format that’s easier to read. In this format, you see pairs that everything is arranged in. Before we figure out what the response means, let’s look at the query, which contains four parts:
The instance name (“planning-XXXXXXXX.oraclecloud.com”) indicates the instance you are connecting to
“HyperionPlanning” shows the technology you are connected to; there are others such as “aif”, “interop”, “epm”, etc., that connect to a different technology in the EPM Cloud
“rest/<version>” is the version of the REST API that you are using for the queries
Every version has a certain set of “endpoints” or tags that can be used to perform the querying
Versions don’t change often, sometimes years in between
“Applications” contains the actual query indicating what information is desired; in this case, the query is asking the instance for the name of the application that is stored in it
Now let’s look at the response. Ignore the “links” section for a later day, though you can notice that it mentions the URL that was used. The “type” is straightforward in this case. “HP” is the code for a Hyperion Planning instance. The actual content we want to look at is in the “items” section.
Here we have a listing of some of the properties of the application. More importantly, the field “name” displays the name of the application (in this case, “NEApp”). If we were using a program to access this, this value would be referred to using the pointer “items”.”name”.
Not so complicated, right? Once we get a hang of it, JSON is a useful format to hold expandable data sets, especially for responses such as these that can have a structure that can vary based on the query endpoint.I hope this provides a good starting point in understanding how the REST API works in the Oracle EPM Cloud. In subsequent blog entries, I’ll expand on this content to show other REST API capabilities, and to demonstrate benefits beyond what EPM Automate offers. In the interim, please don’t hesitate to get in touch at email@example.com if you have any questions or need additional instructions.