Historian Data Storage

Unless otherwise configured, Historian tags will save data to a VTScada proprietary database system within the application's data folder. These files are designed for use solely by VTScada. Values can be read only by VTScada and cannot be modified by any means without damaging system integrity.

While the properties described in this topic can be set in the Edit Properties dialog (StorageLocation & StorageType), you are strongly advised to use the configuration fields of the Historian tag instead. See: Historian properties Datastore tab

You can configure an Historian tag to send its data to an alternate location, or to a third-party database.

Do not use the same storage location for multiple applications.
               Do not attempt to use an external USB drive for your Historian data.
               Do not configure your Historian to write to a storage device located elsewhere on your network.

Compatible databases are as follows:

  • SQLite, using ODBC driver version 0.86 or later
  • Oracle version 10g or later. Requires the following Oracle privileges:

Create any index,  Create any sequence,   Create any table,   Create any trigger,   Create session,   Create user,   Drop any table,   Drop user,   Insert any table,   Select any table,   Update any table

Select,   Insert,   Update,   Delete,   Create,   Drop,   Index

  • PostgreSQL version 9.4 or later

If using a third-party database, you do not need to create tables, as this will be done by VTScada. It is strongly recommended that a specific database and user be reserved for use only by the VTScada Historian.

VTScada stores data in a form that has been optimized for performance, reliability and synchronization. Informal testing shows that the native VTScada Historian can store data at a rate that is roughly an order of magnitude faster than SQL. You are advised to use an SQL database only on a backup server. (See notes later in this topic under Configure Alternate Data Stores.) If you are using a third-party SQL data store, then when creating queries, you will find that it is easier to use the VTScada ODBC Server for direct SQL access because it has been designed to handle the complexities of the data structures for you.

If using an Oracle database as a target for historian data, the database must have a "tablespace" named "Users", which is not present in a new Oracle DB. This is used as the default tablespace for the user created by a historian tag, and is where all the historian tables are stored.
All tables are created with the "owner" (or schema) named after the tag name. So, given a historian tag of "SystemHistorian", then all tables are created with the owner SYSTEMHISTORIAN and have a name such as "SYSTEMHISTORIAN.FRIENDLYTAGNAMELOOKUP".

If you are using the VTScada database system for data storage, then there is no need to set any value for the Historian tag's Storage Type property. You might set the Storage Location property if you want to save the data somewhere other than the default Data\History folder of your application. Writing across a network to another machine is strongly discouraged.

If writing to a third-party DBMS, the storage location should be either a connection string in the form:

Driver=ServerBrand;Server=ServerName;Database=DBName;Uid=user;Pwd=pwd 

Alternatively, it may be a reference to a DSN, as follows:

DSN=MyDSNName

Use of a DSN is recommended to avoid having the user ID and password for your database visible in the Historian tag's configuration.

Prerequisites:

If modifying the location to a new folder on your computer, then the only prerequisite is that the folder exist on all Historian servers. This should be configured before commissioning the application.

  • In general, do not specify a network location other than the hard drive of the Historian server(s).
  • To configure for redundancy see Client / Server Configuration.
  • To configure for backups see Backups

 

If configuring VTScada to use a third-party database, all the following must be in place:

  • The database program must be installed and running.
  • An ODBC driver for your program must be installed. VTScada can work with either 32 or 64 bit ODBC drivers unless you are running the 32-bit version of VTScada.
  • You may use either a connection string or you may use a DSN, created using the Microsoft ODBC Administrator tool.
  • A database must be created for use by VTScada.
  • A user account must be created in your database for use by VTScada. This account must have CREATE and WRITE privileges in the database, so that VTScada can create tables and write data to those tables.
  • Choose whether to set your Historians' storage type to ODBC or ODBCSingleTable.
    This choice affects how your data is stored, and therefore how you can query it. See notes at the end of this topic and also Query a 3rd-Party DBMS
  • After completing all configuration steps, ensure that the database is running and then restart your application.
    VTScada will create the tables required to store data if you do not restart, but may not create support tables such as 'friendlytagnamelookup'.
    It may take up to 30 seconds for all tables to be created.

Example 1:

For example, to direct the System Historian to use a SQL Server database, identified by a connection string, the Historian tag's data store properties would be set as follows:

StorageName: SystemHistorian

Type: ODBC

Storage Location: Driver=SQL Server;Server=ServerName;Database=DBName;Uid=user;Pwd=pwd

Example 2:

Connecting to a MySQL database.

A database named 'vtscada' has been created.

A user named 'VTS' has been created.

'VTS' has been granted CREATE and WRITE privileges on 'vtscada'.

The ODBC connection is created as shown. The user and password are for the MySQL database, not a VTScada account.

Sample DSN creation, using the MySQL connector within the Microsoft ODBC Administrator tool.

 

The VTScada System Historian is configured as follows:

The storage type is ODBCSingleTable for ease of data access.
The DSN matches the previous example image.

When using ODBC Historians, it is suggested that you specify the Storage Name parameter. This will be used as the schema name in the database. If left blank, the unique id of the Historian tag will be used as the schema name rather than its short name. Note that the unique id's length and complexity may cause difficult for some database programs.
The Storage Name must be unique for each Historian.

 

Configure Alternate Data Stores

One Historian with two separate databases

