CYBERWARD

This project analyzes operational performance and reliability of haul trucks in a mining operation using simulated PI System datasets. The interactive Power BI dashboard highlights utilization, breakdowns, fuel consumption, engine temperature, and hauling productivity to support data-driven maintenance and operational decisions.

DATA COLLECTION

Simulated PI Dataset Overview

• Purpose: Practice SQL, KPI calculations, and Power BI dashboards on simulated PI system datasets.
• Dataset source: simulated (python)
• Rows: ~350 rows
• Columns :Columns: Timestamp, Truck_ID, Shift, Status, Metric, Value, Unit, Operator, Location

Purpose of dataset:

• Practice SQL queries like calculating utilization, idle %, tons hauled
• Build Power BI dashboards to visualize fleet KPIs
• Simulate real-life mining operational data analysis

DATA ANALYSIS AND VISUALIZATION

Applied SQL Queries :

The Excel file for the simulated dataset ( thats is generated using python script) is loaded into a local database table (Simulated_HaulTruck) and then I run the SQL queries directly to practise & simulate PI system application for querying and extracting data.

1. Calculated haul truck utilization and total tons hauled per truck:

SELECT Truck_ID,
       AVG(HaulTime) AS Avg_HaulTime,
       AVG(IdleTime) AS Avg_IdleTime,
       (AVG(HaulTime)/(AVG(HaulTime)+AVG(IdleTime))*100) AS UtilizationPercent,
       SUM(TonsHauled) AS TotalTonsHauled
FROM HaulTruckMetrics
GROUP BY Truck_ID;

• Assessed truck downtime frequency by combining operational metrics with maintenance logs:

SELECT t.Truck_ID,
       COUNT(*) AS BreakdownCount
FROM HaulTruckMetrics t
WHERE t.Status = 'Down'
GROUP BY t.Truck_ID;

• Aggregated fuel consumption by shift for KPI reporting:

SELECT Truck_ID, Shift, SUM(FuelUsed) AS TotalFuelUsed
FROM HaulTruckMetrics
GROUP BY Truck_ID, Shift;

• Average Haul Time and Idle Time per Truck

SELECT
    Truck_ID,
    AVG(CASE WHEN Metric = 'HaulTime' THEN Value END) AS Avg_HaulTime,
    AVG(CASE WHEN Metric = 'IdleTime' THEN Value END) AS Avg_IdleTime,
    (AVG(CASE WHEN Metric = 'HaulTime' THEN Value END) /
     (AVG(CASE WHEN Metric = 'HaulTime' THEN Value END) + AVG(CASE WHEN Metric = 'IdleTime' THEN Value END))) * 100 AS UtilizationPercent
FROM Simulated_HaulTruck
GROUP BY Truck_ID
ORDER BY UtilizationPercent DESC;

• Total Tons Hauled per Truck

SELECT
    Truck_ID,
    SUM(CASE WHEN Metric = 'TonsHauled' THEN Value END) AS TotalTonsHauled
FROM Simulated_HaulTruck
GROUP BY Truck_ID
ORDER BY TotalTonsHauled DESC;

• Fuel Used by Shift

SELECT
    Truck_ID,
    Shift,
    SUM(CASE WHEN Metric = 'FuelUsed' THEN Value END) AS TotalFuelUsed
FROM Simulated_HaulTruck
GROUP BY Truck_ID, Shift
ORDER BY Truck_ID, Shift;

• Breakdown Counts (Truck Status = Down)

SELECT
    Truck_ID,
    COUNT(*) AS BreakdownCount
FROM Simulated_HaulTruck
WHERE Status = 'Down'
GROUP BY Truck_ID
ORDER BY BreakdownCount DESC;

• Average Engine Temperature per Truck

SELECT
    Truck_ID,
    AVG(CASE WHEN Metric = 'EngineTemp' THEN Value END) AS AvgEngineTemp
FROM Simulated_HaulTruck
GROUP BY Truck_ID
ORDER BY AvgEngineTemp DESC;

Key Insights

Fleet Productivity & Utilization

• The fleet shows an overall utilization rate of 54.16%, indicating moderate equipment usage with noticeable idle time and opportunity to optimize scheduling.
• A total of 17,020 tons were hauled, with production distributed unevenly across trucks.
• TRK04 stands out as the top-performing truck with the highest utilization (72%) and the largest tonnage moved (~3,936 tons).

Breakdown & Reliability

• The fleet recorded 32 breakdown incidents, with clear variation in truck reliability.
• TRK03 has the highest breakdown count (9), strongly correlating with elevated engine temperatures.
• TRK01 is the most reliable unit with only 4 breakdowns, supported by lower operating temperatures.

Fuel Efficiency Patterns

• Average fuel usage across trucks is 283 L per haul cycle.
• TRK01 and TRK02 consume higher fuel relative to their haul times, indicating potential inefficiency, engine condition issues, or heavy load assignments.
• TRK04 and TRK03 show more balanced fuel-to-haul performance.

Engine Temperature Monitoring

• TRK05 records abnormally high engine temperatures (314°C) despite lower usage, signaling mechanical inefficiencies or cooling system problems.
• Engine temperature aligns strongly with breakdown events, suggesting predictive maintenance opportunities.

Haul Time Analysis

• Trucks exhibit varying haul cycle times, influenced by load, route, and operator behavior.
• TRK02 shows the shortest haul time but still consumes relatively high fuel, pointing to inefficiencies in driving patterns or engine power output.

Recommendations

Maintenance & Reliability

• Prioritize TRK03 and TRK05 for immediate diagnostic inspection and condition-based maintenance (cooling system, lubrication, vibration levels).
• Introduce real-time engine temperature alerts to prevent overheating-related failures.
• Use TRK01’s maintenance history as a benchmark for best practices.

Fuel Optimization

• Investigate fuel inefficiencies in TRK01, TRK02, and TRK05.
• Implement operator training on fuel-efficient hauling cycles.
• Examine tire conditions, load assignments, and idle durations.

Operational Improvements

• Balance workload to avoid overburdening high performers such as TRK04.
• Reduce idle hours by improving dispatch coordination and loader–truck synchronization.
• Rotate trucks more evenly to extend fleet lifespan.

Summary

The fleet demonstrates moderate utilization, but performance varies significantly by truck. TRK04 is performing exceptionally well, while TRK03 and TRK05 present reliability risks. Optimizing maintenance schedules, balancing workloads, and improving fuel efficiency will significantly boost overall fleet productivity and extend equipment lifespan.