8.4 Data Flow Analysis, Historians, Relational Databases & Reporting

Data Flow Analysis, Historians, Relational Databases & Reporting

Two CAP tasks deal directly with data. In System Design, the CAP must analyze the quantity, type and flow of data involved with automation systems, safety systems and security to specify hardware and software; the listed knowledge includes data requirements, data structures of control systems, data flow in safety and security systems, and optimizing, tuning and normalizing databases. In Development, the CAP must develop database and reporting functions to meet the functional requirements, which calls for relational database design, writing queries, data mapping and report design. This section covers both.

---

Analyzing Data Requirements

Data design starts with an inventory of every data class the system must handle:

For each class, record the source, update rate, volume, retention period, latency requirement, and who reads or writes it. That inventory drives server sizing, network loading and backup design.

Sizing example: a historian collecting 20,000 tags, each sending on average one new value every 10 seconds after exception reporting, receives $\frac{20{,}000}{10} = 2{,}000$ values per second, or $2{,}000 \times 86{,}400 = 172{,}800{,}000$ values per day. Storage, compression settings and archive rotation are planned from figures like these.

Data Flow Across Levels and Zones

• Control data stays at Levels 1 and 2; only summarized or contextualized data needs to move up to Level 3.
• Business users read replicated or mirrored data in the IDMZ or at Level 4, never the production servers directly (Sections 3.4 and 6.3).
• Safety-system data flows to the BPCS read-only; SIS sequence-of-events records are kept for trip analysis.
• One-way flows, such as historian replication out of a high-security zone, can use data diodes; security logs flow to a central collector.

---

Time-Series Historians and Relational Databases

Historian design choices include exception reporting at the collector (send a value only when it moves outside a deadband), compression in the archive (such as swinging-door algorithms that keep the shape of the trend within a set deviation), data-quality flags, time synchronization of every source, and store-and-forward buffering so that a network outage does not lose data.

---

Relational Database Design and Normalization

A relational design defines entities (tables), their attributes (columns), a primary key that uniquely identifies each row, and foreign keys that link tables. Normalization removes redundancy so that each fact is stored once:

• First normal form (1NF): every field holds a single value—no lists in a column and no repeating groups.
• Second normal form (2NF): every non-key column depends on the whole primary key, not just part of a composite key.
• Third normal form (3NF): non-key columns do not depend on other non-key columns (no transitive dependencies).

For batch genealogy, a Batch table (BatchID, Recipe, StartTime, EndTime), a MaterialLot table (LotID, Material, Supplier) and a BatchConsumption junction table (BatchID, LotID, QuantityKg) resolve the many-to-many relationship between batches and lots. A query then answers questions such as which lots went into each batch:

SELECT b.BatchID, b.Recipe, SUM(c.QuantityKg) AS TotalKg
FROM Batch b
JOIN BatchConsumption c ON c.BatchID = b.BatchID
WHERE b.StartTime >= '2026-10-01'
GROUP BY b.BatchID, b.Recipe;

Tuning includes indexing the columns used in joins and filters (while avoiding excessive indexes on write-heavy tables), partitioning large tables by time, archiving or purging old data under a retention policy, and reviewing slow queries. Reporting databases are sometimes deliberately denormalized for speed, but the source of truth stays normalized.

---

Data Mapping and Tag Conventions

Data mapping documents how each controller tag, historian tag and database field relate: names, data types, engineering units, scaling, update rates and the interface used (Section 3.4 introduces data interface agreements). A consistent naming convention—typically built on the ISA-5.1 tag plus an attribute suffix—lets the same point be found in the controller, HMI, historian and reports. Configuration should have one source of truth, with other systems generated or synchronized from it, so that a change is not made in one place and forgotten in another.

---

Designing Reports

Start each report from written requirements: audience, purpose, frequency, data sources, calculations, distribution, retention and any regulatory need. Common types are shift and production reports, electronic batch records, regulatory emissions reports from a CEMS data system, KPI dashboards such as OEE, alarm-performance reports (ISA-18.2) and maintenance diagnostic reports. Good practice:

• Put exceptions and decisions first, then supporting detail.
• State units, time zone and the treatment of daylight-saving changes; store time stamps in UTC.
• Show data quality, so that a total based on bad or missing values is flagged.
• Calculate totals from validated sources (for example, flow totalizers rather than integrated trend values) and document the method.
• Keep report templates under version control, and validate regulated reports as part of the computerized system.
• Run heavy queries against replicated data, not against the control-level servers.