Question

A food manufacturing company needs a data mart to summarize facts about orders to move goods. Some orders transfer goods internally, some are purchases from vendors, and some are returns of goods from customers. The company needs to treat customers, vendors, plants, and storage locations as distinct dimensions that can be involved at both ends of a movement event. For each type of destination or origin, the company wants to know the type of location (i.e. customer, vendor, etc.) name, city, and state. Facts about each movement include dollar volume moved, cost of movement, and revenue collected from the move (if any, and this can be negative for a return). Design a star-type schema to represent this data mart. Hint- after you design a typical star schema, think about how you might simplify the design through the use of generalization.

Answer 1

This star schema is representing all four entities/dimension Customers, Vendors, Plants and Storage Units involved to move transfer goods. Each entity has sub entities such as Location(City, State) which is generalized. Datamart for the food also represent the volume of dollar moved, Cost of Moving and Revenue Collected. Revenue will be negative if Customer files a return, that information is presented in Customer entity/dimension.