CPC Programming Attributes for Dimension Tables Excel Sheet

Victoria Ephanor manages a small product distribution company. Because the business is growing fast, she 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 will be used as the basis for a future data warehouse database.)

Using the data supplied in the Ch13_P2.xls file, complete the following seven problems:

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 Microsoft Excel or any other spreadsheet program that can produce 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 P13.2E. f. Using Problem 2e as your base, add a second pivot table (see Figure P13.2E) 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 3D bar graph to show sales by salesperson, by product, and by region. (See the sample output in Figure P13.2G.) Sheet1
Year Region
Agent Product
2016 East
Carlos
Erasers
2016 East
Tere
Erasers
2016 North
Carlos
Widgets
2016 North
Tere
Widgets
2016 North
Carlos
Widgets
2016 South
Victor
Balls
2016 South
Victor
Balls
2016 South
Victor
Balls
2016 West
Mary
Pencils
2016 West
Mary
Pencils
2017 East
Carlos
Pencils
2017 East
Victor
Balls
2017 North
Mary
Pencils
2017 North
Victor
Erasers
2017 South
Carlos
Widgets
2017 South
Mary
Widgets
2017 South
Mary
Widgets
2017 South
Tere
Balls
2017 South
Tere
Erasers
2017 West
Carlos
Widgets
2017 West
Tere
Balls
Value
50
12
120
100
30
145
34
80
89
56
45
55
60
20
30
75
50
70
90
25
100
Page 1