FactorySQL Under the Hood

FactorySQL brought magic to the plant floor. It would have been called "Yesware" except that name was already taken. This is because you could say "yes, I can do that" to practically any PLC to SQL database interaction you could imagine.

Want to log PLC values? No problem. Want to send recipes to PLCs? Go for it. Want to mirror PLC addresses in the SQL database so that web pages can display and control PLC values? Go for it. Want to copy whole sections of PLC memory to another PLC somewhere else. Yes you can. In fact, the possibilities are endless but they boil down to being able to say "yes, I can do that" while everyone else is shaking their heads "no."

I've had two integrators sit beside me on a major project where all they ever said was "I'm not sure how we'd go about that" and all I ever said was "don't worry, we'll take care of it," and then we would, usually in minimal time. The other integrators looked kind of dumbfounded because I kept doing this. But my secret was FactorySQL.

Well, now we have Ignition and under the hood we still have FactorySQL but it's no longer called that. Now we call it the SQL Bridge Module. But it does all the same things and more. For example, you can make programming changes while groups are running without interrupting anything, just like online programming changes in a PLC.

I fear one thing though. With all the excitement about the Ignition platform I'm afraid we're neglecting to tell people about the real power they have under the hood with the SQL Bridge Module. The other day when one of the sales people showed it to someone they were blown away by what they saw - they could hardly believe what they were seeing was true.

So my message to you... check it out. You'll be amazed at what you can do.

The perfect complement to PLCs

PLCs? Okay, you’ve tackled PLCs and now you can program 'em with one hand behind your back. So what’s next? What’s the next logical challenge? Think SQL and relational databases. Why? You’d be amazed the similarity. It’s the next logical progression.

You might ask how it is they’re even related. For one thing, relational databases can sort of be an extension of PLC memory. Live values can be mirrored there bi-directionally. Historical values and events can be recorded there as well. But operators and managers can interact with them too. It’s been over 20 years of working, living, breathing and thinking PLCs, but over the last six years I’ve delved heavily into SQL and learned a lot about relational databases. I’ve discovered that working with SQL is remarkably similar to working with PLCs and ladder logic.

SQL has four basic commands and about a hundred different modifiers that can be applied to each. These can be applied in various ways to achieve all types of results. Here’s an example. Imagine effluent from a wastewater plant with its flow, PH and other things being monitored and logged. That’s what you typically see. But now let’s associate other things with these, such as, discrete lab results, the name of the persons who did the lab work, the lab equipment IDs and calibration expiration dates, who was on shift at the time and the shift just prior, what their certification levels were, what chemicals where added and when, who the chemical suppliers were, how long the chemicals sat before use, and so forth ad infinitum. All of this becomes relational data, meaning that if it’s arranged properly in tables you can run SQL queries to obtain all types of interesting results. You might get insight into the most likely conditions which could result in an improper discharge so it can be prevented in the future.

In my explorations of SQL, I found myself looking at the layout of my tables and evaluating the pros and cons of each layout. I massaged them, turned them on their side, upside-down, and finally ended up with the most appropriate arrangement for my application. And similar to PLC programming, I explored innumerable what-if scenarios. I was struck by the amazing similarity in my approach to developing solutions for PLCs. This has been a lot of fun — in fact exhilarating — just like PLCs used to be. It’s the next logical progression you know.

SQL is a high level language that isn’t very hard to learn and you can be very clever with it. I prefer to think of it as a natural extension to my PLC programming skills. Now that you have the machinery running, what did it do? Furthermore, relational databases and SQL pull people and processes together. Machines don’t run alone. They’re merely part of a containing process and that process was devised by people. SQL and relational databases form the bridge to integrate processes, machinery and people together. I don’t believe a COTS (commercial-off-the-shelf) package can do it any more than you could offer a COTS palletizer program and have it be of any use. It just doesn’t work that way. Every machine is different. And every business process is different. That’s where the SQL comes in. It has to duplicate or augment existing process flows and these are intimately connected to the machinery. And that’s why the PLC programmer is best suited to implement solutions involving PLCs and relational databases.

So where do you start? I would suggest picking up a book at the bookstore like one of those dummies books. Then download and install the open-source MySQL database server along with the MySQL Administrator and Query Browser. It only takes a few minutes to install and then start playing. You can read about a LEFT JOIN or INNER JOIN but typing one in and observing the results is worth about 1,000 words. At the end of an evening you’ll probably be very excited with all of your new found knowledge and be thinking of endless ways to employ it in your own field of practice. Happy SQLing!

Process Historians vs. SQL Databases

I wish to register a complaint. There is a rumor that has been circulating for years that relational databases are too slow for fast process data and that only process historians are up to the job. Vendors of process historians will cite sluggish performance and the lack of data compression as the reasons standard off-the-shelf relational databases won’t work. Apparently the last time they used an SQL relational database was a few decades ago.

While there may be some specialized domains where process historians have a niche, they are not a practical choice for most industrial applications. In effect, historian vendors are saying your Toyota Camry is inappropriate transportation because it is incapable of going 180 mph or finishing the quarter mile in under 10 seconds.

An Ill-Founded Rumor
The rumor denigrating relational databases for poor throughput is baseless. A standard, off-the-shelf Microsoft SQL Server coupled with Ignition's SQL Bridge Module can log in excess of 100,000 tags per second using a desktop machine. In all likelihood, other factors such as the industrial network would become bottlenecks before the database does. Furthermore, today’s generation of SQL relational databases are designed to scale gracefully to power high-volume website traffic, whose load peaks dwarf those of industrial controls applications.

Data compression is an area where process historians do score a point. However, even this consideration can be handled with standard off-the-shelf SQL relational databases. Take a look at the MySQL 5.0 Archive Storage Engine which achieves on average a four to one compression ratio. Proprietary process historians may beat that, but let’s get back to the point of practicality. Hard disk space is so cheap these days that even considering this point is becoming an anachronism. For the rare application that demands it, table compression coupled with intelligent data logging allow databases to compete even in this regard.

One crucial question that process historian vendors omit is: what are IT departments willing to support? When I make initial contact with IT folks, I always ask which relational database they use. Then I assure them we’ll work with that. This generally makes them very happy. Believe me, you want IT on your side or your project will end up on a data island which is useless in an enterprise system. Think of it from their point view; they have the training and tools, generally, to support just one type of database. With these tools and training they can support the database with scheduled backups, tuning and other maintenance.

Support, Relational Data, and Cost Factors
Okay, we’ve heard process historian rants about relational databases; let’s talk about the downside of process historians. Let’s start with support. Just check the Amazon bookstore for any one of the proprietary process historians and you’re likely to come up empty handed. On the other hand, check for “SQL configuration” and you’ll come up with hundreds of books. How about finding people to support these proprietary systems? Good luck.

Then there is the concern about supporting relational data with a process historian. Frankly, the middleware layer is all about relational data. Time-series data, which is what process historians deal with, is just a fraction of what is needed in the middleware layer. Correlating batches, shifts, inventory, orders, downtime, quality, etc., is purely relational in nature, and these are the features that today’s enterprise integration projects demand.

What about a cost comparison? The process historian is going to be 10 to 30 times the cost of a relational database using a driver like the Ignition SQL Bridge Module depending on the number of tags required. The controls industry is still backwards on this point and prefers to price its software per tag as though the extra tags cost money to manufacture.

In summary, we’re talking about practical choices. The Ferrari may be great fun, but do you need a $500,000 vehicle to drive the kids to school or would the Camry suffice? Likewise, do you need a $60,000 process historian to log data? A relational database makes a great historian, but the reverse isn’t true. A process historian cannot process relational data. For the vast majority of systems, a relational database has more than enough power to service the historical and relational data requirements, making it not just the practical, but the wise choice.