Historians vs. relational databases

An important factor when considering implementation of historian software is recognition of differences between plant-floor data and the business environment. Many companies want to approach a manufacturing data archive in the same way they approach an enterprise archive—with a traditional relational database.

By Kevin Bernier April 1, 2005

An important factor when considering implementation of historian software is recognition of differences between plant-floor data and the business environment. Many companies want to approach a manufacturing data archive in the same way they approach an enterprise archive—with a traditional relational database. However, a relational database is rarely the best approach for the manufacturing floor for many reasons.

First, manufacturing operates in real time, requiring very fast data collection for optimal analyses. A plant-wide historian provides 10-20 times faster read/write performance over a relational database and 1-millisecond resolution for true real-time data. Additionally, the plant-wide historian is optimized for ‘time series’ data, while a relational database is built to manage relationships. For example, relational databases are great at answering a question such as: What customer ordered the largest shipment? A plant-wide historian, on the other hand, excels at answering questions such as: What was today’s hourly unit production standard deviation?

With powerful compression algorithms, a plant-wide historian can store years of data easily and securely online, which aids performance and reduces maintenance for lower costs.

As with any major software project, the costs and time to implement a manufacturing data archive can make achievable results and ROI appear to be far in the future. This is absolutely the case with a relational database architecture and the many custom interfaces required for implementation with real-time systems. Relational databases also require companies to manually create and manage custom tables, which can be time intensive.

However, using standard interfaces for a plant-wide historian can decrease implementation time by approximately 50%, reducing overall costs. There is also no management or creation of data ‘schemas,’ triggers, stored procedures, or views. With this ease of use, you can install and configure a system in hours without specialized services, such as custom coding or scripting for the installation.

Long-term maintenance is also greatly simplified. With a plant-wide historian, no on-line maintenance is required. With a relational database, however, maintenance can be a full-time job, as companies must manage archives and disk space due to poor compression (see graphic). Additionally, tag imports and maintenance must be performed during scheduled downtime, as there is no on-line maintenance.

Assuming 500 floating point values/second, a plant-wide historian uses much less disk space and provides faster read/write capabilities. Disk space must be more closely managed with a relational database due to poor compression—even with proprietary, pre-compressed data workarounds. Source: Control Engineering with data from GE Fanuc Automation.

A plant-wide data historian enables companies to collect and analyze tremendous volumes of information generated in plants for improved performance, integrating plant floor and business systems, and reducing the cost of meeting industry regulations. Data collection and analysis can help increase product quality and consistency, for example, by comparing past production runs, analyzing data prior to a downtime event, and plotting ideal production runs against in-process runs. Aggregated data also allows report preparation and information sharing using standard Web-browser tools.

Lastly, the plant-wide historian also serves as the vital link between plant operations and the business, providing business systems with actual data they need to gain a clear, accurate picture of current production status or historical trends. This detailed information can yield numerous benefits, including ability to let customers securely access order status information.

Author Information
Kevin Bernier is director of plant intelligence products, GE Fanuc Automation;