Victoria Ephanor manages a small product distribution company. Because the business is growing fast, Ephanor recognizes that it is time to manage the vast information pool to help guide the accelerating growth. Ephanor, who is familiar with spreadsheet software, currently employs a sales force of four people. She asks you to develop a data warehouse application prototype that will enable her to study sales figures by year, region, salesperson, and product. (This prototype is to be used as the basis for a future data warehouse database.)

a. Identify the appropriate fact table components.

b. Identify the appropriate dimension tables.

c. Draw a star schema diagram for this data warehouse.

d. Identify the attributes for the dimension tables that will be required to solve this problem.

e. Using a Microsoft Excel or any other spreadsheet capable of producing pivot tables, generate a pivot table to show the sales by product and by region. The end user must be able to specify the display of sales for any given year. The sample output is shown in the first pivot table in Figure.

f. Using Problem 2e as your base, add a second pivot table, to show the sales by salesperson and by region. The end user must be able to specify sales for a given year or for all years, and for a given product or for all products.

g. Create a 3-D bar graph to show sales by salesperson, by product, and by region.