Unified Access To Multiple Historical Data Sources Using PHP

Historical data access in many SCADA systems is obtained through proprietary closed tools or in other cases using standards-based means like OPC HDA.

In the utility, I work for we have a SAGE system that provides a PostgreSQL database with historical data and also text-based files that contain short-term (30 days) data stored in the real-time servers. No tools were provided to query the database or generate reports. The text files do have query tools, that only works inside the real-time servers.

We also use an in-house developed system that connects to the real-time SAGE system using the IEC 60870-5-104 protocol to obtain data and records historical data to a MySQL database, this system also writes daily binary files that contain 1440 minute-to-minute snapshots of the most significant data that represents the power system state, we call these files as “SUP” files.

The “SUP” files are very compact, being a convenient mean to store very long-term historical data. We have online data available to query since 1997 when the first implementation was developed for an in-house created (not in use anymore) real-time system. The real-time system was later migrated to SAGE in 2005, but we kept the SUP files as the primary means of historical data storage and access.

So we have four places where historical data can be obtained, each one having digital state data, event data and analog data available. Those four sources provide a safe redundant storage of data, a failure or corruption of more than one source is very unlikely to occur simultaneously. The tradeoff is that apps need to access four very different storage systems.

The users need to query historical data, use special apps and access predefined reports. It’s easy to imagine the mess of many different apps accessing the four data sources using distinct protocols. That can end in apps that are difficult to develop and maintain and hard to migrate to one type of source data to other.

The solution we found most convenient for our case is a PHP Class that accesses all the four data sources and provides a common API to all PHP apps and reports that our Intranet server hosts. This system uses open source, cross-platform tools and web standards that provides easy and low-cost deployment, vendor independence and are almost future proof as possible.


The API allows specifying the tag points to be queried, the begin and end of the time interval, and the sampling period. It’s optional to prioritize one data source, in case of the data nor being available in one source, there is an automatic fallback to the other sources.

There is an Excel sheet created using the Web Query data source parametrized to access data from a special PHP data server that provides historical data formatted to fit a spreadsheet. This sheet is a powerful tool that can be customized by the users to easily create their own reports.

Copyright © 2013 Ricardo L. Olsen. All rights reserved.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s