Category Archives: administration

Pre-seeding Hyperion Planning User Preferences with values for a smoother user experience

Wow, I think I am actually writing an article on Hyperion Planning. I think pigs are flying right now. I have been helping out on a system upgrade for the last few months where we are in many ways “refactoring” a Planning deployment. I’m borrowing that term from the software world. In other words, we are changing how things work under the hood without the explicit intention of changing how things look to users. One of the changes we are making, however, is to introduce some variables for users to be able to change their Version and Scenario.

Just to be clear, the variables are the ones that are set in the preferences menu. And we’d like to provide some defaults so that the users have the most likely choices pre-selected. We can export the User Preferences option from LCM. The corresponding XML file has a section for each user’s variables. It’s like this:

<UserPreferences>
  <UserPreference UserName="jason">
    <!-- some stuff here -->
    <UserVariables>
      <Variable Name="Scen_UserVar" Value="Forecast"/>
      <Variable Name="Ver_UserVar" Value="Working"/>
    </UserVariables>
  </UserPreference>
</UserPreferences>

There’s, of course, a UserPreference section for each user. We can edit the variables here in this config file and the import it to the target system (or back into the current one) to fill the values. A couple of notes to consider:

  • If you try to strip out the other stuff in the UserPreference section so that it doesn’t get touched, it’ll just load defaults for that user. You might not want to blow out the user’s settings that way.
  • The reason for trying to do the above bullet item would be if you’re just trying to copy and paste the same block of code for each user.
  • A user in the target system might not be in the User Preferences export – you can create that manually by copying and pasting a different user.

It’s incredibly likely that there’s a better way to do this or some magical option I don’t know of somewhere that’ll take care of it, but I wasn’t aware of it and decided to “brute” force it. The copying/pasting and editing was the “hardest” part as I couldn’t think if this procedure could be reasonably automated in UltraEdit or Notepad++ or something, so I just did it by hand.

Hope this helps someone!

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!

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…

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! 

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!

Hyperion Essbase wish list: Import a compressed file

I thought this up while attending Dan Pressman’s Kscope presentation How ASO Works and How to Design for Performance, a presentation that definitely appealed to my inner Hyperion geek. Dan did a crazy deep dive on performance tuning with particular respect to loading ASO. He had some pretty bangin hardware to play with too.

Long story short, and many of us have known this for awhile, but there are ways to format your Essbase load files so that they load faster. Basically what you are trying to do is make things easier on Essbase: stream in less data, don’t repeat things you don’t need to repeat, don’t thrash blocks in and out of memory, and so on. That’s all well and good.

The advent and proliferation of SSDs in the enterprise has done wonderful things for Hyperion performance by  eliminating a lot of the performance quirks with rotational media and penalties from fragmentation. But at the end of the day we are still looking for ways to pump ever-increasing amounts of information into our cubes even faster than we were the day before.

For instances where we are loading a file that resides on the same machine as the Hyperion apps/cubes or even across the network, I wonder what, if any, performance benefits are to be had if we had the ability to import a zip file?

Zip files can get awesome compression on text files. They can also have their uncompressed contents streamed. In other words, it’s not necessary to extract the contents of a zip file before you can read the contents (starting at the beginning). In theory, if one achieved moderate to decent compression on their zip file and handed that to Essbase (say with a specialized import data MaxL command), it would be saving time on the disk-read aspect of the data load, at the expense of some additional CPU usage. Many Essbase load operations are disk I/O bound anyway so this seems like a reasonable tradeoff to make.

As an additional benefit or elaboration on the concept, perhaps multiple text files could be placed into the same zip file, perhaps with a “load manifest” or options on the load command, and Essbase would attempt to parallelize the data load to the extent it can. This would likely be an add-on feature once the basic support is in place. In all you would need to augment the data load process with a zip file reader routine (this would be an off-the-shelf library that is quite common), a couple new MaxL import data variants, and an augmentation to the Java API. I suppose you could leave the MaxL command alone and just program the interpreter to look for a .zip extension and treat it accordingly, but it seems like it’d be the better choice to specifically indicate the data load is from a compressed file.

Of course, if you’re loading just from SQL this whole thing wouldn’t apply to you. Loading data files may seem low-tech but it’s incredibly common and often times I prefer it as I have an exact text file to tie back to, if need be, versus a possibly changing SQL data store (but that’s a conversation for a different blog post). This feature would cater to the performance nuts out there – and if Kscope is any indication, there are plenty. I’d be curious to hear anyone’s thoughts on this.

Hyperion Papercut: LCM security migration woes

Raise your hand if you’ve ever blown away your admin password when doing an LCM security migration. You there, in the back. I’m aware of this one because various of my colleagues are aware of it and they have told me to watch out.

Cameron Lackpour sent this one my way, though I was aware of it. Basically the issue is that you are migrating from server to server, presumably from development to production, and this includes user names and passwords (at least for native users). So if you migrate the development admin account to production, you blow away your production password.

So I guess you can say the tool is just doing what it’s supposed to do… but you’d think there’d be some provision on the target system that goes “Hey, maybe let’s not pave that really critical production admin user password with crap from somewhere else, mmkay?”

Anyone else out there shoot themselves in the foot with this? Or I am just missing something incredibly obvious?

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…

Hyperion Papercut: Cross-server cube copy in EAS doesn’t carry over data

So, this might not be a Hyperion papercut in the sense that I have been having some fun with. And it’s not something that I have even thought was an issue until it came up today. When you copy a cube onto the same server as the source cube, you get a full copy plus all the data. But when you copy the cube to a different server, you get everything excluding the data.

I’ve always assumed this was by design and have taken it as such. I mean, if the source cube is huge you could potentially do some bad things when you copy it to the target server. But wouldn’t it be nice if EAS just provided a checkbox to copy the cube with the data when you are going across servers? Obviously it knows how much space is available since the destination server can provide that information.

Of course, the process for getting over the data isn’t too onerous – you just export the data (raw) and load that up as-is to the target. All things considered, is this really a big deal? Of course not. But if you approach the construction of your software with a craftsmanship mentality, would the design be at least a little different? I think so.