Monthly Archives: April 2015

Vess: a Virtual Essbase JDBC driver

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.

Where is the Essbase Java API method to build dimensions from a SQL source?

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.

Essbase Java API Load from FTP quick followup

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. :-)

Essbase Java API digging: data load from FTP?

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!

Issue starting Drillbridge on Windows 2008

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.

Custom Functions in Drillbridge

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…

Running ODI Studio on OS X

I usually run ODI Studio in a Windows VM or just in an RDP session on a Windows server. But, I run a Mac (a lovely MacBook Pro) and am embarking on something of a local development project, and decided to run ODI Studio natively.

I’m hardly the first one to the party. In fact, I found some great instructions that got me 95% of the way up and running over on a blog article from 2011. I couldn’t quite get ODI to run, however. My default Java JDK is 1.8, but the version of ODI Studio I was trying to run is 11.1.1.7 (yeah yeah, it’s older, I’ll get 12c up and running soon enough…). In short, this version of ODI Studio wants a 1.6 JDK.

So the trick was how to leave my system default JDK at 1.8 but then force ODI to use the 1.6 JDK that’s also installed (just not default).

Normally you can use a SetJavaHome option in a conf file, but this didn’t seem to change anything. I think the ODI launcher tool (which is based on a generic Oracle IDE launcher system) just kept quickly seeing that I have a JAVA_HOME set to a 1.8 JDK, so it was just running with that and not trying anything else. So I dug through the files and found that the launcher will respect a special Java home variable named OIDE_JAVA_HOME, and use that first. So without making any other change, this following can be used on a Mac to force the use of a 1.6 JDK when there is a higher version installed as the default:

export OIDE_JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home

Then just run ODI as normal:

./odi.sh

After that, ODI Studio launched like a champ with no error messages:

ODI Studio running on Mac OS XI’m not sure how many people will find this useful (not many), but if this post is like many of my others, I’ll be stuck on this in a couple of years, Google around for a solution, then find my own article and be amazed that I forgot that I solved it…