Data warehouse (DW) and business intelligence (BI) applications are playing an increasingly important role in enabling managers to make high-quality business decisions to achieve the company’s business goals.
Overview of Retail Case Study
Consider the data warehouse of a retail company that contains information on 100 grocery stores in the United Arab Emirates. Each store has a variety of departments, including grocery, frozen food, dairy, meat, fruits and vegetables, bakery, flowers, and health/beauty products. Each store has approximately 60,000 individual products, called stock keeping units (SKUs), on its shelves. Data is collected at several points of interest in a grocery store. Some of the most useful data is captured at cash registers when customers purchase products. The point-of-sale (POS) system scans the bar codes of products at the register. Other data is captured at the back door of the store, where vendors deliver their goods.
In the grocery store, management handles the logistics of ordering, storing and selling products while maximizing profits. The bottom line is to charge as much as possible for each product, reducing product procurement costs and overhead while attracting as many customers as possible in a competitive environment.
Business Requirements
According to Ralph Kimball’s four-step dimensional design process (see textbook), the first step in designing and developing DW/BI projects should focus on the business process that is most important to business users. With this in mind, we have adopted some of the functional requirements identified during the business process analysis as follows:
• The user must be able to access data from various enterprise applications in a single location and in an easy-to-edit format.
• The user should be able to analyze product sales geographically over time. This is done based on actual sales on a monthly basis.
• The company should be able to calculate the profit margin on monthly sales for subscribed customers by different segments.
• The user would like to analyze the sales of their product by geography, by store, and by different outlets.
• The business would like to calculate the cost of sales and profit of subscribed customers on an annual basis by package, demographics, and store.
• The business user will want to classify customers based on their performance and loyalty.
• The business user will be able to determine vendor performance based on product.
• The system should be able to display and print the figures and charts. The system should be able to generate the report in different formats that are useful for the users.
Question 1: Based on the detailed description of the business case study and the business requirements above, you need to create logical and physical data models or data marts for the data warehouse. [5 marks]
a. Declare the Grain [5 marks]
b. Identify the Dimensions [5 marks]
c. Identify the Facts [5 marks]