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:
- Create multiple fact tables. One for each measure that
needs a distinction to be made between a NULL and a zero value.
- Create a cube based on each fact table.
- Create a virtual cube that includes all the base cubes to
view all the measures together.