- 3,726,647 Users
- 2,245,235 Discussions
- 7,852,337 Comments
Increase in aggregation times
Due to functional requirements, our Product dimension has increased its number of members from 1700 to 3200, so almost double. Since then, aggregations are taking not just twice the original time, but more than 3 or 4 times.
Here I share an example:SET CACHE HIGH;
SET LOCKBLOCK HIGH;
SET CALCPARALLEL 8;
SET AGGMISSG ON;
SET CALCTASKDIMS 1;
SET FRMLBOTTOMUP ON;
SET UPDATECALC OFF;FIX(&Bdgt_Year,&Bdgt_Scen,"BegBalance","Jul":"Jun",@RELATIVE("Tipo",0),"Objetivo","Trabajo",@IDESCENDANTS("RT02"), IND_A1_C)
ENDFIXWe have reasons to use @IDESCENDANTS to perform this aggregation instead of the AGG function. Also, we have noted using the AGG function only provides a marginal improvement.
The Total_Productos hierarchy used to be this way:Total_Productos
...Now, the new functional requirements imply the following structure, where new members and a new level has been added:Total_Productos
...The increase of potential blocks is obvious, but the calculation times increase has been more than proportional.We have determined CALCTASKDIMS 1 is the optimum value for this parameter --higher values offer worse performance.The following image shows the dimension order and sparsity definition for this cube --modifying this order should be a last resort, as that would have impact in how some data extractions and data synchronizations between environments are defined:
Using the AGG function only provides a marginal improvement. Any idea on how we could improve aggregation times?
This is Oracle EPM Cloud PBCS, v20.08.67Thank you,