SET EMPTYMEMBERSETS – Surprises in Essbase Part I

I couldn’t think about anything other than “Suprises in Essbase”.

I was working on a Calculation Script and all on a sudden it started behaving weird. (I know that “weird”, “strange” all are strong words)

The feeling was like someone telling you that Baa Baa Black Sheep is having only a bag full of wool!!!!

Coming back to our surprise, I was working on a script were I was using @ATTRIBUTE function and it has EMPTYMEMBERSET command to stop the calculation where it hits a “EMPTYMEMBERSET” (isn’t that kind of obvious). 

The script was running for long time than expected and I started investigating.

I know that, I’m not getting some of the attributes and I was not expecting Essbase to run the calc for those FIXes.

I ended up googling and landed hereI thought of not to re-iterate what Cameron has written there, however what followed made me wrote this.

I fixed my script, added NESTED FIXs and now the problem is solved.

Now you would be imagining why I wrote this much where I could have said. “Hey guys look at what Cameron has posted about EMPTYMEMBERSETS” and here is the reason.

I talked to someone about the problem and he/she replied saying “No buddy I’ve used it all the time and I didn’t face such an issue.”

Then I realized……

Some of us don’t know the effect of hitting an empty member set (How it’ll *****-up the data?) when it not ON.

Some of us don’t know that for EMPTYMEMBERSET we shouldn’t be combining members in FIX. (I fall under this)

This is a bigger problem (or highly overlooked SET command) if you are a Planning developer.

I’ve seen many people using @IDESCENDNATS({RuntimePrompt}) in FIXes. It’s not a misspell that command works, more on that in next post.

What will happen if the user select a level 0 member as the prompt then it’ll be similar to the calc given below.

“100-10” was selected by the user from the prompt. I’ve not included EMPTYMEMBERSET here and see the result of the calc!!!!

Yes it populated entire Product dimension with a 1!!!!! 

Ohhhhh so that’s the effect of not using EMPTYMEMBERSET!!!!! (I think the issue drove me crazy I’m mocking reactions)

If you think that there could be a chance of empty set use the SET command. (Use it wisely)

Also remember to use Nested IFs while using SET EMPTYMEMBERSETS ON; command.



After publishing I realized that this is my 100th post!!!!!

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 thoughts on “SET EMPTYMEMBERSETS – Surprises in Essbase Part I