@QUERYBOTTOMUP

The @QUERYBOTTOMUP calculation function for Essbase helps optimize query times by identifying the required intersections for calculation, making the query time proportional to input data size.

Use this function before a formula to force queries to use only data dependent cells when executing the formula. This optimizes query times by identifying the required intersections for calculation, making the query time proportional to input data size. Note: In Release 21 or later, this function replaces @NONEMPTYTUPLE.

This function can be used as a formula directive. Using it before the formula specification is recommended when the formula is long and contains many cross-dimensional operators. Such formulas often cause the formula cache to grow large while also being sparse (having a relatively small input data set). Using this directive causes query execution to occur in bottom-up mode, to resolve dependency analysis quickly in cases where the formula cache is sparse.

Syntax

@QUERYBOTTOMUP()

When applied to a member formula, all dependent members of the formula are analzyed first (in other words, the formula is executed in bottom-up mode).

You can also use this function in a calculation script (see Examples).

Example

The following outline formula example is based on the Compensation Analytics sample cube, for which the application workbook is available in the HR Analysis directory of the Applications gallery.

@QUERYBOTTOMUP();
IF
 ("Headcount under Target"!=#Missing)
  "Market Movement"*"Size %"->"Actual"->"Sep17"->"No JG"->"unassigned_OU"->"No Job Code"->
  "No EE"->"No_MktComp"*"BASE"*"Competitive Incr %"->"Actual"->"Sep17"->"No Region"->
  "No JG"->"unassigned_OU"->"No Job Code"->"No EE"->"No_MktComp";
ENDIF;

The following example is for a calculation script use case:

"Headcount under Target"(
 @QUERYBOTTOMUP();
  IF
   (
    "COMPARATIO"!=#missing AND 
    "COMPARATIO"<="Competitive Target"->"No JG"->"No Region"->"unassigned_OU"->"No EE"->"No Job Code"->"No_MktComp"
   )
   1;
 ENDIF;
)