jason w. jones

I was digging through some of the Essbase Java API internals the other day, trying to work around an issue I was encountering with Vess, my Essbase JDBC driver. Along the way I came across a constants file with a fun history (of sorts) of Essbase versions.

Complex software packages will often track their version somehow. This is for many reasons, but a very common one is so the software has a way to gracefully handle version discrepancies. For example, some client software (perhaps the latest version of Smart View) may be working with an older version of the Essbase server. If there was some feature that came out that Smart View supports but isn’t available in the older version of Essbase that it’s connecting to, it might find this out by asking the server for its version. The client can then hide/disable the feature as needed.

That said, the Essbase Java API has a file that tracks various versions of Essbase. This shouldn’t necessarily be interpreted as a full version history, but when the Essbase developers need to make a change that could affect version compatibility, it will often result in a change to the internal version matrix.

In any case, the constants in the file tell kind of an interesting story. We can see a handful of code names in the variables, such as Gemini, Orion, Levi, Sunspot, Eclipse, Cromwell, and Beckett.

Other versions are just designated by a major/minor/patch/build combination, such as Essbase 6.2, 6.5, 6.5.1 (this is the version of Essbase I cut my teeth on!), 7.0.0, 7.1, 9.0.1, 9.2.0.2, 9.2.0.3, 9.2.1, 9.3.1 (one of my favorite versions), and 9.3.1.4.

Then the jump to version 11 happens, where we can see 11.1.1, 11.1.1.2.1, 11.1.1.3, 11.1.2, 11.1.2.1, 11.1.2.2, 11.1.2.2.1, 11.1.2.2.5, and 11.1.2.3.

Curiously in the 11.x series, at some point there was an introduction of a seemingly separate series of variables, for editions 11.1.2.2.1 PV0, 11.1.2.3 PV0, and 11.1.2.3 PV1. I’m not totally clear what the PV is meant to indicate (patch version?).

Lastly, at some point someone introduced a set of three variables to track the release/revision/patch that would apparently always be the current version of the API, but it was only ever set with major version 6, minor version 5, and patch 0. I think someone decided that the variable structure, while seemingly clean/useful, was untenable in the long haul.

Lastly, there are a couple of “versions” that coincide with specific features that came out or were otherwise necessitated by testing: ECLIPSE_ALPHA, ECLIPSE_ALPHA_II, Hybrid Analysis (HYBRIDANALYSIS), non-unique names (NONUNIQUENAME), UNICODE, and some sort of “parity” (PARITY) marker that has the same version as 11.1.2.1. Un unqualified version variable seems to match up with the version of the library itself (I took this from an 11.1.2.3 JAR file, hence it matches the entry for 11.1.2.3).

I remember an Oracle product manager at some time indicating that the code names for Essbase versions were authors – hence Beckett and Cromwell – but I also distinctly remember hearing that one of these versions was codenamed “Tallyrand”, which isn’t reflected in the code names for this list. I’d be curious what some of the other code names were along the way, so if anyone out there knows, please send me an email and I can post them here for posterity (if nothing else).

Here’s the full list from the file from 11.1.2.3 for those that are curious:

public static final int ESS_API_VERSION = 725552;
public static final int ESS_API_VERSION_11_1_2_3 = 725552;
public static final int ESS_API_VERSION_11_1_2_2_5 = 725541;
public static final int ESS_API_VERSION_11_1_2_2_1 = 725537;
public static final int ESS_API_VERSION_11_1_2_2 = 725536;
public static final int ESS_API_VERSION_PARITY = 725520;
public static final int ESS_API_VERSION_11_1_2_1 = 725520;
public static final int ESS_API_VERSION_11_1_2 = 725504;
public static final int ESS_API_VERSION_11_1_1_3 = 725296;
public static final int ESS_API_VERSION_11_1_1_2_1 = 725281;
public static final int ESS_API_VERSION_11_1_1 = 725248;
public static final int ESS_API_VERSION_9314 = 602432;
public static final int ESS_API_VERSION_931 = 602368;
public static final int ESS_API_VERSION_BECKETT = 602112;
public static final int ESS_API_VERSION_921 = 598272;
public static final int ESS_API_VERSION_9203 = 598064;
public static final int ESS_API_VERSION_9202 = 598048;
public static final int ESS_API_VERSION_901 = 590080;
public static final int ESS_API_VERSION_NONUNIQUENAME = 589824;
public static final int ESS_API_VERSION_CROMWELL = 463360;
public static final int ESS_API_VERSION_71 = 462848;
public static final int ESS_API_VERSION_UNICODE = 458752;
public static final int ESS_API_VERSION_700 = 458752;
public static final int ESS_API_VERSION_651 = 413952;
public static final int ESS_API_VERSION_65 = 413696;
public static final int ESS_API_VERSION_HYBRIDANALYSIS = 413696;
public static final int ESS_API_VERSION_62 = 401408;
public static final int ESS_API_VERSION_ECLIPSE = 329472;
public static final int ESS_API_VERSION_ECLIPSE_ALPHA_II = 329216;
public static final int ESS_API_VERSION_ECLIPSE_ALPHA = 328960;
public static final int ESS_API_VERSION_SUNSPOT = 328192;
public static final int ESS_API_VERSION_LEVI = 327936;
public static final int ESS_API_VERSION_ORION = 327680;
public static final int ESS_API_VERSION_GEMINI = 262144;
public static final short ESS_API_RELEASE = 6;
public static final short ESS_API_REVISION = 5;
public static final short ESS_API_PATCH = 0;
public static final long ESS_API_VERSION_11_1_2_3_PV1 = 3116222111612928L;
public static final long ESS_API_VERSION_11_1_2_3_PV0 = 3116222111547392L;
public static final long ESS_API_VERSION_11_1_2_2_1_PV0 = 3116157687037952L;

Nothing fancy in this release, just a rollup release with a collection of bug fixes, enhancements, and small improvements. There were some issues with null values in certain situations and Excel files that are now resolved. Thanks to those that provided feedback and testing. Please email for Dropbox link for now, otherwise this new version will be available from the normal downloads site later today.

The other week I mentioned that I’d been playing around with the idea of a virtual JDBC driver for Essbase. Much to my amazement, quite a few people (relatively speaking…) expressed an interest in this project and even graciously offered to help test it.

To reiterate from last week, I still think that Vess is “interesting”. It has also been one of the more complicated things I have tried to create. Implementing such a thing is really kind of an exercise in pounding a round peg into a square whole (or pounding a cube into a cylinder…), requiring advanced knowledge of Java, the JDBC driver model, Essbase, the Essbase Java API, network programming, and more. I’m leveraging code from a half dozen projects I have written, some public and some not.

That said if you wondered why Oracle wasn’t nice enough to make a true JDBC driver for Essbase, it’s because you have to be a little crazy to do it. And apparently I’m just that right kind of masochistic crazy.

Anyway, the driver works – amazingly. It’s not production ready and won’t be for some time, but you can view your substitution variables from a table and even load data to a cube by doing a normal SQL INSERT.

In fact, you can even read a CSV file on the fly and insert it to a cube with no load rule, like this:

INSERT INTO VESS_SCHEMA.SAMPLE_BASIC_DATA ("Year", "Measures", "Product", "Market", "Scenario", "DATA")
SELECT * FROM CSVREAD('/Users/jasonwjones/test/sample_basic_data.txt');

Crazy, eh? So all you load rule haters rejoice.

You can even use SQL transformations to adjust the data and load that into a cube from a remote JDBC table:

INSERT INTO VESS_SCHEMA.DRILLING_DRILLING_DATA ("Years", "Time", "Location", "Account", "DATA")
SELECT 'FY' || RIGHT(FISCAL_YEAR, 2) AS Years, 'Jan' AS Time, DIVISION || '-' || LOCATION AS LOCATION, '0' || ACCOUNT, AMOUNT FROM TRANS_SMALL;

What next?

A lot of the hard stuff is in place so the name of the game now would be to iterate and start to introduce a few more table representations of Essbase concepts, such as cube stats, server sessions, and some other interesting things. I’m still contemplating how you could layer in the outline editing model in a sensible way.

Getting back to this being “interesting” – I think it’s really interesting in a couple of ways. One is the idea of getting to use any off-the-shelf JDBC tool to be able to do things with an Essbase server. The other thing that is interesting is ODI (which I guess is interesting by way of the first reason, since databases/JDBC “fit” into ODI better than OLAP). The way Essbase works in ODI right now is that some custom Java libraries are used and glued together with some Jython scripts and Knowledge Module steps. In theory you could instead drop in the Vess driver as a generic SQL data source and do all of this with vanilla RKMs, LKMs, and IKMs. How cool would that be? To the extent that supporting Hyperion inside of ODI has been “problematic” for Oracle, you could perhaps sidestep it this way.

Anyway, I have a few folks that have valiantly offered to help test this thing out. If you have a spare cube, Java 1.8 (or greater), can download the free Squirrel SQL tool, and want to play with this, please hit me up, I would love some feedback.

 

Just wanted to echo what I said on Twitter: thank you everyone for your kind words and encouragement. I’m honored to join a list of names that I respect and admire.

Normally I finish some programming and then throw it over the wall to you guinea pigs enthusiastic blog readers before I say anything about it (no vaporware here, nosiree!), but I thought I’d share something I’ve been playing with as a proof of concept. It’s sort of inspired by some of the ODI/Essbase integration I’ve been working with lately.

The Essbase/Hyperion knowledge modules in ODI are sort of different from typical knowledge modules because in a lot of ways they cram a round peg into a square hole. So you kind of get this relational facade over an Essbase cube in terms of loading and integrating data/metadata. Instead of relying on a normal JDBC driver, the Hyperion KMs more or less delegate out to some custom Java code that wraps the Essbase Java API. This isn’t a bad thing, it’s just how it works.

Unrelated to ODI, there are many situations where we are jumping through hoops to get data of some kind – not just cube data and metadata – out of Essbase. For example, you run a MaxL script to dump stats from a cube to a text file. Then you run some regular expression voodoo on it to get to the data you actually want.

Side note: parsing MaxL text output is surely amusing to the boffins at Oracle that designed it. I don’t think many people know this, but MaxL communicates with the Essbase server using a hacked up JDBC driver (it’s ingenious really). So when we parse the plusses and minuses and other ASCII crap off of some MaxL output, effectively what is happening is that a real dataset is coming back from the Essbase server, the MaxL interpreter is applying the extra work of prettying it up with some text art (the only thing missing is lime green letters from the 80’s), it gets dumped to a text file, and then what happens in so many places is that the text is reparsed into data.

To some extent the MaxL Perl and Essbasepy modules can be used to get MaxL data in a cleaner way. The Java API can definitely be used. Of course, I have no problem myself jumping in and coding up a Essbase Java API routine to pull some data down. But for people that aren’t as comfortable, stripping some info out of MaxL output offers a clear path with less resistance, so I can’t say I blame them.

So we have all of these instances where we can get data out of Essbase (not just actual cube data, but metrics from the server) using EAS, using MaxL, dumping MaxL to a text file, the C API, Java API, and so on. But it seems like a lot of these approaches have undesirable combinations of effort/quality/reward.

Let’s talk about JDBC for a moment. JDBC is the Java Database Connectivity model. Java is one of the most popular programming languages on the planet (perhaps not the sexiest language, but it’s getting better), and JDBC is the tried and true model for connecting to databases. Every popular database in the world has a JDBC driver – you can grab a generic SQL tool (I’ve been using the simple RazorSQL as of late), pop in the appropriate JDBC driver (such as for MySQL, Microsoft SQL Server, Oracle, DB2, whatever), connect, and start issuing SELECT statements to your heart’s content.

So, this all brings me to something interesting I’ve been playing with for awhile: Vess – a virtual Essbase JDBC driver. Vess is a JDBC-compliant driver that can be dropped into any generic SQL tool that supports JDBC drivers (RazorSQL) as well as used out of the box with ODI. Vess offers a JDBC-compliant URL for connecting to Essbase, such as the following:

jdbc:essbase://epm11123.corp.saxifrages.com:9000/Sample.Basic;mode=embedded

Vess then presents several tables, views, and stored procedures. For example, consider substitution variables. These are modeled in a table where the column types are String, String, String, String (the VARCHAR database type). So you can do this:

