Working with Empty Values
An empty value indicates that a specific member, tuple, or cell is empty. An empty cell value indicates either that the data for the specified cell cannot be found in the underlying fact table, or that the tuple for the specified cell represents a combination of members that is not applicable for the cube.
Note
Although an empty value is different from a value of zero, an empty value is typically treated as zero most of the time.
The following information applies to empty values:
The IsEmpty function returns TRUE if and only if the cell identified by the tuple specified in the function is empty. Otherwise, the function returns FALSE.
Note
The IsEmpty function cannot determine whether a member is empty. To determine whether a member is empty, use the IS operator.
When the empty cell value is an operand for any one of the numeric operators (+, -, *, /), the empty cell value is treated as zero if the other operand is a nonempty value. If both operands are empty, the numeric operator returns the empty cell value.
When the empty cell value is an operand for the string concatenation operator (+), the empty cell value is treated as an empty string if the other operand is a nonempty value. If both operands are empty, the string concatenation operator returns the empty cell value.
When the empty cell value is an operand for any one of the comparison operators (=. <>, >=, <=, >, <), the empty cell value is treated as zero or an empty string, depending on whether the data type of the other operand is numeric or string, respectively. If both operands are empty, both operands are treated as zero.
When collating numeric values, the empty cell value collates in the same place as zero. Between the empty cell value and zero, empty collates before zero.
When collating string values, the empty cell value collates in the same place as the empty string. Between the empty cell value and the empty string, empty collates before an empty string.
Dealing with Empty Values in MDX Statements and Cubes
In Multidimensional Expressions (MDX) statements, you can look for empty values and then perform certain calculations on cells with valid (that is, not empty) data. Eliminating empty values when performing calculations can be important because certain calculations, such as an average, can be inaccurate if empty cell values are included.
If empty values may be stored in your underlying fact table data, and you do not want empty cell values appearing in your cube, you should create queries and data-modification statements that either eliminate empty values or coalesce empty values into some other value. Additionally, you can use the Null Processing option on a measure to modify null facts so that the null is converted into 0, converted to an empty value, or even throws an error during processing.
Empty Values and Comparison Operators
When empty values are present in data, logical and comparison operators can potentially return a third result of EMPTY instead of just TRUE or FALSE. This need for three-valued logic is a source of many application errors. These tables outline the effect of introducing empty value comparisons.
This table shows the results of applying an AND operator to two Boolean operands.
| AND | TRUE | EMPTY | FALSE |
|---|---|---|---|
TRUE |
TRUE |
FALSE |
FALSE |
EMPTY |
FALSE |
EMPTY |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
This table shows the results of applying an OR operator to two Boolean operands.
| OR | TRUE | FALSE |
|---|---|---|
TRUE |
TRUE |
TRUE |
EMPTY |
TRUE |
TRUE |
FALSE |
TRUE |
FALSE |
This table shows how the NOT operator negates, or reverses, the result of a Boolean operator.
| Boolean expression to which the NOT operator is applied | Evaluates to |
|---|---|
TRUE |
FALSE |
EMPTY |
EMPTY |
FALSE |
TRUE |
See Also
Reference
MDX Function Reference (MDX)
MDX Operator Reference (MDX)
Concepts
Other Resources
Defining and Configuring Dimension Usage and Dimension Relationships
Lesson 5: Defining Relationships Between Dimensions and Measure Groups