Evolving the Essbase Java API with JALE

I work with the Essbase Java API quite a bit. In fact, it’s more or less the reason that I picked up Java oh so many years ago. If you’re writing an app to do custom queries against a cube, need to develop a middle tier for your mobile app, or want to pull some cube stats, it’s the place to be. For me it’s a lot more useable than the C or VB APIs.

That being said, working with it can be a little challenging sometimes. Not because of inherent complexity, but because it was designed quite some time ago (especially in technology years) and before a lot of Java niceties came to be. In particular, there are iterators and other constructs that require a fair bit of boilerplate code to deal with. Here’s an example of getting a count of connections from the server:

<br />
public int getConnectionCount2(String username, String password, String server) {<br />
	IEssbase essbase = null;<br />
	IEssOlapServer olapServer = null;<br />
	try {<br />
		essbase = IEssbase.Home.create(IEssbase.JAPI_VERSION);<br />
		olapServer = essbase.signOn(username, password, false, null, "embedded", server);<br />
		IEssIterator connections = olapServer.getConnections();<br />
		IEssBaseObject[] connectionInfoObjects = connections.getAll();<br />
		logger.info("Connection count: {}", connectionInfoObjects.length);<br />
		return connectionInfoObjects.length;<br />
	} catch (EssException e) {<br />
		logger.error("Essbase error: {}", e.getMessage());<br />
		return -1;<br />
	} finally {<br />
		try {<br />
			olapServer.disconnect();<br />
			essbase.signOff();<br />
		} catch (EssException e) {<br />
		}<br />
	}<br />
}<br />

Wow, that’s a lot of code – just to do something that’s ostensibly simple. We can’t realistically expect Oracle to overhaul their API. While it’s technically possible to create a new API (from Oracle’s standpoint) that’s also unlikely to happen. Unwieldy though it may be, it’s battle-tested and thorough (although a few of you out there are miffed that it doesn’t have support for varying attributes).

So if we can’t expect the API to be refactored and offer us some syntactic sugar, and can’t write a new API, what can we do? There’s another approach: wrap the API with another library with the express intent of making the Essbase Java API easier to use, less verbose, and leverage modern language features where it makes sense.

To that end, a side project of mine has been to create such a library, called the Java Antikythera Layer for Essbase (JALE). Here’s an example of some new code that can iterate the current Essbase connections. Note that this isn’t a direct replacement for the above since they do slightly different things:

<br />
@Test<br />
public void testGetConnections() throws Exception {<br />
	try (OlapServer server = Ess.create().signOnEmbedded(SERVER, USER, PASS)) {<br />
		for (OlapConnectionInfo connInfo : server.getConnections()) {<br />
			System.out.println("Conn info: " + connInfo);<br />
		}<br />
	}<br />
}<br />

Note that this is taken from a unit test in one of the projects I have for this little experiment of a library. As the library evolves more or as more people are interested I’ll post more samples and open up a GitHub repository. Some things to note about the above code-snippet and the design of the wrapper layer in general:

  1. Adds a convenience signOn method (signOnEmbedded) to make the intent of the method instantly understandable versus passing in a parameter called “embedded” as the current API requires.
  2. Drops the IEss prefix on classes/interfaces. Modern IDEs have reduced the need for this quasi-Hungarian notation.
  3. Instead of returning an an array of objects that need to be iterated and cast to their real types, a List of the expected object is returned (server.getConnections() returns the list).
  4. Java 7’s new “try-with-resources” construct is used and the JALE server object implements AutoClosable, obviating the need for a finally block here.
  5. Also note that JALE wraps the EssException in disconnect/signoff methods so we don’t need a dummy try block inside our finally block.
  6. Ess.create() is a convenience method that auto passed in the IEssBase.JAPI_VERSION static field. We can use an overloaded method if we need to call it with that parameter.
  7. The more astute of you out there may notice a couple of other things. :)

Internally, JALE has a utility library designed to help ease the implementation of replacement classes and call the right methods. Of particular note is an “IEssIterator” unroll method that is a veritable Swiss army knife for easily replacing the boilerplate code around the Essbase iterator. For example, check this bad boy out:

<br />
public class IteratorUtil {<br />
	@SuppressWarnings("unchecked")<br />
	public static <E, F> List<F> iteratorToList(IEssIterator iterator, ConversionDelegate<E, F> delegate) throws EssException {<br />
		List<F> objects = new ArrayList<F>();</p>
<p>		for (int index = 0; index < iterator.getCount(); index++) {
			E object = (E) iterator.getAt(index);
			F converted = delegate.convert(object);
			objects.add(converted);
		}

		return objects;
	}
}

