- By Deane Horn
- February 23, 2021
- Softing Inc.
A transaction manager controls the world between the operational-technology-centric PLC and the information-technology-centric SQL database so OT and IT systems can interoperate successfully and deliver promised benefits of digitalization.
The worlds of operational technology (OT) and information technology (IT) are coming together in factories and industrial plants around the world, each with the same goal: use digitalization to improve productivity and other business outcomes. But sometimes the two worlds require some translation, some teamwork, so transactions can flow smoothly between the two.
Imagine you are manufacturing an automotive part, like a wheel.
Let’s say the wheel is type “premium mag wheel.” There are a thousand parameters that describe how that wheel will be made, including size, color, where to drill holes, size of the holes, wheel spoke specifications, etc. And this is just one recipe. If you manufacture 10 different wheels, you need 10 different recipes.
Operational technology (OT) systems must handle all the parameters for all the recipes, which amounts to a lot of operational data.
PLCs, the traditional controllers for industrial operations, have challenges with massive amounts of data. Why? Because the memory and performance of a PLC is optimized and dedicated to production: moving machines, moving product, and making product. And, in PLCs, memory is very expensive.
What platform is good with handling complex data and tables, and relieving the computational burden and risk from the PLC? It’s an SQL database—the centerpiece of IT systems.
Figure 1. A representative SQL database.
What’s an SQL database? You can visualize it as a table (Figure 1). Guess who has one? Most likely you! Your IT manager is probably already using an enterprise SQL database across your company to share information throughout your organization. Why? Because businesses need to share information across the company, and an SQL database is designed for this.
Relieving the burden on PLCs
So why are you putting recipes in your PLC? Why are you storing production KPI’s and quality parameters in your PLC? You could be utilizing the towers of servers and databases you already have in your plant, or residing in the cloud, and you could relieve a huge burden from your PLC.
You probably did this because you were in control of the entire process, you know how to write code, and you are good at getting things done yourself.
At some point, however, the PLC will reach its limit. The process will get a little risky. Before that happens, it’s time to step back and architect a solution in a standard, scalable way. You can relieve the burden from your PLC and tap into the massive resource that is your enterprise SQL database(s).
How? Connectivity-wise, it’s pretty easy because there are only three steps: Prep the PLC; prep the SQL database; and implement a transaction manager between the two. Then let the data flow in a standard, scalable, supportable fashion.
Step 1: Prep the PLC. You’ll no longer need hard-coded recipes in your PLC. Instead, change these constants to tags, or UDTs, in your PLC. Why? You’ll have one logic code base now using variables, and you’ll be able to update the variables each time you have a different part to manufacture. Each time there’s a new recipe, just download it from the SQL database to the PLC.
Step 2: Prep the SQL enterprise database. You, the control engineer, need to be prepared to guide the IT manager who owns the SQL database, so here’s what your IT manager needs to know.
In order for your IT manager to create the table in the SQL database, you will need to provide him or her with three pieces of information: headers; recipe names; and recipe data (See Figure 2). Headers are just a short description, or a column title, for your data. If you use Microsoft Excel as your tool to build a template, you can start by defining headers in the Excel spreadsheet and placing these at the top of each column.
Figure 2. Three pieces of information for the IT manager
In an SQL database, each row is called a record. Recipe names are placed in the first cell of each row. If you have 10 recipes, you will have 10 rows, or records.
And finally, you need to fill out your recipe spreadsheet with data. This means for each recipe, fill in the data values, which will be constants, for each column. For example, for header “color” and recipe “premium mag wheel” the data value will be “black”. Now your spreadsheet has recipe names, column descriptions, and data.
This is all the IT manager needs to build the SQL table!
Step 3: implement a transaction manager. After the PLC tags are created and SQL table is created, the data needs to move between the PLC and the SQL database. How does a recipe get downloaded from an SQL database to a PLC? You need a transaction manager—and this is not you writing code!
What transaction managers do
What does a transaction manager do? Let’s say your goal is to download a recipe. Now that your tags are set up in your PLC, and tables are set up in your SQL database, the transaction manager will log into the PLC, log into the SQL database, and browse both tags (destination) and tables (source).
The transaction manager contains the connections between PLC tags “whl colr” and table records (wheel color), and this becomes your map. To facilitate data movement, the transaction manager includes triggers that initiate movement.
For example, perhaps you want a barcode scan to trigger a transaction. If the barcode scan equals “prem mag wheel," the transaction manager will select “prem mag wheel” from the SQL table (the first recipe in our example), which will return all of the data in that row, for that recipe, and load it into the PLC. Your goal is to implement this without writing code, without protocol translation, keeping it standard, scalable, and supportable.
The transaction manager controls the world between the PLC and the SQL database and, luckily for us, it is unique in that it completely understands both PLCs and databases so that we don’t have to. A transaction manager provides failover paths, email alerts upon successful transactions, or status tags when it’s critical to know that transactions completed.
Transaction managers come in many forms, for example, software that runs on a PC, industrial gateway DIN rail devices, or and even in-chassis PLC modules.
Fig. 3 The tManager from Softing is an example of a PLC in-chassis transaction manager in the form of a ControlLogix or CompactLogix PLC module moving plant floor data between PLCs and SQL databases.
A successful digital transformation project starts with creating a team; identifying the work processes, data, data sources and transaction manager; and then creating an action plan.
Whether you are optimizing production processes, monitoring metrics, implementing high speed sorting, tracking quality, or automating workflows, a digital transformation project can save a ton of time, lower risks, and improve scalability. In each case, following a few simple steps and using a team approach will help you achieve a more successful project.
Did you enjoy this great article?
Check out our free e-newsletters to read more great articles..Subscribe