Using Informix to capture TimeSeries data that overwhelms commodity databases

Neil Mulkern, IBM’er, explains why Informix is the right database for Smarter Planet systems.  Enter Neil:

———————–

A smarter database for a smarter planet.

The key component of IBM’s smarter planet vision is to quickly and efficiently process data to improve the effectiveness and efficiency of systems.  A smarter system will efficiently process data to drive intelligent decision-making.  The core of such systems is the database software that stores and structures the data.

Recording pulses of data

A major challenge for the database is to record the potentially massive volume of data produced by multiple monitoring instruments that all generate regular pulses of data.  By pulses of data, I mean data that is constantly generated at regular intervals.  For instance, a smart utility meter will send usage data to a centralized system at regular intervals.

Pulse data tends to have only a small number of attributes – Source identifier, timestamp and value – however there will be a large number of these simple entries stored in the database, one for every beat of the pulse. The pulse nature of the data collected in this sort of model has some specific characteristics that do not naturally match the structural characteristics of standard “commodity” relational database software like Oracle Database, IBM DB2, Microsoft SQL Server, or MySQL.  In this case, a DBA would typically have tall thin tables, with the Source Identification data repeatedly collected and stored with every pulse even though it never changes.

Tracking how values change over time

The key thing to track in such systems is how the values change over time, in order to derive intelligence from this instrumented data. Unfortunately the only way to do this with the above mentioned relational database software is to create a complex set of indexes to imitate a time series.  A better solution is to use the IBM Informix database.

Informix has all the features and functions you expect of enterprise-class relational database software.  It also includes additional features you won’t find in those other products — in particular it has a native TimeSeries indexing structure.

This means that the Source Identification data is only collected once, and an infinitely wide column of time-stamped values are stored alongside that single reference. IBM Informix is the only relational database software that stores TimeSeries data in this way.

How TimeSeries affects the bottom line.

When a direct comparison between IBM Informix and Oracle Database was performed at the Texan utility ONCOR, it was demonstrated that with Informix:

• Storage saving were around 70% (before compression was applied)
• Loading data was improved by a factor of 20x
• Query times were improved by a factor of up to 30x

One of the more interesting bits of information that this company needed to report to the regulator was to identify any the missing meter readings (that is, how many meters required an estimated reading).  If you ask the other database products to find something that isn’t there, they will have a hard time coming up with the answer. IBM Informix simply checks the pre-determined time slots for missing values – easy!

Combining geospatial capabilities with time stamps

Many commodity databases also have a geospatial capability. What is different about Informix is the ability to combine a spatial reference with a time stamp. If the pulses of data are coming from a moving instrument, say for example a vehicle, the potential efficiencies are just as great, but the intelligence is far more sophisticated.

A Smarter transport solution I worked on in May 2011 had instruments that sent pulses of data to a centralized location while moving along a railway line.  The solution needed to record what happens, when it happens, and where it happens.  In other words, it needed to combine TimeSeries capabilities with geospatial capabilities. The IBM solution came in at over £1 million cheaper than the Oracle based alternative.  IBM achieved this substantially lower price through sheer technical efficiency.  The customer has since committed to migrating their solution from Oracle Database to Informix as soon as possible.

Check out the full story of the remarkable Informix technology at www.freeinformix.com