From Line-Level KPIs to Machine-Level Root Cause
In the previous article, we used PostgreSQL advanced aggregations to compare production lines, products and industrial KPIs.
One result immediately stood out: Line L02 was underperforming.
Its overall fulfillment rate was around 95.45%, while its reject rate was approximately 2.31%.
But a production line is not a single machine. L02 contains several machines, and a line-level KPI can hide the real source of the problem.
So the next question is much more interesting:
Which machine is actually responsible for the poor performance of L02?
This is where SQL becomes more than a reporting language. It becomes a tool for industrial root cause analysis.
1. Start with the Line-Level Problem
Before investigating individual machines, let's establish the baseline.
For L02, the production orders contain:
- 127,560 planned units
- 121,759 actual units
- 2,807 rejected units
- 95.45% fulfillment rate
- 2.31% reject rate
At this level, we know that something is wrong. But we still don't know where the problem originates.
The line contains several machines, so the next step is to move from line-level analysis to machine-level analysis.
2. The Machines Behind L02
The database identifies four machines on L02:
| Machine | Equipment | Type | Rated Power |
|---|---|---|---|
| M004 | CNC Machine B1 | CNC | 45 kW |
| M005 | CNC Machine B2 | CNC | 48 kW |
| M006 | CNC Machine B3 | CNC | 42 kW |
| M007 | Grinding Machine B4 | Grinder | 35 kW |
The machine master data tells us something important already: M005 is the largest-rated machine on the line.
But machine size alone does not make a machine problematic. We need operational evidence.
3. First Investigation: Production Performance
Let's aggregate production events by machine.
SELECT
m.machine_code,
m.machine_name,
SUM(pe.quantity_produced) AS produced_units,
SUM(pe.quantity_rejected) AS rejected_units,
ROUND(
100.0 * SUM(pe.quantity_rejected)
/ NULLIF(SUM(pe.quantity_produced), 0),
2
) AS reject_rate_pct,
ROUND(AVG(pe.cycle_time_sec), 2) AS avg_cycle_time_sec
FROM smart_factory.production_events pe
JOIN smart_factory.machines m
ON m.machine_id = pe.machine_id
JOIN smart_factory.production_lines pl
ON pl.line_id = m.line_id
WHERE pl.line_code = 'L02'
GROUP BY
m.machine_code,
m.machine_name
ORDER BY reject_rate_pct DESC;The result is our first important clue.
| Machine | Produced | Rejected | Reject Rate | Avg Cycle |
|---|---|---|---|---|
| M005 | 60,962 | 2,355 | 3.86% | 77.99 sec |
| M007 | 30,592 | 712 | 2.33% | 77.25 sec |
| M006 | 30,205 | 689 | 2.28% | 76.69 sec |
M005 immediately stands out.
Its reject rate is approximately:
- 69% higher than M007
- 69% higher than M006
This does not prove that M005 is the root cause. But it gives us a strong candidate.
4. Second Investigation: Downtime
Quality is only one dimension of machine performance. A machine can produce poor-quality parts, or it can stop production entirely.
Let's investigate downtime.
SELECT
m.machine_code,
m.machine_name,
COUNT(d.downtime_id) AS downtime_events,
ROUND(
SUM(
EXTRACT(EPOCH FROM
(d.end_time - d.start_time)
) / 60.0
),
2
) AS downtime_minutes
FROM smart_factory.downtime_events d
JOIN smart_factory.machines m
ON m.machine_id = d.machine_id
JOIN smart_factory.production_lines pl
ON pl.line_id = m.line_id
WHERE pl.line_code = 'L02'
GROUP BY
m.machine_code,
m.machine_name
ORDER BY downtime_minutes DESC;The result is even more revealing.
| Machine | Downtime Events | Downtime | Downtime / 1,000 Units |
|---|---|---|---|
| M005 | 10 | 511 min | 8.38 min |
| M007 | 18 | 363 min | 11.87 min |
| M006 | 5 | 112 min | 3.71 min |
Here we discover something important.
M007 has the highest downtime intensity per 1,000 units.
So if we simply ranked machines by one KPI, we could easily choose the wrong machine.
This is why industrial root cause analysis requires several independent signals.
5. The Real Clue: Downtime Reason Codes
Let's look deeper into the downtime categories.
SELECT
m.machine_code,
d.reason_category,
d.reason_code,
COUNT(*) AS events,
ROUND(
SUM(
EXTRACT(EPOCH FROM
(d.end_time - d.start_time)
) / 60.0
),
2
) AS downtime_minutes
FROM smart_factory.downtime_events d
JOIN smart_factory.machines m
ON m.machine_id = d.machine_id
JOIN smart_factory.production_lines pl
ON pl.line_id = m.line_id
WHERE pl.line_code = 'L02'
GROUP BY
m.machine_code,
d.reason_category,
d.reason_code
ORDER BY
m.machine_code,
downtime_minutes DESC;For M005, the result is striking.
| Machine | Failure | Events | Minutes |
|---|---|---|---|
| M005 | SPINDLE-VIB | 5 | 372 |
| M005 | DRV-OVR | 1 | 38 |
| M005 | ELEC-01 | 1 | 14 |
Five separate mechanical failures were recorded with the same SPINDLE-VIB code.
The description is also explicit: spindle vibration above normal threshold.
This changes the investigation completely.
We are no longer looking at a generic "bad machine". We have identified a recurring mechanical failure pattern.
6. SQL Can Connect the Operational Signal to Maintenance
Now we can investigate whether maintenance records contain a corresponding event.
SELECT
m.machine_code,
me.maintenance_type,
me.start_time,
me.end_time,
me.description,
me.parts_cost,
me.labor_cost
FROM smart_factory.maintenance_events me
JOIN smart_factory.machines m
ON m.machine_id = me.machine_id
WHERE m.machine_code = 'M005'
ORDER BY me.start_time;The result provides the strongest evidence in the investigation.
| Date | Type | Maintenance | Cost |
|---|---|---|---|
| Feb 10 | Preventive | Spindle lubrication and vibration inspection | $500 |
| Apr 28 | Corrective | Spindle bearing replacement after repeated vibration alarms | $2,470 |
| May 12 | Preventive | Drive cooling system inspection and filter replacement | $400 |
The database itself therefore tells a coherent industrial story:
Vibration alarms → repeated spindle failures → corrective maintenance → bearing replacement.
7. A Before-and-After Investigation
We can go one step further.
The corrective spindle maintenance occurred on April 28, 2026. Let's compare the production behavior before and after the intervention.
| Period | Produced | Rejected | Reject Rate | Avg Cycle |
|---|---|---|---|---|
| Before corrective maintenance | 37,936 | 1,523 | 4.01% | 76.69 sec |
| After corrective maintenance | 22,472 | 788 | 3.51% | 79.27 sec |
The reject rate improved from approximately 4.01% to 3.51%.
More importantly, the downtime history gives us an even stronger signal:
- Before the corrective intervention: 5 SPINDLE-VIB events
- Mechanical downtime associated with them: 372 minutes
- After the intervention: 0 SPINDLE-VIB events
This is a strong operational signal that the spindle problem was addressed. However, it would be incorrect to claim that the maintenance completely solved the machine's performance problem.
The average cycle time actually increased after the intervention. Industrial data analysis requires this kind of nuance.
8. Energy: Another Perspective
We can also compare energy consumption.
| Machine | Energy | Energy / Unit | Avg Power |
|---|---|---|---|
| M005 | 48,470.4 kWh | 0.795 kWh/unit | 30.81 kW |
| M006 | 39,496.7 kWh | 1.308 kWh/unit | 24.70 kW |
| M007 | 32,657.3 kWh | 1.068 kWh/unit | 20.63 kW |
Interestingly, M005 is not the worst machine in terms of energy consumed per unit.
M006 actually consumes more energy per produced unit.
This is another important lesson:
There is rarely one KPI that identifies the root cause of an industrial problem.
9. Building a Machine Investigation Query
We can now combine the independent KPI calculations into a single machine-level diagnostic query.
The important principle here is to calculate each metric at its own grain before joining the results.
WITH production AS (
SELECT
machine_id,
SUM(quantity_produced) AS produced_units,
SUM(quantity_rejected) AS rejected_units,
AVG(cycle_time_sec) AS avg_cycle_time
FROM smart_factory.production_events
GROUP BY machine_id
),
downtime AS (
SELECT
machine_id,
COUNT(*) AS downtime_events,
SUM(
EXTRACT(EPOCH FROM
(end_time - start_time)
) / 60.0
) AS downtime_minutes,
COUNT(*) FILTER (
WHERE reason_code = 'SPINDLE-VIB'
) AS spindle_vibration_events,
SUM(
EXTRACT(EPOCH FROM
(end_time - start_time)
) / 60.0
) FILTER (
WHERE reason_code = 'SPINDLE-VIB'
) AS spindle_vibration_minutes
FROM smart_factory.downtime_events
GROUP BY machine_id
),
energy AS (
SELECT
machine_id,
SUM(energy_kwh) AS energy_kwh,
AVG(power_kw) AS avg_power_kw
FROM smart_factory.energy_consumption
GROUP BY machine_id
)
SELECT
m.machine_code,
m.machine_name,
p.produced_units,
p.rejected_units,
ROUND(
100.0 * p.rejected_units
/ NULLIF(p.produced_units, 0),
2
) AS reject_rate_pct,
ROUND(p.avg_cycle_time, 2)
AS avg_cycle_time_sec,
d.downtime_events,
ROUND(d.downtime_minutes, 2)
AS downtime_minutes,
d.spindle_vibration_events,
ROUND(d.spindle_vibration_minutes, 2)
AS spindle_vibration_minutes,
ROUND(
e.energy_kwh
/ NULLIF(p.produced_units, 0),
3
) AS energy_per_unit
FROM smart_factory.machines m
LEFT JOIN production p
ON p.machine_id = m.machine_id
LEFT JOIN downtime d
ON d.machine_id = m.machine_id
LEFT JOIN energy e
ON e.machine_id = m.machine_id
WHERE m.line_id = 2
ORDER BY
reject_rate_pct DESC;10. What Did We Actually Discover?
At the beginning of the investigation, we only knew that L02 was underperforming.
After drilling down into machine-level data, we discovered several different signals.
| Signal | Finding | Interpretation |
|---|---|---|
| Reject rate | M005 = 3.86% | Worst among L02 production machines |
| Total downtime | M005 = 511 min | Highest absolute downtime |
| Mechanical failures | 5 SPINDLE-VIB events | Repeated spindle vibration |
| Maintenance | Bearing replacement | Direct maintenance response to vibration alarms |
| Energy/unit | 0.795 kWh/unit | Not the worst energy efficiency |
11. The Root Cause Is More Interesting Than the Ranking
If we simply ranked machines by downtime, M005 would be the obvious target.
If we ranked them by downtime per 1,000 units, M007 would become the priority.
If we ranked them by energy efficiency, M006 would look worse.
This is precisely why real industrial analytics cannot rely on a single ranking.
The stronger conclusion comes from correlated evidence:
M005 combines the highest reject rate, the highest absolute downtime, repeated spindle-vibration failures, and a subsequent corrective maintenance operation specifically involving spindle bearing replacement.
That makes M005 / CNC Machine B2 the strongest root-cause candidate for the L02 performance problem.
This is not merely a SQL exercise. It is a simplified example of how an industrial data analyst can move from a KPI deviation to a concrete maintenance hypothesis.
12. What SQL Taught Us
This investigation introduced a very important principle for industrial databases:
Do not join everything together and aggregate afterward. Calculate each KPI at the correct grain, then combine the results.
Production events have one grain.
Downtime events have another.
Energy measurements have another.
Maintenance events have another.
Joining them directly can multiply rows and produce completely incorrect KPIs.
Using separate CTEs allows us to preserve the analytical grain of each source.
13. From SQL Query to Industrial Decision
Our investigation can now be summarized as an industrial workflow:
Line KPI → Machine KPI → Downtime → Failure Code → Maintenance History → Root Cause Hypothesis
This is one of the most useful ways to think about SQL in a Smart Factory.
SQL does not repair the machine.
But SQL can help us identify where to look first.
14. The Investigation Continues
We now have a strong hypothesis: M005 and its spindle system deserve immediate attention.
But another question remains.
Can we detect machine degradation automatically before the next failure occurs?
Instead of waiting for a failure code such as SPINDLE-VIB, could
we use historical production, downtime and energy data to identify an
abnormal machine pattern early?
That takes us from descriptive analytics toward anomaly detection and predictive maintenance.
Next: We will use PostgreSQL to investigate machine performance over time and look for abnormal behavior before a failure becomes obvious.
No comments:
Post a Comment