Microsoft KB Archive/244650

= INF: Working with NULL Values in OLAP Services =

Article ID: 244650

Article Last Modified on 2/19/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft SQL Server OLAP Services

-



This article was previously published under Q244650



SUMMARY
A NULL value, in OLAP Services, represents an empty cell. In relational products such as Microsoft SQL Server 7.0 or later, a NULL value represents an unknown value. In online analytical processing (OLAP) a NULL value means that we know the cell is empty.

For calculations, OLAP Services treats NULLs as a "0", unless the expression involves only NULLs, in which case the result is a NULL. Here are some examples: NULL/1 = 0 NULL + 0 = 0 NULL/NULL = NULL NULL + NULL = NULL 1/NULL = Err Note that you can check if the value of a cell is NULL by using the IsEmpty function. For example, the following calculated member in a MDX query returns a "unit sales" value of 0 for an empty cell and the actual value for a non-empty cell: with member [measures].[emptycells] as 'iif(isempty([measures].[unit sales]),0, [measures].[unit sales])' Question: How does OLAP Services read NULL measure data in a fact table?

Answer: If there is no record in the fact table for a certain combination of dimension members, (such as [1997].[Quarter1].[January], [USA].[Texas].[Dallas] ) then, on cube processing, OLAP Services treats the corresponding cell in the cube as empty. However, when there are records in the fact table that have one or more null measure columns, OLAP Services assigns a 0 value to the corresponding cells in the cube.

This behavior may not always be desirable, particularly, when the user has to make a distinction in the cube's measure between a fact table NULL and zero values. If such a distinction must be made, then the user has to make sure that the fact table (or tables) do not have records with empty measure columns. One solution is to break these empty measure columns into different fact tables. The steps necessary to implement this solution are:
 * 1) Create multiple fact tables. One for each measure that needs a distinction to be made between a NULL and a zero value.
 * 2) Create a cube based on each fact table.
 * 3) Create a virtual cube that includes all the base cubes to view all the measures together.

Keywords: kbinfo KB244650

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.