@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. Note: In Release 21 or later, this function replaces @NONEMPTYTUPLE.

This function should be used as a formula directive before the formula specification. Use of this directive 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;
)

See Also

QUERYBOTTOMUP configuration setting

@NONEMPTYTUPLE