Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 124

Product Supplier City

ProductKey
ProductName
QuantityPerUnit
UnitPrice
Discontinued
CategoryKey

SupplierKey
CompanyName
Address
PostalCode
CityKey

CityKey
CityName
StateKey (0,1)
CountryKey (0,1)

Customer

CustomerKey
CustomerID
CompanyName
Address
PostalCode
CityKey

State

Sales

StateKey
StateName
EnglishStateName
StateType
StateCode
StateCapital
RegionName (0,1)
RegionCode (0,1)
CountryKey

AK: CustomerID

Category

CustomerKey
EmployeeKey
OrderDateKey
DueDateKey
ShippedDateKey
ShipperKey
ProductKey
SupplierKey
OrderNo
OrderLineNo
UnitPrice
Quantity
Discount
SalesAmount
Freight

CategoryKey
CategoryName
Description

Territories

EmployeeKey
CityKey

Time

Country

Employee

TimeKey
Date
DayNbWeek
DayNameWeek
DayNbMonth
DayNbYear
WeekNbYear
MonthNumber
MonthName
Quarter
Semester
Year

CountryKey
CountryName
CountryCode
CountryCapital
Population
Subdivision
ContinentKey

EmployeeKey
FirstName
LastName
Title
BirthDate
HireDate
Address
City
Region
PostalCode
Country
SupervisorKey

AK: (OrderNo,
OrderLineNo)

Shipper Continent

ShipperKey
CompanyName

ContinentKey
ContinentName

AK: Date

Fig. 5.4 Relational representation of the Northwind data warehouse in Fig. 4.2

5.4 Time Dimension

A data warehouse is, in the end, a historical database. Therefore, a time
dimension is present in almost all data warehouses. Time information is
included both as foreign keys in a fact table, indicating the time when a
fact took place, and as a time dimension, containing the aggregation levels,
that is, the different ways in which facts can be aggregated across time.
In OLTP database applications, temporal information is usually derived
from attributes of type DATE using the functions provided by the database
system. For example, a typical OLTP application would not explicitly store
whether a particular date is a holiday: this would be computed on the fly
using appropriate functions. Also, the fact that a particular date belongs
to a week, a month, and so on is not explicitly stored. On the other hand,
in a data warehouse, such information is stored as attributes in the time