Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Allocation (Cal Script) questions

3744957Aug 26 2019 — edited Sep 3 2019

I am trying to write script for allocation in PBCS

Dimension:
-Account, dense
-Person, sparse
-Project, sparse

Allocate from:
Account001 -> No Person -> No Project = 100;
To:
Account001 -> Person A -> Project I = 20;
Account001 -> Person B -> Project II = 80;
based on driver:
Driver01 -> Person A -> Project I = 2;
Driver01 -> Person B -> Project II = 8;

Are there any better ways than code below? It runs very slow (more than 10 mins). Are there any ways to make the calculation quicker. Any advice are greatly appreciated. 

FIX ("FY19",/*DIM:Version*/"Working",/*DIM:Customer*/"No Customer",/*DIM:Period*/"Jun",/*DIM:HSP_View*/"BaseData",/*DIM:Scenario*/"Actual")

  FIX (  /*DIM:Person*/@RELATIVE("Total Person",0))

    FIX (    /*DIM:Project*/@RELATIVE("Total Project", 0))

        Fix (/*DIM:Entity*/@RELATIVE("T", 0))

      SET CREATENONMISSINGBLK ON;

          "598099" = {Source_account}->"P000"->"No Project" * 100 / 100 * "Man-hour" / "Man-hour"->"Total Person"->"Total Project";

        "598099"(

            @xwrite("598099",@loopback,@concatenate("A",@name({Source_Account})));

              );

      ENDFIX

    ENDFIX

  ENDFIX

  FIX (  /*DIM:Project*/"No Project",  /*DIM:Person*/"P000")

    /*STARTCOMPONENT:FORMULA*/

    {Eliminated_Account} = ({Source_Account} * (100 / 100)) * -1;

    /*ENDCOMPONENT*/

  ENDFIX

ENDFIX

Thank you!

Comments

gSlash

Just run individual FIX to check which FIX is taking more time then we can check on which FIX we need to work, and using SET CREATENONMISSINGBLK ON; might take more time. You can open a block using @CREATEBLOCK or something like:

{Source_account}

(

and then the formula

Also "Man-hour"->"Total Person"->"Total Project"; Here Total Person and Total Project are top level members ? if yes you can AGG them and then use there values.

1 person found this helpful
3744957

Thank you very much for you response.

I will run individual fix to check

Just run individual FIX to check which FIX is taking more time then we can check on which FIX we need to work,

I tried remove SET CREATENONMISSINGBLK ON. it took around the same time after removed.

and using SET CREATENONMISSINGBLK ON; might take more time. You can open a block using @CREATEBLOCK or something like:

{Source_account}

(

and then the formula

Total Person and Total Project are top lvl members. I will try that also.

Also "Man-hour"->"Total Person"->"Total Project"; Here Total Person and Total Project are top level members ? if yes you can AGG them and then use there values.

Thanks again.


Wan

3744957

After setting some set commands, I managed to make it faster.

like:

SET CREATENONMISSINGBLK OFF

SET CREATEBLOCKONEQ ON

SET EMPTYMEMBERSETS ON;

Thank you!
1 - 3

Post Details

Added on Aug 26 2019
#business-intelligence-applications, #pbcs, #performance-management-applications, #planning, #planning-and-budgeting
3 comments
167 views