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 11.1.2.4. 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:
- Open Source Code – use it and modify it however you want
- No Cost – free for everyone
- Straightforward Results – simply list the differences between two outlines
- 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:
- Import the appropriate Essbase API files
- Read variables from a config file
- Define output file
- Connect to Cube #1 and Cube #2
- Open the Essbase Outlines
- Loop through all members in both cubes
- For Members in Cube #1 . . . Make sure they exist in Cube #2
- For Members in Both Cubes . . . Make sure properties are the same
- For Members in Both Cubes . . . Make sure member order is the same
- For Members in Cube #2 . . . Make sure they exist in Cube #1
- 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\11.1.2.0\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.
