Each meant for the specific purposes:
AGG :
It consolidates the database values ignoring all member formulas.
It works on the sparse dimensions.
It is faster than CALC for less than 6 consolidation levels.
Syntax: AGG (Dimension list);
CALC ALL:
It calculates and aggregates entire outline database based on Outline.
Order of the calculation depends upon the characteristics of the dimensions in the outline.
Syntax: CALC ALL;
CALC DIM:
It calculates and aggregate values for all members in the specified dimensions.
Order of the calculation based on the Sparse and Dense types.
Ex : CALC DIM(dense1,sparse2,dense3,sparse4) then the order of the calculation is dense1,dense3,sparse2,sparse4.
If at all you want to specify your own order use separate function for each dimension.
CALC DIM(dense1);
CALC DIM(sparse2);
CALC DIM(dense3);
CALC DIM(sparse4);
Syntax : CALC DIM(dimension);
AGG :
It consolidates the database values ignoring all member formulas.
It works on the sparse dimensions.
It is faster than CALC for less than 6 consolidation levels.
Syntax: AGG (Dimension list);
CALC ALL:
It calculates and aggregates entire outline database based on Outline.
Order of the calculation depends upon the characteristics of the dimensions in the outline.
Syntax: CALC ALL;
CALC DIM:
It calculates and aggregate values for all members in the specified dimensions.
Order of the calculation based on the Sparse and Dense types.
Ex : CALC DIM(dense1,sparse2,dense3,sparse4) then the order of the calculation is dense1,dense3,sparse2,sparse4.
If at all you want to specify your own order use separate function for each dimension.
CALC DIM(dense1);
CALC DIM(sparse2);
CALC DIM(dense3);
CALC DIM(sparse4);
Syntax : CALC DIM(dimension);
Nice post! However, I would like to add something here....
ReplyDeleteAGG aggregates the data, ignoring the Member Formulas and also that, it is unable to create data blocks if needed whereas CALC DIM executes the member formulas and also creates the data blocks if needed.
really a nice point !!
DeleteNice and helpful! Thanks.
ReplyDeleteNice
ReplyDeleteDoes CALC ALL calculates member formulas?
ReplyDeleteYes it will calculate the member formula as well
Deleteexcelent post!
ReplyDeleteNote that Dimensions marked Time and Accounts are Calculated in specific order vis a vis the other dimensions regardless of whether they are configured as Sparse or Dense. This can cause unexpected results if not taken into account and can also cause additional passes through the database blocks. For this reason, usually it is best to keep Account and Time dimensions as Dense.
ReplyDeleteSee below...
If both a dimension tagged as accounts and a dimension tagged as time exist, and if formulas are applied to members on the accounts dimension, Essbase calculates in this order:
Dimension tagged as accounts
Dimension tagged as time
Other dense dimensions (in the order in which they are displayed in the database outline)
Other sparse dimensions (in the order in which they are displayed in the database outline)
Otherwise, Essbase calculates in this order:
Dense dimensions (in the order in which they are displayed in the database outline)
Sparse dimensions (in the order in which they are displayed in the database outline)
=Todd Binenstock