Essterm: Terminal-based ad hoc client for Essbase

Remember the last time you thought, “You know, Excel is just a little too modern, I wish I could do multi-dimensional analysis using my keyboard, in a terminal, the way the Pilgrims did it.”

Me neither.

Yet, here we are.

I was going to originally throw this over the fence release this as a bit of an April Fool’s joke, but I didn’t have quite enough time. I actually showed this off to the fine folks at my Collaborate session last month, and believe it or not, some of the people there thought it had some interesting use-cases. Continue Reading…

PBJ 1.0.4 – New password options and start of CLI

The PBJ library has been getting a lot of attention lately from various developers using it to integrate with their own software and projects. Francisco Amores did a great blog post about using PBJ to help with data loading in an FDMEE project. Probably the coolest thing about his efforts is that it’s  use-case I never imagined: using PBJ in Jython to access PBCS.

One of the things that has been so great about collaborating with Francisco is getting targeted, useful, and practical comments on how he’s using the library and how it can be made better. And I have found time to make various improvements, enhancements, and fix bugs to address his feedback. This is one of the greatest things about open source software.

Continue Reading…

JDBC and JNDI connections compared (with a Dodeca example)

Have you ever wondered what the difference between a JDBC and a JNDI connection is? If you’re familiar with at least one of these, it’s likely that you’re familiar with JDBC (but probably not JNDI).

JDBC connections come up often in the Oracle world (for good reason). It’s a standard model/framework for designing drivers that interact with relational databases. As it pertains to us in the Hyperion, Dodeca (and even Drillbridge!) world is that we often define connections in terms of specifying JDBC parameters. This typically means a driver class name (like com.mysql.jdbc.Driver for a MySQL driver), a JDBC URL (a URL specifying a server and optionally a database/schema and other parameters), and credentials (username/password). So if you’ve poked around in your infrastructure much at all, there’s a good chance that you’ve come across a JDBC connection.

You may have even come across something called JNDI and even vaguely known it was sort of an alternate way to configure a connection but never really had to bother with it. I’ll spare you the acronym details, but think of JNDI as a way of organizing database connections (and other objects actually, but we don’t need to worry about that at the moment) such that instead of our app/system having to know the server name and credentials, it just asks “Hello, can I have the resource that was defined for me with name XYZ?”

Continue Reading…

Hacking the Essbase Java API to run Application Calcs

This post might alternately be titled, “So you’re really stubborn and wasted a couple of hours messing with the Essbase Java API”, or something. I was in a discussion the other day and asked about the ability to run an application-level calc script.

Well, back up, actually. Did you know that calc scripts can exist at the application level in Essbase? For a very long time, Essbase has had this notion of applications and databases (with databases often just being called cubes), such that there is usually one database/cube inside of an application, but there can technically be more (at least in the case of BSO). It’s almost always the best practice to have just one cube to an application. This is largely for technical reasons.

Continue Reading…

Hyperion Parent Inferrer Updated (after four years!)

I had a need for the Hyperion Parent Inferrer functionality for an internal project I am working on. It didn’t quite do what I needed out of the box so I updated things a bit. As quick background, the Hyperion Parent Inferrer is a simple one-off Java program/library I developed (apparently four years ago, wow) to parse indented data into an explicit parent/child file.

There are a few (apparently rare) cases where this is useful. In my case, I was modeling some hierarchical data and I find the indented format to be much easier on the eyes. Like so:


But when it comes time to load in to Essbase, clearly we need something more explicit. The Hyperion Parent Inferrer takes that preceding as input and then outputs something like the following:


The program has been enhanced to allow for a custom indentation character (such as tabs), to be able to specify the text rendered when there is no parent (instead of null), and a couple other little cleanups.

Hyperion Parent Inferrer is free, open source (Apache Software License version 2), and can be run as a standalone command-line Java program or as a Java library that can be incorporated into a typical Java program. The updated code is available at the Hyperion Parent Inferrer GitHub page.

Essbase Renegade Members Revisited

For some reason the other day I was thinking “Whatever happened to that renegade members feature?” So I did some digging.

Renegade members, by the way, refers to this concept where instead of a data record being rejected, you can map it to some other member. Other names for this feature might have been “shovel members”, but renegade members sounds cooler. That said, it’s a feature with a cool name but an apparently terrible publicist.

Renegade members were blogged about as early as a few years ago, such as on Cameron’s blog (during the 2013 OpenWorld), in Russian (apparently), and even over at Rittman Mead’s blog (before Mark spent his days trying to get tea kettles to work with the internet, but I digress).

