Database Reference
In-Depth Information
Creating a Date Dimension Table
One common practice in data warehousing is the creation of a date dimension table. We discussed the use of
these tables in Chapter 4 but have not discussed how to create them. There are a number of ways to accomplish
this, but using SQL code is probably the most common way to do so.
As a standard practice, the creation of a date dimension table includes a surrogate key in the form of an
integer value, a natural date key in the form of a datetime value and other descriptive attributes such as month,
quarter, and year. Listing 5-10 shows an example of a date dimension table being created with this design.
Listing 5-10.
Creating the DimDates Table
-- We should create a date dimension table in the database
CREATE TABLE dbo.DimDates (
[DateKey] int NOT NULL PRIMARY KEY IDENTITY
, [Date] datetime NOT NULL
, [DateName] nVarchar(50)
, [Month] int NOT NULL
, [MonthName] nVarchar(50) NOT NULL
, [Quarter] int NOT NULL
, [QuarterName] nVarchar(50) NOT NULL
, [Year] int NOT NULL
, [YearName] nVarchar(50) NOT NULL
)
Once the table is created, fill it with dimensional values. You can do so during the ETL process or
immediately after creating the table. This is different from the other dimensional tables, because the data for a
data dimension table is not imported from the OTLP database and instead is programmatically generated.
The simplest way to accomplish this is to create a SQL
WHILE
loop and specify the range of dates that are to be
placed inside the table. Listing 5-11 shows an example of a transact SQL statement that accomplishes this goal.
Once the data dimension table is filled with data, it can be referenced from both fact and dimensional tables.
Therefore, you create foreign key constraints to all the tables that reference this new table.
In the DWPubsSales example, we must specifically use dates that were appropriate to Microsoft's Pubs
database. These dates included sales records from the 1990s. If the date table was going to be utilized by multiple
data marts or data warehouses, you would include a much broader range of dates.
Listing 5-11.
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='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]
