Hi All,
Can someone help me in reducing BR time. Currently it takes 6 mins based on run time prompt for Entity and Division.
I have 15k members in Backlog, 22k members in Customer, 600 members in People dim and 500 in Entity(Which has RTP). I have set up the cache to 0.125 of num page file and index cache to size of Index file.
SET AGGMISSG ON;
SET CACHE ALL;
SET UPDATECALC OFF;
SET CALCPARALLEL 4;
SET LOCKBLOCK HIGH;
SET CALCTASKDIMS 2;
/* ENDCOMPONENT */
/* STARTCOMPONENT:SCRIPT */
FIX ("Loc",&CurrOL, &CurrOLView,/* DIM:Division */{Division},/* DIM:Version */"Submit", /* DIM:Customer */@RELATIVE("Customer",0),@RELATIVE({MgmtStructure},0), /* DIM:People */@RELATIVE("People",0),/* DIM:Source */@REMOVE(@RELATIVE("Backlog",0),"Backlog_Input"))
"RevenueValue"(
IF (@ISMBR(&CurrYr) and @ISMBR(&CurrYrOLMths))
"01" = "RevenueValue"->"NoProductType"*"HWPct"->"NoProductType"->"BegBalance";
"02" = "RevenueValue"->"NoProductType"*"SWPct"->"NoProductType"->"BegBalance";
"04" = "RevenueValue"->"NoProductType"*"ConsultingPct"->"NoProductType"->"BegBalance";
"06" = "RevenueValue"->"NoProductType"*"CloudPct"->"NoProductType"->"BegBalance";
"54" = "RevenueValue"->"NoProductType"*"TSPct"->"NoProductType"->"BegBalance";
"01"->"ETP" = "RevenueValue"->"01"*(1-"HWGMPct"->"NoProductType"->"BegBalance");
"02"->"ETP" = "RevenueValue"->"02"*(1-"SWGMPct"->"NoProductType"->"BegBalance");
"04"->"OtherNaturalExpense" = "RevenueValue"->"04"*(1-"ConsGMPct"->"NoProductType");
"54"->"OtherSolutionCost" = "RevenueValue"->"54"*(1-"TransSvcsGMPct"->"NoProductType");
"06"->"OtherSolutionCost" = "RevenueValue"->"06"*(1-"CloudGMPct"->"NoProductType");
ELSEIF (@ISMBR(&NextYr) and @ISMBR(&NextYrOLMths))
"01" = "RevenueValue"->"NoProductType"*"HWPct"->"NoProductType"->"BegBalance"->&CurrYr;
"02" = "RevenueValue"->"NoProductType"*"SWPct"->"NoProductType"->"BegBalance"->&CurrYr;
"04" = "RevenueValue"->"NoProductType"*"ConsultingPct"->"NoProductType"->"BegBalance"->&CurrYr;
"06" = "RevenueValue"->"NoProductType"*"CloudPct"->"NoProductType"->"BegBalance"->&CurrYr;
"54" = "RevenueValue"->"NoProductType"*"TSPct"->"NoProductType"->"BegBalance"->&CurrYr;
"01"->"ETP" = "RevenueValue"->"01"*(1-"HWGMPct"->"NoProductType"->"BegBalance"->&CurrYr);
"02"->"ETP" = "RevenueValue"->"02"*(1-"SWGMPct"->"NoProductType"->"BegBalance"->&CurrYr);
"04"->"OtherNaturalExpense" = "RevenueValue"->"04"*(1-"ConsGMPct"->"NoProductType"->&NextYr);
"54"->"OtherSolutionCost" = "RevenueValue"->"54"*(1-"TransSvcsGMPct"->"NoProductType"->&NextYr);
"06"->"OtherSolutionCost" = "RevenueValue"->"06"*(1-"CloudGMPct"->"NoProductType"->&NextYr);
ENDIF
)
ENDFIX
These are the calc logs.
[Tue May 10 11:32:32 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/8032/Info(1013162)
Received Command [Calculate] from user [admin@Native Directory]
[Tue May 10 11:32:32 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/8032/Info(1200481)
Formula for member [RevenueValue] will be executed in [CELL] mode
[Tue May 10 11:32:32 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012675)
Commit Blocks Interval for the calculation is [3000]
[Tue May 10 11:32:32 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012714)
Regular member [RevenueValue] depends on dynamic-calc member [HWPct].
[Tue May 10 11:32:33 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012717)
Remote bitmap cache is [Disabled]
[Tue May 10 11:32:33 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012668)
Calculating [ Account(RevenueValue)] with fixed members [Division(FIN); People(nk185106, BD185044, AC185158, ML128164, TA133636, BP127743, CB185092, JC160014, MM180018, CK122854, KO185031, KB185018, MT185165, RH185134, FW185001, CG210035, TM185107, ]
[Tue May 10 11:32:33 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012672)
Calculator Information Message:
Maximum Number of Lock Blocks: [12944] Blocks
Completion Notice Messages: [Disabled]
Calculations On Updated Blocks Only: [Enabled]
Clear Update Status After Full Calculations: [Enabled]
Calculator Cache: [Disabled]
[Tue May 10 11:32:33 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012672)
Calculator Information Message:
Aggregate #Missing values: [Enabled]
[Tue May 10 11:32:33 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012678)
Calculating in parallel with [6] threads
[Tue May 10 11:32:33 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012679)
Calculation task schedule [1]
[Tue May 10 11:32:33 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012680)
Parallelizing using [2] task dimensions.
[Tue May 10 11:37:00 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012672)
Calculator Information Message:
Total Block Created: [1.2262e+005] Blocks
Sparse Calculations: [2.2475e+005] Writes and [4.5747e+005] Reads
Dense Calculations: [4.7897e+004] Writes and [4.7897e+004] Reads
Sparse Calculations: [0.0000e+000] Cells
Dense Calculations: [1.0058e+006] Cells
[Tue May 10 11:37:00 2016]Local/HYPNA/CUSTDTL/admin@Native Directory/16336/Info(1012579)
Total Calc Elapsed Time for [inline] : [267.87] seconds
Thanks
Pranav