To record to both the VTScada data store and a third party ODBC database, or to save to the C: drive on one machine and the D: drive on another, there must be at least two VTScada workstations in your system. You can then configure the Historian tags using parameter expressions that use a different configuration on each workstation. In the following examples, "SQLBackup" is the name of the computer where the ODBC database is installed. Parameter Expressions are added to the configuration fields of the Datastore tab of the System Historian as follows. These expressions check the workstation name using "WkStaInfo(0)", and configure for the ODBC database if the workstation is SQLBackup, or use default settings otherwise, by returning "Invalid". Note that the test for workstation name is case-sensitive.

StorageName:

WkStaInfo(0) == "SQLBackup" ? "SchemaNameForDB" : Invalid

Type:

WkStaInfo(0) ==  "SQLBackup" ? "ODBC" : Invalid

Storage Location:

WkStaInfo(0) ==  "SQLBackup" ? "Driver=SQL Server;Server=ServerName;Database=DBName;Uid=user;Pwd=pwd" : Invalid

Data store configuration should be done before the application begins to collect information. Upon changing any Historian's storage type or location, that Historian will lose access to previously collected values.

 

ODBCSingleTable - Single Table Schema Structure

When your Historians' storage type is set to ODBCSingleTable, tag values are stored using one of the schemas described here. Note that while the table name is the value shown in the column Table name, in your database that name may be prepended with other information including (but not limited to) the schema name for each Historian tag.

This table represents only standard tags in VTScada version 12 or later. Custom-coded tag types and data collected from applications running under VTScada versions prior to 11.5 are not reflected here.

If your application includes custom-coded tag types whose data cannot be stored with any of the default schemas, you must add new HistorianODBCTableNamesX properties to your Settings.Startup file.

Setting Table name Schema string
HistorianODBCTableNames0 Numerics Timestamp-TSTAMP-T|GenTimestamp-UI4-A|Value-R8|Value!-UI4
HistorianODBCTableNames1 Digitals Timestamp-TSTAMP-T|GenTimestamp-UI4-A|Value-UI1|Value!-UI4
HistorianODBCTableNames2 Strings Timestamp-TSTAMP-T|GenTimestamp-UI4-A|Value-STR|Value!-UI4
HistorianODBCTableNames3 Historians Timestamp-TSTAMP-T|MachineID-STR-M|Availability-BOOL-U
HistorianODBCTableNames4 Notebooks Timestamp-TSTAMP-T|GenTimestamp-UI4-A|Value-STR
HistorianODBCTableNames5 Ports Timestamp-TSTAMP-T|GenTimestamp-UI4-A|Value-I2
HistorianODBCTableNames6 Drivers Timestamp-TSTAMP-T|GenTimestamp-UI4-A|ErrorValue-I2|FailedCount-I2|FailedRetryCount-I2|SuccessCount-I2|Quality-R8|ResponseTime-R8|ErrorAddress-STR
HistorianODBCTableNames7 Modems Timestamp-TSTAMP-T|GenTimestamp-UI4-A|LCallFailures-R8|LCallState-R8|LEnabled-R8|LFailure-R8|LMediaMode-R8|LOffHook-R8|LCurrentNumber-STR
HistorianODBCTableNames8 Transactions Timestamp-TSTAMP-T|GenTime-R8|TransactionID-BYTES|ErrorCode-I4|Encoding-I4|PackedData-BYTES
HistorianODBCTableNames9 Alarms Timestamp-TSTAMP-T|PackedData-PACK|Area-STR|Encrypted-BOOL|PackedInfo-PACK
HistorianODBCTableNames10 DataFlowTag Timestamp-TSTAMP-T|GenTimestamp-UI4-A|ErrData1-UI1|ErrData10-UI1|ErrData11-UI1|ErrData12-UI1|ErrData13-UI1|ErrData14-UI1|ErrData15-UI1|ErrData16-UI1|ErrData17-UI1|ErrData18-UI1|ErrData19-UI1|ErrData2-UI1|ErrData20-UI1|ErrData21-UI1|ErrData22-UI1|ErrData23-UI1|ErrData24-UI1|ErrData25-UI1|ErrData26-UI1|ErrData27-UI1|ErrData28-UI1|ErrData29-UI1|ErrData3-UI1|ErrData30-UI1|ErrData31-UI1|ErrData32-UI1|ErrData4-UI1|ErrData5-UI1|ErrData6-UI1|ErrData7-UI1|ErrData8-UI1|ErrData9-UI1|ErrMod-UI1|ErrorValue-I2|FailedCount-I2|FailedRetryCount-I2|SuccessCount-I2|Quality-R8|ResponseTime-R8|ErrorAddress-STR

If changing a legacy application to use ODBCSingleTable instead of ODBC, there is an advantage to allowing VTScada to rename the tables. Do so as follows.

Use this procedure only if changing from ODBC to ODBCSingleTable, and only after upgrading to VTScada version 12.0.08 or later.

  1. Ensure that you have configured primary and backup servers for your Historian(s), each with a local copy of the stored data.
    You may need to use the expressions provided earlier in this topic to configure separate Historian storage locations on different servers.
  2. Stop the backup server.
  3. Add the HistorianODBCTableNamesX properties to your application's Settings.Startup file.
    (You can copy these from HistorianODBCTableNames)
  4. Delete the tables from the database on your backup server.
  5. Restart the application on your backup server and allow data to synchronize.
  6. Repeat steps 2, 4 and 5 on any server that is writing to the 3rd party database.