This is part two of a five-part series on Manufacturing Intelligence (MI).  This blog post describes data sources and database transactions needed to log data for Manufacturing Intelligence solutions. It is intended for all persons involved in the data stream, including: Automation Engineers, Plant Managers, Data Analysts, and engineers involved in Continuous Improvements and EH & S.  This topic is not limited to manufacturing and is useful for all facilities with a need to collect and use large amounts of automated data. 

Part 1 - What is Manufacturing Intelligence?

Part 2- Data Sources And Transactions For Manufacturing Intelligence

Part 3- Manufacturing Intelligence - Data Storage

Data for Manufacturing Intelligence and data visualization can be found in many different types of devices in a facility or plant.  The correct drivers and software are needed to get the data and move it to a database.

Here are some examples of devices and software that may have the data you intend to capture and log:

• Networked Devices such as power meters and counters.
• Programmable Logic Controllers (PLC)
• SCADA systems
• Building Automation and Control networks
• OPC Servers

Using OPC Servers as the data source is the preferred choice in most cases, since it eliminates the need for device-specific drivers on the downstream transaction manager and data-logging software.  The OPC server software is installed as a service on a Windows server.  It communicates  with PLCs and field devices and can be read or written by the database transaction manager software. The more uniform the data sources, the faster and easier the configuration.  In many cases the transaction manager software may only be capable of reading and writing an OPC server.  The preferred OPC server for new projects is an OPC Unified Architecture (UA) Server.  The OPC UA server is newer technology than the classic OPC DA server, is cross-platform, and does not rely on Microsoft DCOM technology.

Transaction Manager

The key software element for logging the data into a database is the “Transaction Manager”.  This software will have many names from various vendors, but the purpose is to execute database transactions. 

There are a wide range of choices available for logging data software, including: 

• configurable components in the SCADA system
• custom code in the SCADA system
• data loggers
• transaction managers
• MES systems

We will focus on transaction managers.  The transaction manager is configured to read/write from your data source, such as an OPC server.  The transactions can:

• Insert a record into a database table
• Update columns in a database table record
• Read from a database record and write to the OPC server tags
• Execute a stored procedure

The transactions can be executed either periodically or based on a trigger. 

• Periodic provides logging at specified times of the day and/or at a fixed periodic interval.
• Triggers provide logging based on a trigger tag or the change in value of one or more of the OPC tags.

Key criteria for selecting a transaction manager software are:

• Transaction process is reliable
• Easy to configure and troubleshoot
• Flexible options for triggering
• Ability to execute stored procedures
• Ability to modify and add new transactions without affecting or stopping currently running transactions.

Here are some Transaction Manager software products that we use to log data.  Hallam-ICS can help you assess these products for your project and provide installation and configuration services.

1. Software Toolbox OPC data logger and Softing dataFEED OPC Suite provide lower-cost software useful for small-scale projects.
2. Softing tManager Enterprise Appliance Transaction Modules (eATM) are installed in the PLC chassis and can log data directly for the PLC with installing software on a server.
3. Rockwell FactoryTalk Transaction Manager has been around for many years and can provide reliable and stable services.
4. Inductive Automation Ignition SQL Bridge is a modern and modular product that delivers a multitude of options to configure transactions. This product includes excellent features for monitoring status and troubleshooting problems.
5. GE iFIX SCADA has a means to log data to an SQL database directly from the SCADA database. This feature is useful, but is more limited than other choices, as it required iFIX as the data source and is directly integrated into the SCADA database.
6. VBA code within iFIX or other SCADA hosts can be used to log data or call stored procedures; therefore, acts as a custom transaction manager. The code approach is useful when you have a VBA environment, a means to schedule execution code execution, and complex code that needs to be executed at that level prior to database inserts or updates.Example Architecture for Data Collection

Stored Procedures

A powerful and frequently overlooked tool for database transactions is to use stored procedures in the database that are executed by the transaction manager.  A stored procedure is SQL code that is located and executed on the SQL Server.  Data is passed in via input parameters. The procedures can be tested and executed independent of the transaction manager; thereby allowing the transaction code to be well tested prior to integration with the data sources.

Stored procedures provide a flexible and powerful means to log data.  Stored procedures can be used to perform lookups in other database tables, perform calculations, and execute a sequence of steps.  The results can then be logged as inserts or updates to one or more tables. 

The MI team at Hallam-ICS are experts at writing and testing stored procedures and can help you build a solid and reliable data logging system using this approach.

Next steps for the database architecture

In the next blog post of this series, we will dive into data storage, database architectures, and show the useful features of each database type for securely storing  your plant data.

About the Author

Steve has retired from Hallam-ICS, but his contributions to the company continue to be valued.

Read  My Hallam Story  

About Hallam-ICS

Hallam-ICS is an engineering and automation company that designs MEP systems for facilities and plants, engineers control and automation solutions, and ensures safety and regulatory compliance through arc flash studies, commissioning, and validation. Our offices are located in Massachusetts, Connecticut, New York, Vermont and North Carolina and our projects take us world-wide.