SELECT * FROM SYS.SUBSTITUION_VARIABLES

And you get back a dataset kind of like this:

Sample, Basic, CurrMonth, Jan

(It’s not actually text with commas, it’s a normal dataset). There are similar tables with other system/cube information in them, such as database stats, connected users (the Sessions tab in EAS), and more. There are views for dimensions/members/aliases/UDAs. There are stored procedures for calling calc scripts (e.g. sp_exec_calc(‘CalcAll’)). MDX queries can also be crammed into a normal JDBC ResultSet with a little bit of imagination.

So, why bother?

Using a JDBC driver would let us easily (and cleanly) get to Essbase data and metadata using any generic database tool, as well as being able to drop the driver right in to ODI and use it as part of a normal interface. Whereas ODI uses Jython to call custom Java classes, this could potentially simplify the layers a little bit and offer an interesting alternative. This could also potentially be used in places where MaxL output is being parsed out via black magic, obviate the need for writing custom Essbase JAPI programs, and some use cases with the outline extractor.

Status

As of right now, the framework of the driver is created already and you can drop it into a SQL tool such as the aforementioned RazorSQL and run a command like SELECT name FROM SYS.SUBSTITUTION_VARIABLES WHERE APP = 'Sample' and get the proper results back. Over time (as time permits) I will be adding a few more gizmos. If there are any intrepid Java developers out there that would like to play with this, let me know and I can make this an open source project on GitHub that could be refined over time.

I was writing a custom ODI Open Tool the other day so that I could update an Essbase dimension as a custom package step. Normally this would be better suited as a normal update metadata interface, but in this case it seems like a good solution to be able to call an existing load rule without having to worry about the models and topologies.

In the Essbase Java API, most data load and dimension build operations are initiated from the cube object (IEssCube). I’m already familiar with the various loadData() methods that have several variations (variants of providing a rules file name, local/remote file, SQL username/password, error file name, etc), but hadn’t tried to start a dimension build from a SQL datasource from the Java API before.

Interestingly, there is no method in the IEssCube interface for directly performing a dimension build from a SQL datasource. But obviously we know it’s possible – and generally speaking, there’s a pretty clear mapping from EAS functionality to the underlying Java API.

So I was curious, what the hell does ODI do when you’re performing a dimension build (metadata update) from a datasource? Well, after a bit of digging through the ODI internals, I found it: ODI pulls the data from SQL, dumps it to a temporary local text file (a temporary file starting with the name EssData and ending with the .txt extension), then calls one of the normal data load methods on what is just a normal text file.

So, the solution in my case (to implement the custom package step) is just to dump a ResultSet to a text file (no big deal), then call a normal method. It’s a bit of a small workaround, but it works just fine. I thought it was pretty interesting that there wasn’t a built-in method to do this.

I was accused of being a tease for not knowing when the apparently “load from FTP” functionality got slipped in to the IEssCube loadData() function. I did a little digging around and it looks like it shows up no later than 11.1.2.2 and is definitely not in 9.2.1 (based on Javadoc). I’m not sure about 11.1.1.3, 11.1.1.4, and 11.1.2.1 – I couldn’t quickly find their Javadoc pages (11.1.2.1 seems to just redirect to 11.1.2.2 on Oracle’s site now, others just tell you to check out you local APS folder…). I hope you’re happy (happier) now, Tim German. :-)

I spend a lot of time working with the Essbase Java API, so I have become pretty familiar with it. I have used it to build a middle tier for a mobile reporting solution, a core part of the innovative Drillbridge drill-through solution for Essbase, Planning, and Financial Reporting, and countless other little apps. (In fact, my experience with Essbase, Java, ODI, and Jython are all at the confluence of one of my upcoming endeavors, but more on that later…)

In any case, much like a trip to Costco, going through the Essbase Java API can be a bit of a treasure hunt at times. One such thing I came across recently had to do with the loadData() method on a cube object (IEssCube).

There are actually a few loadData() methods – in programming parlance, the method is overloaded. That is, there are multiple functions with the same name, but they differ with their calling argument types, so behind the scenes, Java is intelligently able to figure out which one to call. Method overloading is frequently done for programming convenience.

