9 Replies Latest reply: Feb 11, 2013 3:30 PM by Rowdy RSS

    Essbase Calc Calculation Script taking longer time..

    dhooan
      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..
          Abhishek Garg
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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