Database Reference
In-Depth Information
When 'psychology' Then 'Psychology'
When 'trad_cook' Then 'Traditional Cooking'
When 'UNDECIDED' Then 'Undecided'
End
From [Pubs].[dbo].[Titles]
Group By [Type] -- get distinct values
-- Combine the data from the lookup table and the original table
Select
[TitleId]=Cast( [title_id] as nvarchar(6) )
, [TitleName]=Cast( [title] as nvarchar(50) )
, [TitleType]=[CleanTitleType]
, [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]
Join [DWPubsSales].[dbo].[TitleTypeLookup]
On [Pubs].[dbo].[Titles].[type]=[DWPubsSales].[dbo].[TitleTypeLookup].[OriginalTitleType]
■
Rather than using the
DISTINCT
keyword, you may find it advantageous to use a sQL
GROUP BY
state-
ment that can give you different results than the
DISTINCT
keyword will when working with some functions, such
as
ROW_NUMBER()
. in the example in Listing 6-12, there is no change in results, but it is a good technique to keep in
mind as part of your ETL toolkit.
Note
Generate Date Data
There are times when the ETL process will not just copy and transform existing data but instead will generate
entirely new data. For example, many data warehouses have a table holding a sequential list of dates which are
generated using an
INSERT
statement nested inside a programmatic loop.
In Chapter 5, we created a DimDates table, but did not fill it with data. As we will see in Chapter 8,
Exercise 8-1, the SQL code in Listing 6-13 can be used during the ETL process to fill the DimDates table.
Listing 6-13.
Filling the DimDates Table
-- Because the date table has no associated source table we can fill the data
-- using a SQL script.
-- Create variables to hold the start and end date
DECLARE @StartDate datetime='01/01/1990'
DECLARE @EndDate datetime='12/31/1995'
-- Use a while loop to add dates to the table
DECLARE @DateInProcess datetime
SET @DateInProcess=@StartDate
WHILE @DateInProcess<= @EndDate
BEGIN
-- Add a row into the date dimension table for this date
INSERT INTO DimDates
(
