Integration

For many years, developers have had access to a variety of Essbase API’s that allowed them to write their own programs to automate and interact with their Essbase environments. Hyperion Planning, on the other hand, has had pre-built “utilities”, but it never had anything approaching a true API. With the release of Oracle Planning and Budgeting Cloud Service (PBCS), we now have access to the PBCS REST API. So why is this important?

Automation capabilities affect software adoption (aka sales for Oracle). When you think about it, PBCS offers some truly amazing benefits. It’s cost-effective compared to on-premises environments. It’s faster than many on-premises environments. Administrators don’t have to worry about upgrades or maintaining complex infrastructure. PBCS gets new features long before on-premises. PBCS practically sells itself, right? If PBCS is cheaper, better, faster and easier than on-prem, what could possibly make an organization hold on to their on-prem environment?

For many, it’s the thought of having to rebuild the automation and integration that has been built up around their on-prem applications over the years. Many of the tools and techniques for automating an on-prem environment will not work with PBCS. But while the idea of tearing out years of work related to data integration and application automation can be daunting, it is doable. Let’s look at some options.

Automating PBCS

The primary tool used to automate PBCS is aptly called the EPM Automate Utility. This command line utility allows admins to import and export data and metadata, refresh apps, run business rules, etc. It also allows administrators to upload and download artifacts to the PBCS repository (think automated LCM). The EPM Automate Utility has been discussed extensively in other blogs. It’s well documented and easy to use (albeit somewhat limited). We are going to cover something much more interesting.

RESTing in the Cloud

Oracle’s recent cloud offerings are designed to be RESTful. This means that they conform to a REST (representational state transfer) architectural style. More importantly, it means that apps in Oracle’s cloud are accessible with a REST API. As a matter of fact, the EPM Automate Utility was built on top of the REST API for PBCS.

So why would a developer choose to build a program in Groovy, Perl or PowerShell if they could simply create a batch file that calls the EPM Automate Utility? Because, like all utilities, the EPM Automate Utility has a limited set of commands and invocation methods.  The PBCS REST API opens up possibilities for creative and elaborate automation and integration, using many different languages.

Let’s Build an Example

There are an infinite number of ways developers can invoke and use the PBCS REST API. We are going to build a simple example that uses Groovy to add a Product to the Vision application in PBCS. Before we get started, this example assumes:

• Java is installed on your PC.
• Groovy is installed on your PC. (See my previous post here for instructions.)
• You are familiar with the REST API documentation.
• You have reviewed the Common Helper Functions for Groovy.
• You are vaguely familiar with JSON (JavaScript Object Notation).

Please don’t be too concerned with that last point. I didn’t know what JSON was before I started working with the REST API for PBCS. It’s basically a data structure for passing lists and arrays between web apps.

Step 1 – Download the JSON Jar File

In order to leverage JSON from within Groovy, developers will need the java-json.jar file. This can be downloaded from a variety of locations. It should be placed in the Groovy library. Mine is here:

C:\Program Files (x86)\Groovy\Groovy-2.4.6\lib

Step 2: Get Familiar with the Common Helper Functions

Oracle provides us with a set of Common Helper Functions for Java, Groovy and cURL. This is nice, because they provide a good head start when using the PBCS REST API. Unfortunately, there are defects in the Groovy samples. For example, one of the functions is supposed to return error details, but uses an incorrect property:

Another function omits an important slash. This causes the function to return html/text content instead of json/application content. This was fairly confusing to a new REST API developer!

I would personally like to see some comments added to these samples. With all of that said, they are extremely helpful when getting started with the PBCS REST API, and are definitely worth review.

Step 3: Start Coding

Let’s get started by opening the GroovyConsole. The settings below will enhance your Groovy experience:

• <View>, <Show Script in Output> (Unselect this option as it clutters up your output.)
• <View>, <Auto Clear Output on Run> (Select this so you only see your latest output.)
• <Script>, <Allow Interruption> (Enable this so you can cancel scripts that go south . . . )

The settings above are “sticky”, so developers will only have to set them once.

To begin, we’ll need to import two libraries:

• json.JSONObject
• json.JsonSlurper

The JSONObject library will allow the script to transmit data in a JSON format. The JsonSlurper library (included when you install Groovy) will allow the script to parse JSON data. Type the code below into the GroovyConsole (or copy/paste from here).

Press the Execute Groovy Script button highlighted above. Ensure that the completion notice is successful. If the notice states anything other than “Execution complete. Result was null.” there is a good chance that the java-json.jar file was not extracted into the correct location, or the GroovyConsole was not restarted.

Next, we’ll add some variables that identify our user and PBCS instance:

Be sure to update the URL, domain, user ID and password for PBCS (redacted above). This example will be using the Vision sample application. If using a different application, be sure to update the app name.

Step 4: Encode Your User Credentials

The PBCS REST API expects credentials to be sent in the following format: identitydomain.userID:password. In addition, authorization information is sent in an encoded format. The code below accomplishes this.

Step 5: Get Some Help from Oracle

Next, we will borrow several bits of sample code from the Common Helper Functions for Groovy. The two functions below read input from our REST requests and slurp it into JSON structures. This sounds complex, however it’s really just reading a long string and placing the data into a more usable format.

Another important bit of borrowed code is the executeRequest function (also included in the Common Helper Functions for Groovy). I have made some slight modifications to this code to make it more readable while adding some comments. This function calls the two “fetch” functions above, and is responsible for assembling and executing our REST “request”.

Step 6: Your Wish is REST’s Command

At this point, we have three functions, all geared towards asking the PBCS REST API to handle a request. Now it’s time to actually construct a request and execute it. We will do this by building a function called addMember. We are going to add a Product to the Vision PBCS application. It is important to note that the parent to which we are adding this member must be enabled for dynamic children. We are going to add our new Product to the parent P_TP3.

I wrote the function below to add members to a dimension. For demonstration purposes, we are only adding the required properties (parent and member name). In a “real” application, we would include more properties and they would vary depending on the type of dimension being addressed.

In the end, a REST request needs four things:

• A URL
• A Request Type (“GET”, “PUT”, “POST”, “DELETE”)
• A Payload (usually a JSON data structure)
• A Content Type

The function above simply assembles these four items and passes them to the executeRequest function.

We call the addMember function as follows (passing the dimension, parent and member name):

Step 7: Test the Code!

Before we start our test, let’s look at the Product dimension in the Vision app. Here we see that our parent member P_TP3 has four children.

Our function should add a new child called P_999. Let’s run our script and see what happens! Press the Execute Groovy Script button.

Our script output says that the member was added to PBCS. Let’s look in Planning and make sure.

What happens if we try to add the member again?

As expected, PBCS won’t let us add a member twice. If you don’t receive the error details above, you probably didn’t fix the defects I showed you with the sample code.

Summary

This was a very simple demonstration of the PBCS REST API, but there are some complex use cases for this type of automation. Many other sites on the web are RESTful. Imagine a process that builds hierarchies automatically based upon data from other websites. What about a set of functions that automatically update FX rates in your Planning apps? With the PBCS REST API, you can get very, very creative.