This discussion is archived
1 Reply Latest reply: Dec 29, 2012 3:04 PM by user9513294 RSS

Optimizing a BSO calc script

user9513294 Newbie
Currently Being Moderated
Hey folks. I'm looking for some ideas for optimising a BSO calc script. It's currently running for about 6 hours. The good news is that it does work, does the calculations that I'm expecting. It's an allocation script where I am FIXING on all of the sparse dimensions in the cube, and then taking a sum on about 100 members from the Subaccount dimension and assigning that sum value to a Stat Account member (ST:SE7), then taking the value of all of that summarized Stat Account and assigning it and multiplying it by an allocation rate to Account member "A:~NA~" at the end.

All of this is working as expected but the calc is taking way too long.


I'd thought about possibly using the @SUMRANGE but the subaccounts aren't really in a range, it's a discrete subset of memebrs that are spread all over the outline. I'm not allowed to create either a UDA/Attribute tag to identify them or create an alternate hierarchy. I have to hard-code the list of ~100 subaccounts used inside the FIX.


Thanks in advance!!




calc script:


//ESS_LOCALE English_UnitedStates.Latin1@Binary
/*
SET MSG SUMMARY;
SET LOCKBLOCK HIGH;
SET CACHE ALL;
SET FRMLRTDYNAMIC OFF;
SET REMOTECALC OFF;
SET UPDATECALC OFF;
SET AGGMISSG OFF;


FIX ("U:~NA~","PR:~NA~",&BudgetYear:&BudgetYearPlus4,"Budget","Input","AF:~NA~","D:80006_20001",@RELATIVE("Department_Node1",0),"S:535200",@RELATIVE("ALL_XXX_PROJECTS",0))

SET CREATENONMISSINGBLK ON;

"ST:SE8" = "S:513114"->"BALANCE_SHEET"->"PR:ER1"+"S:510020"->"BALANCE_SHEET"->"PR:ER1"+
"S:511000"->"BALANCE_SHEET"->"PR:ER1"+"S:512010"->"BALANCE_SHEET"->"PR:ER1"+
"S:512020"->"BALANCE_SHEET"->"PR:ER1"+"S:512050"->"BALANCE_SHEET"->"PR:ER1"+
"S:512070"->"BALANCE_SHEET"->"PR:ER1"+"S:513011"->"BALANCE_SHEET"->"PR:ER1"+
"S:513012"->"BALANCE_SHEET"->"PR:ER1"+"S:513111"->"BALANCE_SHEET"->"PR:ER1"+
"S:513112"->"BALANCE_SHEET"->"PR:ER1"+"S:513113"->"BALANCE_SHEET"->"PR:ER1"+
"S:514010"->"BALANCE_SHEET"->"PR:ER1"+"S:514020"->"BALANCE_SHEET"->"PR:ER1"+
"S:514021"->"BALANCE_SHEET"->"PR:ER1"+"S:514030"->"BALANCE_SHEET"->"PR:ER1"+
"S:514035"->"BALANCE_SHEET"->"PR:ER1"+"S:514040"->"BALANCE_SHEET"->"PR:ER1"+
"S:514050"->"BALANCE_SHEET"->"PR:ER1"+"S:514060"->"BALANCE_SHEET"->"PR:ER1"+
"S:514061"->"BALANCE_SHEET"->"PR:ER1"+"S:514062"->"BALANCE_SHEET"->"PR:ER1"+
"S:514070"->"BALANCE_SHEET"->"PR:ER1"+"S:514071"->"BALANCE_SHEET"->"PR:ER1"+
"S:514072"->"BALANCE_SHEET"->"PR:ER1"+"S:514073"->"BALANCE_SHEET"->"PR:ER1"+
"S:514080"->"BALANCE_SHEET"->"PR:ER1"+"S:514081"->"BALANCE_SHEET"->"PR:ER1"+
"S:514082"->"BALANCE_SHEET"->"PR:ER1"+"S:514083"->"BALANCE_SHEET"->"PR:ER1"+
"S:514084"->"BALANCE_SHEET"->"PR:ER1"+"S:514085"->"BALANCE_SHEET"->"PR:ER1"+
"S:514086"->"BALANCE_SHEET"->"PR:ER1"+"S:514087"->"BALANCE_SHEET"->"PR:ER1"+
"S:514088"->"BALANCE_SHEET"->"PR:ER1"+"S:514089"->"BALANCE_SHEET"->"PR:ER1"+
"S:514090"->"BALANCE_SHEET"->"PR:ER1"+"S:514091"->"BALANCE_SHEET"->"PR:ER1"+
"S:514092"->"BALANCE_SHEET"->"PR:ER1"+"S:514093"->"BALANCE_SHEET"->"PR:ER1"+
"S:514094"->"BALANCE_SHEET"->"PR:ER1"+"S:515010"->"BALANCE_SHEET"->"PR:ER1"+
"S:515020"->"BALANCE_SHEET"->"PR:ER1"+"S:515030"->"BALANCE_SHEET"->"PR:ER1"+
"S:515040"->"BALANCE_SHEET"->"PR:ER1"+"S:515050"->"BALANCE_SHEET"->"PR:ER1"+
"S:515060"->"BALANCE_SHEET"->"PR:ER1"+"S:515070"->"BALANCE_SHEET"->"PR:ER1"+
"S:515080"->"BALANCE_SHEET"->"PR:ER1"+"S:516010"->"BALANCE_SHEET"->"PR:ER1"+"S:516020"->"BALANCE_SHEET"->"PR:ER1"+"S:516210"->"BALANCE_SHEET"->"PR:ER1"+"S:516211"->"BALANCE_SHEET"->"PR:ER1"+"S:516212"->"BALANCE_SHEET"->"PR:ER1"+"S:516215"->"BALANCE_SHEET"->"PR:ER1"+"S:516216"->"BALANCE_SHEET"->"PR:ER1"+"S:516217"->"BALANCE_SHEET"->"PR:ER1"+"S:516218"->"BALANCE_SHEET"->"PR:ER1"+"S:516219"->"BALANCE_SHEET"->"PR:ER1"+"S:516220"->"BALANCE_SHEET"->"PR:ER1"+"S:516221"->"BALANCE_SHEET"->"PR:ER1"+"S:516222"->"BALANCE_SHEET"->"PR:ER1"+"S:516225"->"BALANCE_SHEET"->"PR:ER1"+"S:516226"->"BALANCE_SHEET"->"PR:ER1"+"S:516227"->"BALANCE_SHEET"->"PR:ER1"+
"S:516228"->"BALANCE_SHEET"->"PR:ER1"+"S:516230"->"BALANCE_SHEET"->"PR:ER1"+"S:516231"->"BALANCE_SHEET"->"PR:ER1"+"S:517010"->"BALANCE_SHEET"->"PR:ER1"+"S:517020"->"BALANCE_SHEET"->"PR:ER1"+"S:517030"->"BALANCE_SHEET"->"PR:ER1"+"S:517040"->"BALANCE_SHEET"->"PR:ER1"+"S:517050"->"BALANCE_SHEET"->"PR:ER1"+"S:517100"->"BALANCE_SHEET"->"PR:ER1"+"S:518020"->"BALANCE_SHEET"->"PR:ER1"+"S:518030"->"BALANCE_SHEET"->"PR:ER1"+"S:518040"->"BALANCE_SHEET"->"PR:ER1"+"S:519000"->"BALANCE_SHEET"->"PR:ER1"+"S:520010"->"BALANCE_SHEET"->"PR:ER1"+"S:520020"->"BALANCE_SHEET"->"PR:ER1"+"S:520030"->"BALANCE_SHEET"->"PR:ER1"+"S:520040"->"BALANCE_SHEET"->"PR:ER1"+"S:520050"->"BALANCE_SHEET"->"PR:ER1"+"S:520060"->"BALANCE_SHEET"->"PR:ER1"+"S:520100"->"BALANCE_SHEET"->"PR:ER1"+"S:520200"->"BALANCE_SHEET"->"PR:ER1"+"S:521000"->"BALANCE_SHEET"->"PR:ER1"+"S:521010"->"BALANCE_SHEET"->"PR:ER1"+"S:521100"->"BALANCE_SHEET"->"PR:ER1"+"S:521200"->"BALANCE_SHEET"->"PR:ER1"+"S:521300"->"BALANCE_SHEET"->"PR:ER1"+"S:522000"->"BALANCE_SHEET"->"PR:ER1"+"S:524000"->"BALANCE_SHEET"->"PR:ER1"+"S:525010"->"BALANCE_SHEET"->"PR:ER1"+"S:526010"->"BALANCE_SHEET"->"PR:ER1"+"S:526020"->"BALANCE_SHEET"->"PR:ER1"+"S:526030"->"BALANCE_SHEET"->"PR:ER1"+ "S:527000"->"BALANCE_SHEET"->"PR:ER1"+"S:527010"->"BALANCE_SHEET"->"PR:ER1"+"S:528000"->"BALANCE_SHEET"->"PR:ER1"+"S:530000"->"BALANCE_SHEET"->"PR:ER1"+"S:531010"->"BALANCE_SHEET"->"PR:ER1"+"S:531020"->"BALANCE_SHEET"->"PR:ER1"+"S:531030"->"BALANCE_SHEET"->"PR:ER1"+"S:531040"->"BALANCE_SHEET"->"PR:ER1"+"S:532010"->"BALANCE_SHEET"->"PR:ER1"+
"S:532020"->"BALANCE_SHEET"->"PR:ER1"+"S:535010"->"BALANCE_SHEET"->"PR:ER1"+"S:535030"->"BALANCE_SHEET"->"PR:ER1"+"S:535040"->"BALANCE_SHEET"->"PR:ER1"+"S:535050"->"BALANCE_SHEET"->"PR:ER1"+"S:535060"->"BALANCE_SHEET"->"PR:ER1"+"S:535110"->"BALANCE_SHEET"->"PR:ER1"+"S:535130"->"BALANCE_SHEET"->"PR:ER1"+"S:535135"->"BALANCE_SHEET"->"PR:ER1"+"S:535137"->"BALANCE_SHEET"->"PR:ER1"+"S:535140"->"BALANCE_SHEET"->"PR:ER1"+"S:535160"->"BALANCE_SHEET"->"PR:ER1"+"S:535199"->"BALANCE_SHEET"->"PR:ER1" +

"S:513114"->"NET_INCOME"->"PR:ER1"+"S:510020"->"NET_INCOME"->"PR:ER1"+
"S:511000"->"NET_INCOME"->"PR:ER1"+"S:512010"->"NET_INCOME"->"PR:ER1"+
"S:512020"->"NET_INCOME"->"PR:ER1"+"S:512050"->"NET_INCOME"->"PR:ER1"+
"S:512070"->"NET_INCOME"->"PR:ER1"+"S:513011"->"NET_INCOME"->"PR:ER1"+
"S:513012"->"NET_INCOME"->"PR:ER1"+"S:513111"->"NET_INCOME"->"PR:ER1"+
"S:513112"->"NET_INCOME"->"PR:ER1"+"S:513113"->"NET_INCOME"->"PR:ER1"+
"S:514010"->"NET_INCOME"->"PR:ER1"+"S:514020"->"NET_INCOME"->"PR:ER1"+
"S:514021"->"NET_INCOME"->"PR:ER1"+"S:514030"->"NET_INCOME"->"PR:ER1"+
"S:514035"->"NET_INCOME"->"PR:ER1"+"S:514040"->"NET_INCOME"->"PR:ER1"+
"S:514050"->"NET_INCOME"->"PR:ER1"+"S:514060"->"NET_INCOME"->"PR:ER1"+
"S:514061"->"NET_INCOME"->"PR:ER1"+"S:514062"->"NET_INCOME"->"PR:ER1"+
"S:514070"->"NET_INCOME"->"PR:ER1"+"S:514071"->"NET_INCOME"->"PR:ER1"+
"S:514072"->"NET_INCOME"->"PR:ER1"+"S:514073"->"NET_INCOME"->"PR:ER1"+
"S:514080"->"NET_INCOME"->"PR:ER1"+"S:514081"->"NET_INCOME"->"PR:ER1"+
"S:514082"->"NET_INCOME"->"PR:ER1"+"S:514083"->"NET_INCOME"->"PR:ER1"+
"S:514084"->"NET_INCOME"->"PR:ER1"+"S:514085"->"NET_INCOME"->"PR:ER1"+
"S:514086"->"NET_INCOME"->"PR:ER1"+"S:514087"->"NET_INCOME"->"PR:ER1"+
"S:514088"->"NET_INCOME"->"PR:ER1"+"S:514089"->"NET_INCOME"->"PR:ER1"+
"S:514090"->"NET_INCOME"->"PR:ER1"+"S:514091"->"NET_INCOME"->"PR:ER1"+
"S:514092"->"NET_INCOME"->"PR:ER1"+"S:514093"->"NET_INCOME"->"PR:ER1"+
"S:514094"->"NET_INCOME"->"PR:ER1"+"S:515010"->"NET_INCOME"->"PR:ER1"+
"S:515020"->"NET_INCOME"->"PR:ER1"+"S:515030"->"NET_INCOME"->"PR:ER1"+
"S:515040"->"NET_INCOME"->"PR:ER1"+"S:515050"->"NET_INCOME"->"PR:ER1"+
"S:515060"->"NET_INCOME"->"PR:ER1"+"S:515070"->"NET_INCOME"->"PR:ER1"+
"S:515080"->"NET_INCOME"->"PR:ER1"+"S:516010"->"NET_INCOME"->"PR:ER1"+"S:516020"->"NET_INCOME"->"PR:ER1"+"S:516210"->"NET_INCOME"->"PR:ER1"+"S:516211"->"NET_INCOME"->"PR:ER1"+"S:516212"->"NET_INCOME"->"PR:ER1"+"S:516215"->"NET_INCOME"->"PR:ER1"+"S:516216"->"NET_INCOME"->"PR:ER1"+"S:516217"->"NET_INCOME"->"PR:ER1"+"S:516218"->"NET_INCOME"->"PR:ER1"+"S:516219"->"NET_INCOME"->"PR:ER1"+"S:516220"->"NET_INCOME"->"PR:ER1"+"S:516221"->"NET_INCOME"->"PR:ER1"+"S:516222"->"NET_INCOME"->"PR:ER1"+"S:516225"->"NET_INCOME"->"PR:ER1"+"S:516226"->"NET_INCOME"->"PR:ER1"+"S:516227"->"NET_INCOME"->"PR:ER1"+
"S:516228"->"NET_INCOME"->"PR:ER1"+"S:516230"->"NET_INCOME"->"PR:ER1"+"S:516231"->"NET_INCOME"->"PR:ER1"+"S:517010"->"NET_INCOME"->"PR:ER1"+"S:517020"->"NET_INCOME"->"PR:ER1"+"S:517030"->"NET_INCOME"->"PR:ER1"+"S:517040"->"NET_INCOME"->"PR:ER1"+"S:517050"->"NET_INCOME"->"PR:ER1"+"S:517100"->"NET_INCOME"->"PR:ER1"+"S:518020"->"NET_INCOME"->"PR:ER1"+"S:518030"->"NET_INCOME"->"PR:ER1"+"S:518040"->"NET_INCOME"->"PR:ER1"+"S:519000"->"NET_INCOME"->"PR:ER1"+"S:520010"->"NET_INCOME"->"PR:ER1"+"S:520020"->"NET_INCOME"->"PR:ER1"+"S:520030"->"NET_INCOME"->"PR:ER1"+"S:520040"->"NET_INCOME"->"PR:ER1"+"S:520050"->"NET_INCOME"->"PR:ER1"+"S:520060"->"NET_INCOME"->"PR:ER1"+"S:520100"->"NET_INCOME"->"PR:ER1"+"S:520200"->"NET_INCOME"->"PR:ER1"+"S:521000"->"NET_INCOME"->"PR:ER1"+"S:521010"->"NET_INCOME"->"PR:ER1"+"S:521100"->"NET_INCOME"->"PR:ER1"+"S:521200"->"NET_INCOME"->"PR:ER1"+"S:521300"->"NET_INCOME"->"PR:ER1"+"S:522000"->"NET_INCOME"->"PR:ER1"+"S:524000"->"NET_INCOME"->"PR:ER1"+"S:525010"->"NET_INCOME"->"PR:ER1"+"S:526010"->"NET_INCOME"->"PR:ER1"+"S:526020"->"NET_INCOME"->"PR:ER1"+"S:526030"->"NET_INCOME"->"PR:ER1"+ "S:527000"->"NET_INCOME"->"PR:ER1"+"S:527010"->"NET_INCOME"->"PR:ER1"+"S:528000"->"NET_INCOME"->"PR:ER1"+"S:530000"->"NET_INCOME"->"PR:ER1"+"S:531010"->"NET_INCOME"->"PR:ER1"+"S:531020"->"NET_INCOME"->"PR:ER1"+"S:531030"->"NET_INCOME"->"PR:ER1"+"S:531040"->"NET_INCOME"->"PR:ER1"+"S:532010"->"NET_INCOME"->"PR:ER1"+
"S:532020"->"NET_INCOME"->"PR:ER1"+"S:535010"->"NET_INCOME"->"PR:ER1"+"S:535030"->"NET_INCOME"->"PR:ER1"+"S:535040"->"NET_INCOME"->"PR:ER1"+"S:535050"->"NET_INCOME"->"PR:ER1"+"S:535060"->"NET_INCOME"->"PR:ER1"+"S:535110"->"NET_INCOME"->"PR:ER1"+"S:535130"->"NET_INCOME"->"PR:ER1"+"S:535135"->"NET_INCOME"->"PR:ER1"+"S:535137"->"NET_INCOME"->"PR:ER1"+"S:535140"->"NET_INCOME"->"PR:ER1"+"S:535160"->"NET_INCOME"->"PR:ER1"+"S:535199"->"NET_INCOME"->"PR:ER1";



"A:~NA~" = "ST:SE7" * ("ST:SE1"->"Budget"->"Product"->"Subaccount"->"P:~NA~"->"D:80203_20001" / 100) ;


ENDFIX

Legend

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