TECHNICAL NOTE MN914017EN, version 4
Visual T&D Historian Database Data
Dictionary
Contents
Database schema ..........................................................................................2
Additional details about specific table fields .................................................3
Type of alarm commands ("AlarmAction" field) ....................................................... 3
Status of alarms ("AlarmStatus" field) ..................................................................... 3
Alarm categories ("Category" field) ......................................................................... 3
Tag types (“Type" field of table "Tag") ................................................................... 3
Type of the event ("EventType") ............................................................................ 4
Binary and analog transitions "TimestampQuality" field ............................................ 4
Binary and analog transitions "QualityFlags" field .................................................... 5
Template: EAS-SA QF-MKT01 Rev.05
2024/11/24 Partitionning ..................................................................................................5
Tables structure .............................................................................................6
DatabaseVersion ................................................................................................... 6
Tag 7
DataPointDetail ..................................................................................................... 7
EventTransition ..................................................................................................... 7
AnalogTransition (PostgreSQL only) ........................................................................ 8
BinaryTransition (PostgreSQL only) ........................................................................ 8
AnalogTransition View (Microsoft SQL server only) .................................................. 8
BinaryTransition View (Microsoft SQL server only) ................................................... 8
Stored procedure / functions ........................................................................9
Microsoft SQL Server ............................................................................................. 9
PostgreSQL Server ................................................................................................ 9
Getting Assistance .......................................................................................10
MN914017EN, VERSION 4 1
VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY EATON
Warning: As a general rule, it is not recommended to tamper with information
stored in the datalog database as it is the core of the Visual T&D historical
functionnality. Querying information is perfectly legitimate however,
updating, deleting or inserting rows can produce unintended
consequences.
Database schema
Figure 1 Database Schema
2 MN914017EN, VERSION 4
EATON VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY
Additional details about specific table fields
Type of alarm commands ("AlarmAction" field)
0: No alarm commands
1: Appearance or disappearance of an alarm
2: Alarm acknowledgement
3: Alarm deletion
4: Blocking an alarm
5: Make an alarm as "silent"
6: Make an alarm as "permanent"
Status of alarms ("AlarmStatus" field)
Since more than one status may be active at one time, a hexadecimal mask
representation is used.
0x01 (1) = State of the alarm (Active, or returned to normal)
0x02 (2) = Acknowledgement state
0x04 (4) = Cleared state
0x08 (8) = Return-to-normal required for clearance state
0x10 (16) = Multiple alarms state (when alarm return to active before clearance)
0x20 (32) = Blocked state
0x40 (64) = Silent state
0x80 (128) = Permanent state
Alarm categories ("Category" field)
0 = Alarm on a binary point
1 = Alarm on an analog point, when active because of the low threshold
2 = Alarm on an analog point, when active because of the high threshold
3 = Alarm on point quality status
4...14 = Unused
15 = Unknown
Tag types (“Type" field of table "Tag")
This field contains the tag type.
0 = Unknown
1 = Analog input, 16-bit integer tag value
2 = Not used anymore
3 = Binary input
4 = Binary output
5 = Analog input, 32-bit integer tag value
6 = Analog input, 32-bit floating point tag value
7 = Analog output, 16-bit integer tag value
8 = Analog output, 32-bit integer tag value
9 = Analog output, 32-bit floating point tag value
10 = Vector of values (signal)
11 = Event-related tag
MN914017EN, VERSION 4 3
VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY EATON
Type of the event ("EventType")
In Visual T&D Explorer, those types are referred as "Event categories"
0 = UNKNOWN
33 = PT_STATE_TRANSITION
34 = PT_STATE_SOE
35 = PT_STATE_ALARM
65 = SYSTEM_EVENT
66 = SYSTEM_WARNING
67 = SYSTEM_ERROR
68 = SYSTEM_ACCESS_DENIED
69 = SYSTEM_AUTHENTICATION
70 = SYSTEM_CONFIG_ACCOUNT
71 = SYSTEM_CONFIG_SERVER
72 = SYSTEM_CONFIG_SITE
97 = OPERATION_CONTROL
98 = OPERATION_TAG
99 = OPERATION_ALARM
100 = OPERATION_USERLOG
101 = OPERATION_DATA_SIM
129 = DATAFILE_COMTRADE_EVENT
130 = DATAFILE_COMTRADE_ALARM
161 = FAILURE_EVENT
197 = ES_EVENT_DIAG
198 = ES_ALARM_DIAG
199 = ES_OPERATION_DIAG
Binary and analog transitions "TimestampQuality" field
0 = Data source real-time clock
The time tag is assigned by the data source real-time clock (For example, the
SMP Gateway). The data source may be externally synchronized, or not. The time tag
is only as good as the current real-time clock setting.
1 = Slow synchonisation
The time tag is assigned by the data source real-time clock (For example, the
SMP Gateway). The SMP Gateway is externally synchronized up to the second, but
the millisecond is not externally synchronized.
2 = Fast synchronization
The time tag is assigned by the data source real-time clock (For example, the
SMP Gateway). The SMP Gateway is externally synchronized, including the
millisecond.
3 = Device synchronization
The time tag is assigned by the device that provides the data source. It is assumed
that this device is synchronized. It cannot be guaranteed that this synchronization is
coherent with the data source itself.
4 MN914017EN, VERSION 4
EATON VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY
Binary and analog transitions "QualityFlags" field
This field contains the value’s precise quality status. Since more than one quality can
be active at one time, a hexadecimal mask representation is used.
Those qualities refer to a point from an SMP Gateway data source
0x0001 (1) = UNKNOWN
0x0002 (2) = RESTART
0x0004 (4) = COMMUNICATION FAIL
0x0008 (8) = FORCED
0x0010 (16) = BAD HARDWARE
0x0020 (32) = OVER RANGE
0x0040 (64) = BAD REFEFERENCE
0x0080 (128) = OFFLINE
0x0100 (256) = INHIBITED
0x0200 (512) = OUT OF RANGE
0x0400 (1024) = SIMULATED
Visual T&D does not record the exact transitions’ quality statuses coming from an
OPC data source. A mapping is performed following those rules:
BAD HARDWARE = 0x0010 (16) can correspond to
OPC_QUALITY_BAD
OPC_QUALITY_CONFIG_ERROR
OPC_QUALITY_NOT_CONNECTED
OPC_QUALITY_DEVICE_FAILURE
OPC_QUALITY_SENSOR_FAILURE
OPC_QUALITY_OUT_OF_SERVICE
OPC_QUALITY_SUB_NORMAL
OPC_QUALITY_UNCERTAIN
COMM. FAIL = 0x0004 (4) can correspond to
OPC_QUALITY_LAST_KNOWN
OPC_QUALITY_COMM_FAILURE
OPC_QUALITY_LAST_USABLE
BAD REF. = 0x0040 (64) can correspond to
OPC_QUALITY_SENSOR_CAL
OVER RANGE = 0x0020 (32) can correspond to
OPC_QUALITY_EGU_EXCEEDED
FORCED = 0x0008 (8) can correspond to
OPC_QUALITY_LOCAL_OVERRIDE
Partitionning
In order to avoid slow indexing, a partition mechanism was put in place so that tag
transitions are distributed in logical partitions.
Partitioning refers to splitting what is logically one large table into smaller physical
pieces; it brings several benefits:
• Dramatically increases query performance in certain situations like:
When most of the heavily accessed rows of the table are in a single partition
There is a small number of partitions
Partitioning substitutes for leading columns of indexes; it reduces index size and makes it
more likely that the heavily-used parts of the indexes fit in memory.
MN914017EN, VERSION 4 5
VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY EATON
• Improves performances when queries or updates access a large percentage of a single
partition. In these cases, partitioning takes advantage of the partition’s sequential scan
instead of using index and random access reads scattered across the whole table.
• Facilitates accomplishing bulk loads and deletes by adding or removing partitions, if that
requirement is planned into the partitioning design. The ALTER TABLE NO INHERIT and
DROP TABLE commands are both carried out much faster than a bulk operation. These
commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.
• Gives opportunities to reduce cost by using cheaper and slower storage media for
seldom-used data.
Due to cross database compatibility considerations (PostgreSQL and Microsoft SQL Server), a
scheme of 900 partitions for analog values as well as an additional 900 partitions for binary
values was agreed upon. The target was 1000 partitions; 900 partitions are now used, leaving
100 partitions for future use.
How partitioning is implemented is different for the following software packages:
• PostgreSQL (Table inheritance partitioning)
• Editions of SQL Server (View partitioning – All Editions)
• Partitioned Tables and Indexes (limited to Enterprise Edition and not implemented in
Visual T&D)
The partitions have been separated in two schemas for analog and binary partitions. They are
based on tag keys with a limit of 20 000 tags per partition for a theoretical total limit of
18 000 000 tags.
Tags are distributed to partitions is a circular method, so, the first 900 tags to store are each
distributed to one partition, then the next 900 tags are allocated to the same 900 partitions
and so on.
Tables structure
Schema “public”(PostgreSQL), “dbo” (Microsoft SQL Server)
DatabaseVersion
This table stores information which is relative to the current database version; it will inform
Visual T&D if the database schema needs to be upgraded. It is highly recommended not
to tamper with the information stored in this table.
The primary index is made of the [Key] field that is incremented by one for each new
database version. The following table shows an example with three (3) versions.
Key VersionDate VersionText Build Service Description
First version of the
1 2012-08-01 4.0B1 100 NULL
RDBMS datalog
AnalogTransition and
BinaryTransition tables
2 2014-03-18 4.1R2 100 NULL
moved to a partition
scheme
6 MN914017EN, VERSION 4
EATON VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY
Key VersionDate VersionText Build Service Description
Much better handling
of duplicates during
3 2015-10-09 4.3R3 100 NULL
analog and binary
transitions insertion
Tag
The tag table is used to bridge information between the configuration database (Microsoft
Access database unique to every site) and the datalog database. Each tag is assigned to a Key
that is used to distribute the tag transitions to the right partition.
For example:
• if a tag is assigned a key of value above 0 and strictly less than 20 000, the tag
transitions will be stored in the partition [AnalogPartitions].[AnalogTransition00000000]
• if the tag is assigned a key above 20 000 and strictly less than 40 000, the tag
transitions will be stored in the partition [AnalogPartitions].[AnalogTransition00020000]
• and so on
Fields Type and ID are the link used to refer the tag to the configuration database (Visual T&D
site).
The primary index is made of the [Key] field that is unique across the database and is related
to other tables’ [KeyTag].
An unique constraint index [IX_Type_Id], composed of the pair of fields [Type] and [Id], is
related to the configuration database.
DataPointDetail
This table stores the tag name for reference purposes. The tag name is normally stored in the
configuration database but is also stored in the DataPointDetail table for reference.
The primary index is made of the [KeyTag] field which is unique across the database.
The Foreign Key [FK_DataPointDetail_Tag] allows to relate the point detail information to its
tag, if appropriate, as well as to ensure data integrity.
EventTransition
The EventTransition table stores all site alarms and events, related to a tag or not, in
a chronological order to facilitate new event insertion as well as event querying.
The primary index is a unique combination of 3 fields:
• [Timestamp] – the exact time of the event in UTC
• [TimestampIndex] – a unique index that allows to differentiate events with the exact
same timestamp
• [KeyTag] – if the event or alarm is associated to a tag
The Foreign Key [FK_EventTransition_Tag] allows to relate the event to its tag, if appropriate,
as well as to ensure data integrity.
MN914017EN, VERSION 4 7
VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY EATON
AnalogTransition (PostgreSQL only)
In a PostgreSql Table inheritance partitioning scheme, the base table for all analog values is
the AnalogTransition table.
The AnalogTransition base table can be used to query data for a tag, regardless in which
partition the data is physically stored.
The primary index is a unique combination of 3 fields:
• [KeyTag] – the tag to which this transition belongs
• [Timestamp] – the exact time of the event in UTC
• [TimestampIndex] – an unique index that allows to differentiate transitions with the
exact same timestamp and [KeyTag]
The Foreign Key [FK_AnalogTransition_Tag] allows to relate the transition to its tag as well as
to ensure data integrity.
BinaryTransition (PostgreSQL only)
In PostgreSql Table inheritance partitioning scheme, the base table for all binary values is
the BinaryTransition table.
The BinaryTransition base table can be used to query data for a tag regardless in which
partition the data is physically stored.
The primary index is a unique combination of 3 fields:
• [KeyTag] – the tag to which this transition belongs
• [Timestamp] – the exact time of the event in UTC
• [TimestampIndex] – an unique index that will allow to differentiate transitions with
the exact same timestamp and [KeyTag].
The Foreign Key [FK_BinaryTransition_Tag] allows to relate the transition to its tag as
well as to ensure data integrity.
AnalogTransition View (Microsoft SQL server only)
In the Microsoft SQL Server View partitioning scheme, the AnalogTransition view can be used
to query data for a tag, regardless in which partition the data is physically stored. However, be
aware that this selectable, updateable, insertable and deletable view might perform poorly on
very large databases. Using stored procedure, such as [sp_AnalogSelect], will perform better
and shall be considered instead.
BinaryTransition View (Microsoft SQL server only)
In the Microsoft SQL Server View partitioning scheme, the BinaryTransition view can be used
to query data for a tag, regardless in which partition the data is physically stored. However, be
aware that this selectable, updateable, insertable and deletable view might perform poorly on
very large databases. Using stored procedure, such as [sp_BinarySelect], will perform better
and shall be considered instead.
8 MN914017EN, VERSION 4
EATON VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY
Stored procedure / functions
Microsoft SQL Server
The following stored procedure might be usefull for reading data from your Microsoft
SQL Server historical database:
• [sp_AnalogSelect]
• [sp_AnalogTransition]
• [sp_BinarySelect]
• [sp_BinaryTransition]
• [sp_EventSelect]
• [sp_KeyTagFromName]
• [sp_VssAlarmAndEvent]
• [sp_VssPtArchVal]
• [sp_VssPtCurrVal]
The following stored procedure is a Visual T&D system’s procedure and shall not be
used to query data:
• [sp_CreateAnalogTransitionTable]
• [sp_CreateBinaryTransitionTable]
• [sp_AnalogInsertTransitionTable]
• [sp_BinaryInsertTransitionTable]
• [sp_databaseSize]
• [sp_DeleteOldTransitions]
• [sp_TagCreateAndSelect]
PostgreSQL Server
The following functions might be usefull for reading data from your PostgreSQL
Server’s historian database:
• PartitionNameFromKeyTag
• analogselect
• binaryselect
• eventselect
• keytagfromname
• partitionname
• vssalarmandevent
• vssptarchval
• vssptcurrval
The following stored procedure is a Visual T&D system’s procedure and shall not be
used to query data:
MN914017EN, VERSION 4 9
VISUAL T&D HISTORIAN DATABASE DATA DICTIONARY EATON
• analoginsertduplicatetable
• analoginserttransitiontable
• binaryinsertduplicatetable
• binaryinserttransitiontable
• deleteoldtransitions
• partitioncount
• partitionrange
• pg_relpages
• pgstatindex
• pgstattuple
• reindexduplicates
• tagcreateandselect
Getting Assistance
If you have any questions regarding the performance, application or testing of the
Visual T&D product line, do not hesitate to contact us. Our staff will be happy to
assist you.
Technical Support
Eaton’s Energy Automation Solutions
Email: eas-support@[Link]
Phone: +1.877.834-0009 or +1.800.815-2258
Business hours are from 8 a.m. and 5 p.m. CST, Monday to Friday.
10 MN914017EN, VERSION 4
