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.  :)

I’m running for the ODTUG board, again

Probably because I eat pain like candy

It’s time to make your ODTUG voice heard – there are three members of the board whose two year term is expiring, and I am one of them.  Do you like what we’ve done?  Think we’re a bunch of bums who ought to be tossed out on their ears?  Somewhere (hopefully more towards the “like” side of the scale) in the middle?  If you are a paid member of ODTUG as of 1 October 2012, then you can vote until midnight Pacific 30 October 2012 (that’s 3 am Eastern 31 October 2012).

I would love to tell you to vote early, vote often, and you can – for up to four candidates.  Hopefully, one of the four you vote for is me.  :)  

I also hope you vote to return the expiring members of the board (Barbara Morris, Tim Tow, and yr. obdnt. srvnt.) because we are committed, active, and effective members of the ODTUG board but of course the choice is yours.

To give you a feel for why I want to continue in the is-it-pleasure-or-is-it-pain board director role, take a gander at my expanded campaign statement below.

Good luck to all!

Do I still love ODTUG?

You bet.  In the last two years I have:

• Gotten that ODTUG tattoo.
• Blogged, tweeted, and generally bored everyone I can lay hands on, personally or electronically, with the Awesomeness That Is ODTUG.
• Served my freshman term on the ODTUG board.  In spite of knowing exactly how the sausage is made, I am coming back for more.

Why reelect me to the ODTUG board?

I am still passionate about ODTUG because our user group gives us all an unmatched environment for knowledge sharing, camaraderie, and incredible value.  ODTUG continues to be the user group I dreamt of finding.  

ODTUG board membership means (Hah!  More like requests, requires, and demands.) hard work and dedication.  I think that my record shows that I embrace those sometimes almost overwhelming requirements to ensure that all members of ODTUG and the wider Oracle community are served to the very best of my abilities.  I am an independent consultant – when I take time out during the workweek to do ODTUG tasks, the opportunity cost of that volunteer work hits me directly in my pocketbook; I do it regardless because I love ODTUG.

Sometimes the anticipation of an act outweighs the consummation.  I think that my desires, hopes, and accomplishments around my first term have been more than fulfilled.  In the past two years I have:

• Led the charge to lower individual membership rates.  It’s now just $99 a year.  What an incredible (really) bargain.
• Initiated and stewarded the BI/EPM Kscope Labs in the Cloud, i.e., no more Lab Laptops From Hell.  If I had to point to a single accomplishment this is it!  As someone who has been both a trainer and a student, I know that the last thing I want to focus on during a lab as I feverishly try to process the material is “Why doesn’t my laptop have the right software/enough memory/the correct setup?”
• Passionately and effectively advocated for the EPM community.
• Been incredibly active in the BI/EPM content selection process.  Remember that crack about sausage making?  Meet the butcher’s apprentice.

As a returning member of the ODTUG board, I will channel that passion and energy into making ODTUG even better value for your paid membership through:

• New and innovative outreach initiatives like the soon-to-come virtual Ask an Expert Panels.
• Leveraging ODTUG’s relationships with Oracle and vendors to provide better online content.
• Increased outreach to all areas of Oracle’s product line.
• Being a fanatical advocate for continued and sustained focus on the technical subjects that make ODTUG so great.

Biographical Sketch

I first worked with OLAP technology in the dinosaur days of Comshare’s System W and saw the Essbase light in 1993.  

Since that life-altering event, I have:

• Introduced what was then Arbor Software’s Essbase to Johnson & Johnson Corporate.
• Independently consulted since 1996, with a brief foray into working for consulting companies.
• Created solutions for customers using Oracle’s Essbase, Planning, and anything else that ties to those two products.
• Actively posted on OTN’s and Network54’s Essbase board – sharing knowledge makes his day interesting.
• Presented at multiple conferences including Hyperion Solutions and of course ODTUG Kscope.
• Taught multiple formal classes and webinars.
• Served on the ODTUG Hyperion SIG.
• Assisted the BI/EPM Content Chair with conference content selection for the past two Kscopes.
• Been an Oracle ACE since November 2010 and an ACE Director as of August 2012.

Free For All

ODTUG is your user group and as a paid member, you can influence what your user group does.  To do so, you must vote.  Make ODTUG work the way you want.  Be seeing you.