01 — Engineering Objective
15-Minute SLA, On-Premise
How do we calculate OEE within a 15-minute window using existing on-premise infrastructure — no cloud, no new licenses?
Windows Task Scheduler fires Python extraction scripts every 10 minutes,
feeding a T-SQL aggregation layer that completes well within the 15-minute SLA.
Pre-aggregated summary tables ensure Power BI loads in near-instant time
— no ad-hoc queries on raw PLC rows.
02 — OEE Framework
Availability × Performance × Quality
OEE decomposes equipment effectiveness into three measurable pillars, each sourced from a different system.
Availability
run_mins ÷ planned_mins
Measures planned production time against actual operating time. Tracks major downtime events, setups, and scheduled changeovers. Ignition SCADA provides the downtime log; SAP provides the planned shift window.
📡 Ignition SCADA Logs + SAP Shift Schedules
Performance
actual_rate ÷ ideal_rate
Evaluates machine speed vs. standard cycle rate. Captures speed losses, idling, and micro-stops under 5 minutes — events operators historically missed on paper clipboards, now logged automatically by SCADA/PLC.
⚙️ PLC Sensor (OPC-UA) + Ignition SCADA
Quality
units_good ÷ units_total
Measures first-pass yield. Captures items that do not meet quality standards, relying on defect and scrap counts extracted from SAP Quality Module each 10-minute cycle.
🏭 SAP Quality Module (QM)
03 — ETL Architecture
Schematic Data Flow
Six-tier pipeline: from raw machine signals to end-of-shift Power BI dashboard — fully on-premise, within a 10-minute extraction cycle.
⚡
PLC Sensors
Cycle counters, speed tags
OPC-UA historian write
📡
Ignition SCADA
Downtime events, alarm log
Micro-stop detection <5 min
🏢
SAP ERP
QM: scrap & good units
PP/HR: shift schedules & rates
OPC-UA · REST API · RFC BAPI
↓
Bulk INSERT → Staging Tables
↓
📥
stg_scada_downtime
Raw downtime events
event_id · source_tag · reason_code · down_mins
📥
stg_sap_quality
Raw quality lots
insp_lot · material · ok_qty · scrap_qty
📥
stg_plc_cycles
Direct OPC-UA writes
machine_tag · cycle_count · ts
SQL Server Agent Job (T-SQL Stored Procs)
↓
Materialised into Star Schema
↓
⭐
fact_production_event
Grain: 1 row per machine cycle
run_mins · down_mins · units · rates
📐
Dimension Tables
dim_shift · dim_machine
dim_product · dim_reason
📋
oee_shift_summary
Pre-agg: 1 row per shift/machine
availability · performance · quality · oee
On-Premises Data Gateway (DirectQuery / Scheduled Import)
↓
🔌
On-Premises Gateway
Installed on SQL Server host
10-min scheduled refresh cycle
📈
Power BI Dashboard
KPI cards: OEE / A / P / Q %
Waterfall · Pareto · 30-day trend
🔒
Row-Level Security
Scoped by production line
and department — no cross-line leakage
04 — Data Source Mapping
Floor Signal Inventory
Every data point required to compute OEE, its origin system, extraction protocol, and refresh cadence.
| Data Point |
Origin System |
Protocol / Method |
Frequency |
OEE Variable |
| Scheduled production time & planned minutes per shift |
SAP ERP — HR/PP |
RFC BAPI / REST API |
On shift creation + 10 min |
Availability |
| Downtime events — timestamps, duration, reason code, line ID |
Ignition SCADA |
Ignition SDK → Python JDBC pull |
Every 10 min |
Availability |
| Total units produced — cycle counter per machine |
PLC → MS SQL (direct) |
OPC-UA tag historian → DB write |
Real-time / sub-minute |
Performance |
| Micro-stops — process faults under 5 minutes |
Ignition SCADA / PLC |
Ignition alarm log extract via Python |
Every 10 min |
Performance |
| Good units passed & scrap counts by defect code |
SAP QM Module |
RFC BAPI_INSPOPER_GETLIST / REST |
Every 10 min (end-of-lot trigger) |
Quality |
| Ideal cycle rate (spec_rate) per SKU / machine |
SAP PP — Work Centers |
Loaded once to dim_product / dim_machine |
Static — updated on engineering change |
Performance |
05 — ETL Options
Orchestration Approaches
The 2021 on-prem stack uses a lightweight Python + SQL Server Agent approach. Four viable paths — ranging from zero new licenses to full data engineering infrastructure.
Current — 2021
Python + SQL Server Agent
Zero new licenses
On-premise only
Low complexity
Scheduled Python scripts pull from SCADA/SAP, write to staging tables, SQL Server Agent fires stored procs. Fits inside the firewall. Right-sized for this use case.
✓ Best for: ≤5 sources, stable schema, SQL-first ops team
Native MS Stack
SSIS — SQL Server Integration Services
Visual pipeline designer
Already licensed
GUI debugging
Replace Python scripts with SSIS packages. Adds GUI error handling, data flow logging, SSIS catalog for deployment. Can call SAP OData connectors via ADO.NET.
✓ Best for: team using SSMS, need visual debugging
Open Source
Apache Airflow (Self-Hosted)
DAG orchestration
Python-native
SLA alerting
Self-hosted Airflow replaces Task Scheduler. Provides DAG lineage, retry logic, SLA alerting, web UI. Python operators reuse existing extraction code.
✓ Best for: 10+ sources, SLA alerting, Python-first team
Future State
dbt Core + Python
SQL-first transforms
Column-level lineage
CI/CD
Replace stored procedures with dbt models targeting MS SQL Server. Staged → fact layer transformations become version-controlled SQL with auto-documentation.
✓ Best for: data engineering team, CI/CD culture
| Criteria |
Python + Agent |
SSIS |
Airflow |
dbt Core |
| Setup complexity |
Low |
Medium |
Medium |
High |
| Monitoring / alerting |
Manual |
SSISDB catalog |
Airflow UI + SLA |
dbt docs + lineage |
| Version control |
Git for .py |
XML packages (messy) |
Git for DAGs |
Full SQL in Git |
| SAP connectivity |
pyrfc / OData |
ADO.NET / OData |
Python operators |
Extraction via Python |
| Ignition SCADA support |
REST API / JDBC |
JDBC only (custom) |
Python operators |
Extraction via Python |
| On-prem firewall compatible |
Yes |
Yes |
Yes (self-hosted) |
Yes (SQL Server target) |
06 — OEE Results
Performance Improvement
Identified micro-stops and baseline discrepancies led to measurable OEE gains. Pre-implementation vs. post-implementation by machine line.
⚙️
Micro-Stop Visibility
Surfaced automated faults under 5 minutes that operators previously failed to log manually. SCADA alarm integration eliminated the gap entirely.
🤖
Automated Logging
Ignition SCADA flows directly to SQL Server — no clipboard, no post-shift entry, no shift-boundary data loss, no human reporting bias.
🎯
Leadership Action
Enabled shift supervisors to prioritize maintenance resources using accurate OEE data delivered within the 15-minute SLA window.
📐
Baseline Accuracy
SAP-sourced ideal cycle rates replaced manually-maintained spreadsheets, making Performance and Availability calculations defensible.