Manufacturing Asset Health Analytics

A PostgreSQL and RStudio portfolio project that analyzes simulated IoT sensor readings from manufacturing equipment.

Business Scenario

The project monitors robotic arms, CNC machines, and conveyor belts for early signs of failure. It detects:

• Temperature spikes
• Excessive vibration
• Cycle-time delays
• Voltage drops
• Sequential events that may suggest a root cause

Skills Demonstrated

• PostgreSQL schema design
• SQL window functions
• Time-series rolling averages and standard deviations
• Z-score anomaly detection
• Threshold tuning
• Sequential event dependency using LAG()
• Root-cause preprocessing
• RStudio data extraction and filtering
• R visualizations and CSV exports

Dataset

The mock data script creates:

• 3 manufacturing machines
• 120 readings per machine
• 360 total IoT sensor records
• 5-minute sensor intervals
• Embedded anomaly windows for testing

Project Files

1. 01_create_schema.sql
2. 02_insert_mock_data.sql
3. 03_create_feature_view.sql
4. 04_anomaly_analysis.sql
5. 05_rstudio_analysis.R

DBeaver Setup

1. Create a PostgreSQL database named manufacturing_analytics.
2. Open DBeaver and connect to the database.
3. Run the four SQL scripts in numerical order.
4. Confirm that asset_health.sensor_readings contains 360 rows.
5. Review asset_health.anomaly_results.

RStudio Setup

Install the required packages:

install.packages(c(
  "DBI",
  "RPostgres",
  "dplyr",
  "ggplot2",
  "readr",
  "lubridate"
))

Set your PostgreSQL password before running the script:

Sys.setenv(PGPASSWORD = "your_password")

Optional connection settings:

Sys.setenv(
  PGDATABASE = "manufacturing_analytics",
  PGHOST = "localhost",
  PGPORT = "5432",
  PGUSER = "postgres"
)

Run:

source("05_rstudio_analysis.R")

Portfolio Summary

Developed a PostgreSQL-based manufacturing asset health analytics system using 360 simulated IoT sensor readings. Applied rolling window statistics, z-score anomaly detection, and sequential event analysis to identify temperature, vibration, voltage, and cycle-time abnormalities. Enhanced the SQL pipeline with RStudio visualizations and machine-level risk summaries.