I recently dusted off one of my Hyperion/Java flights of fancy the other week: HUMA. HUMA is a tool for scanning a cube and finding which members have no data associated with them at all. As I mentioned in the post talking about some performance tweaks, HUMA scans every single cell in an entire cube to determine what is empty.

One of the reasons I put HUMA on the self for a while is that in a program that does this, there’s a lot that can go wrong to cause the program to fail or run so long that the sun goes supernova before the program finishes. If you were lucky then you’d get a list of members that you could put on the chopping block. If you’re lucky it’s even a member in a dense dimension, whose removal might just increase your block density ever so slightly.

That all being said, let me just say up front that maybe there’s not a lot to be gained from doing this. Maybe the real solution is to buy some nice fast SSDs for your server, or increase the cache size, or check out that fancy new ASO Planning stuff, or whatever. Let me just say upfront that I get that. Nevertheless, this tool exists and if you’re the type of administrator who looks for any edge you can get, well, then maybe you’re one of the dozens of masochists people that have already downloaded this thing.

So, for the curious: how do you and how does HUMA scan an entire cube with a kajillion possible data cells? Glad you asked…

Step 1 – Read the outline

The first thing that HUMA does is read the outline for the list of stored members in each dimension. HUMA also pays attention to which dimensions are sparse and which are dense.

Step 2 – Build a virtual grid with every single cell combination

Imagine you’re in Smart View and you are drilling around Sample/Basic. You drill to the bottom of each dimension, and now there is nothing in your POV – it’s just rows and columns as far as the eye can see. You’d have something like this:

All dimensions in the Sample.Basic database have been drilled into; there are no POV/page members.

In practice, even a barely non-trivial combination of members from multiple dimensions results in a grid that is gigantic – and too big to store in memory all at once. HUMA doesn’t actually generate this grid in memory, thankfully. It generates (incredibly quickly) the member combinations for any place in the grid. Hence why it’s a virtual grid. From here the problem is actually kind of simple sounding: just check all the cells. Before we do that, let’s come back to why we paid attention to dense and sparse:

A single row is highlighted: given columns A-C representing sparse dimensions, then the highlighted row represents the entirety of a single block.

Spare dimensions are on the left, dense dimensions are on the top. So you can see that the highlighted row in the above screenshot corresponds to a data block (in a BSO database). The data is strategically laid out this way so that to the extent possible we will be retrieving data against the same blocks over and over, giving the Essbase engine a chance to leave those in memory and serve them up faster.

Step 3 – Retrieve sub-grids

We can’t, obviously, just pull back all the data at once… we need to iterate through it. For technically reasons we also need to limit the number of cells we pull back at once, and we have a maximum number of columns (for Java API purposes) of 256. We might want to set the max cells per retrieve though. So let’s say that’s about 50 cells. We’d then be retrieving sub-grids roughly shaped like this:

A fully expanded grid with different sub-grids of data represented. The top left starts out as grid 1, then moves right to grid 2 and keeps moving right. The next row picks up at theoretical sub-grid number 24.

See that the first grid is one, then to the right (in the same “hot” blocks) is 2, and then so on through to the right side such that the next grid down in this case happens to be 24, then 25, and so on. Even if we just retrieved (or tried to retrieve) all of these grids, it would still take a considerable amount of time. So there’s one more trick up HUMA’s sleeves: quick member elimination. Let’s look at the data in grid 1:

Another look at the data contained in HUMA sub-grid number 1.

Step 4 – Analyze data and reconfigure search space

We’ve found data for such member combinations as Cola, Actual, New York, and so on. We can now remove these from consideration in the search. HUMA knows that it no longer has to ever check any of those members. Because of it’s extremely fast grid regeneration/iteration, it does this automatically and shrinks the search space by removing those members. This can speed things up incredibly. If you watch the [at the moment very verbose] HUMA output, you’ll see some debugging information about how many grids there are, and as members are found, this number gets recomputed.

HUMA will finish in one of two ways: it eliminates every member from consideration for being unused and it quits, indicating as much, or two, it finishes and it reports back all of the unused members. I guess there’s a third option, which is that HUMA crashes. Actually, HUMA itself won’t crash, but the most likely issue you’ll run into is port exhaustion, in which case you’ll want to increase the milliseconds between retrieves and try again (or just increase the number of ports on the server, as indicated in the help).

