13 Replies Latest reply on Jun 8, 2018 8:38 AM by Teresa Modesta

IF .. apply to numerous accounts

Teresa Modesta Jun 7, 2018 2:05 PM

Hi,

fix(FY18, Jan:Oct, Working, Budget, @relative("Cost Centre",0), @relative(Entity,0), AccountA, AccountB, AccountC, AccountD, AccountE, AccountF, AccountG, AccountH, AccountN, AccountV, AccountQ, AccountL)

&currbudget (

IF ("open month"->"BegBalance" == 11)

&currbudget = #missing;

ENDIF;

endfix

I tried the above, where &currbudget is a sparse dimension and AccountA, B, etc are dense.

The syntax is brief, but the result is very slow running, never break on the wisdom of IF on dense - yes!

Is there no way in a business rule to apply the same IF statement to a large block of accounts to replicate the result that I am trying to get ahead, with multiplying the IFs by 12 - in practise it is more than twelve...

Anybody going to make my day a better one?

thank you!!!!

Teresa

1. Re: IF .. apply to numerous accounts

Robert Angel Jun 7, 2018 3:18 PM (in response to Teresa Modesta)

One idea is to use your 'expensive' conditional logic to make some unused out of the way combination to 1, 0 otherwise where you want the values on the parallel accounts.

Then use the value to multiple the values by to their destination.
1 person found this helpful
Like Show 0 Likes(0)

Actions
2. Re: IF .. apply to numerous accounts

user9928941 Jun 7, 2018 3:36 PM (in response to Teresa Modesta)

Hi,
Did you try using CALCPARALLEL or FIXPARALLEL?

That should help.
Thanks,
Pawan.
Like Show 0 Likes(0)

Actions
3. Re: IF .. apply to numerous accounts

Teresa Modesta Jun 7, 2018 3:58 PM (in response to user9928941)

Thanks for the thought sweetie, already got those settings working for me and it still runs like a pig :-(
Like Show 0 Likes(0)

Actions
4. Re: IF .. apply to numerous accounts

Julien Mallet Jun 7, 2018 4:49 PM (in response to Teresa Modesta)

Hi

If you look at the log, does it run in BLOCK and BOTTOMUP mode?

If not, have you tried to force the calculation to use BLOCK and BOTTOMUP mode by adding

FIX(...)
&currbudget (
@CALCMODE(BLOCK);
@CALCMODE(BOTTOMUP);
IF ("open month"->"BegBalance" == 11)
...

Cheers
JM
1 person found this helpful
Like Show 0 Likes(0)

Actions
5. Re: IF .. apply to numerous accounts

adnan645 Jun 7, 2018 10:21 PM (in response to Teresa Modesta)

if you are sure the target blocks are already created, then put Account dimension in place of &CurrBudget.. something like...

&currbudget (
IF ("open month"->"BegBalance" == 11)
            @Descendnats(Account) = #missing;
ENDIF;

OR

&currbudget (
IF ("open month"->"BegBalance" == 11)
            jan = #missing;
            feb = #missing;
              ....
              ....
          Oct = #missing
ENDIF

It's taking more time because you are using #missing. it works on all the possible combination. You can also try to pick this value (#missing) from any blank cell. I believe in that case it will only work on the combinations on which you need to erase value and it could be faster.
Like Show 0 Likes(0)

Actions
6. Re: IF .. apply to numerous accounts

Teresa Modesta Jun 8, 2018 6:42 AM (in response to adnan645)

Thanks, I think your first suggestion might work for me, I will give that a try.

The second, no, because in my particular case I need to fix on a subset of periods driven by a parameter, I had tried to do this originally, but of course it just errors...
Like Show 0 Likes(0)

Actions
7. Re: IF .. apply to numerous accounts

Teresa Modesta Jun 8, 2018 6:43 AM (in response to Julien Mallet)

Thanks I will check this out.
Like Show 0 Likes(0)

Actions
8. Re: IF .. apply to numerous accounts

Teresa Modesta Jun 8, 2018 7:27 AM (in response to Julien Mallet)

Hi Julien,

You were correct, it was running in topdown, even though my fix was using @relative(.....,0) for all dimensions, aside from &currbudget which is a bottom level member....

I tried your fix on all of my IFs - there are a lot - and on a script that took over 11 minutes to run it became a script that took 4 seconds to run - albeit not on a representative sample of data - but even so - Wow!!!!!!! in fact Wow! raised to the power of Wow! - thank you so much! I could seriously kiss you, even if you do look like a coffee cup ;-)

Before your piece of code becomes my new favorite trick and I put it anywhere, would you mind explaining when I am likely to need it and when it is wrong to use it??
Like Show 0 Likes(0)

Actions
9. Re: IF .. apply to numerous accounts

JohnGoodwin Jun 8, 2018 7:50 AM (in response to Teresa Modesta)

There is a good explanation of bottom up and top down calc modes at @CALCMODE

Cheers

John
1 person found this helpful
Like Show 0 Likes(0)

Actions
10. Re: IF .. apply to numerous accounts

Teresa Modesta Jun 8, 2018 8:06 AM (in response to JohnGoodwin)

Very kind thank you, very useful and comprehensive reference.

One final question of clarification arising from this if I might be so bold?

In the document it says: - "In a bottom-up calculation, Essbase executes formulas only from existing data blocks."

Can I confirm that if I have two dense dimensions period and account - standard PBCS - and in my scenario I have BegBalance and each of the accounts used populated, then the block will exist by definition, even though I am using the business rule to populate Jan:Dec or subsets thereof?

Or to paraphrase the above, when does a block exist?

Teresa.
Like Show 0 Likes(0)

Actions
11. Re: IF .. apply to numerous accounts

JohnGoodwin Jun 8, 2018 8:29 AM (in response to Teresa Modesta)

If you have data then you will have blocks, it just depends on the combination, blocks will exist from any sparse combination that you have data against.
1 person found this helpful
Like Show 0 Likes(0)

Actions
12. Re: IF .. apply to numerous accounts

Teresa Modesta Jun 8, 2018 8:34 AM (in response to JohnGoodwin)

Thank you, that really makes it crystal clear for me.
Like Show 0 Likes(0)

Actions
13. Re: IF .. apply to numerous accounts

Teresa Modesta Jun 8, 2018 8:38 AM (in response to Julien Mallet)

Thank you Julien, your input made my business rule run a huge amount faster, I managed to trim some more time out by using some tricks around order of the IF and using emptysets to not check logic on months not I am not processing as the year goes on, but your input alone made a 99.4% improvement so sending the correct points to you.

Thank you.
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.