On aggregate storage (ASO) cubes, NonEmptyCount() is optimized so that the calculation of the distinct count for all cells can be performed by scanning the cube only once. Without this optimization, the database is scanned as many times as the number of cells corresponding to the distinct count. The NONEMPTYCOUNT optimization is triggered when an outline member formula has the following syntax:

NONEMPTYCOUNT(set, measure, exclude_missing)

Syntax

NonEmptyCount ( set [,numeric_value_expression [, exclude_missing ]] )

Parameters

set

The set in which to count tuples.

numeric_value_expression

Optional. A numeric value or an expression that returns a numeric value. (See MDX Grammar Rules.)

exclude_missing

Optional. A flag that indicates that the count value returned is missing when the Measure value is missing for members in Set, where Set is a one dimensional set from a stored dimension, and Measure is a stored measure.

The exclude_missing parameter supports the NonEmptyCount optimization on ASO cubes by improving the performance of a query that queries metrics that perform a distinct count calculation. See Example 2 below.

By default, a value of zero is returned when the Measure value is missing for all members in the Set.

Example 1

The following MDX query:

With 
Member [Measures].[Number Of Markets]
as 'NonEmptyCount (Market.Levels(0).Members, Sales)'

Select 
{[Measures].[Number Of Markets]} on Columns,
{[100].Children, [200].Children} on Rows
FROM Sample.Basic

returns the following output:

Example 2

In an ASO cube, it is common to count the distinct number of entities (such as customers and products). You can perform a distinct count by defining a formula member or a calculated member. For example, you can add a formula member, [DistinctCustomerCnt], to use with the following formula to calculate the count of distinct customers who bought a Product:

NONEMPTYCOUNT(Customer.Levels(0).Members, [Units])

The following MDX query scans the database as many times as the number of Products, evaluating the distinct customer count for each Product separately:

SELECT
   {[DistinctCustomerCnt]} on COLUMNS,
   Products.Levels(0).Members on  ROWS