That, in a nutshell, is how HUMA operates. This time around, performance is off the charts compared to what the first version was, so that’s cool. This program is available from the Downloads site. This is still a very rough program but please feel free to share your feedback and experiences.

So, just whatever happened to HUMA – the Hyperion Unused Member Analyzer? Well, a few things. One, Drillbridge happened which was a little proof of concept that somehow morphed into a real life tool (now in production in multiple environments and continents… you’re next, South America!) over the course of six months.

Two, it was – and is – a quirky little tool. Put shortly, there’s a lot that can go wrong when running HUMA but not a whole lot that can go right. There’s Essbase version issues, port exhaustion issues, performance issues, and all sorts of things that can pop up.

The main issue is the performance. There’s no magic trick to how HUMA works – it literally reads the entire outline for stored members, generates every single unique combination of members (the Cartesian product), and goes about retrieving them. Every. Single. One.

Ever look at the stats for a cube and go to the tab where EAS tells you the total possible number of cells? If you haven’t, it’s not hard to find. In fact, you can even squint when you look at that tab in EAS. Basically you’ll see a bunch of really reasonable numbers on the page, and then you’ll see a number that’s just incredibly, seemingly out-of-place huge. In fact, the colorblind masochists fine folks that designed EAS should have programmed EAS such that instead of showing the number, it should just say “seriously, it’s huge, why bother?”

Anyway, even for non-trivial amounts of members in a cube I determined that HUMA would almost literally take until the end of time to run. So HUMA needed to get smart in order to try and finish analyzing a cube before the the sun goes supernova.

To that end, I have made a couple of big changes to improve the performance of HUMA. I’m going to geek out here for a minute. You don’t need to read the next few paragraphs in order to use HUMA, and if I come across as a pretentious geek that’s showing off, well… I probably am. I’m proud of this junk.

Grid Generation Speedups

The first big change is to rework how the grids that HUMA retrieves are generated. As I mentioned, HUMA reads in the stored members from dimensions and uses the different combinations of members to generate all the possible data cells in a cube. Funny thing is, even when I took out the Essbase grid retrieve, the thing still took quite awhile to run. It turns out that generating these member combinations across a whole cube is computationally intensive. So I adapted one of my workhorse Java libraries to iterate through all the combinations really fast, so I could keep them from clogging up memory. The result is amazing – grid generation just flies now. This library is called Jacombi (Jason’s Combinatorics Library) and is now robust and battle-tested, being used in Drillbridge, Saxbi, HUMA, and cube-data (a tool that generates test data for cubes). So with the member combination generation problem solved, it was time to turn on grid retrievals and optimize more.

Quick Member Elimination

The next big, and probably even bigger win than the above in terms of performance is quick member elimination. Earlier versions of HUMA would count up the occurrences of data in the cube. In the new version, once data has been detected for a given member, it’s quickly removed from the search space. For example, if HUMA retrieves a grid for scenarios Actual and Budget, then determines that there is data for Actual, it will skip it in all future grid pulls. This can dramatically reduce the search space. In fact, it can hack away at the search space amazingly quickly. The ability to recompute the analysis space is directly enabled by the previous optimization. For non-trivial datasets, the grid generation step was long. Now it’s instantaneous. Effectively HUMA starts to zero in exactly on where there isn’t data. The more data you have in the cube, the quicker it eliminates potential unused members and finishes.

I just ran HUMA on Sample Basic with stock data and it scans the entire cube in a few seconds. I’m not saying this is the performance you can expect with your cube but it’s promising.

Testers?

This new version of HUMA is incredibly alpha quality – not even beta. Right now it’s still a console app and the log file is incredibly messy. A few of you over the last year have expressed quite an interest in helping out, so if you want to do some testing (or testing again), please comment on this post or contact me. I have a build ready to go. In the next week as time permits I’ll put it on the download site where Drillbridge and others are, and eventually build up to a slightly more polished release. I think this tool would look great in a GUI but I don’t quite have the bandwidth right now to design one up, so a nice old-fashioned terminal is what it’s going to be for now.

Thanks!