jason w. jones

software from the trenches

Speaking at ODTUG!

The highly regarded Essbase figure Tim Tow of Applied OLAP called me the other day to inform me that my submissions for ODTUG were accepted! Details are still being worked out, but the focus of the first talk will be on data load optimization (a matter very near and dear to my heart) as well as a sponsored presentation on Dodeca, which is one of Applied OLAP’s flagship products (and also increasingly near and dear to me).

These will be my first presentations in front of the Hyperion/Essbase community so I am hopeful when the time comes I will be able to cram the hour into one of the most useful and comprehensive sessions ever. For those of you out there in the Essbase community that I haven’t met or spoken to, I’ll be seeing you in Monterey next year!

Continue reading Speaking at ODTUG! →

Automate that old cube archive process!

Your server may have dozens or even hundreds of cubes on it.  A common strategy with a large and slowly changing Measures dimension (or some other dimension like Product) is to spin off a copy of the cube after a certain time period, typically the fiscal year end.  There are a number of different reasons that you might do this.  First, the cube may simply focus on Current Year and Prior Year, or a fixed number of years and scenarios such that the cube becomes too unwieldy when you start adding more.  Second, if you need to be able to go back and pull a report so that it looks exactly how it did in a certain fiscal year, then you may need to spin off the cube.  Depending on how many cubes you end up spinning off for each fiscal year, it may be necessary to go and clean them up at some point, but you might still want to keep them around, just in case.  You can do this by hand by stopping the app, zipping up the app folder and all its contents, and deleting the app from within EAS.

Continue reading Automate that old cube archive process! →

Optimizing Essbase Automation Jobs (for fun and profit!) – Part 1

I freely submit that I am a complete geek.  But it’s okay, because I have come to accept and embrace that inner geek.  Anyway… Many organizations run jobs to perform a task as certain intervals.  Your job as a good Essbase/Hyperion administrator is to be lazy — that is, make the computers do the boring stuff, while you get to do the fun stuff, like thinking.  What we end up doing is setting up batch and script files that do all of the things in a particular sequence that we might do by hand.  So instead of logging in, copying a data file, clearing a cube, running a calc, loading some data, running another calc, and all that other stuff, we do this automatically.

Continue reading Optimizing Essbase Automation Jobs (for fun and profit!) – Part 1 →

A MaxL quickie to unload those databases that are eating your precious memory

Due to various business requirements, some organizations end up archiving many of their cubes each year.  For example, if you have a huge Measures dimension that is constantly changing (even in subtle ways), but you need to be able to go back at some point in the future and see what some numbers looked like at some particular point in time, you might find yourself spinning off a copy of the cube that sits on the server.  Most of the time it just sits there, dormant, not being used.  But every now and then someone comes along and spins it up so they can refresh some obscure report.  It’s got a decent sized outline to it, so the overhead just on having this cube running is probably in excess of 50 megs of memory, just to sit there!  If you aren’t rebooting your servers that frequently, that app and database are just going to sit there until someone comes along and stops it.  This might not be a problem for your current situation, but for this particular server, there are over two-hundred apps available at any given time — and RAM is a finite resource.

Continue reading A MaxL quickie to unload those databases that are eating your precious memory →

Jason’s Top 10 Essbase Data Load Optimization Tips

I received an email today from someone looking to speed up their data loads, specifically their ASO data loads that seem to be taking too long.  This is, of course, an important topic for many Essbase cube wranglers.  I would be willing to bet that many people spend more time optimizing calcs and may even neglect profiling their performance on data loads.  You might be surprised just how long your automation spends doing a data load.  That being said, there are several different scenarios you may find yourself in and different places to optimize.  Of course there are more items that can go on this list, but here are the big ones off the top of my head.  In other words, if I was tasked with improving my data load speed, here’s what I would look at:

Continue reading Jason’s Top 10 Essbase Data Load Optimization Tips →

I submitted two abstracts for ODTUG!

Last year in New Orleans, the Hyperion track at ODTUG was an unqualified success.  It was such a success, in fact, that hundreds of abstracts were submitted for next years conference in Monterey.  I submitted two of those, so wish me luck.  I’d like to talk about an end-to-end Essbase automation system profiling/performance/optimization effort which would cover all the Essbase optimizationi techniques collectively (Outlines/Dense/Sparse, Calculations, MaxL, and some other tricks I have up my sleeve).  I also submitted an abstract to talk about how using Dodeca has improved our reporting systems in many cases, and allowed us to ditch some spaghetti code VB apps in the process.  So I am anxiously waiting to see if I get to talk next year….

