Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ITIS5431_OMBA – Assignment 1

Distributed: Week4 W1  2022

Due Date: September 26, 2022

Objective: The objective of this assignment is to introduce the student to the concept of the data model and how it is navigated for ad hoc querying. It is assumed that students will be familiar with basic data manipulation techniques.

Business Context: Sprott Fishing Supplies is a retailer of fishing supplies with a number of stores across Canada. Customers can sign up for a loyalty card for incentives and most due. This allows Sprott to track customer transactions. Customers can purchase goods by going to the store where inventory is on hand. They may wish to order on line or from a catalogue that is sent to customers once a year. Management has hired you as a consultant to perform analytics by looking at the transactional history and answering some of their questions.

Materials:

SPROTT FISHING DATA MODEL and DATA DICTIONARY.xls

SALES_FACT.SAS7BDAT

DATE_DIM.SAS7BDAT

** The other dimension tables (DIM) are found in the tabs of the xls file**

Instructions:

· Review the data model and data dictionary

· Read in the all of the dimension tables in the xls data file into EG

o FILE/IMPORT

Please ensure that the input format for the length and type of data is the same as the data dictionary when you read the files in.

· Import the SALES_FACT and DATE_DIM into EG.

o FILE/OPEN

· Answer the following questions by making the appropriate joins and aggregations.

· Please type your answers and show your results using SAS output or screen shots.

Please ensure that the input format for the length and type of data is the same as the data dictionary when you read the files in.

· Import the SALES_FACT and DATE_DIM into EG.

· Please submit your answers and EG project before due date using Brightspace

Questions

1. How many distinct transactions have been captured in the SALE_FACT? (2 marks)

2. How many unique customers have made purchases? (2 marks)

3. What is the average transaction size by Region? (2 marks)

4. What is the busiest day of the week in terms of transactions for the Toronto Store? (2 marks)

5. What is the total sales for the organization by Month & Year and by Region? (2 marks)

6. What was the product with the highest sales volume in terms of revenue in the western region in December 1997 (month=12)? (2 marks)

7. What is the average number of transactions by customer? (2 marks)

8. What is the breakdown in revenue by Channel and store for fishing line? (2 marks)

9. What is the most popular item in terms of volume for 1998? (2 marks)

10. Who are the top 10 customers in terms of revenue for 1997? (2 marks)

Marking Scheme – 1 mark for correct answer and 1 mark for showing your work