&#91;/java&#93;

Then in our wrapper for the traditional Essbase server object, we can do this:

&#91;java&#93;
public List<OlapApplication> getApps() throws EssException {<br />
	return IteratorUtil.iteratorToList(olapServer.getApplications(), new ConversionDelegate<IEssBaseObject, OlapApplication>() {<br />
		public OlapApplication convert(IEssBaseObject from) {<br />
			return new IEssOlapApplicationWrapper((IEssOlapApplication) from);<br />
		}<br />
	});<br />
}<br />

Note that conversion delegate is a one method interface that just supports the generified convert method. It’s designed so we can just supply a lambda-esque convertion method block in the new method (in this case, getApps()) which in this case contains our logic for going from an IEssbaseObject to the designed type of OlapApplication (a new object in JALE).

Future Directions on JALE/a Hyperion Essbase API wrapper

There’s no timeline on a public release of JALE. At this point it’s highly experimental as I evaluate its feasibility. I am just implementing method as I need them, so don’t expect to see me wrapping the MDX routines any time soon (not that I don’t use them but it’s not a section of the API that “needs” much fixing). I’m not worried about a performance hit from wrapping the API but I do have some concerns about performance if there’s something in the native API that loads lazily or otherwise needs an active connection to the server that would cause things to act up when I wrap them an enumerate on them, for example.

As with all of my little side projects (particularly the open source variety), please let me know if you have any feedback or want to contribute. Unlike the others at this point, this one is very pre-alpha quality so I don’t have immediate plans to make it available, but jut wanted to discuss some of the things I am thinking of an working on.

How to copy an Essbase application from one server to another

I got a question from a reader about how to do this.  Specifically, they were copying an application from one server to another and everything seemed to be going fine, except that there was no data in the resultant database on the target server.  The reason for this is that when you copy Essbase apps between servers, the data does not get copied.  If you copy the app on the server, it will copy the data.  So, how do we accomplish this?

For BSO cubes, the easiest option to do a cross server data copy is to copy the application by right-clicking on it, selecting Copy, then choosing the target server.  Then right-click on the database and select Export…. The export file will show up in your App folder were all of the Essbase applications are.  On your new (but empty) database that you just created from a copy, you can load this data.  If you have access to the File System locations, you can load the file across the servers, otherwise, you may have to copy/move the newly created export text file to a location that you can get to through the EAS Load file dialog box.  You don’t need any load rules since the data is already formatted in a way that is native to the application (just don’t make any changes to the outline before you import the data).

As I mentioned, when you copy an application to a new name on the same server, it will take the data with it — and anything in same folder as the app, for that matter.  So if you’re in the habit of storing gigs and gigs of text files in your database folder, get ready for a long wait as everything copies.  At least in version 7 of Essbase, copying huge applications is not a very graceful operation — it can stall the server while files are copying.  Even the best RAID setup can really take a pounding from all the reading and writing necessary to duplication an application.

For ASO databases, your options are a bit more limited since you can’t just do a database export.  You can still copy the applcation (and all it’s rule files and report scripts and such) across servers, though.  As I’m sure you’re aware by now, ASO databases can be quite a bit more fickle than BSO — and you’re quite used to ASO dumping all of your data when you even so much as look at the outline in the wrong way.  But part of the reason you are using ASO in the first place is for the fast loading times, even with massive datasets.  You can follow your same steps and load back data to ASO through EAS, or if you have setup your automation correctly, you can run your scripts and populate your new copy of the ASO application/database.

Jason’s Top 10 Essbase Data Load Optimization Tips