Continue reading I submitted two abstracts for ODTUG! →

The Essbase Spreadsheet Automation Trick

This one is a bit of a blast from the past.  And I mean that — the timestamp on this file is over five years ago.  You may have heard people refer to this method.  If you’ve ever found yourself getting confused over what-job-runs-when-and-on-what, then this might be a technique that works for you.  Obviously, this one was written when ESSCMD was all the rage, but you could no doubt adapt it to whatever you want.

In the attached spreadsheet, there are rows and rows of different jobs — just ESSCMD scripts — that all serve a particular purpose (and in this case, there are two sets of databases: a “Weekly” database and a “Daily” database.  This is for historical performance reasons — everything is in one cube now).  The days of the week are in columns, with an X to denote if the job runs on that day.  Note that the jobs are numbered so you always run them smallest to largest.  There is a simple Excel formula that populates the corresponding columns to the right if that script has an X for that day.  The idea is that you can then copy that and paste it into your batch file and then call all those scripts in that day’s file.  It’s remarkably simple, and it works well.  It’s also self-documenting: any time you update the spreadsheet, and the subsequent batch file, you just print up the schedule again and you have up-to-date documentation.

Continue reading The Essbase Spreadsheet Automation Trick →

Automated EIS outline builds

Here’s a little quickie that I thought of and put in the other day. If you use the “export script” feature in EIS so that you can use that in conjunction with EIS in order to script your outline builds, you get a script that is specific to a particular server. I like to keep my test machine automation totally synced up with my production machine automation, but if I needed different scripts for different servers, then I would have to have a different automation file. Well, here’s what I did:

Name the outline build script with thte name of the server in it. I have my server name already set in an environment variable (it’s used all over the place for various automation and logging things). So if the .cbs was normally foo.cbs, and the name of the machine (prod) is bar01, and the test machine is bar02, and I reference the outline build command as foo_%SERVERNAME%.cbs, then whichever machine the script runs on, it’ll grab the right script. Kind of a small thing but I like it!

Continue reading Automated EIS outline builds →

More fun with Ubuntu Brainstorm

I haven’t tried my hand at Ubuntu Brainstorm again yet, but I have been keeping up on all the ideas.  I’m quite happy to see that the Brainstorm site has been beefed up a bit with categories  and some other goodies.  I’ve been going through and moderating on some ideas.  One thing of note, however… I am still quite amazed at how many posts have the words “by default” in them.  This tells me a couple of things.  One, a lot of the defaults suck.  Two, it must be perceived as rather inconvenient or difficult to change those defaults.  I would think that it’s really a combination of the two.  A lot of the defaults are probably picked by whoever wrote the particular piece of code, and the systems for managing options seem to be hit and miss.  All too often we have to drop to an esoteric .config file and change something, and on top of that, it takes everything up to and including a reboot to see the new default in question.  And still at other times, even if I have a GUI for managing options, it still generally seems to let me shoot myself in the foot.  But so it goes… for now.

Continue reading More fun with Ubuntu Brainstorm →

Normalize a fact table for a better EIS model

Generally I don’t load data through EIS because quite honestly, I fudged some of the EIS models when I first made them and now I’m pretty much married to them.  They are perfect for loading the outlines, but not data.  Earlier this week I was playing around with one of my newer models, trying to keep it ‘clean’ as it were, and after playing around with it, I got it set just right so the default SQL (quite a gnarly little query, really), much to my amazement, loaded exactly the data it needs from the fact table.  The one major thing I have had to tweak is the format of the fact table.  Upstream stores 3 facts in the table: for each period/location/account we store the value for the current year, prior year, and the budget value.  What I do is kind of normalize the data with a SQL script that takes all that data and puts it into a different table that has a year (YR) field and a scenario type.  This way in the EIS model, I can link this modified fact table cleanly to TIME, YEARS, SCENARIO, LOCATION, and possibly DEPARTMENTS.  It works quite nice!

Continue reading Normalize a fact table for a better EIS model →