Database Reference
In-Depth Information
Figure 6-6.
A typical lookup table
For the lookup table to function properly, you have to determine the meaning of each null found in the
source data and transform the null value into a lookup ID that will cross-reference the lookup table.
The dilemma with a dedicated lookup table is that each dimension with null values now forms a snowflake
design. Although this is not a problem per se, it violates the “keep it simple” rule.
Often you will find that a lookup table can be collapsed into your dimension tables. For example, when
a date column in the fact or dimension table has a null value, you can use a dimension table such as the
DimDates table as a lookup table, as long as it includes rows that contain lookup data. These lookup rows contain
descriptive values that represent your null interpretations. Listing 6-17 shows an example of adding two lookup
rows to the DimDates table.
Listing 6-17.
Adding Additional Lookup Values to the DimDates Table
Set Identity_Insert [DimDates] On
INSERT INTO [DWPubsSales].[dbo].[DimDates] (
[DateKey] -- This is normally added automatically
, [Date]
, [DateName]
, [Month]
, [MonthName]
, [Quarter]
, [QuarterName]
, [Year]
, [YearName]
)
VALUES
( -1 -- This will be the Primary key for the first lookup value
, '01/01/1900'
, 'Unknown Day'
, -1
, 'Unknown Month'
, -1
, 'Unknown Quarter'
, -1
