Create a data model using Power Query and Power Pivot in Excel to provide the information the managers would like. What trends are observed? Were the manager’s comments accurate?

⦁ Complete Activities 1 and 2 – see below. Upload your star schema diagram created in Activity 2 to Nexus by 1pm November 24.
Case Information:
Superstore is a warehouse type store that sells office products to both consumers and businesses across the United States. Products are categorized in one of three ways: 1) furniture, 2) office supplies and 3) technology. There are many products within each category. Products are sold online and in person at the warehouse locations. All product is purchased by a central purchasing department to take advantage of volume discounts.

Each warehouse has a manager who is responsible for sales and product storage and shipping. The following chart provides the region, warehouse location and warehouse manager’s name:

Region Location Manager
Central Houston, Texas Bob Miller
East New York City, New York Preet Hardi
South Monroe, North Carolina Wendy Ross
West Los Angeles, California Hunter Steller

During an annual managers’ meeting the following conversation took place between the managers:

Bob: Hi all. I think we have some key sales issues to discuss. It’s great that overall company sales are up, given office furniture and supplies industry is on a downward trend (see exhibit 1). For my region, it seems technology and furniture sales are down, while office supplies sales are up. Has this been your observation as well?

Hunter: I believe my technology sales have increased dramatically.

Preet and Wendy: We agree with Hunter, technology sales are up.

Bob: Hmm… I guess we better get some information on this to see what the trends are.

Wendy: How are your segment sales? It seems like our consumer and corporate sales are increasing greater than our home office segments. How about your regions?

Preet: Our consumer sales seem to have leveled off while other segments have increased.

Hunter: We are having a banner year in all segments.

Bob: It’s our corporate sales that seems to have dropped off. We can add this to the list of information we need. I think it would also be interesting to see volume trends, as even though my furniture and technology sales are down, I think volume is up.

Preet: Yes, volume information would be helpful.

Exhibit 1:
Office supplies and stationery store sales in the United States from 1992 to 2017 (in billion U.S. dollars)

https://www.statista.com/statistics/197722/annual-office-supplies-and-stationery-store-sales-in-the-us-since-1992/#:~:text=In%202020%2C%20U.S.%20office%20supplies,have%20shown%20a%20downward%20trend.

Required:
Create a data model using Power Query and Power Pivot in Excel to provide the information the managers would like. What trends are observed? Were the manager’s comments accurate?

Activity 1:
⦁ Open the provided cvs file (Superstore data modeling planning Raw Data.cvs). Review the data to gain an understanding of what data has been provided. Which data are unique identifiers to the record (line of information)? Which data is not unique? How is each data item related to other data in the record?
Process:
To explore the data in the cvs file:
⦁ Open file (Superstore data modeling planning Raw Data.cvs)
⦁ Save as an xlsx file with new name
⦁ Convert data to table format for easier sorting

Activity 2: Create a data model for the information.
Need to separate out “Dimensions” from Facts. Dimensions are any element that we may need to report on. Facts are any element we may aggregate for reporting. Best practice is to create 1 to many relationships where the 1 side is the dimension. For example, we want to be able to obtain information about product category. Since there are only 3 categories with many products within the category, we need to set up category as a separate dimension linked to product ID so that we can extract all products within a given category. Category is the one side of the relationship, Product Information is the many side of the relationship.

Using the Star Schema approach detailed in Chapter 5 of the Dzuranin readings, create a data model diagram and upload it to Nexus by 1pm on November 24.

Last Completed Projects