THE DATA WAREHOUSE - Fundamentals of Database Management Systems - page 345

SALESPERSON

Salesperson Salesperson Commission Year of Offic e

Number Name Percentage Hire Numbe r

CUSTOMER

Customer Customer Salesperson

Number Name Number HQ City

CUSTOMER EMPLOYEE

Customer Employee Employee

Number Number Name Title

PRODUCT

Product Product

Number Name Unit Price

SALES

Salesperson Produc t

Numbe r Number Quantity

OFFICE

Office
Number

F IGURE 13.5
The General Hardware Company relational
database

Telephone Size

in the last week. But what if we want to record and keep track of the sales for the
most recent week, and the week before that, and the week before that, and so on
going back perhaps five or ten years? That is a description of a data warehouse. The
SALE table in the star schema of Figure 13.6 also reflects General Hardware's sales
by salesperson and product but with a new element added: time. This table records
the quantity of each product that each salesperson sold in each time period stored.
The SALE table in Figure 13.6 has to have a primary key, like any relational
table. As shown in the figure, its primary key is the combination of the Salesperson
Number, Product Number, and Time Period Number attributes. But each of those
attributes also serves as a foreign key. Each one leads to one of the dimension tables,
as shown in Figure 13.6. Some historic data can be obtained from the fact table
alone. Using the SALE table, alone, for example, we could find the total number
of units of a particular product that a particular salesperson has sold for as long as
the historical sales records have been kept, assuming we know both the product's
product number and the salesperson's salesperson number. We would simply add
the Quantity values in all of the SALE records for that salesperson and product. But
the dimension tables provide, well, a whole new dimension! For example, focusing
in on the TIME PERIOD's Year attribute and taking advantage of this table's foreign
key connection to the SALE table, we could refine the search to find the total number