|
Hi all,
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)
FIX(@LEVMBRS("Total_Mercado",0),@IDESCENDANTS("EC_100"),@IDESCENDANTS("NP_001"),@IDESCENDANTS("Total_Productos"))
@IDESCENDANTS("Total_Sociedad");
ENDFIX
FIX(@IDESCENDANTS("Total_Sociedad"),@IDESCENDANTS("EC_100"),@IDESCENDANTS("NP_001"),@IDESCENDANTS("Total_Productos"))
@IDESCENDANTS("Total_Mercado");
ENDFIX
ENDFIX
We 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
PROD1
SUBPROD11
SUBPROD12
PROD2
SUBPROD21
SUBPROD22
PROD3
etc.
...
Now, the new functional requirements imply the following structure, where new members and a new level has been added:
Total_Productos
PROD1
PROD1_dummy
SUBPROD11
SUBPROD11_dummy
SUBPROD12
SUBPROD12_dummy
PROD2
PROD2_dummy
SUBPROD21
SUBPROD21_dummy
SUBPROD22
SUBPROD22_dummy
PROD3
PROD3_dummy
etc.
...
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.67
Thank you,
G.S.Felíu
|
| |