34 n CONTROLS AND SOFTWARE November /December 2023 www.drivesncontrols.com Relational or historian databases: which is best? Manufacturers are increasingly generating data and it has become increasingly important to collect, save and distribute this data for further use. Access to this information lays the foundation for faster decisions, increased productivity and reduced costs. Many industrial companies use relational databases (RDBs) to increase productivity and cut costs. Relationship databases are designed to handle and store contextual or genealogical information about the production process. But they often do not have the right functions to handle large amounts of time series and/or process data. Historian databases on the other hand are designed to collect, store and distribute large amounts of high-resolution time series/process data. They retrieve time series data from the local system all the way up to the company level, giving you an overview of the entire production process. Implementation Relational databases do not offer built-in data collection capabilities. Custom code must therefore be written for each data source. This type of specifically adapted system can make further development and updates costly and time-consuming. The lack of Web-based interface means that importing tags and maintaining the database must be done during planned downtime. In addition, you are limited to the experience and competence that exists within your company, with few opportunities to use technologies from other industry players. By contrast, historians come with built-in data collection, which can retrieve data from various sensors and systems. GE Digital’s Proficy Historian, for example, uses the OPC and OPC UA standards, which provide access to data across multiple devices. In addition, pre-built interfaces avoid the need for custom coding or scripting. Data is stored and processed seamlessly in one secure, central location. This provides better flexibility, saves time and reduces costs. Because production operates in real time, it is important that you receive the data fast. Relational databases are well-suited to answer queries such as trends in data flows, temperatures, and other analog values. The challenge arises when the queries concern large data sets or longer time periods. This means that the relational database delivers lower performance. For faster read/write performance and continuous access to real-time data, you should opt for a historian database. Historians can collect large amounts of process data in milliseconds. Compared to regular relational databases, they can be 100-1000 times faster at reading and writing. Compression Efficient data storage and compression enables high performance. For a relational database, compression is done manually, by administrating archives and disk space. This can be a time-consuming task. Historians comes with powerful features for compression. Data is first compressed on the collector, and again before storage. Archives can be created, backed up and purged automatically, enabling extended use without needing a database administrator. It’s important that production data is always available. Relational databases offer high availability for data stored through clustering, but they are vulnerable to network availability. Depending on where the collector is located – usually on a server, or near the data source – data collection will stop if the connection is lost. Historian technologies ensure good availability with redundancy at the collector. They may buffer the data at the collector if a disruption occurs. These buffers are eventually uploaded when the server comes back online with automatic reconnection – ensuring that no data is lost. Data security Networks and databases are under constant attack from hackers and viruses, many of which target well-known relational databases. SQL injection/insertion attacks are common. Some historians are immune to such attacks because they do not allow inserting, updating or deletion of data via standard interfaces. In addition, historians track all changes, including uses access, configurations, security violations and system alerts. Some historians are designed to help address regulatory requirements such as the FDA’s CFR Part 11. Historian databases are designed to collect large amounts of data from the entire plant for process visualisation. A plant-wide historian can connect both“islands”of automation data from the plant floor, and plant operations up to business systems. You can compare previous production runs and analyse data before downtime, so you can easily identify trends, root causes and implement improvements. n Manufacturers have a choice when it comes to storing and analysing their data: historian (time series) or relational databases. George Walker, managing director of Novotek UK & Ireland, examines the differences between them, to help you choose the one that best suits your needs.