jason w. jones

software from the trenches

Pump data into an Essbase cube directly from a relational database with just Java and no load rule using Hyperpipe

Kind of a wordy blog title. There is plenty of more information on the Github project page.

Based on a conversation with Cameron Lackpour, I wrote a small utility that can move data from any JDBC data source to an Essbase cube. You don’t need MaxL, a load rule, ODBC, ODI, or any of that stuff.  I mean, you might want to use those things instead of this odd little one-off utility, but if you like living on the edge you can give this a try.

Hyperpipe works by piggybacking off some functionality that is already in the Essbase Java API. Craft a SQL query in a particular format and you can load up an Essbase cube without having to make a load rule or jump through too many other hoops. This could be useful in some situations. Hyperpipe is believed to work with all Essbase versions 9.3.1 or higher but has not been extensively tested. Hyperpipe is an open-source project released under the liberal Apache Software License — a business-friendly license that you can do pretty much anything you want to.

Continue reading Pump data into an Essbase cube directly from a relational database with just Java and no load rule using Hyperpipe →

Install Essbase Java API (jar) files as a local Maven artifact

Maven is a comprehensive build system for Java projects. A lot of people, including myself, have a love/hate relationship with Maven. The reasons for this relationship can be discussed at another time. In any case, used judiciously, it can make managing dependencies in Java projects much easier than handling them by hand.

Eclipse has pretty good Maven integration. It’s possible to setup a new project and browse for dependencies and add them automatically to your project. Everything just works. I develop quite a few Java applications that rely on the Essbase Java API, so I have imported the Essbase jar files to my local repository (since they are not available from a central public repository) to make development a breeze.

Continue reading Install Essbase Java API (jar) files as a local Maven artifact →

Survey Results: What is the one true MaxL script file extension to rule them all?

I was feeling a little bit whimsical last week and wanted to get a little use out of my SurveyMonkey account, so I decided to do a quick poll: what is the proper file extension for MaxL scripts?

This issue initially arose for me when I was heckling Cameron Lackpour at one of his presentations a few years ago. My memory must be a little faulty because at the time I could have swore that he liked .mxl, whereas I am more of an .msh guy. So I wanted to settle this once and for all.

Oracle, for its part, doesn’t provide a ton of consistency on this issue, as scripts created from EAS seem to suggest a .mxl extension, whereas the script interpreter and commands seem to suggest that .msh is a little more on the recommended side. I have seen both in environments. Literally both, as in, some scripts are .mxl and some are .msh, and sometimes this naming inconsistency even exists in the same set of automation. Shudder.

Continue reading Survey Results: What is the one true MaxL script file extension to rule them all? →

The Essbase Book Review to End All Reviews: Developing Essbase Applications

I received my copy of Developing Essbase Applications – Advanced Techniques for Finance and IT Professionals some time ago and spent every spare minute I could find eagerly devouring the book. I was eagerly anticipating the arrival of the book to my doorstep, hoping and wanting it to be the Essbase book to end all Essbase books. While it has its flaws, it is a must-have for the dedicated Essbase practitioner’s bookshelf. As I always say in these reviews, there are precious few Essbase books out there, so anything that helps the cause is welcome as far as I am concerned.

Developing Essbase Applications sports an impressive and diverse array of contributors. Most of the names are easily recognizable to anyone that has taken a break from writing a calc script to seek out an Essbase blog article or Google for some help on the Essbase forums. Seriously, check out this who’s who list of Essbase folks that helped create this thing: Cameron Lackpour, Dave Anderson, Joe Aultman, John Booth, Gary Crisci, Natalie Delemar, Dave Farnsworth, Michael Nader, Dan Pressman, Robb Salzmann, Tim Tow, Jake Turrell, and Angela Wilcox.

Continue reading The Essbase Book Review to End All Reviews: Developing Essbase Applications →

Release of cubedata 1.0.0

Continuing in the same spirit as the release of Jessub, I am happy to announce the release of another open source tool meant to benefit Hyperion Essbase administrators: cubedata. cubedata is a simple tool that makes it easy to generate a text file that can be loaded to a cube. Well, of course, there’s nothing too special about this. The real purpose of the tool is to be able to generate huge text files based on the permutations of data that you specify. For example, let’s look at a simple data definition:

Continue reading Release of cubedata 1.0.0 →

