Reference no: EM132177383

Victoria Ephanor manages a small product distribution company. Because the business is growing fast, Ms. Ephanor recognizes that it is time to manage the vast information pool to help guide the accelerating growth. Ms. Ephanor, who is familiar with spreadsheet software, currently employs a small sales force of four people. She asks you to develop a data warehouse application prototype that will enable her to study sales figures by time, location, sales agent, and product.

Describe the concept and purpose of the star schema. What does a Fact represent in a star schema? What doDimensions represent in a start schema?

Based on data supplied in the table below to complete the following problems:

Time	Location	esAgent	Product	Value
2015	East	Carlos	Erasers	50
2015	East	Tere	Erasers	12
2015	North	Carlos	Widgets	120
2015	North	Tere	Widgets	100
2015	North	Carlos	Widgets	30
2015	South	Victor	Balls	145
2015	South	Victor	Balls	34
2015	South	Victor	Balls	80
2015	West	Mary	Pencils	89
2015	West	Mary	Pencils	56
2014	East	Carlos	Pencils	45
2014	East	Victor	Balls	55
2014	North	Mary	Pencils	60
2014	North	Victor	Erasers	20
2014	South	Carlos	Widgets	30
2014	South	Mary	Widgets	75
2014	South	Mary	Widgets	50
2014	South	Tere	Balls	70
2014	South	Tere	Erasers	90
2014	West	Carlos	Widgets	25
2014	West	Tere	Balls	100

b. Identify the appropriate fact(s) and dimension(s) from the data. (By definition, Facts in a start schema are numeric measurements (values) that represent a specific business aspect or activity, and dimensions are qualifying characteristics that provide additional perspectives to a given fact.)

c. Based on the identified fact(s) and dimension(s), draw a star schema diagram. Remember that you need to come up with, and write down, appropriate attributes for each of the tables. Additionally, you will need to specify foreign keys and types of relationships between tables in the diagram. See Figure 13.11 on page 585 for an example of start schema diagram.

d. Using a Microsoft Excel spreadsheet (or any other spreadsheet capable of producing pivot tables), generate a pivot table to show the total sales values by time (as rows) and by products (as columns). The end user must be able to specify the display of sales for any given location (location as a filtered item in the pivot table). (The sample output is shown in the first pivot table in Figure P13.2E on page 614.)
Here is a link shows you how to create a pivot table: Create a pivot table in Excel 2013.

e. Using Problem d as your base, add a second pivot table to show the total sales values by product (as rows) and by location (as columns). The end user must be able to specify sales for a given time or for all times and for a given sales agent or for all sales agents (time and sales agent as filtered items in the pivot table).

f. Describe your major findings based on reviewing the two pivot tables. What are the suggestions you would provide Victoria to help her make informed business decisions?

Reference:

Excelblogger. (2012, Aug 1). Create a pivot table in Excel 2013 [Video file] [8 min 12 sec].