1. 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.) Using the data supplied in the Ch13_P2.xls file, complete the following seven problems: a. Identify the appropriate fact table components. (5) b. Identify the appropriate dimension tables. (5) c. Draw a star schema diagram for this data warehouse. (5) d. Identify the attributes for the dimension tables that will be required to solve this problem. (5) e. Using the spreadsheet generate a pivot table to show Sales by Product and by Region. The end user must be able to specify the display of Sales for any given Year. The sample oputput is shown below in Figure 1. f. Use problem (e) as your point of departure and add a second pivot table as in Figure 1 (on same spreadsheet) to show Sales by SalesPerson and by Region as in Figure 1 but the lower part. 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. (5) g. Create a 3D Bar Graph as part of your spreadsheet but give it a name CHART as in Figure 2 below. Your spreadsheet must therefore have at least three tabs. One for the raw data as given, one for the pivot tables and one for the CHART. The CHART must display as in Figure 2, the Sales for Salespeople within different Regions for each Product. (5) Figure 1: Pivot Tables showing Sales by Year AND Sales for Year and Product Figure 2: 3D- Bar Graph that shows Sales per Region for SalesPeople and Products