Database Reference
In-Depth Information
, [TitleType]=
Case
Cast( [type] as nvarchar(50) )
When
'business'
Then
'Business'
When
'mod_cook'
Then
'Modern Cooking'
When
'popular_comp'
Then
'Popular Computing'
When
'psychology'
Then
'Psychology'
When
'trad_cook'
Then
'Traditional Cooking'
When
'UNDECIDED'
Then
'Undecided'
End
, [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]
When this code runs, values such as “mod_cook” are converted into the value of “Modern Cooking,” and so
on. The select statement is applied to every row in the table and takes place for every value in the column.
This method is simple and effective, but there is a downside. If you need to apply the same transformation
to other tables, you need to repeat the same SQL code for each table. And if one select statement uses capitalized
values, but another uses lowercase values, your tables will have multiple versions of the same data (i.e., Modern
Cooking vs. modern cooking). These title type examples are unlikely to be used in any other dimension table, so it
is very unlikely that this would become a problem, but it is something to keep in mind with naming conventions.
If you are going to use the same transformation data for more than one table, you may want to use the
second option. The second option provides these types of transformations by creating a lookup table and then
comparing the values in the lookup table to the values of the original table. When the match is found, extract the
conformed value from the lookup table, replacing the original value.
The advantage of this option is that it can be reused multiple times without repeatedly having to define
the list of conformed values. For example, this method is appropriate when you have a lookup table that holds
two-letter state abbreviations in some table cells and the state's full name in others. Any time you want to convert
the two-letter abbreviation to the state's full name in one or more tables, you could reference a lookup table
to accomplish that goal. Because state names are likely to appear in many different tables within a given data
warehouse, a lookup table is a better choice compared to using a Select-Case statement to conform your data.
Listing 6-12 shows a lookup table being created and then filled with original and transformed values via the
lookup table.
Listing 6-12.
Conforming Values with a Lookup Table
-- Create the lookup table
Create table [TitleTypeLookup]
(
[TitleTypeKey] int Primary Key Identity
,
[OriginalTitleType]
nvarchar(50)
, [CleanTitleType] nvarchar(50)
)
-- Add the original and transformed data
Insert into [TitleTypeLookup]
( [OriginalTitleType] , [CleanTitleType] )
Select
[OriginalTitleType]=[Type]
, [CleanTitleType]
=Case Cast( [type] as nvarchar(50) )
When 'business' Then 'Business'
When 'mod_cook' Then 'Modern Cooking'
When 'popular_comp' Then 'Popular Computing'
