Mid Semester Exam Case Description Data Warehouse for Crocoroos’s Online Retail Store (CORS)
Crocoroos’s Online Retail Store (CORS) is a specialized online retailer selling miscellaneous merchandise ranging from toys, video games, electronics, and office items to home and décor, kitchen items, and clothing. As an online retailer, setting up and maintaining a dedicated online e- commerce platform is expensive and attracting potential customers to a dedicated web portal is not only challenging but also time-consuming, expensive, and lacks certainty in terms of sales volume. Thus, CORS operates its business through Amazon’s online sales portal. CORS relies on bulk buying merchandise from various sellers which minimizes the purchasing costs. To maintain its profitability, CORS must focus on products that sell faster or generate higher profit margins. It also needs to understand its customers and suppliers well enough to make sound business decisions. The CORS has a simple business model based on historical sales, and it predicts popular items and purchases these items in a bulk from various sellers.
Once the buyer places a purchase order for its products on Amazon, it generates data periodically and makes it available to the company stores as database files and CSV flat files. To improve the performance and optimize the sales process, CORS business depends on data provided by Amazon. Specifically, the store needs to understand the source of the revenue, identify main customers, also understand the geographical distribution of sellers and customers, which is vital in setting up cost- efficient warehouses. However, as the data comes from transactional systems and is available in multiple formats, it is difficult to analyze data in current formats.
Henry Wang, the owner of CORS, recently attended a business analytics conference where he heard that the data warehouse helps companies to utilize organizational data for managerial decision-making. Henry is seeking to hire a business analyst who could design and develop a data warehouse using the data generated from the sales process and help him understand his business better. As a UQ graduate with a Business analytics course, Henry has shortlisted you as a potential candidate, and as a part of the interview process, he has asked you to prepare a data warehouse and demonstrate its ability to fetch data from analytical queries.
Your task starts with developing a suitable data model for the data warehouse by integrating the data using the SSIS process (cleaning and transforming data), creating the data warehouse, loading integrated data into the data warehouse using SQL, and running the analytical queries (running queries as a part of the mid-semester exam).
Before developing a data warehouse and answering the analytical queries, you should first understand the sales process based on the information provided. The ER diagram of the sales system is your starting point. As you might already know, answering analytical queries requires transforming transactional data to dimensionally modelled data. Your first task is to prepare a dimensional model from ER diagram. Your dimensional model should explain the data granularity, fact measures, and dimensions. Once you have created the fact table, you need to integrate the data using Microsoft SQL Integration services (i.e., SSIS project in visual studio) and load the data in the data warehouse. Once the data warehouse is created and integrated data is loaded, you can run the sample SQL queries to generate the insight.
Code Help, Add WeChat: cstutorcs
Mid Semester Exam Case Description
Additional Information:
• All the information you need for the Mid-semester exam is available under the folder Mid-semester Exam Task in the assessment Section.
• The ER model of the sales system is given as a separate file “CORS_Sales_ER”.
• Sales ER Model (Definition of attributes are provided in the data dictionary in the
Assignment folder on LMS).
• The Sales Database is provided to you as a backup file “CORS_sales_db” that you need
to load to the database systems.
• Some of the data related to sales systems are yet to be imported to the database systems
and are available as CSV files. You need to integrate data from these files if needed to complete your solution.
Analytical Queries for Practice (Sample Questions)
Q1. Who are the five most profitable customers in Australia? Q2. Who are the five most profitable customers in New Zealand? Q3. What is the total profit for year 2020?
Q4. How much is the total revenue from New Zealand
Q5. How many orders are placed from Queensland?
CS Help, Email: tutorcs@163.com