How SQLTags Historian Works

SQLTags Historian gives you the ability to quickly and easily store historical data for your tags, and provides efficient querying of that data. Options for partitioning and deleting old data help ensure that the system stays properly maintained with minimal extra work.

This section describes various aspects of how SQLTags Historian stores and queries data.

The settings for SQLTags Historian providers are set in the gateway under SQLTags > Historian. Historian providers are automatically created and removed according to the configured database connections. By default they will be created with a one month partition size, and will not delete old data.

Note: The standard tag historian features are added by the SQL Bridge module. If this module is not installed, the historian providers will not show up in the historian configuration section.

The first step to storing historical data is to configure tags to report values. This is done from the Historian Properties page in the SQLTags editor in the designer. The properties include a historical scan class, that will be used to check for new values. Once values surpass the specified deadband, they are reported to the history system, which then places them in the proper store and forward engine.

As mentioned, the historical SQLTags values pass through the store and forward engine before ultimately being stored in the database connection associated with the historian provider.

The data is stored according to its datatype directly to a table in the SQL database, with its quality and a millisecond resolution timestamp. The data is only stored on-change, according to the value mode and deadband settings on each tag, thereby avoiding duplicate and unnecessary data storage. The storage of scan class execution statistics ensures the integrity of the data.

While advanced users may change the table according to their database to be more efficient (for example, using a compressed engine), Ignition does not perform binary compression or encrypt the data in any way.

Ignition has the ability to automatically break up data into different tables of fixed duration. This can help make data maintenance easier, by preventing tables from becoming too large. Tables can easily be deleted in order to prune old data, and the database is able to better optimize access to frequently retrieved rows. The built-in partitioning feature can be used with any database.

It is important to note the difference between this feature and any partitioning options that the database might provide. Most modern databases offer their own faculties for defining "partitions", offering similar and greater benefits. While Ignition cannot use these features directly, advanced users may choose to apply these features on top of what Ignition currently offers.

As mentioned above, Ignition does not perform any binary compression on the data. That is, values are stored directly in standard database tables. However, in order to reduce the number of values stored, Ignition offers two different algorithms for pre-compressing the data (trimming unnecessary values). The two modes correspond to the value mode property of the tag: Discrete, and Analog.

•	Discrete: The value uses a simple deadband, and is only stored when a new value is +/- the deadband value away from the previously stored value.

•	Analog: The deadband is used to form a corridor along the trajectory of the value. A new value is only stored when it falls outside the previous corridor. When this occurs, the trajectory is recalculated, and a new corridor formed.

See Historian Properties for more information about the difference between discrete and analog values.

While the data is stored openly in the database, the format does not lend itself well to direct querying. Instead, the Ignition platform offers a range of querying options that offer a tremendous amount of power and flexibility. In addition to simple on-change querying, the system can perform advanced functions such as querying many tags from multiple providers, calculating their quality, interpolating their values, and coordinating their timestamps to provide fixed resolution returns.

Querying can be performed on tables and charts through the Historical Binding, and through scripting.