Piecing together steps

I’m clarifying data point identification, setting up connectivity and hardware, acquiring data, transforming and integrating it, storing in SQL, and using and analyzing the data.

Thought about collecting machine data from PLCs pendant 8 secondes

Below is a comprehensive, technically in-depth, step-by-step explanation of how to collect production machine data from various PLCs (including B&R Power Panel PP420, Panel PC PPC 3100, PPC2100, and Siemens CPU D445-2, ET200S) along with operator data, item number, and manufacturing order number, then transfer and save that data into an SQL Server database.

The process is broken down into clearly defined phases, followed by detailed case studies/scenarios, recommended tools and hardware/supplier proposals, and best practices.

Phase 1: Identify Data Requirements and Sources

1. Define Data Points

   • Machine operating parameters (e.g., cycle times, temperature, pressure, speed).
   • Production information (item number, manufacturing order number).
   • Operator inputs (start/stop times, operator ID/badge number, shift data).
   • Alarms and status messages from the PLCs.

2. Establish Data Collection Frequency and Granularity

   • Determine how often data needs to be collected (e.g., every second, every cycle, event-driven).
   • Decide on the resolution needed (e.g., log data on key events, or capture continuous data).

3. Assess PLC Hardware Capabilities

   • B&R Power Panel PP420, Panel PC PPC 3100, PPC2100:
     • Typically offers integrated HMI, CPU, and communication interfaces such as Ethernet, serial ports, or fieldbus (e.g., CAN, EtherCAT).
   • Siemens CPU D445-2, CPU D445-2 + ET200S, ET200S CPU:
     • Typically uses PROFINET or PROFIBUS, with possible connections for OPC UA or other industrial protocols.

4. Document Communication Protocols

   • Ethernet/IP, PROFINET, Modbus TCP, OPC UA, or proprietary protocols.
   • This will determine the software/hardware gateway approach in later phases.

Phase 2: Establish Connectivity and Hardware Configuration

1. Networking and Physical Connections

   • Ensure each PLC is connected to a plant-wide industrial Ethernet network or a dedicated network for data acquisition.
   • Use managed industrial switches or edge gateway devices if necessary to segment traffic and improve security.
   • Confirm that IP addresses are correctly assigned, and network topologies are stable and documented.

2. Protocol Gateways and Interfaces

   • If a PLC only supports a specific legacy protocol (e.g., Profibus) and your data collection system uses Ethernet, consider a protocol converter or gateway (e.g., HMS Anybus, ProSoft, or Moxa gateways).
   • For B&R PLCs that typically support openSAFETY, EtherCAT, or Powerlink, confirm the exact interface and ensure compatibility with the data acquisition software.

3. PLCs Firmware and Configuration

   • Update each PLC to a recommended firmware version that supports robust communication options (e.g., the latest B&R Runtime version, Siemens TIA Portal compatibility).
   • Configure data blocks (DBs) or variables that need to be read by external systems (e.g., naming them meaningfully: “Prod_ItemNumber,” “Order_Number,” “Operator_ID,” “Machine_Speed”).

Phase 3: Data Acquisition Layer (Software Setup)

1. Data Collection Software or Middleware

   • OPC Servers / OPC UA
     • Commonly used with Siemens and B&R PLCs for data acquisition. For instance, Kepware KEPServerEX, Siemens SIMATIC OPC UA, or B&R’s APROL can be used.
     • Configure an OPC server to connect to each PLC using the respective driver (e.g., PROFINET driver for Siemens, Powerlink driver for B&R).
   • SCADA / MES Platforms
     • Software like Ignition (Inductive Automation), Siemens WinCC, or B&R’s Automation Studio may have built-in drivers to communicate directly with the PLCs.
     • Tag creation and data logging can be configured through these platforms.

2. Tag Configuration

   • Create “tags” or “variables” within the data collection software corresponding to each PLC data point (e.g., “MachineCycleTime,” “OperatorBadgeID,” “ProductionOrder,” etc.).
   • Set up the desired update rate or polling interval.

3. Event-Driven vs. Continuous Logging

   • Event-Driven: Logs data on triggers or changes (e.g., machine start, stop, alarm, or part completed).
   • Continuous Logging: Logs data on a timed interval (e.g., every second or every cycle).

4. Security and User Access

   • Implement user authentication and role-based access in the OPC server or SCADA system.
   • Secure communication channels (e.g., TLS/SSL if using OPC UA over Ethernet).

Phase 4: Data Transformation & Integration

