Working with Empty Cells
Empty cells occur in Multidimensional Expressions (MDX) statements when data for the intersection of two or more dimensions does not exist. For example, the following MDX query example produces many empty cells:
SELECT
{[Store].[Store Name].Members} ON COLUMNS,
{[Product].[Excellent Diet Cola]} ON ROWS
FROM Sales
WHERE [Measures].[Unit Sales]
The product, Excellent Diet Cola, is not sold in all stores. For the stores that sell the product, the Unit Sales measure will contain a numeric value. For the stores that do not sell the product, however, an empty cell will be displayed.
Empty cells affect the evaluation of value expressions and search conditions. To understand why this is so, note that a value expression is composed of value expression primaries. One of the value expression primaries is <tuple>[.VALUE], which returns the value of a cell in the cube (some of whose coordinates are specified explicitly by <tuple>, and others that are available implicitly from the context of the MDX statement). This cell can be an empty cell. Empty cells affect expression evaluation in the following three cases:
- With numeric value expressions. In a numeric value expression, this value can be added, subtracted, multiplied, or divided by other values. It can also appear as the parameter of any function that has a <numeric_value_expression> argument.
- With string value expressions. In a string value expression, this value can be concatenated to another string.
- With search conditions composed of Boolean primaries. A Boolean primary is of the following form:
<boolean_primary> ::= <value_expression> <comp_op> <value_expression>
A value expression will be made up of the value expression primary, and this will lead to the first two cases described listed earlier.
Empty Cell Evaluation
MDX specifically identifies an empty cell by defining a special empty cell value that is present in an empty cell. The empty cell value is evaluated as follows:
- The function IsEmpty(<value_expression>) returns TRUE if <value_expression> is the empty cell value. Otherwise it returns FALSE.
- When the empty cell value is an operand for any of the numeric operators (+, -, *, /), it behaves like the number zero.
- When the empty cell value is an operand for the string concatenation operator (||), it behaves like the empty string.
- When the empty cell value is an operand for any of the comparison operators (=. <>, >=, <=, >, <), it behaves like the number zero or the empty string, depending on whether the data type of the other operand is numeric or string, respectively.
- 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, the empty cell value collates before an empty string.
Empty cells can be handled in a variety of ways; the easiest is to simply remove them from consideration. However, because this is not always practical in MDX, functions have been provided to deal with empty cells.
NON EMPTY Keyword
The easiest way to remove empty cells from consideration is to use the NON EMPTY keyword in an MDX query. The following example is the same MDX query example discussed earlier in this topic, but using the NON EMPTY keyword.
SELECT
NON EMPTY {[Store].[Store Name].Members} ON COLUMNS,
{[Product].[Excellent Diet Cola]} ON ROWS
FROM Sales
WHERE [Measures].[Unit Sales]
All of the stores in the first axis dimension that do not have values for the unit sales of the product are excluded from the result dataset. The empty tuples are screened out of the result dataset of the MDX query.
It is important to note that this function screens out empty tuples, not individual empty cells. Because of this, empty cells can appear in a result dataset even when the NON EMPTY keyword is used. For example, suppose you want to examine the unit sales for two different products in 1997 for each store. The following MDX query example uses the NON EMPTY keyword to screen out empty tuples:
SELECT
NON EMPTY CROSSJOIN ({[Product].[Excellent Diet Cola], [Product].[Fabulous Diet Cola]}, {[Time].[1997]}) ON COLUMNS,
NON EMPTY {[Store].[Store Name].Members} ON ROWS
FROM Sales
WHERE [Measures].[Unit Sales]
However, the result dataset resembles the following table.
| Excellent Diet Soda | Fabulous Diet Soda | |
|---|---|---|
| 1997 | 1997 | |
| Store 6 | 20.00 | 11.00 |
| Store 7 | 25.00 | 6.00 |
| Store 24 | 11.00 | 19.00 |
| Store 11 | 36.00 | 32.00 |
| Store 13 | 25.00 | 22.00 |
| Store 2 | 2.00 | |
| Store 3 | 23.00 | 16.00 |
| Store 15 | 14.00 | 17.00 |
| Store 16 | 13.00 | |
| Store 17 | 22.00 | 12.00 |
| Store 22 | 2.00 | |
| Store 23 | 4.00 | 5.00 |
The result dataset still shows three empty cells, despite the presence of the NON EMPTY keyword. The tuples created by the MDX query may contain empty cells, but the tuples themselves are not empty. For example, in the preceding result dataset, though Store 22 did not sell any of the Fabulous Diet Soda product in 1997, it did sell some of the Excellent Diet Soda product in 1997. So, the tuple created by the CROSSJOIN command does contain a member that does not evaluate to an empty cell; therefore the tuple is not considered empty and is not screened out.
For more information about the use of NON EMPTY in MDX SELECT statements, see SELECT Statement.
CoalesceEmpty Function
This MDX function returns the first nonempty value in a list of values. It is useful when you want to replace empty cell values with another numeric or string expression.
The CoalesceEmpty function allows you to evaluate a series of value expressions from left to right. The first value expression in the series that does not evaluate to the empty cell value is returned. For example, the following MDX query modifies the previous MDX query example to replace all of the empty cell values in the Unit Sales measure with zero:
WITH MEMBER [Measures].[NonEmptyUnitSales] AS
'CoalesceEmpty(Measures.[Unit Sales], 0)'
SELECT
NON EMPTY CROSSJOIN ({[Product].[Excellent Diet Cola], [Product].[Fabulous Diet Cola]}, {[Time].[1997]}) ON COLUMNS,
NON EMPTY {[Store].[Store Name].Members} ON ROWS
FROM Sales
WHERE [Measures].[NonEmptyUnitSales]
The following table demonstrates the result dataset returned by the MDX query example.
| Excellent Diet Soda | Fabulous Diet Soda | |
|---|---|---|
| 1997 | 1997 | |
| Store 19 | 0 | 0 |
| Store 20 | 0 | 0 |
| Store 9 | 0 | 0 |
| Store 21 | 0 | 0 |
| Store 1 | 0 | 0 |
| Store 5 | 0 | 0 |
| Store 10 | 0 | 0 |
| Store 8 | 0 | 0 |
| Store 4 | 0 | 0 |
| Store 12 | 0 | 0 |
| Store 18 | 0 | 0 |
| HQ | 0 | 0 |
| Store 6 | 20.00 | 11.00 |
| Store 7 | 25.00 | 6.00 |
| Store 24 | 11.00 | 19.00 |
| Store 11 | 36.00 | 32.00 |
| Store 13 | 25.00 | 22.00 |
| Store 2 | 2.00 | 0 |
| Store 3 | 23.00 | 16.00 |
| Store 15 | 14.00 | 17.00 |
| Store 16 | 0 | 13.00 |
| Store 17 | 22.00 | 12.00 |
| Store 22 | 2.00 | 0 |
| Store 23 | 4.00 | 5.00 |
The values of the calculated member NonEmptyUnitSales were determined by the CoalesceEmpty function. If the Unit Sales value evaluated to a nonempty cell, the first value in the CoalesceEmpty statement was returned. If the [Unit Sales] value evaluated to an empty cell value, the second value in the CoalesceEmpty statement was returned. Because the CoalesceEmpty function replaced all of the empty cell values with zero, the NON EMPTY keyword has nothing to screen out, so all of the tuples in the query were valid and were presented in the result dataset.
Other Functions
The way that other functions (especially calculation functions) deal with empty cells depends on the capabilities and options that are available to those functions. Functions such as Count and Avg evaluate a count of cells, but whether or not to evaluate an empty cell by this type of function should be given careful thought. In practice, it is sometimes preferable to count the number of empty cells. For example, when the number of sales representatives is counted as part of a performance evaluation query, all sales representatives should be included in the count whether or not they sold anything. In this case, each no-sale results in an empty cell. However, there are other situations in which empty cells should not be counted, such as when getting the average of sales over a certain domain. In this case, counting the no-sale cells would inaccurately decrease the average.
Some MDX functions in which empty cells may change the outcome allow for the inclusion or exclusion of empty cells as part of their calculation. Count, for example, supports the use of INCLUDEEMPTY and EXCLUDEEMPTY flags to handle the inclusion or exclusion of empty cells, respectively, while counting.