Fixing on an empty set, Fix ignored

Fixing on an empty set, Fix ignored

Joined: November 15th, 2007, 1:21 pm

October 26th, 2012, 1:47 am #1

I'm not sure if there's a good solution for this, but I thought I'd ask..

I have a BR that fixes on a user selected parameter for the entity dimension. There are parts of that BR that also fix on a smaller subset of something like FIX(@UDA("Entity","CAD"), but if there are no CAD entities in whatever the user selected, the fix is ignored, and the calc is executed for the the entire entity dimension.

This is my fix.. the
FIX((@ATTRIBUTE("Blue") AND @RELATIVE({Entity}, 0)))

So if the user selects "Texas" for the Entity, and there are no zero level members under Texas with the Attribute "Blue", the calc runs for the entire database.

What to do?
Quote
Like
Share

Anonymous
Anonymous

October 26th, 2012, 3:46 am #2

Quote
Share

Joined: November 26th, 2001, 10:15 pm

October 26th, 2012, 3:58 pm #3

Right below the comment block:

SET AGGMISSG ON ;
SET UPDATECALC OFF ;
SET EMPTYMEMBERSETS ON ;

That last one doesn't turn pretty colors in EAS, but it validates. It works in HBR and Calc Mgr as well.

Regards,

Cameron Lackpour
Quote
Like
Share

Joined: November 15th, 2007, 1:21 pm

October 26th, 2012, 4:02 pm #4

I've never seen that one. Glad I asked! Thanks!
Quote
Like
Share

Joined: April 13th, 2011, 5:00 pm

October 26th, 2012, 10:18 pm #5

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.

Quote
Like
Share

Ben White
Ben White

October 26th, 2012, 10:52 pm #6

And another special case! When EMPTYMEMBERSETS first came out (9.2?), it didn't work for nested sets like Javier's example. So I got in the habit of always including a dummy member from the same dimension alongside, as a failsafe (as long as it didn't affect the calculation).
Quote
Share

Joined: November 26th, 2001, 10:15 pm

October 29th, 2012, 3:35 pm #7

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.
I had to go prove it out, and then once I had that, I wrote it up:
http://camerons-blog-for-essbase-hacker ... 4-set.html

Javier (I hope I got your last name right in the blog post, tell me if I don't) -- you are 100% correct about the difference in nesting FIX statements versus not nesting them when using SET EMPTYMEMBERSET ON and a empty member set condition is FIXed on. Basically, when a dimension resolves to an empty member set and it's on a combined FIX line, SET EMPTYMEMBERSET ON doesn't work. It sure would be nice if that was documented.

I never, ever, ever thought that there was a difference. Ever.

I am pretty darn glad I read each and every post on this board -- I learn new stuff all the time.

Regards,

Cameron Lackpour
Quote
Like
Share

Joined: November 26th, 2001, 10:15 pm

October 29th, 2012, 3:52 pm #8

If ONE of the dimensions in the combined FIX does NOT resolve to an empty member set, then SET EMPTYMEMBERSETS ON does not work.

If BOTH dimensions in the combined FIX DOES resolve to empty member sets, then SET EMPTYMEMBERSETS ON does work.

And of course if you just nest everything, SET EMPTYMEMBERSETS ON works as expected.

Whew.

Regards,

Cameron Lackpour
Quote
Like
Share

Joined: November 15th, 2007, 1:21 pm

October 31st, 2012, 4:17 pm #9

So, thanks to the both of you, I now know about this calc setting, and it's idiosyncrasy regarding breaking out dimensions, but that didn't stop me from writing a bad fix just now. I'm starting to wonder if I *should* get in a habit of nesting all dimensions in different fixes.

IMO, the emptymembersets command isn't 100% intuitve, and should be better documented.

Ben's comment threw me a little, I don't think the statement that he made is valid. If you include a dummy member, it will always defeat the purpose of emptymembersets.
Quote
Like
Share

TimG
TimG

October 31st, 2012, 4:23 pm #10

Tangential but I noticed that Oracle were soliciting customers to join the 'EPM Doc Customer Partner Program' by making contact at EPMDOC_WW@oracle.com. Anyone ever interacted with them and seen requested changes make it into the docs?

Quote
Share


Confirmation of reply: