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 EMPTYdoes not suppress this because the cell is not associated with any empty tuple.