Hear all about Developing Essbase Applications

You’ve read the voice, now hear it

Here’s a stumper for you – when you read a blog, a newspaper article, or a book, whose voice do you hear when you read?  Not yours, unless you’re reading your own work.  Surely not the character or authors either.  I note that Pierre, his mother, his father, the lion, and the doctor all sound rather the same, don’t they?

But of course that’s not what is meant by a writer’s voice – that’s the style, syntax, etc.  C’mon, you have to admit (for better or worse, likely worse) that this blog has a particular style.

But wouldn’t it be great (it would, wouldn’t it?) to hear what the authors really sound like?  You can do that if you come to Kscope.  Or listen to a webinar.  Or even better, buy the book and then listen to us talk about the book.

Oracle Author Podcasts has it

I am very happy to announce that John Baker of Oracle interviewed coauthor Gary Crisci and yr. obnt. srvnt. all about the very best, most advanced, and just generally chock full of good stuff Essbase book there ever has been, Developing Essbase Applications.  In my opinion, of course.  :)

Where oh where do you hear this?

Why, at the Oracle Author Podcast web page, of course.  We’re right there, at the top of the page, and of course if you have iTunes, you can see us there as well:

If you wonder about the title, there was a hard limit on the number of speakers I could bring onto the podcast (yes, I wanted all of you fabulous writing Essbase cats there so I could relive my editor-in-chief joyful agony, but for some reason that was felt to be slightly unwieldy so two of us it was).

Here’s the complete description for your viewing (and listening) pleasure:

Developing Essbase Applications:  Advanced Techniques for Finance and IT Professionals is the proceedings of the best technical Essbase conference there ever could be with unparalleled investigation and explanation of Essbase theory and good practices.

Essbase is a powerful and intuitive tool used to build highly useful analytical models, reporting systems, and forecasting applications.  Essbase’s ease of use and power enables a rapid development cycle.  This highly productive environment does not eliminate the need for good design.  Proper design results in an elegant solution with high data quality.  Poor design results in one-off solutions and difficult to maintain applications with poor data quality.

We love Essbase and hate to see Essbase done wrong.

What’s it all about, Alfie?

Have you ever wondered why we wrote this love letter to Essbase?  I could tell you, but then you wouldn’t listen to the podcast, would you?  So listen to hear who at least some of us are, why we did it, and where we think Essbase is going.  Truly a Ripping Yarn.  Join us there, won’t you?

Stupid Programming Tricks #14 -- SET EMPTYMEMBERSETS ON doesn't work they way you might think

I love/hate it when I learn something new

I always love learning something new – it really makes my day.  No kidding.  I can be in the middle of the very most boring project/meeting/design session/whatever and if I figure out, or have pointed out to me, something that I didn’t know before and it impacts my life – I am 100% happy.  

But I hate it when I think I know something, do things (like, oh, write calc scripts) and then learn that I didn’t know that something, or at least not all of it.

So this post has to be one of those love/hate kind of intellectual moments – I am very glad I learnt it, but how oh how oh how did I not know it?  Please don’t answer.

What is SET EMPTYMEMBERSETS?

Before I go any further into this, let’s get a definition of what an empty member set is, and why you care.  

To quote my very favorite Oracle Essbase documentation set, the Technical Reference Guide:

"EMPTYMEMBERSETS stops the calculation within a FIX…ENDFIX command if the FIX evaluates to an empty member set."

Just to be clear, an empty member set is the result of a FIX statement that does NOT return any members.  It’s valid syntactically, but just doesn’t work.  One example given is FIX(@DESCENDANTS(“100-10”)) – this can’t work in Sample.Basic because 100-10 is already a level zero member and thus there are no descendants.

What happens if you don’t use SET EMPTYMEMBERSETS ON in a calc script?  Every member in that dimension is going to get selected if the FIX is on an empty set.  Don’t believe me?  Take a look at this code:

And this result:

The FIX for @DESCENDANTS of East worked (note that East is not valued), but every single product at all levels got set to 1.  

Clear out Sample.Basic, and try this again, but now with SET EMPTYMEMBERSETS turned ON:

And what does Essbase (in this case through the MaxL shell) tell us?

And in fact nothing is valued in Essbase.

Just as I thought it would work ever since I first stumbled across the command.

But wait, there’s more, and it doesn’t work the way I thought

Check out this thread over on Network54:  http://www.network54.com/Forum/58296/thread/1351216021/Fixing+on+an+empty+set%2C+Fix+ignored

And then pay close attention to Javier Fernandez’ Sy-Quia (I think that is the Javier in question – someone, I imagine Javier (yes he did as you can see through the strikethrough), will correct me if I am wrong) post.

Javier wrote:

Remember to use nested FIX statements.

fix (mbrList1, mbrList2)

does not work the same way as

fix (mbrList1)
   fix (mbrList2)

In the first instance, the calc will run if EITHER mbrList1 OR mbrList2 does NOT return an empty member set. In the second case - nested - the emptymembersets setting will kick in as long as one of those member lists return no members.

