Overview
This page provides detailed information about how the archiving process works, what triggers it and the available options for archiving.
On this page:
| Table of Contents | ||
|---|---|---|
|
Archiving Process Overview
The Historian module process to archive data is composed by 3 steps:
An event triggers the request to archive a group of values. There are two types of events (Trigger or TagChange) that you configure when creating a Historian Table.
The Historian archives the values in the Archive Location after the trigger. You can use SQL databases or a TagProvider when configuring the Archive Location.
If you enable the Store and Forward feature, the system executes the data synchronization. This option stores data in a local database if the configured database is unavailable and sends it to the target when it becomes available.
In the following sections, you find additional details regarding each step.
Triggering Events
In FrameworX, there are two possible actions that can iniciate the archiving process. You can configure a Trigger based on a Tag, or choose to save always the Tag's value changes using the Save on Change option.
Trigger
You have three options to define as triggers in the Historian module:
- A Tag value.
- A Tag property.
- Any object from the runtime namespace, such as Server.minute.
Whenever there's a change in the object's value, it sets creates an archive request event.
To ensure compatibility with the Historian process, Triggers are limited to Tags falling under the domain of Server or objects situated in server-side namespaces. This restriction exists because the Historian process operates exclusively on the Server computer.
You can choose one Trigger for each Historian Table. When the trigger happens, all current values of Tags and objects connected to that Historian Table will be archived, regardless of whether or not they have a new value.
Save On Change
As the Trigger, you set the Save on Change option when creating or edditing a Historian Table.
When you enable the Save on Change the Historian module verifies continuously all Tags connected to each Historian Table. As a Tag changes its value, the archive request event is generated. Only the Tag that changed its value will be arhived.
Archiving the DataSelecting the Target Database
After the archive request is created, the FrameworX system will check how the data will be stored depending on the Archive Location of the current Historian Table. You configure this option when creating the Historian Table.
The process of archeving the data will differs if you are using a SQL database or TagProvider as a Historian.
Archiving to SQL database (TagHistorian)
The Datasets Module has a pre-defined object named TagHistorian. By default, a SqlLite database is used, but you can choose other databases. Access the Historian Tables to learn how to do it.
When archiving to the SQL database defined by the TagHistorian object, you can choose between the Standard and Normalized table schemas.
Standard Tables
If you use Standard tables, both Trigger and TagChange events result in a single additional row in the database. Each column in the table corresponds to a Tag in the HistorianTable group, ensuring that all tags within the group receive an entry, even if only one tag has a new value.
The row's timestamp is determined by the Trigger object when the archive event is triggered or by the timestamp of the Tag that initiated the archive request in the case of OnTagChange events. All tags listed in the associated HistorianTable are stored, independent of whether they have new values or not, sharing a single timestamp as defined earlier. For OnTagChange events involving multiple tag value changes, a single row is inserted with all tags in the group, utilizing the timestamp of the tag that triggered the event.
| Info | ||
|---|---|---|
| ||
To prevent rapid database growth, you can use the Time Deadband configuration to ensure that a new row is not created every time a Tag's value change. The system will not archive a new Tag's value until the dead band time ins't reached. After the deadband, the new row is generated using the timestamp of the last event. |
Standard tables schema
The following table describes all existing columns from a Standard SQL Table contain the following columns:
| Column Name | Data Type | Size | Description |
|---|---|---|---|
ID | BigInt | (8 Bytes) | Primary key serving as a reference within the system. |
UTCTimeStamp_Ticks | BigInt | (8 Bytes) | Date and time in Universal Time, represented in 64-bit .NET ticks. The value is based on 100-nanosecond intervals since 12:00 A.M., January 1, 0001, following the Microsoft .NET Framework standard. |
LogType | TinyInt | (1 byte) | Auxiliary column indicating the insertion event:
|
NotSync | Int | (4 Bytes) | Auxiliary column to show if the data was synchronized or not when the Redundancy option is enabled. See Deploying Redundant Systems. |
TagName | Float | (8 Bytes) | Automatically generated column with the tag name as the title, storing data values using double precision. |
_TagName_Q | Float | (8 Bytes) | Automatically generated column for the data quality of each tag, following the OPC quality specification. |
You can usually assign up to 200 tags to each historian table. However, the exact number can vary depending on how many columns your target database can accommodate. As a best practice, define tags in the same table if they have similar storing rates and process dynamics.
Normalized Tables
Normalized tables archive data only after a OnTagChange events. If you check the Normalized feature when creating or editing the Historian Table, the Trigger option is disabled.
Normalized tables store In this table schema, each one has only the TimeStamp of Tag, ID of the Tag, and the Value of Tag that generated the arquive event.
Normalized Tables Schema
| TagsDictionary default columns | |||
|---|---|---|---|
ID | BigInt | (8 Bytes) | The primary key of the table used as a reference by the system. |
TagName | NVarchar | The name of all the tags configured as normalized databases on the Historian. | |
NotSync | Integer | (4 Bytes) | Not used for this release. It was created for future changes and new features. |
The system automatically creates more four tables:
- TableName_BIT
- TableName_FLOAT
- TableName_NTEXT
- TableName_REAL
The schema for these table is:
| Tables default columns | |||
|---|---|---|---|
ID | BigInt | (8 Bytes) | The primary key of the table used as reference by the system. |
UTCTimeStamp_Ticks | BigInt | (8 Bytes) | Date and time in Universal Time for that row in 64-bit .NET ticks. The value of this property is 100-nanosecond (1/10th of a microsecond) intervals that have elapsed since 12:00 A.M., January 1, 0001. This is a new date/time standard used by the Microsoft .NET Framework. |
ObjIndex | Integer | (4 Bytes) | The foreign key used as reference to the column ID on the TagsDictionary table. |
ObjValue | Can be: Bit, Float, NText, or Real, depending on which table it is | Represents the value of the tag on the specified timestamp. | |
ObjQuality | TinyInt | (1 Byte) | Represents the quality of the tag on the specified time, using the OPC quality specification. |
NotSync | Int | (4 Bytes) | Not used for this release. It was created for future changes and new features. |
| Info | |
|---|---|
| title | It is not possible to synchronize a normalized database using the Redundancy option. |
Archiving to a ExternalTags Historian
When archiving to a ExternalTags Historian, the schemas are defined by the system defined in the ExternalTags. It means that when data is archived into these historians, the structural organization, naming conventions, and other specifics are determined by the ExternalTags settings.
About Providers:
The Providers essentially act as intermediaries between the software and the external data historian systems. They interpret and translate data formats, protocols, and other communication specifics to ensure seamless data archiving and retrieval.
Specifics:
CanaryLabs: A robust data historian system that's optimized for real-time data collection and analytics. When archiving to CanaryLabs, the data is stored in a highly compressed format that facilitates faster retrieval and analytics.
InfluxDB: An open-source time series database designed for high-availability and real-time analytics. InfluxDB is particularly useful when working with large sets of time-series data where timely data retrieval is of the essence.
GE Proficy: A comprehensive platform that provides real-time data collection and advanced analytics capabilities. GE Proficy is a scalable system designed to integrate and analyze vast amounts of industrial data.
On the Historian tab, navigate to TargetDBs and click on the "+" icon to add a new entry.
Configuring a Historian TargetDB:
Name: Enter a descriptive name for the TargetDB.
Description: Provide a brief description or note regarding this specific TargetDB.
Store and Forward: This setting determines if the data will be temporarily stored (and forwarded later) in case the direct archiving to the historian fails, ensuring no data loss.
Target Type: Define the type or nature of the target. This could be related to the specific kind of data or its use-case.
Target Provider: Choose the external data historian system you wish to archive to. Options include CanaryLabs, GE Proficy, and InfluxDB.
Station: Input the connection string specific to the chosen Target Provider. This ensures proper communication and data archiving to the external system. Configure your Provider clicking on a three point button and always test your connection.
Using Store and Forward
When the option to use Store and Forward is disabled, the archive requests events are sent directly to the Target Database as the events occurs.
There is a built-in protection when using the SQL-Dataset-TagHistorian target with Normalized tables. In this case, the new rows are buffered and included in the database every 5 seconds.
Store and Forward process
When receiving data archive requests, The Historian module will try to store the data in the Target Database, and if there is a fail, it will store the data in a local database, automatically created using SQLite.
Every 5s, the process tries to copy from the local SQLite database (the Rows inserted when the Target database was not accessible) to the the Target Database, in maximum blocks of 250 rows.
All Historian tables are verified for a maximum of 4s. If there is not enough time to process all tables, the verification is resumed in the next 5 seconds cycle.
If the copy process is successful, the rows are deleted from the temporary SQLite database, and if it is empty, the database file itself is deleted.
When an application queries data, if the Target Database is not available, the system will search the temporary SQLite database for data
This is a summary of the steps to execute the database synchronization:
The temporary local SQLite database is accessed, checking in all tables for the NotSync column flag (not synchronized rows), with a select limit of 250.
The result of the Selected Query (up to 250 rows) is inserted in the Target Database.
After successful completion of the Insert in the Target Database, the rows are deleted from local SQLite cache.
Suppose many tables are to be synchronized with a large amount of data. In that case, the availability of this data in the main database may take some time, depending on the insertion performance of the main database and the local database (SQLite). However, after a certain period, the data will become available. On average, it takes around 1 second per table for these steps (i) to (iii).
Another important consideration is the volume of data. For large projects with a significant amount of data, it is recommended to use more robust databases such as SQL Server or Oracle, as they offer better performance and can handle high data volumes. However, this limitation does not apply to SQLite, which has a maximum limit of 10GB with limited performance. Therefore, using the "Keep Local Copy" functionality for large projects means expecting SQLite to replicate the entire history in these large databases with 100% availability. This functionality works well for smaller data models or when immediate synchronization with the main database is not necessary, especially if the main database is being used by other projects or third-party software and may experience occasional unavailability.
In this section...
| Page Tree | ||||
|---|---|---|---|---|
|