Database Reference
In-Depth Information
, [PublisherKey]=[DWPubsSales].[dbo].[DimPublishers].[PublisherKey]
, [TitlePrice]=Cast( [price] as decimal(18, 4) )
, [PublishedDate]=[pubdate]
From [Pubs].[dbo].[Titles]
Join [DWPubsSales].[dbo].[DimPublishers]
On [Pubs].[dbo].[Titles].[pub_id]=[DWPubsSales].[dbo].[DimPublishers].[PublisherId]
Where
[Pubs].[dbo].[Titles].[Title_Id]
Is Not Null
l
And
[Pubs].[dbo].[Titles].[Title]
Is Not Null
And
[Pubs].[dbo].[Titles].[Type]
Is Not Null
l
And
[Pubs].[dbo].[Titles].[Price]
Is Not Null
And
[Pubs].[dbo].[Titles].[PubDate]
Is Not Null
This approach works for some tables; however, it has the potential to exclude much of the data that you need
for your BI solution. Whenever possible, it is more accurate to transform nulls into descriptive values. One way to
accomplish this is by using the
ISNULL()
function. In Listing 6-16 the
ISNULL()
function converts null values into
the word
Unknown
. The value of Unknown is more descriptive than the use of null, although admittedly not by
much.
Listing 6-16.
Converting Null Values with the ISNULL() Function
Select
[TitleId]=Cast(
isNull( [title_id], -1 )
as nvarchar(6) )
, [TitleName]=Cast(
isNull( [title], 'Unknown' )
as nvarchar(50) )
, [TitleType]=Cast(
isNull( [type], 'Unknown' )
as nvarchar(50) )
, [PublisherKey]=[DWPubsSales].[dbo].[DimPublishers].[PublisherKey]
, [TitlePrice]=Cast(
isNull( [price], -1 )
as decimal(18, 4) )
, [PublishedDate]=
isNull( [pubdate], '01/01/1900' )
From [Pubs].[dbo].[Titles]
Join [DWPubsSales].[dbo].[DimPublishers]
On [Pubs].[dbo].[Titles].[pub_id]=[DWPubsSales].[dbo].[DimPublishers].[PublisherId]
Nulls in columns that consist of noncharacter data types, such as integers and datetime, will not accept the
string value of Unknown. Consequently, you can use the
ISNULL()
function to convert the published date into
something like 01/01/1900. The year 1900 is a starting point for many date data types and is unlikely to mark a
real event, at least in this particular database. This means that it has no relevance to an actual publication date
and can be used as an indicator for missing data. Keep in mind, this date will not be appropriate when the data
includes this particular date as a legitimate value.
A Null Lookup Table
Although the previous techniques for dealing with nulls are used in many data warehouses, another option is to
reference lookup values in either a dedicated lookup table or existing dimensional tables.
Lookup tables (also known as
domain tables
) are additional tables that are not part of the dimension model
but instead have descriptive values that can be referenced from the dimensional tables. Figure
6-6
shows the
contents of a typical lookup table.
