Coffee Merchant Enterprises (CM) and Home Warranty Corporation (HWC) Data Warehouse OLAP database

Coffee Merchant Enterprises (CM) and Home Warranty Corporation (HWC) Data Warehouse OLAP database

The purpose of this assignment is to promote students analytical skills in analyzing a case scenario and utilizing the given information and resources to design an ER diagram and develop an OLAP database.

1. Case Scenario
Coffee Merchant Enterprises (CM) sells coffee from a variety of different countries to consumers in the US.  Their sales are all on-line.  Their OLTP database for handling their day-to-day operations is shown in the ER Diagram below:

CM wants to make data more available to their marketing analysts so that they can develop more effective marketing programs.  They want to accomplish this goal without slowing down the performance of their existing OLTP system.  They have decided to create a Data Warehouse to provide the information that the marketing analysts need.  Your task is to design the database for the Data Warehouse.
The marketing analysts have asked for the ability to see sales data by product (inventory), consumer, employee who placed the order, and by various date variables including date, week, quarter, year, day of the week, and season.  They do not need to see every sales but would need to see product sales summarized for each product on a daily basis.

2. Case Scenario
Consider the following ER Diagram for the OLTP database you designed from Home Warranty Corporation in the Case Project for Module 11.  Your design may have differed from the one below, but this is the design that HWC decided to use.

HWC has decided to develop a Data Warehouse so that they can analyze costs to assist in making a number of business decisions.  For example, they want to determine if certain customers are costing more because they may be too hard on their appliances or if certain appliances that they cover tend to break down more than others, thus incurring more cost.  Your task is to design the database for the Data Warehouse. The business managers have asked for the ability to see costs by customer, appliance, employee, and by various date variables including date, week, quarter, year, day of the week, and season.  The need to see this cost information for every Service Call and need know the labor cost, material cost, and sum of both.