LogiFreight's Data Warehouse Dimensional Design

LogiFreight is a logistics company handling 500,000 shipments daily across a network of 120 distribution centers in North America. The company is building its first enterprise data warehouse to enable analytics on shipping performance, route optimization, and customer profitability. The current reporting relies on direct queries against transactional databases, causing performance degradation during business hours. The data modeling team must design a dimensional model that supports analysis across multiple business processes: order fulfillment, shipment tracking, delivery performance, and customer billing. Key analytical questions include average delivery time by route and carrier, cost per shipment by weight class and distance, customer profitability by segment, and seasonal demand patterns. Complicating factors include that shipment status changes multiple times (picked up, in transit, at hub, out for delivery, delivered, exception), dimensions like customer and route change slowly over time, and the company is acquiring a European logistics firm that will need to be integrated within 18 months. The design must handle both current operations and the upcoming international expansion.