It is common for companies to use databases for long-term storage of their critical information. In our experience, we also see machine setup information, short term operational data and much more besides traditional historical data put into shop floor databases in the operations technology or “OT” world.
Over on the business side or the “IT” world, everything somehow ends up in a database: Production plans, forecasts, orders, it’s all there. Increasing business demands and IT/OT convergence are driving more real-time integration between these databases and control systems.
This blog discusses some of the key reasons users like yourself need this information and then shows how the DataHub can help move this data from a database to your control systems via OPC DA, OPC UA, Modbus and other interfaces or protocols that are common in the automation layer.
Reading data from a database and exposing it to your control system does not have to be a daunting task. With just basic SQL query knowledge, you can easily get the information you want out of the database and make it available to your HMI/SCADA or other OPC clients.
You might need to integrate this data for a number of reasons important to your process and operations. Some of the more common reasons might be:
- Reading recipe and/or batch or machine setup data for control processes
- Data verification
- Displaying the DB query results in a web page
- Playing back historical events
Let’s go ahead and lay out the benefits of each of those particular use cases for integrating your DB.
Reading Recipe, Batch, or Machine Setup
In batch processes, the PLC needs to know how much of each ingredient is needed to make this specific product and normally the different recipes are defined in a database. One of the most common reasons our users query information from their database is to retrieve a recipe or batch and then write that data to items in the PLC before starting the new batch. Another common reason is for machine setups to produce a specific product.
Using the Cogent DataHub, you can write a query then take the resulting set of data and expose the values as tags in the DataHub. Because the DataHub supports multiple client and server protocols, once the data from your database is in the DataHub, it is easy to integrate it with your control systems using standard protocols like OPC DA, UA, and others.
For example, if all you need to do is write this information out to an external control system that exposes an OPC DA or UA interface, then you can simply write each piece of data from your query result to the OPC item in DataHub and the DataHub will in turn write that tag to the OPC Server automatically.
Another common reason to query information from a database is to validate the information that has been logged to the database. This is most common in industries with strict government regulations like pharmaceuticals, oil & gas or power generation. In these industries, organizations can face stiff sanctions if they do not have the accurate data required for EPA, FDA or other government agency reporting and accountability.
To ensure the data they need is getting logged completely and accurately, many of our users will query the database after the each piece of data is logged to validate the information is there. If the data is not there, this provides the opportunity to then attempt logging it again while they still have access to the live data values and before it is lost forever. It also provides the opportunity to notify parties that need to know about the mismatch so they can find and address the root cause.
Display the Results of a Query in a Web Page
Sometimes, all our users need is an easy way to visualize the data from their database alongside other real-time data. The DataHub makes it easy to take the results from any database query and display them in a table in DataHub WebView.
The WebView is a web-based HMI that allows you to display any available data in the DataHub in your web browser, even if it is the result of a database query. This can be done in two easy steps using the DataHub’s ODBC Query Interface.
- Write the result of your query to a WholePoint in DataHub.
- Map this data point to a Filtered Data Table in WebView.
The WholePoint is a complex structure automatically available in the DataHub that includes your database columns and rows from the query result. The Filtered Data Table knows how to read this complex structure and displays the entire results in tabular format, making it as easy as filling out the configuration screen above to display a query result on a web page. Once the data is in the Filtered Data Table you can sort, group and filter the data to meet your viewing needs.
It is also important to note that this is a read-only view into the database which ensures it is a safe, secure method for your operators to view the data without the ability to accidentally change any values.
Event Playback
Have you ever need to pull timestamped data out of some other database and feed it into your process historian? If you have tried to do this before, you may have realized that there isn’t always a straightforward way to accomplish this. That is because you are converting historical data to real-time data and then back to historical again.
One of the most critical pieces of information that is part of the data is the timestamp. You need a way to preserve the original database’ timestamp which probably came from a device also, so that when you log it with your process historian you don’t lose the time of the original event. Otherwise, you end up with values that all have current timestamps, which completely invalidates the usefulness of the data.
Most tools that can convert database data to OPC can’t do this as they just apply the timestamp that they retrieved the data at for the OPC items. However, this can be accomplished with the DataHub because it can query the database and play back the data, including the original timestamp, so that the process historian can log it again and maintain the history of the data.
We hope this blog post has given you some ideas on why users like yourself need to integrate database information with their control systems. If you have any questions about your own particular use case, submit a question and I will be happy to answer it.
The DataHub offers a wide range of functionality for accomplishing an extremely diverse set of challenges many users, like you, may experience on different integration projects. For those of you not familiar, some of that built-in functionality includes bridging data between different sources, sending email or SMS text reports or notifications, acting as a gateway solution between different types of OPC and other data sources and more.