I received an email today from someone looking to speed up their data loads, specifically their ASO data loads that seem to be taking too long.  This is, of course, an important topic for many Essbase cube wranglers.  I would be willing to bet that many people spend more time optimizing calcs and may even neglect profiling their performance on data loads.  You might be surprised just how long your automation spends doing a data load.  That being said, there are several different scenarios you may find yourself in and different places to optimize.  Of course there are more items that can go on this list, but here are the big ones off the top of my head.  In other words, if I was tasked with improving my data load speed, here’s what I would look at:

  1. Do you need to load the data in the first place? I know this seems a bit rudimentary, but if you are loading data that you don’t even need (and can possibly help it), then don’t waste your time on it.
  2. Use the fastest connection. Architecturally you may simply have to load data one way or another (from a text file, from a SQL server, off the SAN, etc), but, hands down the fastest data loads I’ve seen (short of trying to load from a RAMdisk, although I’m dying to try it) are from different physical hard drives attached to the Essbase server.  With a good RAID setup the performance is still quite good if you are loading text files from the Essbase server.  If you are loading your records from an RDBMS across the WAN, you might be killing your performance due to network bottlebecks.  Another option is to put the RDBMS on the same box as Essbase.  I know many people do this, but personally I am not a huge fan of this option.  My Essbase servers tend to have plenty enough to do without having SQL software on the box to worry about.  Additionally, we license SQL by the CPU, and since my Essbase servers are all quad-proc and the SQL servers are getting by just fine with dual-proc, the cost to license it for two more CPUs is quite significant — particularly when all of the other optimization methods are essentially ‘free’.
  3. If loading from a SQL RDBMS: your bottleneck may very well be the network speed here.  If you can, make sure your Essbase server is on the same LAN as the SQL server, with the fastest possible connections (Gigabit or better).  If you are loading from a SQL table and using the WHERE clause in your load rule, or you are loading from a SQL view, make sure you have good indexes setup in SQL.  This can make a HUGE difference if you are loading just the records from Period 08 and you have an index, versus making the SQL server scan the entire table.  If performance is extremely critical see if you can pre-stage text files on the Essbase server (like tip #2).  If loading to BSO, order the rows to match your dense/sparse settings and the order of the outline (basically, you are trying to give Essbase a hand and load up a whole dense datablock in as few passes as possible).
  4. Do as little work as possible in the load rule.  The cost of doing text replacements, column swaps, accept/reject rules, and all that stuff can really add up.  If you can do this elsewhere then do it there (e.g., in a SQL view or having your ETL software prep it for you in the format you need).
  5. Tweak your settings. The Essbase.cfg has some black magic stuff in it.  Try the DLTHREADSWRITE parameter (check the DBAG for details) to see if you can throw some threads at the problem.  Watch your performance on the server — slamming all the CPUs may cause performance for other users to decrease.
  6. (BSO) Sort the data.  Try to give Essbase a a hand.  If your records are sorted such that Essbase is looking at the fewest blocks at a time, and reading the items in the same order as the outline, you’ll reduce the punishment to Essbase and help it load records faster.
  7. Outline optimization.  This one, of course, applies to just about everything you do in Essbase.  Smaller datablocks are your friends — in the sense that if you are committed to your dense/sparse settings, see if you can lighten up the dense blocks with some strategic dynamic calcs and labels.  For instance, my Time dimension is usually Time –> Quarters –> Periods and nine times out of ten, when Time is dense, the Quarters and Time members are dynamic calc instead of stored.  Of course, there may be numerous other reasons that the dense/sparse settings are what they are (calc performance, retrieve performance, etc), so don’t go making changes without understanding them.
  8. Load and Swap. You may find it useful to load up a cube separate from the production cube, then implement MaxL to drop it on top of the production cube (all on the same server of course).  This way you do all of the hard work in one and when it’s ready you can just pop it in place.  I think this works better in theory than in practice, at least for me.  I initially tried this with some very large ASO cubes, and although the performance wasn’t terrible, at least with version 7.1.x of Essbase, the swap process (the MaxL was a “create or replace” command) was not very graceful — it would shake the server to its knees during the swap process.  I eventually dropped this method in favor of using all the other points to optimize and make the down time even smaller.
  9. Use your own load rules instead of EIS. Out of convenience, and particularly on small cubes, I will load data with EIS.  However, you are going to see better performance when you use your own load rules and optimize them.  Besides, you probably already did it this way since you are concerned about performance, and this is probably in an automation script anyway.
  10. Reduce the size of the data members.  Any time your bottleneck is the speed of the transmission of information, try to cut it down (particuarly for SQL loads across the LAN).  For example, don’t have the field be “Period 01” if you can use “P01” instead.  Use “08” instead of “Yr2008”.  Try to balance this with how much work your load rule is doing (Tip #4).

As always, experiment!  Try different combinations to see what works and what doesn’t.  Remember that for squeezing out that extra bit of performace, you are trying to help Essbase do its job better.  Always remember that dimensions can be and probably are (on cubes you didn’t build) setup how they are setup for a reason — i.e., you might have worse load time but the benefit is faster calc or retrieve time.  If you have your own tips please let me know!