Database Reference
In-Depth Information
Truncate Table dbo.DimDates
Go
-- Step 2) Code used to fill tables (Will be used with SSIS Data Flow Tasks)
-- 2a) Get source data from pubs.dbo.authors and
-- insert into DimAuthors
Select
[AuthorId]=Cast( au_id as nChar(11) )
, [AuthorName]=Cast( ( au_fname+' '+au_lname ) as nVarchar(100) )
, [AuthorState]=Cast( state as nChar(2) )
From pubs.dbo.authors
Go
-- 2b) Get source data from pubs.dbo.stores and
-- insert into DimStores
Select
[StoreId]=Cast( stor_id as nChar(4) )
, [StoreName]=Cast( stor_name as nVarchar(50) )
From pubs.dbo.stores
Go
-- 2c) Get source data from pubs.dbo.publishers and
-- insert into DimPublishers
Select
[PublisherId]=Cast( pub_id as nChar(4) )
, [PublisherName]=Cast( pub_name as nVarchar(50) )
From pubs.dbo.publishers
Go
-- 2d) Create values for DimDates as needed.
-- Create variables to hold the start and end date
Declare @StartDate datetime='01/01/1990'
Declare @EndDate datetime='01/01/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
( [Date], [DateName], [Month], [MonthName], [Quarter], [QuarterName], [Year],
[YearName] )
Values (
@DateInProcess -- [Date]
, DateName( weekday, @DateInProcess ) -- [DateName]
, Month( @DateInProcess ) -- [Month]
, DateName( month, @DateInProcess ) -- [MonthName]
, DateName( quarter, @DateInProcess ) -- [Quarter]
, 'Q'+DateName( quarter, @DateInProcess )+' - '
+ Cast( Year( @DateInProcess) as nVarchar(50) ) -- [QuarterName]
, Year( @DateInProcess )
