This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
  << Previous Topic | Next Topic >>Return to Index  

Fixing on an empty set, Fix ignored

October 25 2012 at 9:47 PM
No score for this post

Jeff McAhren 
from IP address 72.64.81.64

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?

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AuthorReply
Anonymous

174.255.81.44

Re: Fixing on an empty set, Fix ignored

No score for this post
October 25 2012, 11:46 PM 

Have you read this post? http://www.network54.com/Forum/58296/message/1286643797/SET+EMPTYMEMBERSETS+ON

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

173.161.250.177

Three lines at the top of every calc script

No score for this post
October 26 2012, 11:58 AM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   

Jeff McAhren

63.108.38.204

Wow

No score for this post
October 26 2012, 12:02 PM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   

Javier

96.48.4.220

Nested

No score for this post
October 26 2012, 6:18 PM 

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.


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Ben White

108.75.118.61

Re: Nested

No score for this post
October 26 2012, 6:52 PM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

98.111.148.148

Wow, I didn't know that

No score for this post
October 29 2012, 11:35 AM 

I had to go prove it out, and then once I had that, I wrote it up:
http://camerons-blog-for-essbase-hackers.blogspot.com/2012/10/stupid-programming-tricks-14-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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

98.111.148.148

Arrgh, that needs clarification

No score for this post
October 29 2012, 11:52 AM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   

Jeff McAhren

63.108.38.204

Knowing this..

No score for this post
October 31 2012, 12:17 PM 

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.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
TimG

108.86.221.231

Re: Knowing this..

No score for this post
October 31 2012, 12:23 PM 

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?


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

173.161.250.177

I have interacted with them

No score for this post
October 31 2012, 1:25 PM 

I can't say if I have seen any changes to the docs, however. I *think* so, but I send a lot of emails to a lot of people and my memory is pretty bad.

In any case, that's exactly who I sent the note about SET EMPTYMEMBERSET to. I'd encourage everyone who has an interest in making Oracle EPM documentation better to contact them. Oracle are more than willing to listen to feedback.

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
John A. Booth

205.144.73.92

Ben's idea

No score for this post
October 31 2012, 1:00 PM 

What Ben is saying is if you had @UDA function used against say Accounts or other function which if it had a bogus value say "USDA" (rather than "USD"). Put a "No_Account" in along with the @UDA so that if the @UDA resulted in a null member set the No_Account would prevent the whole accounts dimension from being in scope.

Personally I think you just need to be really really careful and if you have a function that could possibly result in a null member set put it in a lone FIX and ensure the setting.

Regards,

John A. Booth
http://www.metavero.com

 
Scoring disabled. You must be logged in to score posts.Respond to this message   

Javier

199.198.251.108

Re: Knowing this..

No score for this post
October 31 2012, 1:40 PM 

You should ALWAYS nest your FIX statements. It makes your code so much prettier.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
 
  << Previous Topic | Next Topic >>Return to Index  

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.