Author: jturrell

PBCS Pro Tip: Manage Multiple Test Accounts with One Gmail Address

During the testing phase of most Planning implementations, developers need to create test user accounts.  I typically create at least one test user for each security group so I can verify that the correct access has been assigned.  With an on-premises Hyperion Planning implementation, this is easy – simply create user ID’s in the Shared Services native directory.  With PBCS, creating bulk test ID’s can be difficult, as each user ID requires a unique e-mail address.  If you need 50 test users, should you create 50 fake/temporary e-mail accounts?  Luckily the answer is no.

The Easy Way . . .

Gmail has a clever feature that allows developers to use the same address (almost) for all test user e-mail accounts, and it involves the “+” sign.  Specifically, the plus sign and everything to the right of a “+” in a Gmail e-mail address is ignored.  Here’s an example.  Let’s say I want to create one test account per security group, and I have the following groups:

  • Sales_Read
  • Sales_Write
  • Marketing_Read
  • Marketing_Write
  • Executive_Read
  • Executive_Write

I created a free Gmail account with the e-mail address  If I try to use this same e-mail address for all six of my PBCS test accounts, I’ll get an error.  Each account requires a unique e-mail address.  However I want to manage all of my test accounts from a single e-mail address.  To do this, I need to use a “+” sign in the e-mail addresses of my test users.

Here’s an example.  For the Sales_Read group, I might create an account called Test_Sales_Read.  When I assign an e-mail address to this account, I’ll use  Gmail will ignore the plus sign and the text to the right of the “+”.  All confirmation e-mails will be send directly to the account.  I can create as many users like this as I want, as long as the text to the right of the “+” sign is unique for each account. PBCS will treat the e-mail addresses as if they are unique.  My six accounts above might look something like this:


When Oracle Cloud sends the user ID confirmation messages with temporary passwords, they will all be delivered to

On a Personal Note . . .

This feature isn’t just useful for PBCS.  Imagine creating rules within Outlook that route your personal e-mails to folders based upon the following:


Let your e-mail OCD run wild!  (and simplify the creation of PBCS test accounts)

Getting Groovy with the PBCS REST API

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:

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.


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.

Essbase Pro Tip: Compare Outlines with Groovy

I’ve had a recurring challenge at several of my more recent clients. They have had lots of apps, lots of environments, and lots of developers working on different initiatives at the same time. So when it came time to move a cube from DEV to TEST, or TEST to PROD, I was always a bit nervous. I wanted to fully understand the differences between my outlines across environments before I migrated anything, so I set out to build an Outline Comparison utility.

Reinventing the Wheel . . .

I am, of course, not the first person to attack this challenge. Terry Ledet at US-Analytics has a very sophisticated and fast utility that leverages a relational database. I’m hoping he’ll post a blog about his approach soon. Doug Bliss has a solution that appears to leverage the VB API. Unfortunately, this API is no longer supported as of Essbase Evgeniy Rasyuk has a utility written with the Java API, however it requires a commercial license. Other people have used a combination of the Applied OLAP Outline Extractor and file comparison utilities. While useful, this approach produces less-than-readable results when there are a variety of differences across cubes.

In the end, I wanted something that met my specific needs and would allow others to leverage my code. I wanted a solution that had:

  1. Open Source Code – use it and modify it however you want
  2. No Cost – free for everyone
  3. Straightforward Results – simply list the differences between two outlines
  4. Simple Architecture – no relational database required

Note – Readers may download the latest version of the Outline Comparison utility here.

I am Not a Java Developer . . .

The Java API is clearly a part of Oracle’s path forward for Essbase, and this makes it a logical choice for developing a utility of this sort. However, I’m not a Java developer. But I was lucky enough to co-edit Cameron Lackpour’s first book Developing Essbase Applications. And in this book, Joe Aultman wrote a chapter on automating common Essbase activities with Groovy. Now, I’ve always had a lot of respect for Joe’s Essbase knowledge. But to be completely honest, I thought his love of Groovy was a bit . . . eccentric. After all, how many people are actually going to write Java API code to automate Essbase tasks? Well, it turns out I was wrong, because Groovy is really easy to use and very powerful when combined with the Essbase Java API. Groovy provided the “training wheels” I needed to build a Java-based Outline Comparison utility.

What is Groovy?

Groovy is a programming language that leverages the Java platform and takes a lot of the hard work out of developing Java apps. While I’m probably oversimplifying, it’s basically a “crutch” for Java. Rather than go into the specifics of Groovy, I’d encourage you to read Joe’s chapter in Developing Essbase Applications. I’m going to jump into the good stuff – let’s build something.

Download and Install Groovy

Click here to navigate to the Groovy distributions. For Windows, select the “Windows Installer”, circled in green below.


Download and launch the installer.

Select your preferred language.


Select OK.


Select Next.


Select I Agree.


Perform a Full installation. Select optional components. (I selected them all.)


Tell Groovy where to install. Select Next.


Select Install.

Groovy will begin installing.


Look for the word Completed. Select Next.


Configure Environment Variables and Path. Select Next.


Configure File Associations. Select Next.


Select Finish.

In your applications, find the shortcut to Start GroovyConsole. Launch Groovy with this shortcut. A blank GroovyConsole will be displayed.


