I’ve seen this exact scenario multiple times. Here’s what you need to check:
SELECT event_type, COUNT(*) as late_events,
AVG(DATEDIFF(hour, event_time, created_time)) as avg_delay
FROM production_events
WHERE created_time > event_time + INTERVAL '1' HOUR
GROUP BY event_type;
This query will show you which event types are being logged late and by how much.
For the OEE formula consistency issue, you need to ensure both calculation engines are using identical formulas and data validation rules. Here’s the complete breakdown:
1. OEE Formula Consistency Between Engines:
Your real-time and batch engines must use identical calculation logic. Check the configuration files for both:
- Real-time: `AnalyticsManager/config/oee_realtime.xml
- Batch: `Reporting/config/oee_historical.xml
Verify these parameters match exactly:
- Availability calculation method (planned vs total time basis)
- Performance rate calculation (ideal vs standard cycle time)
- Quality rate calculation (first-pass vs total good units)
- Micro-stop threshold and handling
- Planned downtime categorization
2. Event Finalization and Consolidation Timing:
Your 4-hour finalization window is reasonable but may need adjustment. The batch consolidation job should run AFTER this window closes. Check your job scheduler:
- Finalization window: 4 hours (current)
- Consolidation job start time: Should be at least 4.5 hours after shift end
- Late event handling: Configure whether to reprocess previous shifts if late events arrive
Implement an audit trail for late events:
CREATE TABLE event_audit (
event_id INT,
original_shift_id INT,
logged_shift_id INT,
time_delta_hours DECIMAL(5,2),
impact_on_oee DECIMAL(5,2)
);
3. Shift Boundary and Timezone Handling:
This is critical. Your server is UTC but plant operates in CST - that’s a 5-6 hour offset depending on DST. Configure timezone handling explicitly:
In server.properties:
shift.timezone=America/Chicago
reporting.timezone=America/Chicago
server.timezone=UTC
event.timestamp.conversion=true
For events spanning shift boundaries, implement a split rule:
- Events <15 minutes spanning boundary: Attribute to shift where majority occurred
- Events >15 minutes: Split proportionally between shifts
- Configure this in `shift_boundary_rules.xml
4. Real-time vs Batch Data Validation Rules:
The batch engine applies stricter validation. Align the rules:
Real-time validation (less strict):
- Accept events with provisional status
- Use estimated good counts for in-progress orders
- Allow overlapping events temporarily
Batch validation (more strict):
- Require finalized status on all events
- Use confirmed quantities only
- Resolve overlapping events (merge or prioritize)
- Apply business rules for invalid sequences
Create a validation reconciliation report:
SELECT
shift_date,
realtime_oee,
historical_oee,
(historical_oee - realtime_oee) as variance,
late_events_count,
adjusted_events_count,
timezone_corrections
FROM oee_reconciliation
WHERE ABS(historical_oee - realtime_oee) > 2.0
ORDER BY variance DESC;
Implementation Steps:
- Standardize OEE formulas in both configuration files
- Adjust consolidation job timing to run after finalization window
- Configure explicit timezone handling for all timestamp conversions
- Implement validation rule alignment between engines
- Create audit and reconciliation reports
- Test with a single shift before rolling out plant-wide
After implementing these changes, your variance should drop to under 1-2%, which is acceptable given the nature of late event logging. The key is consistency in how both engines handle edge cases at shift boundaries and how they process events that arrive after initial calculation.