Extraction, Transformation, and Loading - Data Warehouse Systems: Design and Implementation - page 295

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. 8.15 Schema of the Northwind data warehouse (repeated from Fig. 5.4 )

(activity Sales Load ) can only start when all other tasks have been completed.
If the process fails, a cancelation event is triggered and an error message in
the form of an e-mail is dispatched.
Figure 8.19 shows the task that loads the Category table in the data
warehouse. It is just composed of an input data task and an insertion data
task. The former reads the table Categories from the operational database.
The latter loads the table Category in the data warehouse, where the
CategoryID attribute in the Categories table is mapped to the CategoryKey
attribute in the Category table. Similarly, Fig. 8.20 shows the task that loads
the Time table from an Excel file. It is similar to the previously explained
task but includes a conversion of columns, which defines the data types of the
attributes of the target table Time in the data warehouse, and the addition of
acolumn TimeKey initialized with null values so the database can generate