Database Reference
In-Depth Information
To fill the DimTitles table with correct PublisherKey data, we first need to look up the publisher's new
surrogate key value by referencing the original relationship between publisher IDs that formed the original
natural keys. A simple way to accomplish this is by creating a SQL join that queries the natural key columns in the
source and destination tables. Listing 6-9 shows an example.
Listing 6-9.
Referencing the Natural Keys to Find the Surrogate Key Value
Select
[TitleId]=Cast( [title_id] as nvarchar(6) )
, [TitleName]=Cast( [title] as nvarchar(50) )
, [TitleType]=Cast( [type] as nvarchar(50) )
, [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
]
For the publisher key data to be available, you have to fill the DimPublishers table first. Otherwise, the
surrogate key that is autogenerated will not be in existence. In Listing 6-10 we have provided a simple insert
statement that accomplishes this.
Listing 6-10.
Inserting Values into the DimPublishers Table
Insert Into [DWPubsSales].[dbo].[DimPublishers]
( [PublisherId], [PublisherName] )
Select
[PublisherId]=Cast( [pub_id] as nchar(4) )
, [PublisherName]=Cast( [pub_name] as nvarchar(50) )
From [pubs].[dbo].[publishers]
Provide Conformity
One additional common ETL task is the process of conforming data to be more readable or more consistent.
There are two simple ways to accomplish this using SQL code; the first is to use a
SELECT-CASE
statement, and the
second involves using a lookup table.
A
SELECT-CASE
statement is created by adding the
CASE
clause to any statement. For example, in
Listing 6-11, we extract the values of the TitleType column in the
WHEN
clause and match the patterns of
“business,” “mod_cook,” “popular_comp” and so on. We do this because the current values do not read well.
They should have been capitalized and fully spelled out to read as “Modern Cooking” and “Popular Computing.”
Because the abbreviations are not particularly legible, a transformation that conforms the data into a more
readable format is in order.
To do this, examine a particular expression and then compare it to the pattern that you want to match. When
the result of the expression, defined in the
WHEN
clause, matches a particular pattern, the output of the select
statement is transformed into the value of the expression found in the
THEN
clause. Listing 6-11 shows how to
accomplish this type of transformation using the SQL
SELECT-CASE
statement.
Listing 6-11.
Conforming Values with a
SELECT-CASE
Statement
Select
[TitleId]=Cast( [title_id] as nvarchar(6) )
, [TitleName]=Cast( [title] as nvarchar(50) )