Hmm, I wonder what that means.  I always, but always thought that if you threw a SET EMPTYMEMBERSETS ON into a calc script, anything that was empty, like that FIX(@DESCENDANTS(“100-10”)) would cause everything to stop.  Nesting, smesthing, who cares?  

After all, isn’t this:

Which results in this:

Functionally the same as this:

That results in this:

In fact, there’s a thread over on OTN where the issue of nesting (Kyle Goodfriend) versus non nesting (Tim German, but with qualificatons and yr. obdnt. srvnt.) where we essentially say it’s a matter of preference.  Or is it?

Get rid of the nesting

You are not going to like this.  Well, if you are anything like me, i.e., someone who thought there was no difference between nesting and non-nesting FIX statements and blithely wrote scads of code using SET EMPTYMEMBERSETS ON in non-nested FIX statements.

What do I mean?

Let’s modify GoodFix.csc to this:

All I’ve done is put the two dimensions, Market and Product, onto a single FIX statement line.  There’s no other functional difference and I’ve proven that when empty sets are not a consideration, there is no difference between nested FIX statements and combinted FIX statements.  What oh what oh what do you suppose happens?  :)

Just like not having SET EMPTYMEMBERSETS ON in the calc script at all.  Oops. :(

Take it one step further

Let’s make both dimensions resolve to empty member sets.

What happens?  Nothing.

Javier had this right – if ONE of the dimensions on the combined FIX statement does NOT return an empty set (like @DESCENDANTs of East and @DESCENDANTs of 100-10, the first is valid while the second is not), SET EMPTYMEMBERSETS ON is ignored.

And if all of the dimensions return empty member sets (like the @DESCENDANTs of New York and the @DESCENDANTs of 100-10), SET EMPTYMEMBERSETS ON does work.  

And what have we learnt?

1. It’s a good thing I wrote here that I promised not to be right all of the time.  This was an easy one because it is so true.  :)
2. Use nested FIX statements each and every time you use SET EMPTYMEMBERSETS ON because that works.
3. Do NOT use single FIX statements and SET EMPTYMEMBERSETS ON when at least one of the dimensions has an empty member set condition because that does not work.
4. OTN and Network54 are crucial sources of knowledge.
5. I learnt something new, so I am happy.  I wish I had known it before, so I am sad.  Such is the duality of man, or at least Cameron.
6. It would be kind of nice if this distinction was in the documentation.  I will bug my documentation contact at Oracle with this blog post.

Stupid Programming Tricks #13 -- Exporting in EAS so you can import

The export problem

Oh, I cannot believe that I didn't know this one. 

Can

Not

Believe

It

Have you ever wanted to do an export in EAS but realized that you didn't have file rights to the Essbase server?  Most locked down environments are this way, right?  And so what do you do?  You find someone (anyone) who has file rights to the Essbase app folder and he moves it to a location you can access.  The thing is, if that was a big export, and it usually is, it takes time to move it to a semi-open location and of course when you load via EAS, you will load first to EAS, and then EAS loads that data to Essbase.  At least three copies of the data file(s) occur.  Ugh.

Wouldn’t it be great if there was a way to export that Essbase data to a location you could access directly?  Wouldn’t it?  Why yes it would.

The export Stupid Trick

I have to give credit to Dave Anderson for showing me this technique.  This is now the second time he has shown me a Stupid Trick.  Sigh.

Application level

So easy, even Cameron can do it.  

When you export, put in the application name into the Export to file (shown here in 11.1.1.3 ‘cause that’s what’s on my VM, but trust me, it’ll work in the latest release as well), then a backslash, and then the file name.

See the “Sample\”?  How oh how oh how did I not try this in like, oh, the last 18 years?  How?  Don’t answer.

Do you see the file?  This is on a data load – I simply navigated to the application folder.
Arrrrgh!  

And Yeeeeeaaaaaahhhhhh!  

All at the same time.  The jubilation outweighs the frustration, thankfully.

Database level

So if I can export to the application level, surely I can export directly to the database folder itself, right?  And so it was.

 

Note that I have now typed in “Sample\Basic\” in front of the file name.  Could it, would it, does it work?  Yes indeedy.

 

Multiple file export
Can this be expanded to multiple files?  Oh, yes, it most absolutely can.

 It’s difficult to see, but I typed: “Sample\Basic\Tst1.txt,Sample\Basic\Tst2.txt”

And here they are, in their parallel export glory:

 

It's that easy.

So do you feel dumb or smug?

I have an email list of fellow consultants, former and current customers, and various people who are too polite to tell me to go away – this is my Stupid Tricks list.  When Dave Anderson showed me how this worked, I had to share with everyone.  Some people (Opal, Natalie, Sébastien) thought this was way cool and others (Glenn, Rich, Josie – hmm, all colleagues from a particularly difficult project, I think I now know who was the weak sister) couldn’t believe I didn’t know this.  I couldn’t believe I didn’t know this trick.  But now everyone does, I hope.  

I have banged my head against the wall with exactly the problem this Stupid Trick solves, oh, a zillion times during my Essbase life and this would have been so nice to know.  At least I learnt it before I retired.  :)