Hyperion code drop: Parent Inferrer tool

I am pleased to release another small one-off Hyperion-related open-source tool into the wild: Hyperion Parent Inferrer. This Java program/library can be used to translate a space-delimited hierarchy file into one with explicit parent-child mappings. For example, consider the following input file:

Time
 Q1
  January
  February
  March
 Q2
  April
  May
  June
 Q3
  July
  August
  September
 Q4
  October
  November
  December

Hyperion Parent Inferrer will generate the following output:

null,Time
Time,Q1
Q1,January
Q1,February
Q1,March
Time,Q2
Q2,April
Q2,May
Q2,June
Time,Q3
Q3,July
Q3,August
Q3,September
Time,Q4
Q4,October
Q4,November
Q4,December

As with most of the things I release these days, this tool is just a cleaned up version of something that I needed or used once or twice. In this case I wrote this up to help with a conversion process at some time in the past.

Now, I’m not saying that the approach I had to take was ideal or there weren’t better solutions. But oftentimes the environment that we operate in is less than ideal and you just gotta find a way to duct tape everything together. Hence this tool. The code as it sits today works perfectly fine but does not provide much in the way of configurability or options that might make it useful in more contexts. But as with some of the other projects I have released, I thought it would be nice to toss this over the fence in case anyone can benefit from it.

If you find a use for this or want to show me your clever Python/Perl one-liner that does the same thing, let me know!

Hyperion Health Check Hit List

I am asking for your Hyperion wisdom again, oh beloved readers! In particular, I am soliciting information from you consultanty types and those of you who otherwise hop into a lot of different Hyperion systems.

Oftentimes a client needs help with speeding up an Essbase/Hyperion process/server/cube that has become unwieldy and slow. So you take a look at things. When you happen to hop in to an environment and assess its health, what do you look for, from a Hyperion point of view? For example, on BSO cubes I go right for the stats and check out the block density and average cluster ratio. From there I can go in any number of directions, looking at the overall outline, automation, cache settings, and so forth. So I have this already:

  1. Check block density and other cube stats
  2. Review outline for any red flags
  3. Check size of index cache with respect to the size of the index itself
  4. Take a look at outline for things that can be removed/deleted/dynamic calc, etc
  5. Ensure logs are not huge
  6. Look for XCP files, if any
  7. And a few others

I am really, really, curious if you have something you look for, particularly if it’s something you might dive into with EAS. I’m working on something interesting and your feedback is very appreciated! It can be anything at all: checking the server, checking the app or the cube, checking the file system, calc scripts, business rules, automation, and so on. Thanks!

ADS File Reader 1.0.0 released

As promised, I cleaned up the Java code for very easily parsing through files in the ADS format. ADS File Reader has an incredibly intuitive, clean, and functional API that makes parsing through ADS files a breeze. You won’t believe how easy it is to burn through an ADS file (if you have to). I can’t imagine that there are a ton of people out there that need to do this, but if you wind up having to (and especially if you need it in a larger Java program) then you should be all set. As with most of my other projects, ADS File Reader is licensed under the Apache Software License version 2.0 and is available on GitHub. For more information and a link to GitHub, check out the ADS File Reader project page.

Thanks to all of you that sent in some ADS files that I could play with. It definitely helped flush out a couple of kinks. If you end up using this library please let me know, and definitely let me know if you run into any issues.

Hyperion Essbase Papercut: EAS unable to save custom views

The Essbase Administration Services console has an ostensibly nice feature: custom views. Instead of having to navigate from the top of the hierarchy down to the thing you want to see, you can create a custom view at a lower level in the navigation tree. This is a custom view and it gets its own tab along with the normal enterprise view.

So… nice feature in theory. I used it a few times but now never really mess around with it. This is probably more to do with the fact that I jump in and out of a multitude of environments much more quickly these days than I ever used to.

In any case, it’s clear that EAS stores this setting in a database and on the server. This is also nice in theory because it means the settings you set aren’t locked to a particular machine and that you’d have to set everything back up if you ever installed EAS elsewhere. The console, however, takes its sweet time relaying your settings up to the server, though. In fact, it waits until you exit to sync things up.

