High Level Design

Overview

When arranging merchandise in a warehouse it is best to do so in a way that can reduce the time it takes to collect and move the merchandise in the future. In addition, it is important to allocate enough inventory on the shelves so that the minimal amount of replenishes is necessary. This can be achieved by sorting the products in an optimal manner and allocating enough shelves for heavy products which require a considerable amount of replenishes per work day. Implementing this BI solution can improve worker performance in the warehouse and thus help reduce labor costs and redundant workforce.

Dashboard Example (sample data)

Click on the image to open and interact with the dashboard:

Warehouse Optimization - Manufacturing Dashboard

 

Goals

Reduce labor costs by sorting merchandise in an optimal manner.

Objectives

  • Reduce collecting time
  • Allocate correct number of shelves per products by minimizing inventory refresh process

KPI Architecture

Objectives KPIs Measures Data Source
Reduce collecting time Avg collecting time avg([OrderID],sum([DistancePerRecord]))/60) Fact_WorkLog
Max collecting time max([OrderID],sum([DistancePerRecord]))/60 Fact_WorkLog
Avg number of orders per day ([# of unique OrderID])/[# of unique Days in Dates] Fact_WorkLog, DimActions, DimDate
Max number of orders per day MAX([Days in Dates],[# of unique OrderID]) Fact_WorkLog
Avg Number of workers per shift Avg number per shift Fact_WorkLog
Max number of workers per shift (([Max number]*[Max time])/60)/[# of unique WorkerID]/3 Fact_WorkLog
15 most ordered products avg([Distance Per Record]) Fact_WorkLog, DimProduct
15 least ordered products avg([Distance Per Record]) Fact_WorkLog, DimProduct
Allocate correct number of shelves per products by minimizing inventory refresh process Top 15 products with highest daily average replenish rate [Total TotalWeightPerRecord]/[# of unique Days in Dates]/[Max MaxWeightLimit_Kilos] Fact_WorkLog, DimProduct, DimShelve, DimDate
Current shelf status (Operational data) ShelveID, CategoryName, shelve status act_WorkLog, Dimproduct, DimShelve, DimDate

Data Modeling (Elasticube Design)

Suggested data model for warehouse operations optimization