A new approach to historical data helps a FL utility prepare for the future.
Many SCADA Historians Make Light Work for Gainesville Regional Utilities (GRU)
GRU is the 5th largest municipal utility in Florida and serves over 100,000 people. In 1999, the utility unified much of its monitoring and control infrastructure into a single SCADA system based on VTScada HMI software from Trihedral. By 2011, the system had grown considerably to include many new features and remote sites. Over time, one of the challenges became how to manage the historical data being logged 24/7 from over 6,700 data points so that it could be easily accessed by both operators and engineers.
At the same time, Trihedral had created version 10 of VTScada which included a new historian that had been rebuilt to deal specifically with these types of issues in large data-rich applications. In this article, Trihedral President and VTScada Chief Architect, Glenn Wadden, describes the new design and how it is helping the utility meet its disparate needs.
Monitoring & Control Infrastructure
“At Gainesville, they are running two wastewater treatment plants; the Kanapaha Water Reclamation Facility and the Main Street Wastewater Treatment Plant,” says Wadden. “There are also 167 lift stations, each with a remote telemetry unit (RTU) tied to the central SCADA system via VHF radios.” Together, these facilities process approximately 20 million gallons of wastewater a day. Monitoring and control of the system is handled by four servers running VTScada HMI soft-ware.
For the last ten years, the Gainesville SCADA system has been logging data 24 hours a day from over 6,700 I/O points. VTScada software includes an integrated historian for logging and analyzing historical data. “One of the problems with the old historian was that once it had collected three or four year’s worth of files, the response time started to slow down for operators who needed to get historical data from the system. Since the operators are typically more interested in information about the last couple of days, it was decided to archive off older data.
This became a problem for the engineering group who needed older data to analyze long-term trends. They are trying to decide when to put in bigger pumps, when to add new water lines, how the system behaves in the summer versus the winter, that sort of thing. They want to have ten years of data! They decided that archiving the historical data every three years would be a reasonable compromise for both the operators and the engineers. So that was what they did until we released the feature list for version 10.”
The New Design
When the team at Gainesville read the list of new features, they saw an opportunity for a better solution. VTScada 10 includes a new historian that allows developers to configure multiple redundant historical databases that can sync across a wide area network. In addition to the default database format included with the product, the historian also supports simultaneous logging to MySQL™, SQL Server™, SQLite and Oracle™.
The VTScada historian at the Kanapaha plant logs data to the default VTScada database format. This is the primary historical database for operator workstations. “The advantage of the default database format is that it’s free and very fast,” says Wadden. “It doesn’t require the system to touch every file in the database each time you access data; therefore, performance does not degrade as you add more and more data in the future. It’s also more reliable than SQL Server across a wide area network and is much more capable of handling high volumes of data.”
The historian at the Main Street plant logs the same data to an existing SQL Server database located at a third location. “As it turns out, the utility had already set up an SQL Server database on a machine across town that was being used for non-SCADA activities,” says Wadden. “They thought it would be great to make that the backup so the engineers could use it without bothering operations. All we had to do was to provide VTScada with the network path to this server and then grant VTScada permission to write to it.”
“During normal operations, each plant logs to its own historical database. If the Kanapaha server goes offline, VTScada workstations failover to the historian at the Main Street plant. When it is restored, the missing data are automatically back-filled from the backup SQL Server.” The reverse happens if the Main Street or backup SQL servers become unavailable. “It is all perfectly seamless. As far as performance goes, each server is logging exactly the same data on average of about 35 tags per second. This is well within the tested capacity of 5,000 tags per second.”
ODBC Server
The updated system also allows the town’s engineers to make ODBC connections directly to VTScada’s historical data-base from computers that do not have VTScada installed. “An operator would typically use VTScada’s Historical Data Viewer (HDV) to see the data plotted on a timeline,” says Wadden. “Although engineers might do that too, what they really want is to work with the raw data from the database. The problem is that this can be a mountain of information. They would have to manually sift through it or write software to programmatically sift through it to get the summary statistics they are looking for, such as the number of pump starts per month or the average flow for a week. They can retrieve data from the VTScada system from any reporting or analysis software tool that supports an ODBC interface. VTScada looks like a relational database to that software. The connection is redundant so that it will return data seamlessly even if one of the historian servers is off-line.
The ODBC server creates virtual tables which simplify the process of returning summary information. “They can write a query that says, ‘give me the hourly table,’ even though there is no such table. VTScada pretends that there is, returns the hourly min/max, the average number of starts or whatever you asked for. This helps them quickly get to the core of what they are looking for, so they can focus on other things that are much more high-level.”
Configuration Management & Version Control
Version 10 also introduces new configuration management tools that greatly simplify the process of updating and backing up an application’s configuration settings. “This also gives them a history of all the changes that anybody has made to any computer in the system.” In the event of a problem, operators can quickly roll back to the last known good version of the application. Then, they can use the audit log to quickly track down where the problem originated.
This feature has already started saving the utility debugging time. “I just got a call from GRU,” says Wadden. “They had made a change to their system. They added a bar level indicator to one of their displays and then deployed it at 3 o’clock in the afternoon. When they went to another workstation a little while later they couldn’t see it and they assumed the change had failed to deploy. So I looked at the application’s audit log and, sure enough, the change had deployed to every machine at 3 o’clock. I could tell in just a few seconds that the deployment was not the issue. What appears to have happened is that the communication had failed between the time they added the new element and the time they checked it on the other computer. The new indicator could not be seen because it had no value. That took five minutes to diagnose rather than chasing down what went wrong with the deployment. When you are bringing a new lift station online with a new radio and lots of new I/O points, this audit log can be pretty handy.”
Conclusion
The staff at GRU is enthusiastic about having all of their historical data available in an SQL database so that the diverse needs of operations and engineering can both be met without compromise. “Their eyes lit up when they first saw the con-figuration management,” says Wadden. “They immediately saw the benefit in terms of convenience, reliability, and accountability. As a typical customers who do configuration infrequently, they can now proceed with greater confidence since they know that they can always roll back to any previous revision at any time.”
| Try It For Yourself Download the 90-day Trial |
Images reproduced with permission from Gainesville Regional Utilities.