IGNORECONSTANTS
Controls whether #Missing values, when used as operands in formulas, should remain #Missing after the formula calculation.
Syntax
IGNORECONSTANTS TRUE [BOTTOMUP] | FALSE
-
TRUE—Default option. #Missing values remain missing regardless of interaction with formula constants.
-
BOTTOMUP—If used after TRUE, this parameter additionally causes query execution to occur in bottom-up mode, to resolve dependency analysis quickly in cases where the formula cache has a relatively small input data set.
-
FALSE— #Missing values can be changed by interaction with formula constants.
Description
If a #Missing data value is processed in a formula with a constant or other data-independent construct, the default behavior is that #Missing is not treated like a data value. For example, if A is missing, A+5 returns #Missing.
If you set IGNORECONSTANTS to FALSE, #Missing is treated like a data value. For example, if A is missing, A+5 returns 5.
To optimize cubes in the application for hybrid mode for faster formula execution, you can use this setting configured as follows:
IGNORECONSTANTS TRUE BOTTOMUP
The above usage is recommended for hybrid mode performance tuning if both of these are true:
- you experience query performance problems
- formulas are complex and contain many cross-dimensional operators or IF/ELSE statements
Complex formulas can cause the formula cache to grow large while also being sparse (having a relatively small input data set). Using IGNORECONSTANTS TRUE BOTTOMUP forces query execution to occur in bottom-up mode, to resolve dependency analysis more efficiently in cases where the formula cache is sparse.
Example
If the configuration is as follows:
IGNORECONSTANTS TRUE
then the result for X in the following formula is #Missing
IF(X)
5;
ELSE
3
ENDIF