By Jeremy Pollard, CET
We've talked about ways to implement SCADA/HMI from commercial off-the-shelf (COTS) software, rolling your own and fixed terminals. In the background with all of these is the database.
A database is a collection of fields in records that are organized into tables and a relational hierarchy. Flat file-type databases are rarely used in this day and age.
The relational aspect of the database involves having a common key between two tables so software can find common records. For instance, if you were logging data for the tag name "Bearing_Temp," the logged data might be in one table, and the specific information for the data point would be in another table. The link between them would be the tag name.
Common databases are Microsoft's Access, Structured Query Language (SQL, sounds like sequel), and Oracle. Some might still use DBF file formats. Open Database Connectivity (ODBC) is a method of connecting to these databases, but most programs have their own interface into the database.
This is where it might get interesting. If you have the option to create the database from scratch, you might choose Access since it is easy to work with. Microsoft provides runtimes to interface with the data and to run reports.
Be careful, though. Access isn't designed for a large user pool, nor is it meant for large databases. If you are logging tons of points, the database could fill up in a hurry.
All information is stored in an Access database, which means that if the header gets corrupted, the file is useless. Data searching is slow on larger databases, and lack of data integrity is a potential threat. That there are many tools available to fix or extract Access data from a failed database is testament to the issues that could confront you.
I highly recommend that you become familiar with SQL as a platform. My SQL is a free, open-source database server with different flavors. Stuff that works with Database A might not work with Database B due to vendor formatting.
Microsoft released SQL Express server. It is free, and it conforms to the ANSI SQL standard. There are limitations, but the database server is scalable to the enterprise SQL database.
SQL is a relational database with the ability to extract complex record sets with a simple query statement. If you use Ignition, the Java-based SCADA application from Inductive Automation, its database is pure SQL, so you can extract your own data from anywhere using your own application, as long as you can log into the database.
The benefit of using SQL goes beyond third-party applications to get at your data. SQL is atomic, meaning the data integrity is solid. Many users can use the database simultaneously. Searches are very fast, seemingly regardless of the database size. The speed of the results is really dependent on the number of components there are in the query statement.
A typical query statement would be "Select * From TEMPERATURES Where Temperature > 100 Order by Tag name." This would display all records from the table TEMPERATURES in alphabetical order. All fields would be displayed. You can create reports that really are queries with formatted results.
Visual Basic and thin-client technology work very well in this environment. Certain SCADA products such as Rockwell Automation's RSView use a proprietary database for alarm and data logging and for screen data. While it can use ODBC, it doesn't use SQL natively.
The problem with a proprietary format of any kind is that you need the vendor's client to access the data, or at least have an export routine. I prefer a native solution to SQL.
The main complaint in the community is lack of portability of coded SQL. If you write a script in RSSQL (Rockwell's interface), it most likely would not work in Ignition. The database itself is open, but the language interface might not be.
It might seem easier to use Access, but I strongly advise otherwise. SQL will be around forever in some form, and you can use many environments, even Linux, to create and access databases.
It is my data, and I want it when I want it.