In a perfect world, I am opening up EAS, doing what I need to do, then closing it right away. And perhaps this is how the creators envisioned it being used. But we don’t live in a perfect world, and I have a tendency to leave EAS and many other programs open indefinitely. For one reason or another I might lose my network connection and connection to EAS. Then I want to exit EAS, which is EXACTLY when it wants to talk to the EAS server… which it can’t. And EAS gleefully tells me that it can’t save my custom views. Even if I don’t have any. Which I don’t (see above).

Every time.

As a software developer this absolutely kills me on several levels. One being why don’t we just relay the preference at the time it’s set rather than on exit (which statistically is a very likely time for not having a connection to the server). Two, when I want to close a program I generally want to close-it-n0w-thankyouverymuch. Finally, three, there’s nothing I can do about the lack of setting saving going on anyway – so just silently log it, if you must, shut up, and shut down.

As I said, this is pedantic, but that’s the very definition of the notion behind this papercuts: no single one is bad but put together… ugh.

While I’m at it, a fresh coat of paint on EAS wouldn’t kill anyone either. Actually, last time I looked under the hood a bit I noticed that EAS was using some pluggable “LAF” (look and feel) architecture and I was able to tweak the colors a bit, away from “Interdimensional Irrelevance Grey” and “Low Block Density Cyan” but it’s been awhile since I played with things…

Do you have any ADS files you can share with me?

As you may have noticed I have a penchant for throwing my various Java and other language endeavors over the wall and seeing what happens. Most of these are open source programs ostensibly in the Hyperion Essbase space, but every day my focus seems to broaden a bit. I’ve open sourced data generators, validation tools, substitution variable updaters, and more.

The particular code that I am cleaning up now and getting ready to kick out as a small Java library for reading the ADS file format. Awhile back it was necessary for me to work with raw ADS files and use them as the input to another system. For those of you that aren’t familiar, the ADS file format is used by LCM when you kick out an EPMA dimension. I’m sure it’s used in other contexts too but this is just what I know it from. It’s not incredibly complex but it does have its nuances.

I’d like to be able to test it against some files other than some of my own, so if you have an ADS file laying around or can generate one that you can share with me so I can test with that too, I’d appreciate it. I will absolutely NOT distribute it with the rest of the program or otherwise share it with anyone else unless you explicitly say I may do so.

Thanks!

Essbase Java API jars in Maven

This is a bit of a repost with respect to content as I’ve already talked about installing Essbase Java API JAR files into a local Maven repo. The other day I installed the newest JAR files for 11.1.2.3 and thought I’d do a quick post. There doesn’t seem to be a “CPLD” JAR anymore – I’m not totally sure what it did but some googling awhile back suggested we don’t need it anymore. So that leaves the main JAPI JAR and the server JAR:

ess_es_server.jar
ess_japi.jar

I’ve renamed them as thus:

essbase-es-server-11.1.2.3.jar
essbase-japi-11.1.2.3.jar

Then the following two Maven commands:

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

A whole ton of output will show – hopefully some version of a sucesss message. And then this:

mvn install:install-file -Dfile=essbase-es-server-11.1.2.3.jar -DgroupId=com.essbase -DartifactId=essbase-es-server -Dversion=11.1.2.3 -Dpackaging=jar

And again a bunch of output. If you use Eclipse like I do then you might want/need to rebuild your local Maven repository index so they are picked up there, then you can add them via the GUI if you’re so inclined by just filtering on “essbase”. Add them both to your project and then go from there. You might need some other dependencies for the new Oracle Diagnostic Logging (ODL) which seems to have been implemented. I haven’t played with this much but it seems to depend on the version of the server you use. I typically use SLF4J and bridge the Essbase JAPI logger, but with ODL things might have changed a bit. I’ll post an article at some point hopefully discussing a resolution. If you bump into any issues or solutions please let me know.

Evolving the Essbase Java API with JALE

I work with the Essbase Java API quite a bit. In fact, it’s more or less the reason that I picked up Java oh so many years ago. If you’re writing an app to do custom queries against a cube, need to develop a middle tier for your mobile app, or want to pull some cube stats, it’s the place to be. For me it’s a lot more useable than the C or VB APIs.

