Any Databases
You can also use the Datasets module to store data in SQL, but the Historian module has a simplified configuration, with database tables being created automatically and an already predefined connection to trend charts.
The system can also use the information available in the historian database, in user-made dot NET scripts, to obtain historical values in Tags and show them on graphical screens or to export these values. The following sections describe how to set up data logging:"
You can select any SQL database, such as Microsoft SQL Server, Oracle, MySQL, or any OLEDB or ODBC-compliant database for historical data storage. By default Action.NET the user uses an inline SQL database engine, SQLite. For information about selecting the historian database, see the item "Historian database provider" .
You can also use the PI OSIsoft (tm) system for data storage. In this case there is no need to do any Historian configuration to access the stored data. For details on using the PI System, see "Using osisoft's PI System(tm)".
Configuring Historian Tables
By default, the Tag Historian database already has a table configured. The configuration of this table controls when tag values should be written to the historian and how long these values should be retained in the historian. If you want some tags to be treated differently, you must configure additional tables with the settings you need and assign the tags to any of these tables. You can also edit the default table settings if you find it convenient.
The configuration of the table is independent of the type of database selected to store the information, as described in "Provider of the Historian database."
In general, don't store more data than you need. Storing a large amount of data reduces performance in data recovery. You should use triggers and dead bands as wide as possible to ensure you get the information you really need without overloading the system.
To set up a historian table:
Go to Edit > Tags > Historian
Follow one of the following:
To edit an existing table, select it from the drop-down list Historian Tables and click Config.
To create a new table, click New York, U..
A window appears for filling the columns in the table.
Alternatively one can go straight to Edit > Tags>Historian Tables and directly fill in the data in the columns.
The columns to be populated are explained in the following table:
Option | Description |
Name | Type a name for the table in the database |
Auto Create | Select for the system to automatically create the table in the database. |
Save on Change | Select to store data in the table (add a row) each time a tag associated with the table changes. |
Trigger | Use to record data in the table each time a tag or tag property changes. When used with the Save on Change option, the system stores data in the table both when the tag value changes and when or the Trigger value changes |
Time Deadband (TimeSpan Log) | Enter with the minimum interval of time between recordings. This is how much the system should expect after generating a record for a tag before generating a new record for the same tag. Use with save on change to avoid generating too many records in the database. |
Life Time | Number of days on which historical data must be retained. After this time, the oldest rows in the table will be deleted from the database. So that the data is never deleted leave this field empty or type a zero. |
SaveQuality | This option should be checked so that when recording a sampling in history is included beyond the tag value also the quality |
GetSampleMethod | Name of a script method that will be called to customize the data before it is written in history, if necessary. See in "Customizing Sample Retrieval" |
ValueColumnsType | You can choose to store the value of the data types: Double or Single |
Adding Tags for the Historian
After you set up your tables for the tag historian, you can configure tags whose values must be recorded by the historian.
In Edit>Tags>Historian are the definitions of the historian tables.
In top right of the tab is the name of the Database Manager used. In the figure is the SQLite;
To set up tags for the historian:
Go to Edit > Tags > Historian.
Click the Historian Tables drop-down list and select the table to which you want to add tags.
Start by filling the columns in the table with the chosen tags
You can copy and paste the Tags from the Objects.
Enter or select the information as needed.
Column | Description |
Name | Enter a tag name or click to select a tag. |
DeadBand | When using the Save On Change option in the historian table, DeadBand is how much the value must change to trigger the value recording in the historian system. (see dead band type below for setting how to use this dead band) |
Deviation | When using the Save On Change option in the historian table, the deviation is how much the tag value should change for the system to store a new value in the historian. This value overrides the Time DeadBand record time interval. |
RateOfChange | When using the Save On Change option in the historian table, the |
HistorianTable | Select the table that has the settings that you want to use for record periodicity and for how long to keep this tag's value |
Deviation DeadBand Type | You can choose for the value of the Dead Band to be considered as a Absolute or Percentage |
Deviation DeadBand Limit | Value deviation limit |
Keep adding as many Tags as you need.
Historian Database Provider
The database used to store the Historian is defined in Edit > Datasets > DBs by the database connection object named TagHistorian.By default, when a new project is created, this TagHistorian name record is defined to use the SQLite database that is available with the Action.NET.
Caution: The SQLite database should be used for small to medium-sized databases. If the number of tags and periodicity of saves requires greater robustness and especially distribution across multiple disks and client/server management characteristics, you must define another SQL database system for the Historian. To define another database for the Historian, you only need to create a new database connection, as explained in "Configuring Database Connections," and naming it TagHistorian
Tip: The system does not allow duplicate names, so to create a DB connection with the TagHistorian name you must rename or delete the existing row using that name.
Using osisoft's PI System(tm)
The Action.NET use the OSIsoft PI System as a provider to the Historian. In this scenario, you do not need to do any additional configuration. If the tag is mapped to an OSIsoft PI point, the system will automatically call the PI Server for data when plotting trend charts or any other scripting or screen methods that require information from the Historian.The system can work with the PI System and the built-in Historian at the same time. When historical information from a point is requested, from trend charts or scripts, the system will search for this information in the built-in Historian module and, if not found, will try to find this data on the PI server. For information about connecting to PI systems, see the item "Import and Export of projects".
Schema of Historian Tables
The Historian tables contain the following columns:
UTCTimeStamp_Ticks- Date and time in UTC (GMT) for this record (in 64-bit NET ticks format). The value of this property is the number of 100-nanosecond intervals (1/10th of a millisecond) that have passed since 00:00, January 1, 0001. This is a new date/time pattern used by the Microsoft dot NET framework..
LogType- Auxiliary column to show when the row was inserted: 0 = in the starting, 1 = as normal record, 2 = in the system shutdown.
TagName - Column created automatically using the tag name, as column title. It stores the value of the data using double precision. There will be a column of these for each tag defined for this table
_TagName_Q - Column created automatically for data quality, using opc quality specification. There will be a column of these for each tag defined for this table
Typically, you can associate up to 200 tags with each historian table, but this number is dependent on how many columns the database is being used to allow. Tags must be defined in the same table when they have similar write criteria, time intervals, variation rates, and dynamics in the process, since to save a tag to the table, you need to save the entire row.
Viewing Trend Charts
To view trend charts with historical information, you can use the trend object available in the Engineering space used to define screens or reports. See the "Setting up the Trend Window "
Customizing Sample Retrieval
Typically the graphical trend object calls the Historian server to get the data to plot the charts. In some situations, you may want to override this setting and set a dot NET script code to get the values. This is used, for example, to plot calculated data for a recipe, or future data, or data from other SQL tables or any scenario where customization is needed when retrieving data. For this customization a GetSamples method must be defined in any script class, to take samples. In the Edit>Tags>Historian tab this method should be defined in the GetSamplesMethod column. The prototype for this method is:
DataTable GetSamples(string[] tagNames, object startRange, object endRange)
The parameters:
The startRange and endRange parameters are of type:
datetimeoffset dotNET, when used to get tables used in time charts.
Double, when creating data for X-Y charts.
The object returned by the DataTable method must have the following columns:
DateTime: The date and time of sampling
TagName: The name of the tag used as fieldname for the column with the tag value in Double.
TagName_Q: Optional column with data quality in OPC encoding.
Historian runtime objects
The Historian namespace has the properties and current state of the Historian server.
The Historian.Table object has the list of tables defined in the historian and the properties. of each table.
The tag property, Tag.tagname.Historian, is enabled for tags if there is a definition of history record in the project.
On this page: