Database Reference
In-Depth Information
, 'Unknown Year'
)
, -- add a second row
( -2 -- This will be the Primary key for the second lookup value
, '02/01/1900'
, 'Corrupt Day'
, -2
, 'Corrupt Month'
, -2
, 'Corrupt Quarter'
, -2
, 'Corrupt Year'
)
Set Identity_Insert [DimDates] Off
■
we now have lookups for two interpretations of null values, unknown and corrupt. Remember that you can
create as many null definitions as you want, but you will also have to create additional programming logic to distin-
guish between each case. Determining the meaning of a null value is not an easy task, nor is it within the scope of
this topic. A temporary simplistic approach is to use one interpretation for all your nulls, such as Unknown, until you
have time to create a programmatic resolution.
Note
In Chapter 5, you created the DimDates table with an
IDENTITY
option on the DateKey column. This option
automatically inserts numeric values every time a new row is added. In Listing 6-13 we added rows of dates to the
DimDates table but did not include null lookup values.
By default the
IDENTITY
option prevents values from being inserted into the column manually. However, you
can manually insert a value if you enable SQL's
IDENTITY_INSERT
option, by using the
SET IDENTITY_INSERT<table name>ON
SQL command. Once that is done, additional lookup values can be
added to the dimension table, as shown in Figure
6-7
.
Figure 6-7.
Additional lookup dates have been added.
Once you add the new lookup rows, you can use them in your other tables. Listing 6-18 shows code that will
do this very thing. It utilizes an outer join to include every value in the DimTitles, even when no matching date is
found in the DimDates table. On every row where a matching date is not found, the outer join forces a null value
