Databases Reference
In-Depth Information
SALESPERSON
Salesperson
Number
Salesperson
Name
SALE
Commission
Percentage
Salesperson
Number
Year of
Hire
Product
Number
PRODUCT
Time Period
Number
TIME PERIOD
Product
Number
Quantity
Time Period
Number
Product
Name
Year
Unit Price
Quarter
Month
Week
F IGURE 13.6
General Hardware Company data
warehouse star schema design
of units of a particular product that a particular salesperson sold in a particular single
year or in a particular range of years. Or, focusing on the PRODUCT table's Unit
Price attribute and the TIME PERIOD table's Year attribute, we could find the total
number of units of expensive (unit price greater than some amount) products that
each salesperson sold in a particular year. To make this even more concrete, suppose
that we want to decide which of our salespersons who currently are compensated at
the 10% commission level should receive an award based on their sales of expensive
products over the last three years. We could sum the quantity values of the SALE
table records by grouping them based on an attribute value of 10 in the Commission
Percentage attribute of the SALESPERSON table, an attribute value greater than
50 (dollars) in the Unit Price attribute of the PRODUCT table, and a Year attribute
representing each of the last three years in the TIME PERIOD table. The different
combinations and possibilities are almost endless.
Figure 13.7 shows some sample data for General Hardware's star schema data
warehouse. The fact table, SALE, is on the left and the three dimension tables are
on the right. The rows shown in the SALE table are numbered on the left just for
convenience in discussion. Look at the TIME PERIOD table in Figure 13.7. First of
all, it is clear from the TIME PERIOD table that a decision was made to store data
by the week and not by any smaller unit, such as the day. In this case, even if the
data in the transactional database is being accumulated daily, it will be aggregated
into weekly data in the data warehouse. Notice that the data warehouse began in
the first week of the first month of the first quarter of 1997 and that this week was
Search WWH ::
Custom Search