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:
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.
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:
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:
The transactions can be executed either periodically or based on a trigger.
Key criteria for selecting a transaction manager software are:
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.
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.
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.
Steve has retired from Hallam-ICS, but his contributions to the company continue to be valued.
Read My Hallam Story
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.