Opinion: Do you need a $60,000 process historian to log data? One SQL database expert says no.
By Control Engineering Staff -- Control Engineering, 3/26/2009
Steve Hechtman, Inductive Automation
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 a 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.
The rumor denigrating relational databases for poor throughput is baseless. A standard, off-the-shelf Microsoft SQL Server coupled with FactorySQL 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.
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 ten to thirty times the cost of a relational database using a driver like FactorySQL 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.
Steve Hechtman is president ofInductive Automation(Sacramento, CA), maker of FactorySQL, a product that enables ERP systems to interact with plant floor data directly through SQL databases. This article originally appeared in his blog, accessible through hisWebsite.
Do you agree with Steve, or think he’s blowing smoke? Voice your opinion below in the TalkBack section.
– Edited by Renee Robbins, senior editor Register here and scroll down to select your choice of eNewsletters free.
Control Engineering News Desk
-
I would like to know who's process historian are you buying for $5k and how many tags. I really have a hard time believing this statement without some examples. I do not know what Steve was referring to, but I doubt you are going to buy a historian with a "full blown" SQL Server 2005 for $5k from Rockwell, OSI PI, GE, Wonderware or any of the other people that play in the large process area that is slowly moving from historically a DCS to name your favorite up and coming PAC controller. So, give some examples.
Darren Ash - 2009-30-11 19:53:53 CST -
The way I see it, it's a matter of choice:
On one hand there's proprietary SCADA vendor historians. The vendor has essentially made most of the important choices for you, leaving you a few "wizards" and dialog boxes to complete a configuration. This is a perfectly valid pick for companies that have minimal requirements and just want to plug it in and move on. Many companies fit this profile, but I suspect their numbers are diminishing.
On the other hand is the increasing number of tech savvy companies that want their historian to be a deeply and seamlessly integrated cog of their corporate processes and databases machine. They want their IT, manufacturing, maintenance, quality assurance, and other departments to partner and contribute expertise in making implementation choices that will leverage ALL available company intelligence. They want (and sometimes demand) the right to pick the DBMS of their choice, because they already have a DBMS standard with a site license and in-house experts. They want database-intrinsic redundancy and clustering ability. They want to build and monitor the historian the way THEY want to, with DBMS tools they already know. They want the ability to customize its capabilities with Python or Java. They want to change it any time they want. Companies like Inductive Automation provide tools to build it visually, intuitively, and quickly. Import 10,000 tag references? Drag/Drop/Done!
Sure it will take a little more up-front effort to achieve their "perfect" (as defined by the user, no one else) historian, but tech savvy companies realize that the dividends pay out perpetually, making the small additional effort extremely rewarding. And companies like Inductive Automation are continually squeezing the "little" ever smaller with improved tools.
Choices can make some people break into a cold sweat, while others enthusiastically jump in and see how much enabling power it affords. There are historian products for both types of people. You choose...
Dennis Runo - 2009-22-10 16:07:56 CDT -
Nice try but I think you missed the mark with this one. I think it is the writer who is out of date. A process historian can be had for as little as $5K and that includes a full copy of MS SQL Server 2005 (Similarly, you'll have to show me the Ferrari that costs $500K). More important is that the author oversimplifies the effort involved in creating and maintaining a process historian. How long would it take to create 10,000 tag references in his system? It's a simple wizard with Wonderware's historian. What happens if the applications in the field change? Again, a simple import wizard. What about modifying ranges, failover or store and forward? Sure you can create your own database and use SQL inserts - that's free in Wonderware's Intouch - but there is a value in having an optimized data schema. Now developers can develop standard client applications that will work against the database. With Wonderware's installed base many companies have adapted their client applications to seemlessly look at this data without any configuration.
Sure a Camry would do just fine if you needed to drive the kids to school - but then again so would a little red wagon if you lived close enough. Comparing a driver that can connect to a SQL database to a process historian is like comparing a little red wagon to a minivan. If a company's data is so insignificant that it is only willing to invest in a little red wagon, then they may want to reconsider why they are collecting it in the first place
Kenny Banks - 2009-13-10 16:04:07 CDT
Historians vs. relational databases
02/09/2010Canary Labs releases new process historian
02/09/2010MS Windows CE to SQL
02/09/2010
View All All Webcasts
