🏗️ Architecture in brief

🚚 Logistics Model at a glance

• Network Topology
  • 20 specialized suppliers mapped to key manufacturing clusters
    • Tiruppur (Cotton Apparel)
    • Kanpur (Leather Goods)
    • Jaipur (Jewellery)
  • 3 regional hubs each specializing in a product category: HUB-DEL (Apparel), HUB-BOM (Footwear), HUB-BLR (Accessories)
  • ~100 stores (major/minor) assigned to a “home” hub for fulfillment
  • Suppliers mapped to key Indian manufacturing clusters (Tiruppur, Kanpur, Jaipur) by specialty
• Demand Generation
  • ~40 daily orders (Poisson process) over Jan 2022-Jun 2025
  • Multi-SKU orders (1-5 lines), 3-10 day delivery window
  • Reorder Point (ROP) = lead-time demand + safety stock (95% service level)
  • Lead times sampled 3-14 days (μ=7, σ=2)
  • Single inbound batch per hub when any SKU falls below ROP
• Shipment & Transportation Logic
  The model simulates two transport tiers:
  
  Inter-Hub Shipments
  • Shipments consolidated and dispatched by truck between hubs. Transit time: calculated by distance (e.g., DEL-BOM: 1400 km, DEL-BLR: 2100 km, BOM-BLR: 1000 km).
  • Cost: ₹300 + ₹15/km + ₹8·chargeable_kg, with a 1.2x multiplier for inter-hub bulk shipments.
  • Truck capacity: 10,000 kg or 60 m³, minimum load threshold 30%. Express shipments bypass minimum load threshold.
  Final-Mile Shipments
  • Direct store shipments: local pick if all lines from same hub or cross-docked consolidation.
  • Delivery lead time: 1-2 days for distances ≤100 km, up to 2 days for longer distances.
  • Cost uses the same cost function without the inter-hub multiplier.
• • Returns are simulated with a 3-6% chance per line, random quantity, and category-based reason probabilities.
  • Backorders: Unfulfilled order quantities are tracked as backorders, with status and date.

📂 Output of Synthetic Data Generator

• Dimensions:
  • dim_hubs.csv
  • dim_stores.csv
  • dim_products.csv
  • dim_suppliers.csv
• Facts:
  • fact_orders.csv
  • fact_shipments.csv
  • fact_inventory_snapshot.csv
  • fact_returns.csv
  • fact_inbound_shipments.csv
  • fact_backorders.csv
• Link: link_shipment_orders.csv
• Logs: SupplyChain_Data/data_gen.log

📊 Power BI Dashboard - Analytics Layer

• 🔧 Data Import
  • Open Power BI Desktop
  • Use Get Data → Web
  • Paste GitHub raw URL for each CSV and import data to power BI power Query
  • Promote headers and change data type of columns as required
• 🗂️ Model View

📐 DAX Measures for KPI and Visualization

• Avg Inbound Lead Time
  Calculates the average number of days between expected and actual arrival dates for inbound shipments:

  Avg Inbound Lead Time =
  AVERAGEX(
      fact_inbound_shipments,
      DATEDIFF(
          fact_inbound_shipments[Expected_Arrival_Date],
          fact_inbound_shipments[Actual_Arrival_Date],
          DAY
      )
  )

  Purpose:
  Helps monitor supplier performance and logistics efficiency.
  Supports KPI dashboards for lead time analysis.
  
  
• On-Time Delivery Rate
  Percentage of shipments delivered on time:

  OnTimeDeliveryRate =
  DIVIDE(
      COUNTROWS(
          FILTER(fact_inbound_shipments, fact_inbound_shipments[Delay Days] = 0)
      ),
      COUNTROWS(fact_inbound_shipments)
  )

  Purpose:
  Monitors delivery performance and supplier reliability.
  
  
