This discussion is archived
9 Replies Latest reply: Feb 11, 2013 1:30 PM by Rowdy RSS

Essbase Calc Calculation Script taking longer time..

dhooan Newbie
Currently Being Moderated
Hello All,
I have following calculation script and it taking so much time to run, I am trying to calculate Scenario Member for Current year. Scenario is Sparse dimension member. On Fix Statement FY Year (CurYR) also a sparse dimention member. I have only 2 dense dim Account and Period. I am using Essbase V 11.1.1.3. Is there anything I am missing?
I appreciate your help in advance.

//ESS_LOCALE English_UnitedStates.US-ASCII@Binary
/* 1) Use substitution variable &CurYr to determine the current year */
*/
/***************************************************************************************************************************/

SET MSG SUMMARY;
SET NOTICE DEFAULT;
SET UPDATECALC OFF;

SET LOCKBLOCK HIGH;
SET CACHE HIGH;

SET AGGMISSG OFF;

/***************************************************************************************************************************/
/* If possible, should fix on sparse dimensions for speed */
/*Use Data Copy function when possible in order to force Essbase to create new blocks */
/***************************************************************************************************************************/

/* Clear all data out of Feb Est */

FIX("Feb Est", &CurYr)

CLEARBLOCK ALL;

ENDFIX

/* */

FIX (&CurYr)

"Feb Est"="FAB" + "TSA" + "Standalone" + "Day1" + "AcqInt";

ENDFIX

Thank you,
T.Khan

Edited by: dhooan on Feb 10, 2013 11:34 PM
  • 1. Re: Essbase Calc Calculation Script taking longer time..
    982668 Newbie
    Currently Being Moderated
    Hello,


    Try using CLEARDATA, this will save time on deleting blocks and recreating them on calculations.. if rest remains constant.

    Best
    Abhishek
  • 2. Re: Essbase Calc Calculation Script taking longer time..
    CL Oracle ACE Director
    Currently Being Moderated
    Assuming that you have more than two sparse dimensions, you are calculating this for all members in all sparse dimensions other than Year. Do you really want to do that?

    Why don't you FIX on level zero of all sparse members (and level zero of Account as well assuming you have made it dynamic for upper level rollups) and then do the calc? When finished, roll up the database. You will find Essbase generally much better at agging than assigning a value at each and every block.

    Some of your code comments, btw, don't make sense (or at least suggest that someone thought of the above before) and maybe you are doing some or all of the above.

    Regards,

    Cameron Lackpour
  • 3. Re: Essbase Calc Calculation Script taking longer time..
    dhooan Newbie
    Currently Being Moderated
    On my current script all are sparse dimension members, I have my fiscal period and account dimension members are dense dimension members. I am using this script first time. My formula include all sparse dim members. I think calc script is not creation blocks? Is there anything I need to add on script of just add @relative (accounts,0) on fix statement with &CurYr.

    Thanks
    T.khan
  • 4. Re: Essbase Calc Calculation Script taking longer time..
    CL Oracle ACE Director
    Currently Being Moderated
    Could you provide the dimensionality of your outline? I am getting confused.

    Thanks,

    Cameron Lackpour
  • 5. Re: Essbase Calc Calculation Script taking longer time..
    dhooan Newbie
    Currently Being Moderated
    I have following Dimensions:
    Account --- Dense
    FY Periods ---- Dense
    Entity---- Sparse
    Cost Center---- Sparse
    Channel---- Sparse
    Scenario-----Sparse
    FY Year-------Sparse...

    All members in formula is belongs to Scenario Dimension Members....

    We have level 0 data....

    Do I need to add any Command, I guess block is not creating because of sparse dimension members?

    Thanks

    Edited by: dhooan on Feb 11, 2013 12:57 PM
  • 6. Re: Essbase Calc Calculation Script taking longer time..
    CL Oracle ACE Director
    Currently Being Moderated
    You need to FIX on all of the level zero members in Entity, Cost Center, and Channel. Something like this:

    FIX(@LEVMBRS("Entity", 0), @LEVMBRS("Cost Center", 0), @LEVMBRS("Channel", 0), &CurYr)

    "Feb Est"="FAB" + "TSA" + "Standalone" + "Day1" + "AcqInt";
    ENDFIX

    That will make everything happen at the lowest level then you can FIX on Feb Est and &CurYr and AGG Entity, Cost Center, and Channel. At least that would be what I would do. As for block creation, that is only an issue if you can't get any values into Feb Est. You stated (and the title of this thread) is that you have slow performance and I think that's because you are not constraining the scope of the member formula.

    Regards,

    Cameron Lackpour
  • 7. Re: Essbase Calc Calculation Script taking longer time..
    Rowdy Journeyer
    Currently Being Moderated
    There is no point of using CLEABLOCK command with a fix on dense member as it will ignore clearing the blocks and just clear the data, using CLEARDATA might improve some time.

    I am also not sure about why you can't use the scenario member on the left side of your equation and use block creation set command; and move month to fix. This will address the block creation issues as well.

    If block creation command taking extra time, it is faster to copy the data to all possible intersections and then run your formula.


    -Ram Reddy
  • 8. Re: Essbase Calc Calculation Script taking longer time..
    dhooan Newbie
    Currently Being Moderated
    Ram,
    I agree with you on Cleardata.
    Feb Est is scenario member and all the members after = also scenario dimension members.
    Can you please help me on writing the Calc Script, How this should be written?

    Appreciate your help,

    Regards,
    T.Khan
  • 9. Re: Essbase Calc Calculation Script taking longer time..
    Rowdy Journeyer
    Currently Being Moderated
    I see, I was having an impression that Feb Est. is a period and thus dense member. Anyways as CL suggested it usually is faster to calc at lvl0 and agg to parent levels. I am just trying to put something together to begin with..

    FIX(&CurrYear)

    CLEARDATA "Feb Est";

    ENDFIX;

    FIX(@RELATIVE("Entity",0),@RELATIVE("Cost Center",0),@RELATIVE("Channel",0),&CurrYear)

    SET CREATEBLOCKONEQ ON; /* If this is taking more time; you can use copydata from some other scenario, but make sure that you are copying data to all valid intersections that the equation would create data values */
    "Feb Est"=equation;
    SET CREATEBLOCKONEQ OFF;

    ENDFIX;

    FIX("Feb Est",&CurrYear)

    CALC DIM(Accounts,Periods);
    AGG(Entity,"Cost Center","Channel") /* Assuming no member formula on these 3 sparse dimension members */

    ENDFIX;


    -Ram Reddy

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points