Database Reference
In-Depth Information
Because of the ambiguity of the term
null
, a decision has to be made as to which interpretation is most
accurate, or you may face endless arguments over the validity of your reports. Let's break each option down to
help with this decision process.
Nulls in a Fact Table
Null values in the fact table can be either dimensional keys or measured values. When the null is a measured
value, we recommend that you keep the null value indicating that it is either unknown or simply does not exist
yet. This is because most database products can properly aggregate null values into totals and subtotals. If you
substitute values such as a zero for null, the calculations may become incorrect.
To understand this issue, take a look at Listing 6-14. It shows an example of the difference when using the
aggregate function
AVERAGE()
.
Listing 6-14.
How Nulls Work with Aggregate Functions
-- Using a null you get the correct answer of 4
Select Avg([Amt]) From (
Select [Amt] = 2
Union
Select [Amt] = 6
Union
Select [Amt] = null
) as aDemoTableWithNull
-- Using a zero you get the incorrect answer of 2
Select Avg([Amt]) From (
Select [Amt] = 2
Union
Select [Amt] = 6
Union
Select [Amt] = 0
) as aDemoTableWithZero
In cases where the fact table's null value is a dimensional key, we suggest that you create an artificial key
value that can further describe the meaning of the null.
Be aware that you may need to provide more than one artificial key for each interpretation of the null values.
Programmers often want to group null data together and assign implicit meanings to these nulls. Although this
approach is useful for determining how many orders have not shipped, there is a downside to this grouping.
What happens when the implicit meaning is not correct? For example, if a package was shipped but the shipping
date was not recorded, the package may be sent twice.
To alleviate these issues, provide ways to replace null values with a set of descriptive dimensional attributes.
You can then use a
SELECT-CASE
lookup transformation, similar to the one we just discussed in the “Provide
Conformity” section of this chapter, to exchange the null values for these more descriptive dimensional values.
Here is an example of a simple null lookup: if you have a sales record in your fact table that does not have
a ShipperId associated with it, for no other reason than one has not been chosen yet, you could use a ShipperId
whose name column had a value of “Shipper not selected,” as shown in Figure
6-5
.