1. Data Cleansing and Validation

   • Validate operator inputs (e.g., if they scan a badge ID, ensure it matches the expected format).
   • Ensure item and order numbers conform to the ERP or MES format (e.g., 10-digit item numbers).
   • Filter out erroneous or noisy data (e.g., if sensor reads invalid values during machine power-up).

2. Timestamping and Sequencing

   • Use PLC or server-side timestamps to ensure chronological integrity (especially useful if network delays occur).
   • Consider a consistent time source (NTP server) for all equipment to maintain synchronized timestamps.

3. Mapping Data to SQL Columns

   • Operator data → [OperatorID], [OperatorName], [Shift]
   • Item number → [ItemNumber]
   • Manufacturing order → [OrderNumber]
   • Machine parameters → [CycleTime], [Temperature], [Pressure], etc.
   • Timestamps → [Timestamp]

4. Enrich Data with Context (from MES/ERP)

   • Pull relevant product information (e.g., BOM details, route steps) from MES/ERP if needed.
   • Store this along with machine data to create a unified dataset.

Phase 5: Data Transfer to SQL Server

1. Choosing the Transfer Method

   • Direct Database Connection: SCADA/MES or OPC servers often have built-in “historian” modules that can write directly to SQL databases.
   • Custom Middleware / .NET / Python Scripts: A custom application that reads from OPC or SCADA tags via an API and inserts records into SQL Server.
   • Message Queuing / MQTT: Publish data to a broker (e.g., Mosquitto, HiveMQ) and have an MQTT consumer insert into SQL. This can be beneficial for decoupled, scalable architectures.

2. Schema Design in SQL

   • Create tables that reflect the data structure. For instance:

     CREATE TABLE ProductionData (
       ID INT IDENTITY(1,1) PRIMARY KEY,
       Timestamp DATETIME NOT NULL,
       MachineID VARCHAR(50) NOT NULL,
       OperatorID VARCHAR(50) NULL,
       ItemNumber VARCHAR(50) NULL,
       OrderNumber VARCHAR(50) NULL,
       CycleTime DECIMAL(10,3) NULL,
       Temperature DECIMAL(10,3) NULL,
       Pressure DECIMAL(10,3) NULL,
       ...
     );
     

   • Consider normalized vs. denormalized schemas, depending on reporting requirements.

3. Connection Configuration

   • Ensure correct connection string (server name, port, database name, user credentials).
   • Use Windows or SQL authentication as per company policy.
   • Test connectivity with a lightweight insert or with a DB client before going live.

4. Transaction Management

   • Decide if each insert is a separate transaction or if batch inserts (bulk insert) are used for efficiency.
   • Monitor transaction logs to ensure performance and avoid transaction log bloat.

5. Error Handling and Logging

   • Implement robust error logging in the SCADA/historian or custom middleware.
   • If a record insert fails, capture the cause and attempt a retry or store the failed record in a buffer for re-processing.

Phase 6: Data Usage, Reporting, and Maintenance

1. Data Visualization and Analytics

   • Use tools like Power BI, Tableau, or custom SSRS (SQL Server Reporting Services) to create dashboards.
   • Monitor KPIs such as OEE (Overall Equipment Effectiveness), production counts, scrap rates.

2. Advanced Analytics and Integration

   • Feed data into advanced analytics platforms or ML algorithms for predictive maintenance, production optimization, or anomaly detection.
   • Integrate with ERP/MES to close the loop on production orders, inventory, scheduling.

3. Ongoing Maintenance

   • Back up the SQL Server database regularly.
   • Keep SCADA/historian software up to date.
   • Periodically review performance and optimize indexing and queries in SQL.

4. Scaling and Future Expansion

   • As data volume grows, consider partitioned tables or data lakes for historical data.
   • Evaluate whether an edge computing solution is needed to preprocess data or handle large sensor arrays.

Practical Step-by-Step Case Study / Scenario

Scenario 1

