A Three-part Stupid Trick
This is the second in the Stupid Trick series for the ODTUG Hyperion SIG newsletter.
A recap – Stupid Tricks are not stupid, although sometimes (as you will see below with part one) I feel rather stupid when I finally figure them out. Stupid Tricks are just…tricks, tips, and things that drive me crazy ‘til I figure them out. Some of them will likely make you shake your head and say, “Shame, shame” others will (I hope) make you laugh out loud. And with that hopefully evenhanded introduction, let’s explore Essbase BSO restructuring tricks.
Trick the First, Planning Drives Me Nuts Yet Again
Or is that me driving Planning nuts? I suppose this is a matter of perspective.
Off we go
By default, when creating members in Planning (I am talking Classic – I have zero idea what EPMA does, but I’ll bet the same) interactively, the member automatically inherits the parent’s storage type. IOW, if the parent member is set to dynamic calc (which is pretty normal in Account and Time dimensions), all interactively added child members inherit that storage type.
What happens if you do not assign a formula to that member? Planning “helpfully” assigns a formula of “’;” like below:
Hmm, not terribly informative, is it? No error, either. But the point is, you don’t see anything. And what’s in Essbase (as we all know in BSO Essbase, if you have a dynamically calculated level zero member, it must have a formula, and yet we don’t see one above)?
Ah, a completely useless formula.
And that is a kick in the head because if it’s in the block, you must first (as I wanted to) set the storage type to Store or Never Share and then restructure the entire database which takes, oh, approximately forever.
I have no even moderately clever fix for this except perhaps a comment that Planning really, really, really ought to flag incomplete settings like the one above. Essbase doesn’t accept it, why should Planning? And oh yeah, you (or is that me?) ought to pay better attention.
Trick the Second: Everyone Knows This, Right?
That BSO Essbase uses twice the disk space on restructure is well known. It's even right there in the DBAG:
If the database contains data, you need enough free disk space on the server to create a backup copy of the database. Backup ensures that any abnormal termination during the restructure process does not corrupt the database.
But what about this?
Did you know that a restructure requires double the RAM (temporarily)? Not me until I started playing with some very large BSO databases (larger than I ever would likely use on a "normal" server). Here's Windows Task Manager with the ESSSVR.exe process on top:
Where does that 3.45 gigabytes of RAM come from? From this:
So what happens when I issue (to be more fully illustrated in the next Stupid Trick) a restructure?
6.45 gigabytes of RAM is consumed. If you do the math, the size of the index cache + data cache is 2.95 gigabytes. And the difference (I hope you are all appreciating how I am doing basic math drills) between the large (during restructure) and small (after restructure) RAM size is...just about 2.99 gigabytes. Close enough for government work.
Essbase does release the memory when the restructure (not outline or sparse but dense or forced via MaxL/EAS) is finished.
And why do you care?
If you are on a server that is not over supplied with RAM, and you have a Really Big BSO Database (henceforth I shall call these RBBDs), you can eat all of the memory with a restructure. Which could be bad if you, oh, tried to do anything else at the same time, like reporting, calcing, etc. on other databases.
The moral of the story (thanks, Jason Jones): If you have big cache settings, and not a whole lot of RAM, temporarily set the caches smaller, do the restructure, then set them to their original values, remembering to stop the db and start it back up on each change. And maybe buy some more memory.
Just another thing to remember when working with Essbase.
Trick the Third, Moving Data Across Drives
It is good to know that Stupid Tricks are not solely the province of my febrile (feeble?) mind, as this particular trick was demonstrated to me by my fellow ACE, John Booth. It made me laugh out loud – I never thought of this and it is pretty cool.
What’s the problem?
When you set a disk volume for BSO Essbase, Essbase writes its files there. That isn’t actually a problem.
What is a problem is when you want to move those files from one drive to another. The way I always thought this had to be done was to:
1) Export the data (level 0, all levels, whatever)
2) Clear the database
3) Set the database disk volume to whatever (btw, this could come before #1 if you wanted, I just never did this)
4) Click on Set
5) Click on Apply
6) Stop the database
7) Start the database
8) Import #1 back in and maybe do an agg all if level 0
Whew, what a pain. And slow. And manual. So a slow, manual pain. It sounds like something you get from gardening too much. Is there a better way? Why yes there is.
Here is a big (by my standards) BSO database. I need to move it to C (hey, it’s an example, you’ll move it to E or some Linux volume). Do I do the above eight steps? I do not and neither should you.
So what do I do?
Oh, I love leading questions, especially when I write them. What I do is the following on the database properties Storage tab.
1) Add a new drive
2) Set it
3) Apply it
I hope you are noticing that you do not need to restart the database to set this. I may have been doing that for, oh, 18+ years of Essbase for no reason whatsoever. Nice.
4) Clear out the upper level blocks in EAS
5) Restructure the database
6) We are done – everything is over on C. Pretty cool, eh?
But it gets better, much better
So I thought to myself, “Self, that’s pretty cool, and you had no idea that Essbase could do that. I wonder what happens if you follow the above and just skip step #4 which clears out the upper level blocks? And I wonder if you’ll ever stop talking to yourself ‘cause it scares people when you do it out loud?”
In case you haven’t guessed, Essbase just moves the IND and PAG files from one drive to another – the restructure is the Stupid Trick catalyst. I was gobsmacked, but in a nice way. So, just:
1) Add a new drive
2) Set it
3) Apply it
4) Restructure it
Of course you must have adequate disk space for this to happen – there’s no magic bullet here but following steps 1 to 4 along is way better (and less manual and hence less error prone, at least if your name is Cameron) than my original eight step approach. If your database is big, really big, plan on doing something else for a while as everyone will be kicked out while the restrucutre is occurring.
Three of a Kind
So there you have it – three things about BSO restructures that either make you wonder how I manage to do this for a living, tells you something maybe you never noticed but you should, and gives you (thanks to multiple contributors) a really cool way to move data across drives.
Be seeing you at the next newsletter.