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 Requirement
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: Draw a sketch of an appropriate star/snowflake data warehouse design (i.e., the relational logical model). Indicate which data elements will be in each of the dimension and fact tables. Specify any necessary keys. Explain/justify your design, paying particular attention to the structure of the facts. [8 marks]
Answer: Logical Design of the Sales data warehouse shown below