Forum Stats

  • 3,735,188 Users
  • 2,247,137 Discussions
  • 7,857,769 Comments

Discussions

Increase in aggregation times

G.S.Feliu
G.S.Feliu Member Posts: 76 Bronze Badge

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:
dim_order.JPG

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
Tagged:

Answers

Sign In or Register to comment.