Study guide
8.4 Data Flow Analysis, Historians, Relational Databases & Reporting
ISA CAP Certified Automation Professional Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction
About the ISA CAP Exam (2024 Content Outline)
2 Chapter 1: Feasibility Study — Scoping & Justification
1.1 Business Needs, Scope Definition & Operational Strategies 1.2 Automation Opportunity Assessment & Technology Screening 1.3 Benefit-to-Cost Analysis, Payback & Financial Metrics (NPV, ROI, IRR)
3 Chapter 2: Feasibility Study — Technical Analysis & Risk
2.1 Technical Feasibility Studies & Automation Architecture Alternatives 2.2 Preliminary Risk Assessment, Process Safety & Environmental Constraints 2.3 Preliminary Project Cost Estimating, Scheduling & Resource Requirements
4 Chapter 3: Definition — Requirements & Specifications
3.1 User Requirements Specification (URS) & Stakeholder Analysis 3.2 Functional Requirements Specification (FRS) & Functional Safety Requirements 3.3 Process & Control Strategy Determination (Continuous, Discrete, Batch & Hybrid) 3.4 Automation Boundaries, High-Level System Architecture & Scope Definition
5 Chapter 4: Definition — Safety, Cybersecurity & Cost Baseline
4.1 Regulatory Compliance & Safety Criteria (OSHA PSM, EPA RMP, 21 CFR Part 11) 4.2 Preliminary Security Architecture & Industrial Cyber Threat Assessment (ISA/IEC 62443) 4.3 Baseline Cost Estimating, Financial Justification & Project Viability Analysis
6 Chapter 5: System Design — Control Architecture, Field Devices & Motion
5.1 Control System Architecture Selection: PLC, DCS, SCADA & Hybrid Systems 5.2 Process Instrumentation, Sensor Selection & Analytical Measurement 5.3 Final Control Elements: Control Valves, Actuators & Variable Frequency Drives (VFDs) 5.4 Hazardous Location Classification, Electrical Safety & Enclosures (NEC, NFPA, ATEX/IECEx) 5.5 Discrete Devices, Motor Control, Motion Control & Mechatronics
7 Chapter 6: System Design — Safety Systems & Cybersecurity
6.1 Safety Instrumented Systems (SIS) & Safety Lifecycle (ISA-84 / IEC 61511 / IEC 61508) 6.2 SIL Determination, Verification & Layer of Protection Analysis (LOPA) 6.3 Industrial Cybersecurity by Design: Zones, Conduits & Security Levels (ISA/IEC 62443) 6.4 Hardware/Software Specifications, Procurement & Detailed Engineering Packages
8 Chapter 7: System Design — Networks, Continuous Control & Standards
7.1 Industrial Fieldbuses & Digital Communications (HART, Fieldbus, Profibus, Modbus) 7.2 Industrial Ethernet & Open Protocols (EtherNet/IP, PROFINET, OPC UA, MQTT) 7.3 Continuous Control Strategies: Feedback, Feedforward, Cascade, Ratio & Advanced PID 7.4 Batch Control Architecture, Recipes & Procedural Standards (ISA-88 / S88)
9 Chapter 8: System Design — HMI, Alarms, Enterprise Integration & Data
8.1 Human-Machine Interface (HMI) & High-Performance Graphics Design (ISA-101) 8.2 Alarm Management Lifecycle, Rationalization & Performance (ISA-18.2) 8.3 Enterprise Integration & Manufacturing Operations Management (ISA-95 / S95) 8.4 Data Flow Analysis, Historians, Relational Databases & Reporting
10 Chapter 9: Development — Software Logic & Programming
9.1 IEC 61131-3 Programming Languages: LD, FBD, ST, SFC and Legacy IL 9.2 Discrete Control Logic, Sequencing, State Machines & Interlock Implementation 9.3 Safety Logic Development, Burner Management & Interlock Verification 9.4 Software Configuration Management, Version Control & Change Tracking
11 Chapter 10: Development — Simulation, Tuning & FAT
10.1 Loop Tuning Methodologies & Dynamic Process Response (Ziegler-Nichols, Cohen-Coon, Lambda) 10.2 Simulation, Emulation & Hardware-in-the-Loop (HIL) Verification 10.3 Factory Acceptance Testing (FAT): Planning, Test Protocols, Execution & Punch Lists 10.4 Software Quality Assurance, Code Review & Validation Documentation
12 Chapter 11: Deployment — Installation, Wiring & Pre-Commissioning
11.1 Field Installation Oversight, Wiring Practices, Grounding & Shielding 11.2 Pre-Commissioning Checkout: Cold Loop Checks, Hot Loop Checks & Calibration 11.3 Site Safety Protocols, Lockout/Tagout (LOTO) & Permitting during Deployment 11.4 Installation Punch List Management & Defect Resolution
13 Chapter 12: Deployment — Commissioning, SAT & Turnover
12.1 Site Acceptance Testing (SAT): Execution, Environmental Validation & Variance Management 12.2 Commissioning & Startup Sequencing (Cold/Hot Commissioning & Fluid Runs) 12.3 Cutover Strategies, Legacy System Migration & Contingency Fallback 12.4 System Turnover, Operator/Maintenance Training Delivery & As-Built Documentation
14 Chapter 13: Operation & Maintenance — Diagnostics, Reliability & Training
13.1 Maintenance Strategies: Reactive, Preventive, Predictive & Reliability-Centered (RCM) 13.2 Troubleshooting Methodologies & Root Cause Failure Analysis (RCFA) 13.3 Calibration Management, Proof Testing & SIS Periodic Validation (IEC 61511) 13.4 Spare Parts Strategy, Critical Spares Analysis & Obsolescence Management 13.5 Asset Performance Management (APM) & Overall Equipment Effectiveness (OEE) Tracking 13.6 Training Needs Analysis, Training Delivery & Competency for Automated Systems
15 Chapter 14: Operation & Maintenance — Optimization, MOC, Security & Decommissioning
14.1 Loop Auditing, Process Variability Analysis & Control Optimization 14.2 Management of Change (MOC) Procedures & Software Modification Controls 14.3 Backup, Disaster Recovery & Disaster Recovery Testing for Industrial Systems 14.4 Operational Cybersecurity: Patch Management, Vulnerability Monitoring & Incident Response 14.5 Decommissioning Planning, Asset Retirement & Environmental Disposition
8.4 Data Flow Analysis, Historians, Relational Databases & Reporting
Key Takeaways Explain This Topic AI
A data requirements analysis lists each data class with its source, update rate, volume, latency need, retention period and consumers.
Process historians store compressed, time-stamped values, while relational databases store structured records such as batches, recipes and genealogy.
Third normal form removes repeating groups, partial-key dependencies and transitive dependencies from relational tables.
Reports and enterprise queries should read replicated or mirrored data rather than control-level production servers.
A historian receiving 2,000 values per second stores about 172.8 million values per day.
Last updated: October 2026
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:
Data Class Typical Source Rate and Volume Latency Need Typical Consumers
Control I/O and setpoints Controllers and field devices Milliseconds to seconds, many points Real time, deterministic Control logic, HMI
Alarms and events Controllers, SIS, alarm server Event-driven, bursts during upsets Seconds Operators, alarm analysis
- Process history Historian collectors Seconds, very large volume Seconds to minutes Engineers, optimization
- Batch records and genealogy Batch and MES systems Per batch Minutes Quality, regulators
- Quality and laboratory data LIMS, analyzers Per sample Minutes to hours Quality, production
- Device and network diagnostics Smart devices, switches Minutes to hours Hours Maintenance
- Security logs Firewalls, hosts, switches Continuous Minutes Security monitoring
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 20,000 10 = 2,000 \frac{20{,}000}{10} = 2{,}000 10 20 , 000 = 2 , 000 values per second, or 2,000 × 86,400 = 172,800,000 2{,}000 \times 86{,}400 = 172{,}800{,}000 2 , 000 × 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
- Attribute Process Historian Relational Database
- Data model Tag, time stamp, value, quality Tables of structured records with keys and relationships
Strength Very high insert rates; compression; interpolation over time Transactions, integrity rules and flexible queries (SQL joins)
Typical content Temperatures, flows, valve positions, states Batches, recipes, material lots, genealogy, work orders
Retrieval By tag and time range; raw, interpolated or aggregated By relationships and conditions across tables
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.