The Schema Above: Sale Business Process Of Healthy Food

The schema above is the dimensional model for sale business process of Healthy Food Company. This company sales several types of healthy food on the internet. The company has two business requirements. First, the company would like to analyze daily quantity-on-hand inventory levels by products. In order to meet this requirement, I designed the dimensional model by using the periodic snapshot approach so the company could see the quantity-on-hand of each product at the end of the day. This design has two dimension tables and one fact table: DimDate, DimProducts, and InventoryFact. There is no need to have the DimStores in the schema because the company has only one store at this moment.
Second, the company also would like to analyze the audit trail of shipments of products to customers. This process is involved with the customer the products is being shipped to, what product and in what quantity and different shipping categories (type and container). In order to meet this requirement, I designed the dimensional model by using junk dimension technique. Since the shipment has two categories, I combined both categories into one dimension table, instead of having one dimension table for category of type and another …show more content…
In order meet this business requirement, I constructed the factless fact table (Sales Rep-Customer Assignment Fact) to capture the sales rep coverage and role-playing dimension technique to improve the query performance. The fact table would provide a complete map of the historical assignment of sales reps to customers, even if some of the assignments never resulted in a sale. In order to identify rep assignments that have not yet resulted in order activity, we can compare the Sales Rep-Customer Assignment Fact table with the Order Line Transaction Fact table by using set operation in