For example, an object might have a method getGreeting(String name) that takes a name and returns “Hello ” plus that name. Another method with the same name might be getGreeting(String name, Date time) and this returns a greeting that is customized by the time of day.

The Essbase cube object in the Java API contains three loadData methods. One of them caught my eye. There’s a loadData() method that takes a username and password. According to the docs, you can actually load a file over FTP (in which case the file name would be an FTP path, presumably with a server and maybe even prefixed with ftp://), and the username/password are at the FTP username/password.

I thought this was kind of cool because it’s not something that’s ostensibly visible in EAS. So it could be something that’s buried in the API that is used behind the scenes, or maybe it was some plumbing done for PBCS. Maybe it has even been there forever. Like I said, I thought this was interesting… there are also a few other fun tidbits I’ve seen over the years in the API so I’ll try and point those out in the future. If you know of some please send them my way!

I’m still tracing this through, but there’s an occasional issue I’m seeing on Windows 2008 servers when it comes to starting Drillbridge. If you get an error when you try and start the service, check your logs and try to find the cause, as you normally would. If you see an error along these lines:

INFO   | jvm 1    | 2015/04/07 10:09:39 | org.springframework.context.ApplicationContextException: Unable to start embedded container; nested exception is org.springframework.boot.context.embedded.EmbeddedServletContainerException: Unable to create Tomcat tempdir

It could be a permissions problem such that the account running Drillbridge does not have high enough privileges to create a temporary folder for some web server files. If this is the case, you can actually specify the temporary folder yourself.

Open up /config/wrapper.conf and search for the text “additional”. You should find a commented our line like this:

#wrapper.java.additional.1=

You can leave that uncommented but just add in the following line:

wrapper.java.additional.1=-Djava.io.tmpdir=D:/Drillbridge/tmp

Then go make sure that you create the /tmp folder so that the folder path exists. You don’t have to use this folder name, drive, path, or anything. It just has to be a folder that exists and that the Drillbridge user has the ability to write to. Save the file and then start (or restart if Drillbridge was already running) Drillbridge. Everything should now start fine.

I’m still tracing down the root cause of this to try and make it so that users would never have to apply this fix themselves but until then, this fix works well enough. I haven’t seen this issue on Linux editions of Drillbridge – just Windows Server 2008 in environments that are relatively locked down.

The upcoming version of Drillbridge has some really exciting features in it. I’d like to talk about one of those features right now. Drillbridge now has support for custom functions. Drillbridge has a very powerful and flexible mapping system for easily translating between the members in your cube and related detailed data in a relational table. This is a big part of what makes Drillbridge such a compelling solution for quickly implementing drill-through.

One of the more popular functions is #removeStarting. This function strips a prefix off of a string (member name) if it has a certain prefix. For example, let’s say that the names of members in the Years dimension are FY14, FY15 and so on. You need just the numeric value in order to perform a query for the detailed data. You can use #removeStarting like this:

#removeStarting("FY", #Years)

And the value returned by the function will be 14, 15, or whatever the proper year is. The newest version of Drillbridge now supports adding custom functions by way of dropping in a Java file that contains them. This way if you have a complex mapping that needs to happen, you can use the full power of the Java language to accomplish it. For example, consider a function that needs to do special processing with respect to a combination of the year and month, in order to get a particular time value stored in a GL system.

public static int getJulianLastFromYear(String fiscalYear, String shortMonthName) {
  Integer year = Integer.valueOf("20" + DrFunctions.removeStarting("FY", fiscalYear));
  Integer month = Integer.valueOf(DrFunctions.monthToTwoDigits(shortMonthName, "en"));
  return getJulianLastOfMonth(year, month);
}

Then just compile this class as normal, drop it in the Drillbridge drivers folder, restart Drillbridge, and you can now use this function inside of your own expressions, just by calling it with whatever name you give it (in this case, it might be #getJulian).

This is a really exciting feature that will offer tremendous flexibility, particularly in cases where complex mapping is needed that might be difficult or impossible to perform with the relational database. Stay tuned – there are a few more very interesting features coming down the pike…