Hyperion Essbase rejected record automation one-liner

I’m just cleaning up some old files here and came across a post from quite some time ago that never got published. Whoops. This was before I officially released the Rejected Record Summary Java routine for analyzing/summarizing a Hyperion data load rejected record file.

So imagine row after row of something like the following:

\\ Member Ac.0170001 Not Found In Database
09 0170001 900 11 .00

\\ Member Ac.0170001 Not Found In Database
09 0170001 904 11 .00

\\ Member Ac.0170001 Not Found In Database
09 0170001 905 11 .00

\\ Member Ac.0170001 Not Found In Database
09 0170001 906 11 .00

You could run the following one-liner on it:

grep \\\\ sample1.txt | sed -e 's/\\\\ Member //' -e 's/Not Found In Database//' | uniq -c | sort -nr

And get something like the following:

24 Ac.0453902
24 Ac.0397511
24 Ac.0171026
24 Ac.0170926
24 Ac.0170126
23 Ac.0909100
23 Ac.0901100
23 Ac.0201220
23 Ac.0170326

Now, hopefully you aren’t getting any rejected records (and certainly not this many, but then again, it’s just test data), but no matter what, your Hyperion automation practices should include regularly inspecting your rejected records, if any, and this might help if you happen to work it in to some automation.

This example of course presumes your Hyperion automation server is running Unix command tools, so alternatively you could install Cygwin or something similar on Windows if you script there. And for complete platform independence, check out my Java library!

EIS, IES, and things that make you go hmm…

This will be another one of those “fun” posts that I get to occasionally do. Every now and then I check out the web traffic stats for this blog and other online endeavors and take a look at who is coming and how they are getting here.

I wrote an article years ago about my experiences with EIS (Essbase Integration Services). It has turned out, hit-wise, to be pretty popular. There’s just not a lot of information about this wonderful little tool out there.

So, naturally one of the search terms for those looking up information on EIS is either Essbase Integration Services or just EIS. Curiously, though, one of the major traffic contributors to the EIS page is actually from people typing in “Boobeis” (presumably a misspelling of “Boobies”). When searching on the former, Google seems to helpfully break up the search term into constituent parts Boob + eis and suggest my site as a result.

So, yeah. According to Google, my EIS posts get two types of visitors: those looking for information on how to build a cube from a database schema and those wanting to see naked females. Hopefully those two demographics don’t overlap… at least during work hours.

Viva La Database Notes

I originally titled this post “The best little Hyperion Essbase feature you’re not using” but thought better of it. So, database notes. Have you ever wondered what that little Notes button in the Excel add-in does? Or rather, what it did (since even yours truly has finally made the official jump to Smart View)?

Essbase Excel add-in Database Note dialog

Essbase Excel add-in Database Note dialog

It is basically a facility for letting the Hyperion administrator set a “message of the day” on a per database basis. This message can be set manually from EAS, set via automation from MaxL, and of course, updated via the Java API. Since 99% of the cubes I am used to maintaining are wrapped with automation, the notes are a a great place to put some information about the automation process, even if I’m the only one that is going to refer to it. I honestly don’t know of any environments where even a significant portion of users is aware of or uses this feature, but I am sure there are some.

Just off the top of my head, here’s some ideas for information that can be put in the database notes:

  • Last cube refresh time
  • Rejected record stats (such as those generated by the Rejected Record Summary tool)
  • Automation stats (duration, errors, etc)
  • Next cube refresh time
  • Notes about business process calendar/schedule
  • Link to cube status information website
  • Timestamp/version information from file(s) used to load the cube
  • Indicate date of archival for an “archive” cube

As an example, I created an automation routine once that depended on files from various divisions. Each division was on its own schedule, which was denoted by an integer, typically between one and 40. It was useful to me to know which was the most recent file loaded in. I programmed the notes to contain this information, as well as some rejected record stats. So, on a day to day basis I didn’t need to refer to the notes very often, but when things went a little haywire, it was a great first place to be able to check for information before having to dig in further.

Continue Reading…

One extra thing to validate in that load rule, even if all looks well

