Real-Time Manufacturing Database Architecture

AT A GLANCE

Information system design

IT and engineering cooperation

Process data collection

Data speed and quality

Sidebars: Real-time databases provide real-time feedback

In 1988, I lead a project team that developed one of the first manufacturing information systems to use a relational database. It recorded nylon spool production information for DuPont. The essential architecture was an Allen-Bradley PLC 2/30 talking to a DEC MicroVax cluster. Although we knew what data we needed, the hardware and software did not have the horsepower to log and report the detailed information desired. By 1998, the building blocks for a robust manufacturing information system were available.

Over the past five years, the engineering- and business-driven need for manufacturing process data has led to a preference for a structured and modular approach to develop manufacturing information systems, with the focus on the relational database.Expectations for maintenance of an installed plant process information system are the same as for a control process software system. That is to say, the same in-house control-oriented personnel must understand and troubleshoot the plant process information system, without need of an IT expert available 24/7.The primary design criteria for manufacturing information system projects are: documented code, plant maintainability, customer ownership, open database connectivity, and future product obsolescence. Based on these criteria, three essential building blocks in this architecture are: process database server, process data collector, and Web-based reporting.

Process data server

As shown in the graphic at right, the process data server is a database server and an Internet/intranet Web server. The process data server can be located in the front office computer room or offsite via secure Internet. Memory and storage requirements for most manufacturing facilities are a nominal 512 MB and 40 GB of storage. The system archives data older than six months.

To support the needs for production and process information and succeed in the design/build of a manufacturing database requires a level of communication and cooperation between the IT department and control engineers not commonly exercised. The reason: computer and software skills needed to set up a manufacturing database are the same basic skills that a database administrator would possess to design and build a financial, inventory, or contact database. When comparing the development of manufacturing database to other types of database projects, the differences begin with conception and design. It is here the input of the control engineer is needed to identify the origin of the data and format it into proper engineering units or production metrics so that it can be understood by production management.

Plant-floor controllers automatically feed data to a manufacturing database. This can be done with a direct communication link to a PLC or controller in a DCS. With a legacy system or a microprocessor (compiled code) controller, an alternative approach is to place sensors in key locations to transfer manufacturing data via an industrial network to the database. Manufacturing database design can provide a marketplace edge if the information is used to advance business goals. To successfully design the database, one needs to understand table definition—where database views are needed and how to write stored procedures. When a query is made for a report, if it takes more than a few seconds to come up on screen, it will likely be too slow and require some redesign.

This diagram shows the basic architecture of a manufacturing informatino system, built around the company process database, Web server, and process database collector.

Control engineers should have an essential role in determining minimum essential criteria for speed and quantity of data. In a homogenization process, for instance, the record for the process stored in the manufacturing database need only have a high temperature, low temperature, and an average temperature logged for a certain time interval. The control engineer should write PLC code to provide these parameters to the process data collector.

Process data collector

Though the database server may be designed and built by the IT staff, the data collector resides on the plant floor and is therefore typically the responsibility of the control engineer.

The collector is the data acquisition point for all manufacturing data. Its essential function is to collect data from plant-floor controllers, store data in a “lite” version of the manufacturing database, and provide transaction management of records to the process data server.

The manufacturing database in the collector will store real-time data values in the same table structure and data formats as the process data server. The control engineer can take responsibility for data in the collector and the database administrator can take responsibility for data in the process data server. In this way, the manufacturing database becomes common ground for IT and control engineers to support the needs for plant information.

The server can be an industrial computer located in proximity to the manufacturing process; a NEMA 4/12 enclosure is recommended. The collector “gray box” has three connections: Ethernet (to server and other plant computers), high-speed industrial network interface (Modbus, DeviceNet, etc.), and power. A number of excellent drivers on the market can help get real-time data from PLCs directly into the process data collector database.

By using a Web server, such as Microsoft IIS (Internet Information Services) Server, to provide diagnostics and essential reports, the control engineer can ensure data integrity between the process data server and the collector.

When the network connection between the data collector and data server is lost or blocked, data transactions are stored on the data collector and rolled forward to the data server once the connection is re-established. Typically, the process data collector is configured to store three shifts of data.

Web-based reporting

End-users of manufacturing information are typically: plant managers, quality control staff, and production supervisors. I’ve found that the most commonly desired reports are the “bottom line” and the “deliverables” from the manufacturing database. Following are some examples of reports this architecture can provide:

Quality data;

Downtime reports;

Compliance reports for shipments;

Work order system with ties into time management software;

Ingredient usage reports; and

Production reports on a per shift basis.

Reports are provided via an Internet browser. When the plant manager points the Web browser at the process data server, an information portal becomes available.

The report can be thought of as a “process database record,” providing an information snapshot for a selected set of parameters, such as shift, batch, or work order.

Under the hood on the process data server, where the programming and configuration exist for reports, the Web-based server runs active server pages, with Visual Basic scripts often triggering stored procedures in the database to bring up the desired information.

Web-based reporting, constructed in this way, requires no applications to be loaded on the desktop (or additional licenses purchased), and information is accessible over an intranet or secure Internet connection.

Author Information

Walter J. Cholawsky, P.E., is principal engineer at Program4 Engineering Inc., an automation engineering firm specializing in industrial control and information software;

Real-time databases provide real-time feedback

Collecting and analyzing real-time data from the plant floor plays a critical role in meeting the market’s demand for consistent product quality and improving time to market for a manufacturer’s products. By sending production data to a control system, real-time control databases allow for near real-time decisions and enforcement of quality specifications. In many ways, real-time control databases simply provide “decision support systems” for a plant.

Many customers use real-time control feedback for closed-loop quality control. For example, government regulations require that airbag manufacturers log and verify their quality data at each step throughout the assembly line process. Here, the control system (usually a PLC for discrete manufacturers) sends product codes and quality information to a database, where the data are stored and the airbag is checked to ensure it meets appropriate criteria. The same transaction communicates back to the control system that “yes, this product’s data are logged, and the quality is okay,” or “yes, this product’s data are logged, but the quality is bad.” If necessary, this feedback initiates a process to remove the bad part from the line.

Manufacturers combine networks, intelligent data-centric programmable controllers, solid OPC servers, and reliable transaction engines to connect real-time databases to the control systems. These products help provide a transport architecture to move data to and from the control system. Real-time control system databases require high-speed, reliable systems. For manufacturers to initiate processes (manual and automated) to remove quality failures, they need to be able to quickly and reliably send data from the plant floor to the database and back.

So, why don’t manufacturers store the data closer to the plant floor in their architecture? Today’s commercial databases provide great uptime. For years, Microsoft, Oracle, and IBM have focused efforts on highly available architectures. The closer information is stored to the plant-floor architecture, the more difficult it can be for manufacturers to access it. Storing data in a real-time control database provides a secure and open way for many users to access the data.

A multitude of solutions exist for customers requiring data-centric real-time control—database or otherwise. Manufacturers can choose to keep their business/quality logic in the database, because real-time control database solutions can simply wire data from the plant floor to the top floor. Since real-time control databases may never replace the security of keeping all data at the PLC-level—due, in part, to safety reasons and incredible uptime statistics—users also can choose to access manufacturing information via a data-centric controller. Manufacturers also can choose to augment a hybrid solution by downloading all quality specs to the controller via a real-time control database application, and compare programmable controller data against live quality data directly from the line.

Mike Pantaleano is product marketing manager at Rockwell Software.

Do you have experience and expertise with the topics mentioned in this content? You should consider contributing to our WTWH Media editorial team and getting the recognition you and your company deserve. Click here to start this process.