Warehouse Profiling Case

Warehouse Profiling

I.

Problem Statement

A warehouse is a complicated and busy place and it can be hard to get an accurate sense of what is happening. Warehouse activity profiling is the careful measure and statistical analysis of warehouse activity. This is a necessary first step to almost any significant warehouse project: Understand the customer orders, which drive the system.
Two types of data are applied to profile the warehouse: data pertaining to each sku and data pertaining to customer orders. The information of each item is stored as:
Sku_id
Desc
Vendor Zone Aisle Bay Sell_unit Pack_1 Pack_2 Pack_3 DOFT
ACC11038 COV,PSBD,2.75"CC,5.5X8.5,RD
ACC
A
41
16
EA
EA
BX
950402
ACC15004 FLDR,PSBD,LTR,1DIV,W/FAS,RD
ACC
A
41
16
EA
EA
BX
950402
3. Which zones are generating the greatest physical volume of product?
Query_1
SELECT lines.order_id, lines.sku_id, lines.order_qty, skus.zone
FROM lines, skus
WHERE lines.sku_id=skus.sku_id;
Query_2
SELECT zone, sum(order_qty)
FROM Query_1
GROUP BY zone;

Since there is no information about the exact volumes of the products, we only obtained the order quantity for each zone, which are shown in the following table and graph. Again, we can see that zone A and B contain most of the order quantities.
Zone
A
B
C
D
E
G

Order_Quantiry
378737
439433
162199
88210
15257
115970

450000
400000
350000
300000
250000
200000
150000

A
B
C
D
E
G

100000
50000
0

4. How many lines per order are generated by the customers?
SELECT Lines.Order_id, Count(Order_id) AS Num_of_Lines
FROM Lines
GROUP BY Lines.Order_id
ORDER BY Count(Order_id) DESC;

3

We found the top 5 order_id with most lines as the following table:
Order_id
22767
40455
31829
26458
38674

Num_of_Lines
189
182
170
165
Order_Size
1
2
3
4
5

Oder_Num
34276
8404
3960
2337
1585

7. What percentage of orders is completed within zones A and B?
Entirely zones C and D? Entirely zone E? Entirely within zone G?
Query_7
TRANSFORM Count(ques2_1.sku_id) AS [The Value]
SELECT ques2_1.order_id, Count(ques2_1.sku_id) AS [Total Of sku_id]
FROM ques2_1
GROUP BY ques2_1.order_id
PIVOT ques2_1.zone;

5

10

>10

Query _AB
SELECT ques7.order_id, ques7.[Total Of sku_id], ques7.A, ques7.B, ques7.C, ques7.D, ques7.E, ques7.G
FROM Query_7
WHERE (((ques7.C) Is Null) AND ((ques7.D) Is Null) AND ((ques7.E) Is Null) AND ((ques7.G) Is Null));
(Other queries are carried out in the same way.)

The percentages of orders entirely in some zones are distributed in the following graph, from which we can see that zone A and B contain most orders entirely within one area, which occupies more than 50% of all orders.

0.6

0.16
0.5
0.14
0.4
0.12
0.3
0.1

0.08
0.2
0.06
0.1
0.04

A and B
C and D
A and B
E
C and D
G
E

G

0.020
0

8. Are there any items that contain many selling units per storage unit but are picked significantly by storage unit?
Since we don’t have the number of items in each storage unit, it is not possible to figure out this problem.

III.