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 here. I 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.
HTH
Update
After publishing I realized that this is my 100th post!!!!!
Thanks Celvin for the post, could not have got to Cameroon's post with out this one. Thanks for highlighting it.
Good to know that it helped
Thanks celvin for this post, it was very helpful