Database Reference
In-Depth Information
to automatically be inserted into the results. Therefore, we can use the ISNULL() function to convert the new null
value into -1 , which we then cross-reference to the Unknown date in the DimDates table.
Listing 6-18. Cross-Referencing the DimDates Table's Dimensional Keys
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) )
, [PublishedDateKey]=isNull( [DWPubsSales].[dbo].[DimDates].[DateKey], -1 )
From [Pubs].[dbo].[Titles]
Join [DWPubsSales].[dbo].[DimPublishers]
On [Pubs].[dbo].[Titles].[pub_id]=[DWPubsSales].[dbo].[DimPublishers].[PublisherId]
Left Join [DWPubsSales].[dbo].[DimDates] -- The "Left" keeps dates not found in DimDates
On [Pubs].[dbo].[Titles].[ pubdate ]=[DWPubsSales].[dbo].[DimDates].[ Date ]
The problem with each of these solutions is your ability to interpret the meaning of each null value. if your
source data does not provide you any way to identify the meaning, the null values will have to be assigned as an Un-
known. This is not particularly satisfying, but there is no magic that can fix these issues. You must work to improve
the source data before you can resolve the interpretation of nulls in your data warehouse.
Note
The SQL Query Designer
As you can see, SQL code can become complex very quickly. To make the programming process easier, you
can utilize the SQL Query Designer that comes as part of SQL Management Studio or the one that comes with
Integration Services (SSIS). Both these tools are nearly identical and create SQL code using a GUI interface.
The Query Designer that is part of SQL Management Studio makes it easy for you to create multiple
statements in a single SQL script file, whereas the one that is part of the SSIS development environment does not.
Therefore, it is likely that you will find Management Studio's Query Designer to be the more powerful of the two
options.
To start SQL Server Management Studio's Query Designer, open a new query window and simultaneously
press the Ctrl + Shift + Q keys. You can also access the designer through the Query menu at the top of Management
Studio or by right-clicking a blank area of a query window and selecting the Design Query in Editor option from
the context menu. We recommend using this last option. Usually, we start by adding a comment in the query
window to identify what we are trying to accomplish, then creating a new line below the comment and right-
clicking the blank spot to access the context menu (Figure 6-8 ).
 
 
Search WWH ::




Custom Search