• Average Product On Hand per SKU
  Average quantity on hand for each SKU:

  Average Product On Hand per SKU =
  AVERAGEX(
      VALUES(fact_inventory_snapshot[SKU]),
      CALCULATE(AVERAGE(fact_inventory_snapshot[Quantity_On_Hand]))
  )

  Purpose:
  Supports inventory optimization and stock level analysis.
  
  
• Qty On Hand (Latest Snapshot)
  Total quantity on hand for the most recent snapshot:

  Qty On Hand (Latest Snapshot) =
  VAR MaxDate = LASTDATE('fact_inventory_snapshot'[Snapshot_Date])
  RETURN
      CALCULATE(
          SUM('fact_inventory_snapshot'[Quantity_On_Hand]),
          'fact_inventory_snapshot'[Snapshot_Date] = MaxDate
      )

  Purpose:
  Provides real-time inventory status for dashboards.
  
  
• Order Fulfillment Rate %
  Percentage of ordered quantity shipped:

  Order Fulfillment Rate % =
  DIVIDE(
      SUM('fact_orders'[Quantity_Shipped]),
      SUM('fact_orders'[Quantity_Ordered]),
      0
  )

  Purpose:
  Tracks order fulfillment efficiency and customer service level.
  
  
• Total Backordered Quantity
  Total quantity ordered but not shipped:

  Total Backordered Quantity =
  SUMX(fact_orders, fact_orders[Quantity_Ordered] - fact_orders[Quantity_Shipped])

  Purpose:
  Identifies backlog for fulfillment planning.
  
  
• Return Rate %
  Percentage of shipped items returned:

  Return Rate % =
  DIVIDE(
      SUM('fact_returns'[Quantity_Returned]),
      SUM('fact_orders'[Quantity_Shipped]),
      0
  ) * 100

  Purpose:
  Tracks product quality and customer satisfaction.
  
  
• Avg Delivery Lead Time (Inter-Hub)
  Average days for inter-hub shipments:

  Avg Delivery Lead Time (Inter-Hub) =
  AVERAGEX(
      FILTER('fact_shipments', 'fact_shipments'[Leg_Type] = "Inter-Hub"),
      DATEDIFF('fact_shipments'[Dispatch_Date], 'fact_shipments'[Arrival_Date], DAY)
  )

  Purpose:
  Measures hub-to-hub transfer efficiency.
  
  
• On-Time Delivery % (Final-Mile)
  Percentage of final-mile deliveries completed on time:

  On-Time Delivery % =
  VAR FinalMileDeliveries =
      ADDCOLUMNS(
          FILTER(link_shipment_orders, RELATED(fact_shipments[Leg_Type]) = "Final-Mile"),
          "ArrivalDate", RELATED(fact_shipments[Arrival_Date]),
          "RequiredDate", RELATED(fact_orders[In_Store_Required_Delivery_Date])
      )
  VAR OnTimeDeliveries =
      FILTER(FinalMileDeliveries, [ArrivalDate] <= [RequiredDate])
  RETURN
      DIVIDE(COUNTROWS(OnTimeDeliveries), COUNTROWS(FinalMileDeliveries), 0)

  Purpose:
  Tracks last-mile delivery performance.

💡 Key Insights from Dashboard

• Where are orders growing or softening by category and city, and how does this affect store-level demand planning and assortment decisions across the network
• What is on-time performance by shipment tier (inter-hub vs final-mile) and how does it trend by month, origin-destination, and mode, guiding carrier and lane optimizations ?
• Which destinations, hubs, or lanes drive the highest transportation cost, and how do distance and chargeable weight explain cost variance for targeted savings ?
• How do current on-hand levels and average on-hand per SKU trend relative to demand, and which categories or hubs risk stockouts under the current ROP and target policies ?
• What is the inbound supplier lead-time profile and on-time rate by specialty, and how do delay distributions impact hub availability and service levels downstream ?
• What are the top return reasons and categories, and how do return rates move over time to inform product, fit, color, or quality actions with suppliers ?
• How well do shipped quantities align with orders across categories and stores, and where are fulfillment gaps indicating inventory or sourcing constraints to address ?