Book review: Oracle Essbase 11 Development Cookbook

I was recently given the opportunity to review another Essbase book from Packt: Oracle Essbase 11 Development Cookbook by Jose Ruiz. Overall I would say I am pleased with the book. It covers a lot of ground and a lot of disparate tools, many of which are scantily documented elsewhere.

Before I really get into the review, I must say that I have never been a big fan of the approach that technology cookbooks take. I’m also not a huge fan of having a book for a specific version of software. Of course, in order for the cookbook approach to work you don’t have a choice but to tie to a version of software. This is because the recipes are sequential and very explicit — as with cooking  a recipe in real life — and rely on the exact version of the software in order for the detailed steps of the recipe to work. I’ve grown up with software, and am a cross between a visual and a kinesthetic learner, so my preference is to have concepts and goals explained to me, then to go exploring on my own. To this end, I find technology/recipe books to be tedious as they laboriously lay out the steps: click this, then click that, enter this text in, and 15 steps later you have a result.

Continue reading Book review: Oracle Essbase 11 Development Cookbook →

Saxbi is now beta testing

Well, there are a couple of reasons I don’t post here as frequently as I used to. The big one, and the one I am not ashamed to say has taken the bulk of my time, is to do development on Saxbi. When I first held an iPad in my hands, a little over a year ago (thanks Jill!), I knew it was the perfect platform for designing the Essbase app that *I* would want to use. So I set about creating an app to do exactly that.

Prior to developing Saxbi, the biggest app that I had ever singlehandedly written was probably a dozen classes and a few thousand lines of code. I never would have imagined that scarcely over a year after deciding to write my own Hyperion-related app, I would emerge with a beautiful app, a robust and flexible server for that app to talk to, a simple to use management utility, a robust RESTful API, a set of XML schemas to describe everything, and a business named after a book character that is named after a flower. And yet, here I am.

Continue reading Saxbi is now beta testing →

Release of Jessub 1.0.0

I am happy to announce the release and open-sourcing of a nifty little chainsaw of a utility. Jessub, short for Java Essbase Substitutions, is a small text-replacing utility designed specifically to update the code in a MaxL script template by replacing specialized tokens with values you specify. You can specify the values as being almost any aspect of a date, and you can specify the date as being the current date plus or minus a number of days, months, or years.

In terms of automation, you would write a batch file that calls Jessub (a single Java jar file), giving it the name of a specially formatted MaxL file, and the name of an output file. You then call the output file with the normal MaxL interpreter. This approach can be useful when you just want to drop in a quick solution without having to code custom Java against the Essbase API. The automation is also resilient to upgrades since it’s just generating plain MaxL script.

Continue reading Release of Jessub 1.0.0 →

Book review: Oracle Essbase 9 Implementation Guide

There’s a new Essbase book out, and I was fortunate enough to receive a copy to review.   Glenn Schwartzberg already beat me to the punch with his review, which I would say is fairly thorough as well as fair.  There are precious few Essbase books out there, so the more the merrier, I say.

The book starts out with the basics, covering some of the history of Essbase, and explaining multi-dimensionality, then walks you through the install process.  Obviously, based on the title, you can tell that this book is for System 9, whereas the current version is 11 (or “eleven dot one dot something dot ahh screw it” as I like to say).  While this means that the specific installation instructions are different, from a conceptual standpoint, everything else is quite similar.  In fact, the core of Essbase has changed precious little over the years, so even people in shops running Essbase 7 or 6 could benefit as well.

Continue reading Book review: Oracle Essbase 9 Implementation Guide →

A quick and dirty recipe for automating the yearly cube archive process

Due to various business requirements, I often find that one of the strategies I use for preserving point-in-time reporting functionality is to simply spin off a copy of cube.  In many organizations, the Measures dimension in a financial cube (modeled on the chart of accounts) can be quite large — and a moving target.  By spinning off a copy of the cube, you can provide the ability to query the cube later and recreate a report that was created earlier.

So, normally firing up EAS, right clicking on the application, and hitting copy isn’t too big of a deal.  But, what if you actually have to do this to a couple dozen large cubes?  Additionally, since these reside on the same server, we need to come up with different names for them.  Again, do-able in EAS, but good admins are lazy, and I’m a good admin.  So let’s cook up some quick and dirty automation to do this.

Continue reading A quick and dirty recipe for automating the yearly cube archive process →