Describe the concept and purpose of the star schema

Assignment Help Computer Engineering

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].

Reference no: EM132177383

Questions Cloud

How many page frames are there : In a virtual memory system with the following parameters: Physical Memory is 2GB. Virtual Memory is 4GB. Page Size is 4KB

Explain the difference between page fault and valid bit : A virtual memory system is one that mechanizes the process of overlay generation by performing a series of mapping operations.

Write a program that takes as input two values : Write a program that takes as input two values: the first represents the cost of an item, and the second is the amount of money being paid for it.

The schematic generated on rtl analysis : In VHDL, What is the difference between the schematic generated on RTL analysis and schematic generated after synthesis?

Describe the concept and purpose of the star schema : 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?

How you would find the area of atriangle defined : Write C++ code or a mathematical derivation that shows how you would find the area of atriangle defined by the vertices a, b, and c.

Criticisms regarding the neoclassical theory : What are some criticisms regarding the neoclassical theory based on this following statement:

Indicator of a standard of living : With the unemployment rate being as low as it is, do people live well? (is it a good indicator of a standard of living)

Hhi for the industry : Suppose an industry has 4 firms each with 20% of sales, and 4 firms each with 5% of sales. The HHI for the industry is?

User Account

All Pages