Empty Cells in a Dataset

OLE DB Programmer's Reference

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:

-	Venkatrao	-	Netz	-
	Office	Operating systems	Office	Operating systems
1996	4032	-	9876	13
1997	10002	54	18734	21

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.

1998-2001 Microsoft Corporation. All rights reserved.