Category Archives: automation

Essbase automation tips and techniques

GNU CoreUtils for Windows

Did you know that many Unix core programs are available as Windows executables? Because the code for these utilities is open source, there are a couple of different organizations that have taken the liberty of compiling them for the Windows platform and making them available. One such project is GnuWin32. If you want to, say, use common Unix text processing programs on Windows as part of your Hyperion automation, you definitely can. You would just install the programs on your server, probably add their folder to your system PATH, and then be able to call them like any other program.

Why would you want to do this? There are some really great programs available on Unix platforms that might be useful for some one-off processing you do as part of your automation. In the coming days I’m going to post a few examples of some interesting text processing examples you might do with these utilities that are inspired by Essbase/Hyperion. Of course, for those of you already running Linux/AIX, you should already be set (and of course, as a smug Mac user myself, I am good to go at least with my development machine…).

Another such project that otherwise makes Unix tools available on Windows is the Cygwin project, which you might want to check out too if you need to get this functionality on a Windows machine.

Essbase Outline Export Parser released

I had a use-case today where I needed to parse an XML file created by the relatively new MaxL command “export outline”. This command generates an XML file for a given cube for either all dimensions or just all dimensions you specify. I just needed to scrape the file for the hierarchy of a given dimension, and that’s exactly what this tool does: pass in an XML file that was generated by export outline, then pass in the name of a dimension, and the output to the console will be a space-indented list of members in the dimension. More information on usage at the Essbase Outline Export Parser GitHub page including sample input, sample output, and command-line usage.

Also note that the venerable Harry Gates has also created something similar that includes a GUI in addition to working on the command line. While both written in Java, we’re using different methods to parse the XML. Since I’m more familiar/comfortable with JAXB for reading XML I went with that, which in my experience gives a nice clean and extensible way to model the XML file and read it without too much trouble. The code for this project could be easily extended to provide other output formats.

Performance nuances with MaxL data imports with local and server

Some time ago, I reviewed and revamped the MaxL automation for a client. One of the major performance gains I got was actually pretty simple to implement but resulted in a huge performance improvement.

Did you know that the MaxL import data command can can be told whether the file to load is a local data file or a server data file? Check out the MaxL reference here for a quick refresher. See that bold “local” after from? That’s the default, meaning if we omit the keyword altogether, then the MaxL interpreter just assumes it’s a local file.

Imagine that you have an Essbase server, and then a separate machine with the MaxL interpreter. This could be your local workstation or a dedicated automation server. Let’s say that there is a text file on your workstation at C:/Essbase/data.txt. You would craft a MaxL import command to import the local data file named C:/Essbase/data.txt. That’s because the file is local to the MaxL interpreter.

Now imagine that the file we want to load is actually on the server itself and we have a drive mapped (such as the Y: drive) from our workstation to the server. We can still import the data file as a local file, but this time it’s Y:/data.txt (Assume that the drive is mapped directly to the folder containing the file).

In this scenario, MaxL reads the file over the network from the server to the client, then uploads that data back to the server. This data flow is represented in the figure in the left of this diagram:

MaxL data loads: server vs. local

You might be thinking, “But wait, the file is on the server, shouldn’t it be faster?” Well, no. But there’s hope. Now consider server file loading. In this case we use the server keyword on the import statement and we specify the name of the file to load. Note that the file location is based on the database being loaded to. If you’re loading to Sample Basic, then Essbase will look in the ../app/Sample/Basic folder for the file. If you don’t want to put files in the database folder, you can actually cheat a little bit and specify a path such as ..\..\data.txt and load the file from a relative path. In this case by specifying the ..\..\, Essbase will go up two folders (to the \app folder) and look for the file there. You can fudge the paths a little, but the key is this: Essbase will load the file from itself, without the MaxL client incurring the performance penalty of two full trips of the data. This is depicted in the right figure in the diagram: the MaxL client issues a data load command to the server, which then loads the file directly, and we don’t incur the time needed to load the file.

In my case the automation the written to load a file that was already on the server (in the \app folder), so I just changed the import to be a server style import, and immediately cut the data import time dramatically.

I wouldn’t be surprised if this “anti-pattern” is being used in other places – so take a look at your automation. Let me know if you find this in your environment and are able to get a performance boost!

 

The MaxL “Repeated calls to startMaxl.bat stops working” on Windows issue

I’ve run into this before and a few colleagues have recently run into this. Sometimes you’ll write some MaxL automation that loops a lot, for example, looping over a bunch of input files (perhaps you have a single text file per day of data or something).

So in your batch file you have a for loop or just a bunch of repeated calls to startMaxl.bat (note that startMaxl.bat is a wrapper of sorts for essmsh, the MaxL interpreter). The first X number of runs work just fine, then all of a sudden new calls to the script stop working. Closing the command prompt window and reopening it “fixes” the issue.

In the past when I bumped into this it was very tricky to troubleshoot. Let’s look at the code for startMaxl.bat:

@ECHO OFF
@REM This file is created to startMaxl
rem Set ESSBASEPATH
call "%~dp0\setEssbaseEnv.bat"
set PATH=%ESSBASEPATH%\bin;%PATH%
%ESSBASEPATH%\bin\essmsh.exe %*

Nothing too fancy, but notice that second to last line – where we are setting the PATH to be the ESSBASEPATH variable PLUS the existing PATH. This is a fairly common pattern. The problem, though, is that Windows has a limit to how long the value of a variable can be. The repeated calls to startMaxl are increasing the size of the PATH variable in that environment (i.e., the changes aren’t permanent) and once the length limit is hit, the job fails.

The workaround? There’s a couple. If you can call essmsh directly then use that. Sometimes you get a “MaxL initialization error” or similar  – this is due to environment variables not being set (check the setEssbaseEnv.bat file for the variables that need to be set). You can add these to your system variables and call essmsh directly. Alternatively we could change startMaxl or create a new file next to it that will only append the PATH once (say, by tracking this in another temporary variable).

Just wanted to pass this on since it ca be a little tricky to troubleshoot. Good luck!

 

A quick trick to avoid hard-coding folder names in batch files

I’m still surprised (although I guess I shouldn’t be) how often I come across batch automation files that have a first line of setting the current folder – using a hard-coded folder name. For example, if the automation is located in D:\Essbase\Automation, then the first line of the script looks like this:

cd /d D:\Essbase\Automation

99% of the time, this line is the same as the folder containing the batch file. Instead of hard-coding this, you can actually just use a handy shortcut on Windows:

cd /d %~dp0

The %~dp0 token/variable gets replaced at runtime with the current folder containing the executing file. The /d parameter simply tells the cd command to change drives, if necessary (so that the change directory command works if it’s going from the C drive to the D drive, for example).

Using the above technique, you can write batch files that are more standard looking, portable, and more flexible. It’s a good thing (said in the voice of Martha Stewart).

essbasepy Python MaxL module for Hyperion moved!

As some of you may know, I am now the active maintainer for the essbasepy Python module for MaxL. This project is an analog to the MaxL Perl module that was originally created by David Welden. I have put a fair bit of time into getting up and running with it, updating it, and testing it against EPM 11.1.2.3. I have now moved the code from its previous home on Google Code to an open repo on my GitHub account.

Other than moving to GitHub, I have included a few updates for the newest version of Hyperion, updated the documentation, and consolidated the distribution down to one master set of files. The future plans for this project are to keep validating it against Hyperion updates, polish it a bit, and enhance the documentation even more. At this point I don’t have any plans to significantly change the functionality.

I know there’s a handful of you out there that are hardcore users of this so if you have any issues or questions, please don’t hesitate to contact me.

Inputting to level 1 and a crazy app idea

Happy Friday! These weeks are flying by like a blur, it seems.

As you may know from my previous posts, I’m constantly thinking up little Hyperion-related app ideas. And since it’s Friday, I’m feeling a little whimsical and have YET ANOTHER app idea. How about an app that detects when you are designing a cube that takes input at level 1 (or level 2 for good measure), then you run the app and it automatically emails you too tell you that you’re an idiot. BONUS POINTS for turning off Aggregate Missing Values!

Genius. I like this.

Possible idea for a tool: cube delta

I have a question for my audience about a tool idea. Would it be useful to be able to tell what the data differences between two cubes with the same (or highly similar) dimensional structure is? For example, let’s say you had Sample/Basic on one server, and Sample/Basic on another server. Would it be useful to check for differences in the data loaded to them, if any?

I could see this as possible being helpful in checking for differences between cubes in development/qa/production, between archive cubes and ‘real’ cubes, and possibly during testing when you spin off a side cube to check some calcs.

Just a thought. Let me know! After HUMA is kicked over the wall I’ll be looking for my next side project (as time permits) and I am trying to focus on things that will increase the productivity of Hyperion developers.

Thank you to Hyperion Unused Member Analyzer testers, and thoughts on future tools

Thank you all so much for helping out. I am absolutely blown away at the response that this utility has generated from all of you. Please let me know if you run into any issues.

Changing subjects (and zooming out) a bit, back to my efforts to understand what you (as consultants and Hyperion professionals) check during your “health check hit list“, it’s my goal over the next year to put together a suite of power tools that enable all of us to create, analyze, and maintain more robust solutions. HUMA is one such tool in the toolbox.

I have a few other ideas up my sleeve, but if you ever find yourself saying, “Self, I wish I had a tool for [fill in the blank]” or “I wish there were an easy way to…” then I would love to know about it. Even if it’s something you do already that’s perhaps manual and laborious, perhaps it can be automated, sped up, improved, and made useable by the community at large.

Using the Unix paste command to join files together by column

I can’t believe I didn’t know about command-line utility until very recently. I was doing a little research on some text processing utilities and came across the “paste” command. As a Mac user I have this installed already, and this appears to be a fairly common LInux/Unix tool as well. It’s part of a suite of text processing utilities that are fairly standard. Oddly, I am very familiar with the likes of sed, grep, awk, and so on, and yet have not stumbled upon this.

Anyway, imagine the following files, starting with names.txt:

Jason
Cameron
Tim

And numbers.txt:

555-1234
555-9876
555-2468

Then we just run paste:

paste names.txt numbers.txt

And we get this:

Jason   555-1234
Cameron 555-9876
Tim     555-2468

Paste just marries the files up by column, reading from each file. You can supply more than two files.

I don’t have an immediate need for this utility for processing Essbase data, but it just might come in handy someday, so I’m going to keep it in my back pocket. And for you Windows users out there, well, you know the deal: get cygwin or whatever the latest and greatest Unix-on-Windows environment is.