That being said, working with it can be a little challenging sometimes. Not because of inherent complexity, but because it was designed quite some time ago (especially in technology years) and before a lot of Java niceties came to be. In particular, there are iterators and other constructs that require a fair bit of boilerplate code to deal with. Here’s an example of getting a count of connections from the server:

public int getConnectionCount2(String username, String password, String server) {
	IEssbase essbase = null;
	IEssOlapServer olapServer = null;
	try {
		essbase = IEssbase.Home.create(IEssbase.JAPI_VERSION);
		olapServer = essbase.signOn(username, password, false, null, "embedded", server);
		IEssIterator connections = olapServer.getConnections();
		IEssBaseObject[] connectionInfoObjects = connections.getAll();
		logger.info("Connection count: {}", connectionInfoObjects.length);
		return connectionInfoObjects.length;
	} catch (EssException e) {
		logger.error("Essbase error: {}", e.getMessage());
		return -1;
	} finally {
		try {
			olapServer.disconnect();
			essbase.signOff();
		} catch (EssException e) {
		}
	}
}

Wow, that’s a lot of code – just to do something that’s ostensibly simple. We can’t realistically expect Oracle to overhaul their API. While it’s technically possible to create a new API (from Oracle’s standpoint) that’s also unlikely to happen. Unwieldy though it may be, it’s battle-tested and thorough (although a few of you out there are miffed that it doesn’t have support for varying attributes).

So if we can’t expect the API to be refactored and offer us some syntactic sugar, and can’t write a new API, what can we do? There’s another approach: wrap the API with another library with the express intent of making the Essbase Java API easier to use, less verbose, and leverage modern language features where it makes sense.

To that end, a side project of mine has been to create such a library, called the Java Antikythera Layer for Essbase (JALE). Here’s an example of some new code that can iterate the current Essbase connections. Note that this isn’t a direct replacement for the above since they do slightly different things:

@Test
public void testGetConnections() throws Exception {
	try (OlapServer server = Ess.create().signOnEmbedded(SERVER, USER, PASS)) {
		for (OlapConnectionInfo connInfo : server.getConnections()) {
			System.out.println("Conn info: " + connInfo);
		}
	}
}

Note that this is taken from a unit test in one of the projects I have for this little experiment of a library. As the library evolves more or as more people are interested I’ll post more samples and open up a GitHub repository. Some things to note about the above code-snippet and the design of the wrapper layer in general:

  1. Adds a convenience signOn method (signOnEmbedded) to make the intent of the method instantly understandable versus passing in a parameter called “embedded” as the current API requires.
  2. Drops the IEss prefix on classes/interfaces. Modern IDEs have reduced the need for this quasi-Hungarian notation.
  3. Instead of returning an an array of objects that need to be iterated and cast to their real types, a List of the expected object is returned (server.getConnections() returns the list).
  4. Java 7’s new “try-with-resources” construct is used and the JALE server object implements AutoClosable, obviating the need for a finally block here.
  5. Also note that JALE wraps the EssException in disconnect/signoff methods so we don’t need a dummy try block inside our finally block.
  6. Ess.create() is a convenience method that auto passed in the IEssBase.JAPI_VERSION static field. We can use an overloaded method if we need to call it with that parameter.
  7. The more astute of you out there may notice a couple of other things. :)

Internally, JALE has a utility library designed to help ease the implementation of replacement classes and call the right methods. Of particular note is an “IEssIterator” unroll method that is a veritable Swiss army knife for easily replacing the boilerplate code around the Essbase iterator. For example, check this bad boy out:

public class IteratorUtil {
	@SuppressWarnings("unchecked")
	public static <E, F> List<F> iteratorToList(IEssIterator iterator, ConversionDelegate<E, F> delegate) throws EssException {
		List<F> objects = new ArrayList<F>();

		for (int index = 0; index < iterator.getCount(); index++) {
			E object = (E) iterator.getAt(index);
			F converted = delegate.convert(object);
			objects.add(converted);
		}

		return objects;
	}
}

&#91;/java&#93;

Then in our wrapper for the traditional Essbase server object, we can do this:

