Database Reference
In-Depth Information
Figure 6-5.
Using a surrogate key with null values
■
we use negative numbers for the null related dimensional keys, because ssAs does not handle using a
zero as a dimension key well. But even if it did, the advantage to using negative numbers is that you can specify
multiple interpretations by marking each with a different number such as -1, -2, -3, and so on, and easily distin-
guish them from the shippers that have positive iD numbers.
Note
Nulls in a Dimension Table
We do not recommend leaving null values in dimensional tables. Instead, they can be either excluded or
transformed.
The simplest technique to handle null values is to disallow them in the data warehouse. The idea is that
these null values can still be reported against the OLTP environment when necessary but are too ambiguous to be
used in the data warehouse. For specific occasions where disallowing nulls is appropriate, it is easily handled, as
shown in Listing 6-15.
Listing 6-15.
Excluding Nulls with the Where Clause
Select
[TitleId]=Cast( [title_id] as nvarchar(6) )
, [TitleName]=Cast( [title] as nvarchar(50) )
, [TitleType]=Cast( [type] as nvarchar(50) )
