MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions

Empty Cells in a Dataset

Frequently, the MDX statement that specifies a dataset will include empty cells in the dataset. Just like any other cell, an empty cell is associated with a set of tuples. There are some tuples for which every cell is empty at the intersection of this tuple and any other tuple in all of the other axes. Such tuples are called empty tuples.

Depending on an application's presentation requirements, it is sometimes desirable to have empty tuples in the dataset and sometimes not. The MDX keyword NON EMPTY is used to filter out empty tuples. Using this keyword in a query filters out all empty tuples along an axis. The BNF construct is as follows:

<MDX_Statement> ::= SELECT <axis_specification>
                        [, <axis_specification>...]
                    FROM <cube_specification>
                    WHERE <slicer_specification>

<axis_specification> ::= [NON EMPTY] <set_expression> ON <axis_name>

If NON EMPTY is specified, empty tuples are removed from the axis. The default is for empty tuples to appear on the axis.

Empty cells are possible even if NON EMPTY is specified. For example, suppose that the sales rep Venkatrao did not sell anything during the first half of 1996. Suppose further that during the second half of 1996, he sold Office products but no Operating System products. Finally, suppose that in 1997, he sold all products. Now consider the dataset that results from the following MDX statement:

SELECT 
   NON EMPTY CROSSJOIN({Venkatrao, Netz}, {Office, [Operating Systems]}) 
      ON COLUMNS,
   NON EMPTY {[1996], [1997]} ON ROWS
FROM SalesCube
WHERE (Sales)

This results in the following dataset:

The cell that has operating systems for Venkatrao in 1996 is empty because Venkatrao did not sell any operating systems in all of 1996. NON EMPTY does not suppress this because the cell is not associated with any empty tuple.

Note   It is not possible to filter out the empty tuples by applying some kind of a set expression during the axis specification because the cell data is populated after all axis specifications are evaluated. Any filter in the axis specification cannot be based on data in the cells of a dataset. Therefore, filtering out the empty tuples from an axis is done only after the set expressions of all of the axes have been evaluated.