&#91;java&#93;
public List<OlapApplication> getApps() throws EssException {
	return IteratorUtil.iteratorToList(olapServer.getApplications(), new ConversionDelegate<IEssBaseObject, OlapApplication>() {
		public OlapApplication convert(IEssBaseObject from) {
			return new IEssOlapApplicationWrapper((IEssOlapApplication) from);
		}
	});
}

Note that conversion delegate is a one method interface that just supports the generified convert method. It’s designed so we can just supply a lambda-esque convertion method block in the new method (in this case, getApps()) which in this case contains our logic for going from an IEssbaseObject to the designed type of OlapApplication (a new object in JALE).

Future Directions on JALE/a Hyperion Essbase API wrapper

There’s no timeline on a public release of JALE. At this point it’s highly experimental as I evaluate its feasibility. I am just implementing method as I need them, so don’t expect to see me wrapping the MDX routines any time soon (not that I don’t use them but it’s not a section of the API that “needs” much fixing). I’m not worried about a performance hit from wrapping the API but I do have some concerns about performance if there’s something in the native API that loads lazily or otherwise needs an active connection to the server that would cause things to act up when I wrap them an enumerate on them, for example.

As with all of my little side projects (particularly the open source variety), please let me know if you have any feedback or want to contribute. Unlike the others at this point, this one is very pre-alpha quality so I don’t have immediate plans to make it available, but jut wanted to discuss some of the things I am thinking of an working on.

How are you using the Essbase Outline Extractor?

The venerable Tim Tow and his crew have recently released a next generation version of the Essbase Outline Extractor. As many of you know, this is a tool that has been around for years and is used to ransack a Hyperion cube’s outline an generate a text file. I have talked to people for years that absolutely swear by this tool and attest to it saving their lives.

And yet, I’ve never used this tool in a professional context. I mean, I’ve used to the tool to see that it works and otherwise experiment with it, but I’ve never had an occasion where I had to use this tool to unlock some of my outline data on my Hyperion servers. 

Generally speaking when I have been in situations where I needed the data that was in an Essbase outline, there has been some upstream system that had the data that I really wanted and could be used to build the dimension I needed. So from where I sit (which is apparently an architectural ivory tower, but I digress), the use case for needing the outline extractor is that metadata is trapped in the outline itself.

Business metadata being trapped in the outline isn’t inherently bad. However, I suspect that the propensity to manage the system this way has a strong correlation to the slow and steady migration of Hyperion system management from the laissez-faire finance department to the fortress of the IT department. In other words, finance users had the Hyperion server at their knees and could manage it by shooting from the hip, wild west style – meanwhile, IT wants forms filled out in triplicate in order to dole out EAS access. 

A question for my Hyperion readers and enthusiasts

That all being said, I am curious to hear about your  real world and practical usage of the tool. I am very curious to hear about the context you are using it in, such as:

  • Is it part of the normal automation process?
  • Did you use it to import the data into some other system that would then be used to update the outline (EIS, Studio, etc.)
  • Did you just need the data for something else?
  • Was ODI available, if it was, why didn’t you use it?

I don’t need any particulars unless you care and are able to share them. I am just very curious about the context that you use or have used this tool in. And for the record, there’s nothing wrong or indicative of a bad environment if you have used this tool, it’s just that I haven’t personally been able to use it, owing to having other options available. Please leave comments or email me, thanks! 

Cool Hyperion Infographic

Just a quick post for this beautiful Monday. A colleague of mine, Daniel Poon, has put together a really cool Hyperion infographic showing the development of Essbase over the last 23 years. The timeline lays out when various versions came out and what the major features of the time were.

It’s especially cool for me to see since I am a relative Hyperion/Essbase “newbie”, having started working with it in 2005. At the time I was taking care of a few cubes on a server that had just been upgraded to Essbase 6.5.1. My server hardware was something of a corporate “hand me down” and sported quad 200 megahertz CPUs. Yes, that’s 200 megahertz, not 2000. Sometimes you didn’t know if a calc was just taking a long time (but still working) or had just completely run off the rail. Of course, I know many of you out there have been at this much longer than I have and will have stories about your even more quaint-by-today’s-standards hardware – and I’d love to hear about your first Hyperion server in the comments.

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!