Special Value: ALL()

The FACT table contains rows that represent aggregates, as well as rows that represent atomic data. The aggregate rows have a special value in the columns that represent aggregated levels — for example, in the CITY and ZIPCODE columns of a row that contains the STATE level aggregation. Because queries for mapping MDX make heavy use of such special values, this makes it easier to define cleaner and simpler semantics for this.

This special value is denoted syntactically as the result of the built-in function ALL(). Semantics of this value are determined by the following rules:

• ALL() = ALL() is always true.
  
  
• ALL() <comparison_operator> <any_non-ALL()_value> is a well-formed expression, regardless of the data type of <any_non-ALL()_value>.
  
  
• The semantics of comparing ALL() to any non-ALL() value are the same as comparing NULL to any non-NULL value. Specifically, ALL() != <any_non-ALL()_value> is always true.
  
  
• NULL collates before ALL(), and ALL() collates before all non-NULL and non-ALL() values.
  
  
• With respect to NULL comparison, ALL() behaves like all other non-NULL values.