These days, I find myself working with on-premises applications quite a bit. So, once in a while, it is refreshing to look at what Planning’s cloud counterpart can do. One of my absolute favorite features that Oracle has rolled out over the last few years is the advent of the REST APIs. I thought I’d do a “cloud” take on William’s excellent post on extracting substitution variables from Essbase (on-premises). The documentation shows us the available methods, particular to substitution variables.
If we look back at an earlier post, we can see some of the commonalities that Oracle is trying to apply across applications. For instance, for Planning, we need the following components to make a REST call:
- The base PBCS URL, something along the lines of:
http://PBCSInstanceName.com
- Specify the application we are trying to call, i.e., “HyperionPlanning”.
- “REST” and the version of the API. As of writing this post, PBCS is on version 3.
Add all of those together, and we get something like:
https://PBCSInstanceName.com/HyperionPlanning/rest/v3/
We can now go about adding further levels based on our intent. In our case, trying to get substitution variables out of an application.
Here as well, we will be using Groovy scripting, to handle the REST submission and response. I’ve added the following authentication code on the Groovy Console (check the earlier post for reasons):
Further, our completed URI to “GET” substitution variables, based on the REST documentation would look like:
The URI can be opened with the code below.
That variable “json” will allow us to capture the result of our “GET”. And also, to see the results when we run the script.
The application has a lot of variables…and there is no need to panic when you see that blob of text. If we look at the results in an editor, it will make more sense.
All we care about, from the JSON response are the plantype name (or “ALL” for global variables), the name and the value fields. To get those results in a more palatable fashion, we can add one more bit of code which will iterate and give us a nice comma-delimited list.
Run the script again…
Much better. The whole script looks like this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
// Import JSON library import groovy.json.* // Enter username, pwd username = "vk@unlk.com" password = "NoBeefBAN!" // Credentials for basic authorization need to be passed through as username:pwd userCredentials = username + ":" + password; // Basic authorization is set up, but the encoding for credentials need to be changed. basicAuth = "Basic " + javax.xml.bind.DatatypeConverter.printBase64Binary(userCredentials.getBytes()) // Complete URL variable address = "https://unlk.oraclecloud.com/HyperionPlanning/rest/v3/applications/APPNAME/substitutionvariables" urlInfo = address.toURL() // Variable converted to URL connection = urlInfo.openConnection() // Connection opened connection.setRequestProperty("Authorization", basicAuth); // Basic auth specified and credentials passed def json = new JsonSlurper().parseText(connection.getInputStream().text) // Capture JSON response // Seeing is "beliebing"...sigh println json json.items.each { println it.name.toString() + "," + it.planType.toString() + "," + it.value.toString() } connection.disconnect() |
I haven’t bothered with variablize-ing things or doing error checks, as the main purpose of this post is just to show how simple your PBCS automation needs can become with the use of REST and Groovy. It’s not a black box and it doesn’t need to be. Cheers.
