OEE Analytics on Live Data: How to Move from Nightly Exports to Real-Time Dashboards

The OEE dashboard on the shift supervisor's screen shows 73.2% for Line 4. The supervisor asks what happened at 9:15. Nobody is certain: the data covering that window will not arrive until the next export job runs. The window to investigate and act opened and closed before anyone saw it.

This is the operational cost of building OEE analytics on a batch export architecture. The calculation is correct. The data is accurate. It describes a factory that existed 15 to 60 minutes ago.

How most OEE pipelines are built

Sensors on the production line write readings to a SCADA system or PLC. That data flows into an industrial data historian — OSIsoft PI, Wonderware, or AspenTech — a system built to capture and retain process data at high frequency with the reliability that OT environments require. That capture layer performs exactly as designed.

The challenge begins at the analytics step. Historians are not SQL analytics engines. They do not serve complex aggregation queries across multiple assets and plants under concurrent dashboard load. To get OEE data into a reporting tool, teams build an export job: a scheduled process that extracts data from the historian, transforms it into a relational schema, and loads it into SQL Server or a similar analytical database. The BI tool connects to that database and renders the OEE dashboard.

The export job runs on a schedule. Every 15 minutes, or every hour, or once per shift. Each run the dashboard reflects a slightly more recent state. But it is never current. The latency is structural, not incidental; it is the batch step itself.

What a 15-minute lag costs on the production floor

OEE (Overall Equipment Effectiveness) measures how well a production line uses its available time. It is the product of three components: Availability (fraction of planned production time the line was running), Performance (actual throughput relative to the ideal cycle rate), and Quality (conforming output as a fraction of total output produced).

OEE = Availability x Performance x Quality.

A line degradation event hits all three components in real time. When a bearing runs hot, Performance declines first: the line slows below its rated cycle rate before it stops. When a material batch runs off-specification, Quality drops while the line continues producing. When an unplanned stop occurs, Availability falls to zero for the duration. The degradation is happening now. The export-job dashboard reflects what was happening when the last batch ran.

A shift supervisor who sees a Quality drop at 9:17 can identify the batch, halt further defect production, and investigate the input material within minutes. The same supervisor seeing the same drop at 10:00 has already produced 43 minutes of out-of-specification output. In a high-volume packaging or automotive line, that is a measurable, unrecoverable quantity of waste. Faster exports reduce the window. They do not close it.

The architecture that removes the export step

The batch step is not a technical limitation of the historian. It is a reflection of what analytical databases could handle at industrial sensor volumes when these architectures were designed. A real-time analytics database changes the equation.

A live OEE architecture writes sensor data directly to CrateDB, either via Telegraf (using the CrateDB output plugin), a Kafka connector, or direct HTTP ingestion. CrateDB auto-indexes every field on ingestion: data is available for SQL queries within milliseconds of arrival. No export job. No staging table. No scheduler.

The dashboard tool connects to CrateDB via the PostgreSQL wire protocol using a standard PostgreSQL driver. The OEE query runs against live data every time the dashboard refreshes. Grafana, Tableau, Power BI, and Superset all support this connection out of the box.

The data flow:

The historian stays at the OT layer for high-frequency process data capture and long-term process data retention. CrateDB serves the analytics layer above it, handling the SQL queries your dashboards and operations teams run. These are complementary roles. The modern data historian guide covers the full OT/IT integration pattern for industrial deployments.

An OEE query that runs on live data

The following query calculates OEE per production asset per hour, across the last 8-hour shift window. The shift_production table holds one row per asset per time window, written continuously by an edge agent or Telegraf as the line runs.

Schema:

CREATE TABLE shift_production (
    ts              TIMESTAMP WITH TIME ZONE NOT NULL,
    asset_id        TEXT,
    plant_id        TEXT,
    runtime_seconds DOUBLE,    -- actual machine runtime in this window
    planned_seconds DOUBLE,    -- planned production time in this window
    actual_output   DOUBLE,    -- units produced
    ideal_output    DOUBLE,    -- units at ideal cycle rate
    good_units      DOUBLE,    -- conforming units produced
    month_bucket    TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS DATE_TRUNC('month', ts)
) PARTITIONED BY (month_bucket);

OEE calculation:

-- Live OEE per production line, rolling 8-hour shift window
-- Availability x Performance x Quality, recalculated on every dashboard refresh
SELECT
    asset_id,
    plant_id,
    DATE_TRUNC('hour', ts) AS hour_bucket,
    ROUND(
        (SUM(runtime_seconds) / NULLIF(SUM(planned_seconds), 0.0))  -- Availability
        * (SUM(actual_output)  / NULLIF(SUM(ideal_output),   0.0))  -- Performance
        * (SUM(good_units)     / NULLIF(SUM(actual_output),  0.0))  -- Quality
        * 100.0,
        1
    ) AS oee_pct
FROM shift_production
WHERE ts > NOW() - INTERVAL '8 hours'
GROUP BY asset_id, plant_id, hour_bucket
ORDER BY asset_id, hour_bucket;

When the shift supervisor opens the Grafana panel at 9:17, this query covers everything through 9:17. Not through the last export cycle. The NULLIF prevents a division-by-zero result for assets not yet active in the current window.

To extend this to a cross-plant view, remove any plant filter and keep plant_id in the GROUP BY. CrateDB's distributed SQL engine executes the query across all cluster nodes in parallel. Query time does not scale linearly with the number of facilities in the result set.

ALPLA: 250x faster across facilities

ALPLA manufactures packaging for Coca-Cola, Unilever, and other global consumer goods brands. They operate 181 facilities in 46 countries. Each facility runs up to 900 distinct sensor types, all feeding into a centralized production monitoring system.

Before CrateDB, their production queries ran against Microsoft SQL Server. Execution time was 3 to 5 minutes per query. Shift supervisors and plant managers waited for dashboards to populate. Cross-facility comparisons — the queries that let central operations direct improvement resources to the facilities with the most to gain — were too slow to use as day-to-day operational tools.

After migrating to CrateDB, query time dropped from 3 to 5 minutes to milliseconds: a 250x improvement. ALPLA moved their existing SQL queries directly from SQL Server to CrateDB without rewriting them. The PostgreSQL wire protocol compatibility meant their existing reporting tooling connected without changes. Their 900 sensor types per factory land in a single CrateDB table using dynamic schema: new sensor types are absorbed at ingestion without schema migrations or pipeline downtime.

"By collecting and analyzing sensor data in real time, we can direct people to the 'hot spots' and improve waste rate and efficiency." — Jodok Schäffler, General Manager, ALPLA

The full story is at cratedb.com/stories/alpla.

What this means for your data team

Moving from a batch-export OEE pipeline to a live one does not require replacing your dashboard tool, rewriting your OEE logic, or rebuilding your ingestion pipeline from scratch. The OEE formula does not change. Standard SQL runs the same way in CrateDB as it did in SQL Server. The change is that the query runs against data that arrived seconds ago instead of data from the last scheduled batch.

CrateDB is a real-time analytics database built for high-cardinality sensor data at industrial scale. For manufacturing teams operating across multiple sites, that distinction has a concrete operational meaning: shift supervisors see what is happening now, not what happened before their last coffee break.

Nightly exports were a constraint of what analytical databases could handle at industrial sensor volumes when these architectures were first built. That constraint no longer applies.

Try CrateDB Live: from setup to a live dashboard in under 30 minutes.