Turn Operational Data into BI — SCADA Integration Recipe

Why this recipe matters

Operational data in SCADA and OT systems contains the signals you need to improve uptime, throughput, and quality — but only if it is captured, labeled, aligned, and made trustworthy for BI and decision workflows. This recipe guides a pragmatic path from raw tags and historian records to queryable, governed time-series that analysts and decision systems can rely on.

Quick overview (one-paragraph)

Collect data at the edge with buffering and secure read-only interfaces; normalize timestamps and sample cadence; create a tag catalog that maps OT tags to business semantic names and asset hierarchy; store rollups and raw events in a time-series store or data lake; compute common aggregations (availability, performance, quality, anomaly scores); expose curated views to BI via well-documented query endpoints; and protect systems with segmentation, least privilege, auditing, and retention rules.

Stepwise recipe

1. Map the scope and priorities

   Identify the critical assets, KPIs, and use cases (e.g., OEE dashboards, predictive maintenance alerts, downtime root-cause). Prioritize a small pilot area (one line, one unit) to build a repeatable pattern.

2. Design connectivity and ingestion

   Prefer read-only, buffered collection at the edge. Typical options: OPC UA (secure endpoint), OPC DA/UA wrappers, MQTT (edge publishers), historians (OSIsoft PI, Honeywell PHD), or direct device telemetry. Use an edge agent that can:

   • Buffer during network outages
   • Apply simple preprocessing (unit conversions, quality filtering)
   • Publish to a message gateway or time-series DB (Kafka, MQTT broker, Timescale, Influx)
3. Define sampling and preprocessing rules

   Document sampling rates per tag, quality flags, and preprocessing rules. Keep raw high-frequency events where important; store downsampled values for analytics. Consider:

   • High-frequency alarms/events: keep raw (event log)
   • Continuous sensors: resample to a common analytic cadence (1s, 1m, 5m) depending on use case
   • Derived calculations at the edge to reduce noise and bandwidth (e.g., compute moving averages)
4. Time alignment and gap handling

   Align all incoming series to a consistent timestamp standard (UTC, monotonic clock) and analytic cadence. Decide policies for gaps:

   • Short gaps: forward-fill last-good value for stateful signals (e.g., mode) or interpolate for continuous sensors when appropriate
   • Long gaps or sensor failure: mark as missing and surface as data quality alerts
   • Preserve original timestamps and arrival timestamps (two separate fields) so analysts can reason about latency
5. Build a tag catalog and semantic mapping

   Create a machine-readable catalog for each tag with these fields (store as JSON, CSV, or in a small master data table):

   • TagID / OTAddress
   • BusinessAlias (e.g., Pump_A_Flow)
   • Description
   • Units
   • SamplingRate
   • SourceDevice and AssetHierarchy
   • QualityFlags and acceptable ranges
   • TransformationRules (scaling, offsets)
   • KPI mappings (which KPIs this tag contributes to)
   • RetentionPolicy
6. Choose storage and retention strategy

   Keep raw high-resolution data for a defined retention window (e.g., 30–90 days) and keep aggregated rollups for longer (monthly/yearly). Store patterns:

   • Hot store: time-series DB for recent raw and resampled data used by operational dashboards
   • Cold store: compressed, aggregated data and op logs in a data lake for long-term analytics
   • Materialized views: precomputed KPIs and rollups (e.g., 1-minute mean, count, percent-good) for fast BI queries
7. Define common aggregations and KPI computations

   Example building blocks:

   • Counts and durations: event counts, downtime duration
   • Rates and means: mean throughput, average temperature
   • Percentages: percent-good, yield
   • OEE components: Availability = (Operating Time / Scheduled Time), Performance = (Actual Output / Ideal Output), Quality = (Good Output / Total Output), OEE = A * P * Q
   • Rollup window example: for each minute compute mean(value), min, max, count, pct_good
8. Expose curated views for BI

   Provide BI teams with documented, queryable endpoints (SQL access to Timescale/ClickHouse or a semantic layer). Deliver curated datasets: asset-level OEE, shift summaries, anomaly scores, downtime logs. Include the tag catalog and transformation rules as part of the dataset documentation.

9. Alerts, anomalies, and decision workflows

   Define alert rules and connect them to operator workflows. Use simple threshold/rate-of-change rules first; pilot unsupervised anomaly detection later. Always include context (asset, shift, recent events) in alerts to reduce noise and speed resolution.

10. Security, governance and operational constraints

    Operational safety and security must guide every integration:

    • Use network segmentation and DMZs; prefer one-way replication or read-only access where possible
    • Authenticate with certificates and rotate keys
    • Apply RBAC and least privilege for access to tag data and catalogs
    • Audit all data exports and transformations
    • Document retention, masking, and compliance requirements
11. Operationalize: testing, validation, and runbook

    Create data quality tests (expected ranges, freshness checks, spike detection) and link failures to runbooks so operators or data engineers can respond quickly.

Practical examples & patterns

Tag naming and business alias example

• OT tag: PLC1:DB1000:Tag23
• BusinessAlias: Line1.PumpA.Flow (units: L/min)
• AssetHierarchy: Plant > Line1 > PumpA

OEE rollup example (conceptual)

Compute on a per-shift or per-hour basis:

• Availability = (ScheduledTime - Downtime) / ScheduledTime
• Performance = ActualProducedUnits / (IdealRate * OperatingTime)
• Quality = GoodUnits / TotalUnits
• OEE = Availability * Performance * Quality

Checklist before you ship data to BI

• Have you documented tag catalog and business alias mapping?
• Is there an agreed analytic cadence and retention policy?
• Are timestamps normalized to a single clock and timezone (store UTC)?
• Do you preserve both source timestamp and ingest timestamp?
• Are data quality rules and alerts in place?
• Is access secured and auditable (RBAC, certificates, read-only where possible)?
• Have you validated KPI calculations against manual or known-good baselines?

Common pitfalls and how to avoid them

• Aggressive downsampling that loses alarms or short-duration events — keep event logs raw.
• Mixing local timezones without clear conversion — standardize on UTC and record offsets.
• Poor or missing tag metadata — build and maintain a lightweight tag catalog early.
• Direct write-access to controllers — always prefer read-only collection and segmented networks.
• Presenting raw OT tags to BI without semantic mapping — provide business aliases and documentation.

Next steps for your pilot

1. Pick a pilot line and agree KPIs with operators and BI owners.
2. Stand up an edge collector and buffer; configure read-only OPC UA or MQTT feed.
3. Create the tag catalog for pilot tags and define sampling/retention.
4. Store raw and 1-minute rollups; compute pilot KPIs and validate with operators.
5. Iterate on alert thresholds and dashboards; document the pattern for rollout.

Where this recipe fits in the larger domain

This recipe is a practical bridge from OT historians and SCADA to BI and decision workflows. It should be paired with domain resources on KPI design, OEE improvement, anomaly detection, and data governance to create repeatable operational intelligence across the site or enterprise.

Image idea

industrial control room dashboard