• Equipment: A Siemens CPU D445-2 controlling a packaging machine, plus a B&R Power Panel PP420 controlling a labeling station.
• Data Points: Production counter (packaged units), label application speed, temperature, operator ID, order number.
• Approach:
  1. Connectivity:
     • Both PLCs are on an industrial Ethernet network.
     • We install Kepware KEPServerEX on a server in the control room.
  2. OPC Configuration:
     • Configure the Siemens driver in KEPServerEX to poll the CPU D445-2 using PROFINET.
     • Configure the B&R driver in KEPServerEX for Power Panel.
     • Create tags for the required data points (e.g., PackagingMachine.Counter, PackagingMachine.Temperature, LabelStation.OperatorID).
  3. Data Flow to SQL:
     • In KEPServerEX, enable the OPC UA server and use the “DataLogger” or “IoT Gateway” plugin to insert records into a Microsoft SQL Server.
     • Map each tag to a column in the ProductionData table.
  4. Validation:
     • Validate that label speeds are within an expected range (e.g., 5–50 labels/min). If outside range, log a warning.
  5. Testing & Go-Live:
     • Run a pilot test, verify data is correctly inserted.
     • Train operators to check a dashboard (e.g., Power BI) that reads from SQL.

Scenario 2

• Equipment: A B&R Panel PC PPC2100 (handling a complex mixing process) and a Siemens ET200S CPU (handling packaging).
• Data Points: Batch ID, mixing duration, operator name, shift data, packaging count.
• Approach:
  1. SCADA Option: Use Ignition by Inductive Automation as the SCADA platform with built-in drivers for Siemens and B&R.
  2. Tag Configuration:
     • In Ignition, create device connections to each PLC.
     • Create real-time tags (MixingTime, PackagingCount, OperatorName).
  3. SQL Bridge Module:
     • Use Ignition’s “SQL Bridge” to automatically log tags to a table named BatchProductionData in SQL.
     • Trigger inserts on “Batch Complete” or “Packaging Cycle Complete” events.
  4. Operator Input:
     • Provide an HMI screen in Ignition that prompts the operator for batch ID, shift, or other relevant data.
  5. Reporting:
     • Ignition’s reporting module or MS Power BI can be used to generate shift-based performance metrics.

Recommended Tools, Suppliers, and Hardware

1. PLC / HMI / IPC (Industrial PC)

   • B&R: Power Panel PP420, Panel PC PPC3100, PPC2100.
   • Siemens: CPU D445-2, ET200S CPU.
   • Both are robust, recognized in industrial environments, with long product lifecycles.

2. Connectivity and Gateways

   • HMS Anybus, Moxa, or ProSoft gateway modules for bridging different protocols (e.g., PROFIBUS to Ethernet).
   • Managed Industrial Ethernet Switches from Cisco, Hirschmann, or Phoenix Contact.

3. OPC Servers and SCADA

   • KEPServerEX (from Kepware) – wide range of device drivers.
   • Siemens WinCC – native for Siemens PLCs.
   • B&R APROL – specifically for B&R systems.
   • Ignition (Inductive Automation) – flexible, cross-platform SCADA/MES solution.

4. Database Platform

   • Microsoft SQL Server – robust, commonly used in industrial settings.
   • Azure SQL for cloud-based expansions.

5. Visualization & Analytics

   • Power BI, Tableau, or Siemens WinCC Advanced for real-time dashboards and historical trends.

Best Practices

1. Tag Naming Convention

   • Use a consistent and descriptive naming convention (e.g., [MachineName]_[DataPoint]). This simplifies maintenance and troubleshooting.

2. Data Quality and Filtering

   • Avoid flooding the database with high-frequency data that is not actionable.
   • Implement hysteresis or event-based triggers to log only significant changes.

3. Security

   • Use VLANs or firewalls to separate control networks from enterprise networks.
   • Implement secure protocols (OPC UA with encryption) and keep all systems patched.

4. Performance Monitoring

   • Track database size growth, CPU usage on the SQL server, and network bandwidth.
   • Use indexing strategies in SQL to speed up common queries.

5. Disaster Recovery

   • Regular database backups, at least daily or more frequently if required.
   • Redundant hardware or virtualization for the OPC server or SCADA system to minimize downtime.

6. Scalability

   • Plan for additional lines or machines by using modular solutions (e.g., additional OPC drivers, new database tables).
   • If data volume becomes large, consider a time-series database or data lake approach.

Conclusion

By following these phases—from identifying data points, configuring PLC connectivity, setting up data acquisition software, transforming and validating the information, and finally transferring and storing it in SQL Server—manufacturers can create a robust, scalable system for collecting critical production data. This architecture enables real-time monitoring, effective troubleshooting, advanced analytics (like predictive maintenance), and reliable historical reporting, thus driving continuous improvement across the shop floor.

When selecting tools, focus on compatible OPC servers or SCADA systems that can seamlessly talk to both B&R and Siemens PLCs. Ensure proper network design, security hardening, and consistent maintenance practices to keep the entire data collection workflow performing effectively in the long term.