4. Time-series data modeling for KPI analytics

KPI analytics lives or dies by the data model underneath it. A dashboard that renders in 200 ms versus one that times out after 30 seconds usually differs not in the visualization library but in how time-series data was modeled: how tags were structured, how assets were related, how events and batch context were attached, and how sampling rates and retention policies were chosen. This chapter builds a practical modeling discipline for industrial time-series data — one that supports fast queries, correct aggregation, and meaningful KPI context.

1. The Four Data Classes in a KPI Model

Industrial KPI systems almost always contain four distinct data classes, and conflating them is the most common modeling mistake:

• Time-series measurements — numeric samples at a timestamp (e.g., motor_01.current_a = 12.4 at 2024-06-01T08:00:00Z).
• Tags / metadata — the identity and attributes of a signal: unit, engineering range, asset binding, quality code.
• Events — discrete, non-periodic records: alarms, operator actions, state transitions, maintenance windows.
• Batch / context records — the production context that gives measurements meaning: batch ID, product code, recipe, shift, order.

Each class has different cardinality, write patterns, and query patterns. Storing them in one wide table forces compromises; storing them in four coordinated stores lets each be optimized.

2. Tag Naming and Hierarchy

A tag is not just a name — it is a contract. A well-designed tag encodes the asset hierarchy and the measurement semantics so that queries can be written without a lookup table. A common industrial convention is a hierarchical path:

site/area/line/cell/asset/measurement
plant01/packaging/line2/filler1/motor01/current_a
plant01/packaging/line2/filler1/motor01/vibration_rms
plant01/packaging/line2/filler1/valve03/position_pct

This path is queryable with prefix scans: plant01/packaging/line2/* returns every signal on the line. The trade-off is that renaming an asset breaks history unless the model supports aliasing. Production systems therefore keep a stable tag_id (immutable) and a separate tag_path (mutable, human-readable). Dashboards query by tag_id; humans browse by tag_path.

3. Asset Model vs. Tag Model

Tags describe signals; assets describe physical things. The asset model is a graph: a line contains cells, a cell contains machines, a machine contains components. KPIs are usually defined at the asset level (OEE for a line, availability for a machine) but computed from tag-level measurements. The model must therefore support a mapping:

asset_id  | tag_id       | role
LINE2     | motor01.cur  | energy_input
LINE2     | filler1.ct   | cycle_time
LINE2     | filler1.good | good_count

This mapping is what allows a KPI definition to be written once and applied to any asset of the same type. Without it, every new machine requires new dashboard logic.

4. Sampling Rates and Aggregation Windows

Sampling rate is a modeling decision, not a device default. Three rates matter:

• Raw rate — what the PLC or historian emits (e.g., 100 ms for vibration, 1 s for current, 1 min for temperature).
• Storage rate — what is persisted after deadbanding or downsampling (e.g., 1 s for current, 10 s for temperature).
• Query rate — the resolution the dashboard actually needs (e.g., 1 min for trend charts, 1 h for KPI tiles).

Storing raw at 100 ms for a year across 5,000 tags is roughly 1.6 trillion points — impractical for most deployments. A common pattern is to store raw for 7–30 days, then 1-minute aggregates for 1 year, then 1-hour aggregates for 5 years. This tiered retention is what makes long-range KPI queries feasible.

5. Events and Batch Context

Events and batch records are what turn a number into a KPI. A current reading of 12.4 A is meaningless; the same reading during batch B-2024-0601-014 producing product SKU-4471 on recipe R-12 is a data point in a yield analysis.

The modeling requirement is that every measurement can be joined to the context that was active at its timestamp. Two approaches are common:

• Interval join — batch and shift records have start_ts and end_ts; measurements are joined by timestamp containment.
• Denormalized context columns — the active batch ID is written alongside each measurement (or each 1-minute aggregate). Faster queries, larger storage.

For dashboards, the interval join is usually preferred because batch boundaries change rarely and the join is cheap when both tables are indexed on time.

6. Schema Design for Efficient Querying

A practical schema for KPI analytics separates the four data classes into coordinated tables:

Partitioning by time (daily or monthly) and clustering by tag_id within each partition is the standard physical layout. It makes range queries on a single tag fast and keeps retention enforcement cheap — dropping a partition is O(1) versus deleting rows.

7. Retention Policies

Retention is a policy, not a cleanup job. Define it explicitly per data class:

• Raw samples: 7–30 days, then dropped.
• 1-minute aggregates: 12–24 months.
• 1-hour aggregates: 5–10 years (often regulatory).
• Events: 3–7 years, depending on compliance.
• Batch records: 5–10 years, tied to product traceability.

Retention must be enforced by the storage engine (partition drop, TTL) rather than by application code, otherwise it silently fails under load.

8. Worked Example: Modeling a Filler Line

Consider a packaging line with three fillers, each with a motor, a valve, and a counter. The modeling steps are:

1. Register assets: LINE2, FILLER1..3, MOTOR1..3, VALVE1..3.
2. Register tags with paths and units: .../motor01/current_a (A), .../valve01/position_pct (%), .../filler1/good_count (count).
3. Set storage rates: current at 1 s, position at 5 s, good_count at 1 min.
4. Define aggregates: 1-minute avg/min/max for current and position; 1-minute sum for good_count.
5. Attach batch context via interval join on batches.start_ts/end_ts.
6. Set retention: raw 14 days, 1-minute 18 months, 1-hour 7 years.

A KPI query for line availability over the last 30 days then reads only from ts_1h and events, joining on asset_id and time — a query that returns in milliseconds rather than scanning raw samples.

9. Visualizing the Model

The diagram below shows how the four data classes relate: tags bind to assets, measurements flow into tiered aggregate tables, and events and batches provide the context that turns measurements into KPIs.

10. Practical Checklist

• Every tag has an immutable tag_id and a human-readable tag_path.
• Every tag is bound to exactly one asset in the asset tree.
• Storage rate and deadband are documented per tag, not left to device defaults.
• At least two aggregate tiers exist (1-minute and 1-hour) with explicit retention.
• Events and batches are stored separately and joined by time interval.
• Retention is enforced by the storage engine, not by application cleanup jobs.
• KPI queries read from aggregates; raw is reserved for diagnostics and short-range drill-down.

With this model in place, the dashboard layer becomes straightforward: KPI definitions reference asset IDs and tag roles, queries hit the correct aggregate tier, and context is joined at query time. The next chapters build on this foundation to define KPI formulas, aggregation semantics, and interactive drill-down behavior.