SQL Integration

Why

• Regulatory reports: monthly operating reports built from daily totals, maximums, minimums, and compliance measurements.
• Maintenance: run hours and start counts to the maintenance management system, which schedules work from them, and work orders back to the SCADA for display.
• Laboratory: sample results from the laboratory system alongside the online analyzers, for comparison and calibration.
• Dashboards and management reporting: production, energy, chemical use, and cost, in tools the office already uses.
• Billing and wholesale accounting: totalized flows at meters shared with other systems.
• Asset and event records: alarm history, operator actions, and equipment events in a form that can be queried by anyone with a report tool.

Methods

A schema that lasts

Time-series data in a relational database is stored one row per sample in a narrow table, with the tag identified by a key into a metadata table, the timestamp in universal time, the value, and the quality. A wide table with a column per tag looks convenient and breaks the first time a tag is added, renamed, or has a different sample rate. The narrow table is indexed on the tag key and the timestamp, partitioned by time on large systems, and its rows are inserted in batches. Aggregates that reports use repeatedly, such as daily totals, are computed once by a scheduled job into their own table rather than recomputed from raw rows on every report.

CREATE TABLE tag (
  tag_id      INT PRIMARY KEY,
  tag_name    VARCHAR(120) NOT NULL UNIQUE,
  description VARCHAR(255),
  units       VARCHAR(32),
  site        VARCHAR(64)
);

CREATE TABLE sample (
  tag_id   INT      NOT NULL REFERENCES tag(tag_id),
  ts_utc   DATETIME NOT NULL,
  value    FLOAT,
  quality  SMALLINT NOT NULL,   -- 0 bad, 1 uncertain, 2 good
  PRIMARY KEY (tag_id, ts_utc)
);

CREATE TABLE daily_total (
  tag_id     INT  NOT NULL REFERENCES tag(tag_id),
  day_local  DATE NOT NULL,
  total      FLOAT,
  min_value  FLOAT,
  max_value  FLOAT,
  good_pct   FLOAT,               -- share of samples with good quality
  PRIMARY KEY (tag_id, day_local)
);

-- Daily flow total for the plant effluent meter, last 31 days,
-- computed from a totalizer tag that the controller integrates every scan
SELECT d.day_local, d.total, d.good_pct
FROM daily_total d
JOIN tag t ON t.tag_id = d.tag_id
WHERE t.tag_name = 'FIT-401_TOTAL'
  AND d.day_local >= DATEADD(day, -31, CAST(GETDATE() AS DATE))
ORDER BY d.day_local;

The daily total in that example comes from a controller totalizer, which counts every scan, rather than from an integral of sampled flow, which misses whatever fell between samples. The job that fills the aggregate table reads the totalizer value at the day boundary in local time, converts it from universal time correctly across daylight saving changes, and records what fraction of the day the tag had good quality so that a report can flag a day that is incomplete.

Mistakes

• Writing every tag every scan. A thousand tags at one second is 86 million rows a day; the database fills, the inserts fall behind, and the SCADA logging queue overflows. Log summaries and events; the historian keeps the raw data.
• Synchronous writes. A logging path that waits for the database blocks the SCADA when the database is slow or down. Writes are queued, buffered to disk, and retried.
• The database on the control network with office users connected to it. Every report tool in the office then has a path into the control zone. The office reads a replica in the DMZ.
• Credentials in scripts and controllers. Service accounts with minimum rights, stored in the platform credential store, rotated.
• Local time in the database. Stored data in universal time; conversion in the report.
• Floating point money. Totals for billing computed with appropriate precision and rounding rules, agreed with the billing system.
• No quality column. A report that cannot tell a zero from a bad sample produces wrong totals with confidence.

Security