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.
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)
Data Connectors