The Groovy Console

The Groovy console is pretty straightforward. To make sure things are working after the install, we’ll write a simple program below that outputs the text “Is this thing on?”.


Enter the text println “Is this thing on?” in the white script section. Press the Execute Script button. If the text “Is this thing on?” was printed in the yellow output section, you’re ready to start building your Outline Compare Utility.


Developing the Outline Compare Utility

The basic steps for building an Outline Compare Utility are as follows:

  1. Import the appropriate Essbase API files
  2. Read variables from a config file
  3. Define output file
  4. Connect to Cube #1 and Cube #2
  5. Open the Essbase Outlines
  6. Loop through all members in both cubes
    1. For Members in Cube #1 . . . Make sure they exist in Cube #2
      1. For Members in Both Cubes . . . Make sure properties are the same
      2. For Members in Both Cubes . . . Make sure member order is the same
    2. For Members in Cube #2 . . . Make sure they exist in Cube #1
  7. Close and disconnect

Importing the Essbase API Files

To take advantage of the Essbase Java API when using Groovy, we need to help Groovy find the appropriate jar files. These files will not exist on your computer unless you have installed the Essbase client software.

The location of the Essbase Java API jar files will vary depending on your installation options. On my laptop, they were located in C:\Oracle\Middleware\EPMSystem11R1\common\EssbaseJavaAPI\\lib.

Copy the files below into the C:\Program Files (x86)\Groovy\Groovy-2.4.5\lib directory.

  • ess_es_server.jar
  • ess_japi.jar
  • ess_maxl

Please note – there are multiple ways to handle external jar files with Groovy. This is one of several methods.

Now we can import the jar files into our Groovy script with the code below:


Create a directory for your program, and save the file as OtlCompare.groovy.  (Place your properties file in the same directory.)

The Config File

I didn’t want to modify my code whenever I wanted to compare a new set of cubes. But I wasn’t ready to build a GUI to prompt users for login information, cube names, etc. This is something I may add at a later date. Instead, I decided to store this information in a config file and have the program read that file. Luckily, Groovy has a utility called ConfigSlurper() that reads config files. I simply created the file with the variables I wanted, defined the file and told ConfigSlurper() to go get my variables.

My Config file looks like this:


Please note that I placed all of the files related to this application in the logDir referenced above, including the Groovy source code, properties, and output. You will need to modify this location.

Next, I updated my Groovy script to read the config file as follows:


On line 12 above, we see the config file being defined. On line 13, ConfigSlurper parses the file. After that, we can reference any of the variables in the config file by prefixing the variable name with “config.”. We can see this on line 16, where we reference config.logDir. logDir was the location for the log file that we defined in our config file. This really highlights the beauty of Groovy. With two lines of code, we processed an entire config file, storing 13 separate variables.

On line 19, we create the log file. We will append to it in future steps. Starting on line 21, we write the current time to the log file.

Note – appending “\t” inserts a tab into a text file.

Note – appending “\r\n” inserts a carriage return into a text file.

Running this script should produce output similar to the image below. It should create a new log file whose location is defined by your config file:


The log file should look like this:


Note that we are sending the output to both the console (using println statements) and the log file, (using logfile.append statements).

Connect to Essbase and Open Outlines

The lines below initialize the Essbase Java API and connect to Essbase. Note the use of variables defined in the config file. Any variable prefixed with “config.” is defined in the config file.


Now that the two outlines are open, we can begin looping through the member names comparing properties.

Looping Through Members

To loop through the members in the outlines, we will create a function called checkMembers. What makes this function interesting is that it is recursive – it calls itself. The function starts by looping through the dimensions. It then loops through the children of each member until there are no more members to process, working its way through the entire tree.

We first loop through Outline #1, checking to see if each member exists in Outline #2. If the member exists in both outlines, we check to make sure the properties are the same. Anytime we run across a mismatch or missing member, we make a note in the log.

Next we loop through the members in Outline #2, checking to see if each member exists in Outline #1. But this time, if the member exists in both outlines, we don’t bother checking the properties, because we would have already done this in the first loop. Instead we’re simply looking for extra members that exist in Outline #2, but not in Outline #1. The function has a parameter that allows us to skip the member property check.

There is a significant amount of code in this section. Rather than list all of the code, readers can download the entire OtlCompare.groovy file here.

Close Outlines and Log Off

After we have looped through the members, we need to close the outlines and log off from Essbase.


In this example, I also write the ending time to the log and calculate the elapsed time for the procedure.

Final Thoughts

I’m really happy with the results of this script. It simplifies the process of comparing outlines, making it faster and more accurate. With that said, there’s always room for improvement. The script works well against small and medium sized outlines. But when I ran it against a pair of outlines with 100,000+ members each, it took almost 30 minutes. I’m sure I can make it faster. I’d also like to add support for checking all alias tables, attribute dimensions, non-unique member outlines, etc. Lastly, when the script flags members as missing, it then flags other members as “out of order”, when they really aren’t in the wrong order.  Their “previous member” or “next member” simply isn’t there. I should be able to clean this up.

In the end, it does exactly what I needed it to do – identify differences between outlines. And it does so in a tab-delimited file that can easily be sorted and filtered in Excel.