But there’s a a curious lack of information on renegade members since then. There is, however, just enough information on the internet to piece this together. There’s a little documentation about renegade members over on the official documentation. Just as important (for my purposes), there are two methods relating to renegade members that are in the Essbase JAPI Javadoc.

Continue Reading…

Vess + ODI to extract Essbase metadata

Well, apparently it’s Friday Fun Day over here in Seattle. I was going to head up to the mountains for the weekend but plans changed. So how about a little frankendriver Vess update with some ODI goodness thrown in?

Vess has some really interesting data integration possibilities that I’ve mentioned before, one of which is being able to drop it into Oracle Data Integrator and use it as you would any other JDBC driver. I hadn’t really tested this out though, until yesterday. It turns out that it works surprisingly well.

Continue Reading…

Creating Windows symlinks for Java installation

This is a note about configuring Java on Windows that is mostly for reference later. Usually when I set Java up on a Windows machine, one of the first things I need to do manually is make sure that JAVA_HOME is set and that the PATH variable contains the folder with the Java executables. The variable on PATH is typically a “bin” folder, whereas the JAVA_HOME variable is up one from that, or up a few more directory levels if it’s a JDK.

Recent versions of Java on Windows have changed the way that Java is installed. Now it creates a C:\ProgramData\Oracle\Java\javapath folder that is added to the PATH, then this folder contains symlinks to for each of java.exe, javaw.exe, and javaws.exe (the main Java executables) over to wherever they are installed on the file system (typically in C:\Program Files or perhaps C:\Java). This is a nice idea so that people are constantly fiddling with their PATH to configure it to a specific versioned Java install (complete with major, minor, and build numbers).

Yes, Windows supports symlinks. While more commonly seen or known in Linux filesystems, Windows’ NTFS filesystem has had support for file links for quite some time. They’re just not used a whole lot by end users themselves.

In any case, unfortunately, this install/configuration process doesn’t always seem to work in terms of creating/updating the symlinks. But they can be fixed pretty easily. Just navigate to the javapath subfolder:

Viewing symlinks in Java folder on Windows

Viewing symlinks in Java folder on Windows

Then run these three commands, altered of course to match up with your actual version of Java:

mklink java.exe "C:\Program Files\Java\jdk1.8.0_102\bin\java.exe"
mklink javaw.exe "C:\Program Files\Java\jdk1.8.0_102\bin\javaw.exe"
mklink javaws.exe "C:\Program Files\Java\jdk1.8.0_102\bin\javaws.exe"

Playing with the Thriller MDX/JDBC Driver in Drillbridge

Last week I talked about a new side project, which is a JDBC driver called Thriller for executing MDX queries against Essbase and mapping the results back into a normal relational database. And at the time, I said that this driver had some really interesting use cases, such as in Dodeca, Drillbridge, ODI, and other tools that work with JDBC drivers.

Speaking of Drillbridge – in the very near future I will be sharing Drillbridge’s official future direction, which I think is really exciting, but more on that later. In the meantime, let’s drop this baby into Drillbridge and see what happens!

The following walkthrough of using Thriller with Drillbridge will show off some features that are only available in the licensed version of Drillbridge, although this should in theory work with Drillbridge Community Edition (the free edition of Drillbridge), assuming you have the Thriller driver JAR file.

Continue Reading…

Running MDX queries through a JDBC driver (for fun?)

So there I am, sitting in front of the Alaska Airlines gate at Boston Logan airport, waiting for my flight home to Seattle. It’s not a particularly glamorous terminal – the divorce from Delta hasn’t been too kind to Alaska at BOS; Delta seems to have kept the house and kids while Alaska microwaves Lean Cuisine on a futon in its bachelor pad…

As I’m pondering why there are white rocking chairs in the terminal, my phone rings with a familiar name: Mr. Brian Marshall. We catch up and exchange pleasantries before pivoting over to more important matters (all things EPM of course!).

Brian: “So… Vess.”

Jason: “Oh boy…”

So we get to talking about accessing Essbase data through a Java database driver, á la Vess. And we get to talking about running MDX queries and dumping the output – á la Camshaft.

And as the talk goes on I end up saying something stupid like this: “You know what might work? Jjust pass an MDX query through the driver over to Essbase and map the output to a fake table… It’d be like an unholy combination of Vess and Camshaft. You could probably knock it out in a day or two.”

And at that moment I knew I wouldn’t be able to resist opening my laptop for the five plus hour flight home. Continue Reading…