When it comes to aggregation, there are a few ways to achieve this. Let’s consolidate a few options and see what are the differences and benefits among them.

 

AGG

The most common one is AGG. It consolidates the database values ignoring all member formulas. Keep in mind that it works for sparse dimensions. The aggregation order of the calculation depends on the hierarchy defined in the database outline.

Tips: It is faster than CALC for less than 6 consolidation levels.

Syntax: AGG (Dimension list);

Example: AGG(Product, Entity);

 

CALC DIM

CALC DIM is another common solution. It calculates and aggregate values for all members in the specified dimensions. The calculation order is based on the Sparse and Dense types. If we use, CALC DIM(dense1,sparse1,dense2,sparse2), the order of the calculation is dense1,dense2,sparse1,sparse2. To specify calculation order, simply use separate function for each dimension.

CALC DIM(dense1);

CALC DIM(sparse1);

CALC DIM(dense2);

CALC DIM(sparse2);

Tips: CALC DIM is taking time looking for formulas even though they don’t exist. Being said, for sparse dimensions that don’t have formulas, AGG, which only does aggregation, is typically faster.

Syntax: CALC DIM(dimension);

Example: CALC DIM(Product);

 

CALC ALL

Similar to CALC DIM, CALC ALL calculates and aggregates entire outline database based on Outline. The calculation order depends upon the characteristics of the dimensions in the outline.

Tips: To exclude certain dimension, simply add the EXCEPT part to it. CALC ALL EXCEPT DIM(Product);

Syntax: CALC ALL;

 

Data Storage – Dynamic Calc/ Dynamic Calc and Store

Use Dynamic Calc/ Dynamic Calc and Store, and aggregation operator to get data aggregated.

Dynamic Cal calculates data values of members and disregards the values. Dynamic Calc and Store calculates data values of members and store the values.

Tips:

• For sparse dimensions, use Dynamic Calc and Store for members with complex formulas and where users retrieve frequently. Mostly, we can use Dynamic Calc to optimize calculations.
• For dense dimensions, use Dynamic Calc for members. Dynamic Calc and Store provides only a small decrease in retrieval time and regular calculation time, and does not significantly lower disk usage.
• Changing a member’s storage type to Dynamic Calc may result in loss of data.

 

@ANCESTORS

Use AGG or CALC DIM will aggregate all members in that dimension. What if we would like to aggregate only where data is changed?

For example, on a webform we have Product and Entity dimensions. We changed data for Entity1, and Product1. In order to aggregate data, use AGG or CALC DIM will aggregate all members which is not necessary.

Let’s think about how Essbase works behind the scenes when using AGG(Entity, Product). Firstly, product is aggregated for all level 0 Entity where data exists. Then Entity gets aggregated for all level 0 Product where data exists. Being said, we can use @ANCESTORS to just aggregate the data we need. This way we just need to calculate the member’s ancestors.

Tips: Variables can be used in the scripts to make it more dynamic.

Example:

FIX(“Product1”)

@ANCESTORS(“Entity1”)

ENDFIX

FIX(@IANCESTORS(“Entity1”)

@ANCESTORS(“Product1”)

ENDFIX

 

Hopefully, you have a better understanding on aggregating data now. We will talk about more tricks in the future. See you next time.