This isn’t one you will run into too often, if ever, but seems to be a small issue. I was working on a load rule the other day (yay load rules!) and everything validated just fine. There’s nothing better than the smell of an “everything validated correctly!” dialog in the morning. I am on a project refactoring a cube and one of the small changes made was that the dimension name has changed for one of the dimensions from Accounts to Account. One of the core load rules for this cube uses the “sign flip on UDA” feature. Of course, for this feature to work (which I have used many times before and love [but not as much as I love EIS…]) it needs to specify the dimension and the UDA of the members in that dimension to flip the sign for. Well, sign flipping wasn’t working and even though the load rule validates just fine, the problem was that non-existant dimension in the sign flip configuration. So, although there could be a reason to not want to validate this, it seems somewhat reasonable (if nothing else, for the sake of completeness) that the load rule verification should include verifying that if the dimension name for sign flipping does exist. It would have saved me a little bit of time.

Portable Firefox to the rescue!

Just a quick tip that I’ve been meaning to mention as my schedule allows for a few more posts these days… Have you ever heard of PortableApps.com? I used to use them quite bit, although not too much lately. These are popular software applications that have been packaged with the specific intent of running them from a USB stick. They have web browsers, email clients, virus scanners, image editors, and more. All of these are open source applications. You don’t have to install them, though, which is the nice thing. They are ready to run as-is. This also comes in particularly handy if you have an existing installation of some software (again, using Firefox as an example) and don’t want to mess with it. I ran into an issue with a client awhile back where Internet Explorer was having some issues with logging in to Workspace/Planning, but this particular version (something around didn’t work with the current version of Firefox. It was, however, certified to work with a much older version of Firefox – version 3.5. What to do? Go to PortableApps.com, download the old Firefox binary from version 3.5, run it from the local hard drive without having to install, and presto, I was good to go. Next time you have a browser acting up or need to switch things up, this might be a decent way to go.

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.

Here’s how you can do the same. First, you need to go get your Essbase jar file. These are installed on the Hyperion server. You might have to search around a little bit since the directories seem to change from release to release, but in the case of this stock Hyperion 9.3.1 server (with Hyperion installed in C:\Hyperion) they can be found at C:\Hyperion\AnalyticProviderServices\lib.

Here’s what the directory looks like on one of my machines:

Hyperion Java lib folder screenshot

Hyperion Java lib folder screenshot

Right now we’re just interested in the ess_japi.jar file. We’re going to import this in to our local machine’s Maven repository. This assumes you have Maven installed locally, of course. If not it’s pretty straightforward. Just Google around and all will be clear.

Maven is very particular about the versions of everything. It allows you to store multiple versions of files. This means that our single repository can store the files for Essbase 9.3.1,,, and so on, all next to each other. Since we’re importing this resource manually we are going to tell it the version. First though, let’s rename this local file to something more consistent with Maven naming conventions. Let’s rename it from ess_japi.jar to essbase-japi-9.3.1.jar (since this is a file from a 9.3.1 server). Change it accordingly for other versions. If this were then we would make it essbase-japi- Note that Maven “prefers” a versioning scheme of major.minor.revision but not all software (particularly Essbase) adheres to this, so we’ll do our best.

So now we have essbase-japi-9.3.1.jar. A simple command line will import this. From a command prompt in the same folder as the jar file, execute this command:

mvn install:install-file -Dfile=essbase-japi-9.3.1.jar -DgroupId=com.essbase -DartifactId=essbase-japi -Dversion=9.3.1 -Dpackaging=jar

Each -D indicates a parameter we are filling out: the name of the file, a Maven group ID (which we’ll decide to make com.essbase), what the name of the artifact itself should be (essbase-japi), the version, and lastly that it is a jar file. You’d think Maven could infer some of this for us but we only have to do this once in a blue moon so it’s not so bad. Maven will copy the file to the local repository. To make it visible from Eclipse you will likely have to rebuild your Maven repository index which is no big deal.

Essbase Jar import success

Success importing Essbase jar file

Now when we are specifying the dependencies for our projects from Eclipse, we can easily browse it by name and add it in to our Maven POM file:

Eclipse Select Essbase Jar Dependency

Eclipse Select Essbase Jar Dependency

Now we’re good to go. We can easily include this artifact in future projects quickly and easily. This is particularly useful if you happen to download the source code for some of my Essbase-related open source projects, which as of late rely on Maven for dependency management.

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.

I’ll create a new folder somewhere to keep these files.   First I will create a text file that has the current name of the application, a comma, and what the copy of the application should be named.

Here is my file, called mapping.txt:

;       ,        x

The semi-colon at the top is actually a comment, and I put it in along with the comma and the x so that in a fixed-width editor like Notepad, I can easily see that I am indeed staying within the 8-character limit for application names (remember that this is a bunch of cubes with all different names so this just helps me avoid shooting myself in the foot).

Next, we need the MaxL file.  Here is the file copy.msh:

create or replace application $2 as $1;

What?!  But there’s hardly anything in it!  We’re going to tell the MaxL interpreter to login with parameters on the command line.

Okay, and lastly, we have the batch file (this is Windows we’re running the automation from).

: Parameters:
: server, username, password, mapping file

FOR /f "eol=; tokens=1,2 delims=, " %%i in (%4) do (
    ECHO Original app: --%%i-- target app: --%%j--
    essmsh -s %1 -l %2 %3 copy.msh %%i %%j

Okay, so what’s going on here?  It’s a batch file that takes four parameters.  We’re going to pass in the analytic server to login to, the user on that server to use (your batch automation ID or equivalent), the password for that user, and the mapping file (which happens to be the text file in the same directory, mapping.txt).

So from a command line, in the same folder as all these files, we can run this command as such:

copydb.bat analyticserver.foo.bar admin password mapping.txt

And of course replace the parameters with the actual values (although I’m sure some of you out there have admin and admin’s password is… password… tisk, tisk).

And there you have it!  We can easily adapt this to copy things out next year, we didn’t leave our master ID and password buried in some file that we’ll forget about, we don’t have any hard-coded paths (we need essmsh in the PATH but that should already be the case), and more importantly, instead clicking and typing and waiting and clicking and typing and waiting in EAS (some of these app copies take awhile), we’ll let the server knock it out of the park.  Be sure to watch the output from this to make sure everything is running to plan, since we aren’t logging the output here or doing any sort of parameter sanity checking or error handling.

Essbase and SQL Server Express: just don’t

I’m working on a writeup on how to get a fully functional Essbase server up and running in a virtual machine (using Sun VirtualBox).  I’m currently working on the steps — setting up the Essbase funtionality is relatively straightforward, although you need a relational database to store various information.

“No problem,” I thought to myself, “I’ll just pop on a copy of SQL Server Express.” Oh how naive I was.  In theory, I’m sure it’s possible to use SQL Server Express as the backend for a Hyperion installation, but after fiddling around with various options, enabling TCP/IP, and doing everything else I could think of, I just couldn’t get things to work.  So I yanked it off the virtual machine and put the real deal on — a full copy of SQL Server 2005 — and presto!  I was up and running in no time.  So if you happen to reach this blog article because you googled “SQL Server Express Essbase oh for the love of god why do you mock me” or something similar, I hope this popped first to let you know you may be in for a bumpy ride.

Update: Several of my readers have commented on how they’ve been successfully using SQL Server Express for their purposes.  I didn’t spend an incredible amount of time trying to configure Express to work, and given that the full version of SQL Server is available to me, I went ahead and installed that without looking back.  As I suspected, you need to play with the connection settings a bit in order to get things to work, and if someone cares to do a writeup of what that procedure looks like, I’d be more than happy to post it here.  Thanks for the suggestions, all.

Launch ClickOnce apps [such as Dodeca] through Firefox

I’m a Firefox man.  I’ve been a fan since version 1.5, liked version 2 (even though it was a memory pig), and I am quite happy with 3.0.  I am eagerly looking forward to some of the memory optimization and performance improvements that are coming down the pipe with 3.1.  I even look to Firefox as an example of subtley evolving a user-interface and polishing it as time goes on — I try to implement some of the same refinements in my own projects.

I also use Dodeca extensively as a front-end to much of my Essbase functionality.  As a ClickOnce app (.NET technologies), it has been necessary to launch it with Internet Explorer.  Of course, I can invoke it directly with a shortcut on my desktop, but frequently I find myself using a link to launch it since it’s just easier.  Sadly, this does not work out of the box with Firefox because Firefox just sees the .application file and doesn’t know what to do with it.  Some of my users have Firefox as their default web browser and have run into some slight issues as well.

Well, unbeknownst to me, there has been a Firefox ClickOnce add-in for some time.  One of the things I love and use in Firefox is it’s extension capabilities — I typically have the Foxmarks, Delicious, Greasemonkey, Web Developer, and Flashblock extensions installed as a minimum (I used to use Sage as well but I find myself in Google Reader now).  So I bounced over to the Mozilla addons page, clicked the button to install FFClickOnce, restarted my web browser, punched in my Dodeca URL, and without a hitch, I was prompted to run Dodeca.  Not that I have anything against Internet Explorer, but now I can do just about everything in Firefox and have less reason to fire up IE (I’m looking at you, Windows Update…).  Sometimes it’s the little things in life!

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.