Database Reference
In-Depth Information
Go
-- Step 2) Create Data Warehouse Database
Create Database DWWeatherTracker
Go
Use DWWeatherTracker
Go
Create the Tables
The next three steps outlined in the
InstWeatherTrackerDW.sql
code file creates three tables (Listing 2-3).The
first table is to hold raw data imported from the text file
WeatherHistory.txt
. The second table, DimEvents, is
our one and only dimension table in this example. The third table, FactWeather, is our fact table.
Listing 2-3.
Creating Three Tables
-- Step 3) Create a Staging table to hold imported ETL data
CREATE TABLE [WeatherHistoryStaging]
( [Date] varchar(50)
, [Max TemperatureF] varchar(50)
, [Min TemperatureF] varchar(50)
, [Events] varchar(50)
)
-- Step 4) Create Dimension Tables
Create Table [DimEvents]
( [EventKey] int not null
Identity
, [EventName] varchar(50) not null
)
Go
-- Step 5) Create Fact Tables
Create Table [FactWeather]
( [Date] datetime not null
, [EventKey] int not null
, [MaxTempF] int not null
, [MinTempF] int not null
)
In step 4, the DimEvents dimension table is created (Figure
2-11
)
. In this table, we have both a key column
and a name column. This is characteristically the minimum design seen in real-life examples. In most cases,
however, there are also additional descriptive columns in the table.
Using the Identity Option
In Listing 2-3, we included an identity attribute on the EventKey column. In SQL Server, a column marked
with an identity attribute automatically adds incremental integer values to the column each time a row of data
is inserted into the table. In other words, because we have configured the EventKey column to be an identity
column, adding a new event name to the DimEvents table will automatically insert an integer of “1” into the
EventKey column. When we add another event name, an integer of “2” is inserted for the second row, and so on.
