Thursday, February 1, 2024

Data Mapping, Ensuring Real Time Data Flow - Part 3

Data Mapping, Ensuring Real Time Data Flow

By Harneet Gill, MESA Marketing Committee Chair, Rockwell Automation

Data Mapping 
In the realm of manufacturing operations, data is akin to the lifeblood of an organism, flowing seamlessly from one component to another, ensuring vitality and dynamism. At the heart of this flow lies the process of data mapping—converting raw data from automation and control systems into structured information that ERP and MES systems can consume, analyze, and act on. A significant tool in this endeavor is the ETL (Extract, Transform, and Load) process. 

Understanding the Data Landscape
Automation and Control Systems and HMI/SCADA Systems: These are real-time control systems used to monitor and control machinery on the shop floor. They generate vast amounts of granular data including machine statuses, sensor readings, alarms, and event logs.

ERP and MES Systems 
ERP (Enterprise Resource Planning) systems manage business processes like accounting, HR, procurement, and sales, while MES (Manufacturing Execution Systems) focus on managing and optimizing production processes. Both demand structured, summarized, and contextual data.

The Data Mapping Challenge

  • Granularity: Automation and control system data is often too granular for ERP and MES systems. For instance, an ERP system might need hourly production counts, while automation and control might collect data every second.
  • Formats: Automation and control systems may output data in proprietary or raw formats, whereas ERP and MES systems might expect XML or JSON structures.
  • Relevance: Not all data from automation and control systems is relevant to ERP and MES systems. Filtering out noise is crucial.

This is where the ETL process shines.

The Role of ETL in Manufacturing Data Integration

  1. Extract: The first step is extracting data from automation and control systems. This is often in the form of logs, event streams, or sensor readings. The challenge here is that automation and control systems can generate a vast amount of data at a high velocity, so it's important to identify what is relevant for the ERP and MES.
  2. Transform: This stage involves converting the extracted data into a format that the ERP and MES systems can understand. This could involve: 
    • Data Cleansing: Removing noise and correcting errors in the data. Data Cleansing: Removing noise and correcting errors in the data.
    • Data Enrichment: Adding additional context to the data, perhaps with metadata that is meaningful for business processes.
    • Normalization: Scaling data into a uniform format, particularly necessary when dealing with measurements from various types of sensors.
    • Aggregation: Summarizing detailed data, such as calculating average temperature readings over time to provide a more concise and meaningful view for operational decisions.
  3. Load: Finally, the transformed data is loaded into the ERP or MES system, where it can be used for various functions like updating production schedules, forecasting maintenance, and refining quality control measures.

Implementing ETL for SCADA to ERP/MES Data Mapping

  1. Identify Key Data Points: Collaborate with stakeholders to determine which data points from automation and control systems are valuable for the ERP and MES processes.
  2. Design a Data Model: Create a data model that can accommodate the automation and control system data within the ERP and MES structure. This could mean adding new fields, tables, or even new schemas to the target system.
  3. Select the Right ETL Tool: Choose an ETL tool that can handle the scale of data and the complexity of transformations required. This tool should also offer compatibility with the industrial protocols used by automation and control and HMI/SCADA systems.
  4. Map the Data: Develop the actual data map, specifying how each piece of data from the automation and control system translates to the ERP or MES schema. This will involve a mixture of direct mappings, where data can be copied as-is, and transformations where data must be manipulated.
  5. Automate the Process: To handle the continuous flow of data, the ETL process should be automated. This means setting up scheduled jobs or triggering ETL workflows in response to specific events or conditions in the automation and control systems.
  6. Ensure Data Integrity and Quality: Implement checks within the ETL process to ensure that the data being loaded into the ERP or MES systems is accurate and reliable.
  7. Monitor and Maintain: Once operational, monitor the ETL workflows to identify and address any failures or inefficiencies. Maintain the data maps as changes occur in either the automation or control systems or the ERP or MES systems.

Mapping in Practice
Imagine a scenario where an automation and control system monitors a production line, and the MES system needs daily production statistics.

  1. Extraction: Every 24 hours, the ETL tool fetches production count data from automation and control systems.
  2. Transformation: The tool aggregates counts, associates them with product codes, validates against any machine downtime, and formats the data into an XML structure that the MES understands.
  3. Loading: The structured data is then loaded into the MES database, updating daily production figures.

Choosing the Right ETL Tools
Several ETL tools cater to manufacturing needs. When choosing, consider:

  • Connectivity: Ensure compatibility with your automation and control system and your HMI/SCADA system data output methods and the input mechanisms of your ERP and MES.
  • Scalability: The tool should handle increasing data volumes as your operations grow.
  • Flexibility: A good tool allows custom transformations and logic to adapt to your unique manufacturing processes.

Ensuring Real-time Data Flow 
In the context of Industry 4.0, the ability to transfer data in real time across various levels of an organization—from shop floor machinery to top-level enterprise applications—is crucial for enhancing operational efficiency and enabling better decision-making. Two of the key technologies that facilitate real-time data transfer in smart manufacturing are MQTT (Message Queuing Telemetry Transport) and AMQP (Advanced Message Queuing Protocol). Here’s an exploration of these technologies and their application in manufacturing.

MQTT is a lightweight messaging protocol designed for limited bandwidth and high-latency environments. It's a publish/subscribe model that allows devices to publish information to a broker, which subscribers can then access. This model is particularly well-suited for the Industrial Internet of Things (IIoT) applications, where many devices are constantly sending and receiving data.

Key Features

  • Efficiency: It's designed to minimize network bandwidth and device resource requirements.
  • Reliability: Offers three levels of message delivery assurances, including "at most once," "at least once," and "exactly once."
  • Lightweight: Has a small code footprint and minimal network bandwidth requirement.
  • Decoupling: Producers of data (publishers) and consumers of data (subscribers) are decoupled, which means they do not need to interact with the system at the same time.

AMQP is an open-standard application layer protocol for message-oriented middleware with a focus on queuing, routing (point-to-point, publish-and-subscribe), reliability and security. Unlike MQTT, AMQP is a more feature-rich protocol that provides robustness in message queuing and transactional support.

Key Features

  • Interoperability: Designed for interoperability between different systems and organizations.
  • Reliability: Provides reliable messaging with features such as message queuing, delivery acknowledgment, and redelivery of unacknowledged messages.
  • Security: Supports secure authentication and encryption mechanisms.
  • Flexibility: Has a variety of messaging patterns, including request/reply, event notification, and publish/subscribe.

Integration of MQTT and AMQP in Manufacturing

Manufacturers may choose to implement both MQTT and AMQP within their ecosystems for different purposes. For instance, MQTT could be used for simple, low-overhead messages directly from sensors to a cloud service, while AMQP could manage the heavier, transactional messages that need guaranteed delivery across the enterprise systems.

Applications in Manufacturing

  1. Real-time Machine Monitoring with MQTT:
    • Use Case: Monitoring machine health and performance in real-time across the shop floor.
    • Implementation: IIoT-enabled sensors on machines publish status, temperature, vibration, or performance data to an MQTT broker. Manufacturing systems or MES can then subscribe to the relevant topics on the broker, receiving machine data in real-time.
    • Benefits: Enables predictive maintenance, reduces downtime, and optimizes machine utilization.
  2. Shop Floor to ERP Communication using AMQP:
    • Use Case: Transmitting shop floor production data to an ERP system for inventory management, order tracking, and financial accounting.
    • Implementation: The MES (Manufacturing Execution System) collects and aggregates shop floor data, then sends structured messages to the ERP using AMQP. This ensures reliable data delivery even if the network is temporarily unavailable.
    • Benefits: Real-time inventory updates, accurate order tracking, and timely financial data for costing and profitability analysis.
  3. Quality Management with MQTT:
    • Use Case: Real-time quality inspection and defect tracking.
    • Implementation: Inspection systems and cameras capture product data and publish quality metrics and defect alerts to an MQTT broker. The QMS (Quality Management System) subscribes to these topics, alerting operators, and quality personnel immediately when defects or deviations are detected.
    • Benefits: Rapid defect identification, reduced waste, and enhanced product quality.
  4.  Real-time Resource Allocation with AMQP
    • Use Case: Dynamic allocation of resources based on real-time demand and supply conditions.
    • Implementation: Shop floor control solutions gather data on resource utilization, machine availability, and order backlog. This data is sent using AMQP to higher-level planning systems to facilitate real-time allocation and scheduling decisions.
    • Benefits: Efficient resource utilization, reduced cycle times, and on-time order fulfillment.
  5. Best Practices:
    • Secure Your Data Streams: Both MQTT and AMQP offer security features such as TLS/SSL encryption. Given the sensitive nature of manufacturing data, always enable these features.
    • Optimize Network Infrastructure: Ensure that your network infrastructure, including routers, switches, and gateways, is optimized for the volume and frequency of data transfers.
    • Scalability: As your manufacturing operations grow, so will your data. Ensure that your MQTT broker and AMQP server can scale with the increased data load.

Data in manufacturing operations is akin to the lifeblood of an organism. It must flow seamlessly from one component to another to ensure the manufacturing operations work effectively and efficiently. The heart of this data flow is data mapping—converting raw data into structured information that people can consume, analyze, and act on. A significant tool in this endeavor is the ETL (Extract, Transform, and Load) process.

The ability to transfer data in real time across various levels of an organization, from shop floor machinery to top-level enterprise applications, is crucial for enhancing operational efficiency and enabling better decision-making. MQTT and AMQP are technologies which facilitate real-time data transfer in manufacturing operations. They’re both worth looking at.

In our next blog we’ll look at establishing feedback loops, leveraging middleware, and scaling with cloud platforms.

If you’d like more information on anything, please contact me through MESA International (

No comments: