OVERVIEW

Starting in the 2023 version of Historian Server, users have the ability to apply mathematical and logistical operations to tag data. This is accomplished by using the wwExpression retrieval option. The wwExpression option can be used with the following Retrieval Modes:

• Cyclic Retrieval
• Delta Retrieval
• Full Retrieval
• Best Fit Retrieval

NOTE: Using the wwExpression retrieval option requires an “Advanced” Historian Server license. If an Advanced license is not available, the query will result in a error and no results will be returned.

APPLIES TO

• Historian Server 2023 and later

EXPLANATION

An Expression is made up of the following components:

VALUES
Comprised of a base value (of Boolean, Integer, Double or String data tyles), a date and time in UTC format, and a 16-bit integer representing the OPC quality of the value.

STREAMS
A time-ordered collection of values. In the context of an expression, a stream is represented by the Historian tag name. For example: Level_001.PV represents a stream consisting of the stored historical values for the Level_001.PV tag. Level_001.PV + 10 is an expression that adds 10 to the values in the Level_001.PV stream. Streams have either a star-step or linear interpolation model and an associated engineering unit.

ARITHMETIC AND LOGICAL OPERATORS
Standard mathematical operators and logic operators for comparisons.

SCALAR FUNCTIONS
A variety of functions for manipulating data, such as rounding values or converting engineering units.

TIME-SERIES FUNCTIONS
A variety of time series functions for comparing data such as calculating minimum/maximum values or time in state.

ARITHMETIC OPERATORS

AVEVA Historian Server supports the use of standard mathematical functions in expressions:

+	Addition
–	Subtraction
*	Multiplication
/	Division
^	Exponentiation
%	Modulo
()	Grouping operations

DimensionMath Table

The DimensionMath database table defines the relationship between engineering unit dimensions (i.e. volume, speed, temperature) when they are multiplied or divided by each other. For example, dividing gallons (gal) by minutes (min) provides results in gallons per minute (gal/m).

When adding or subtracting streams with the same dimension, the engineering units are converted to the same as the first stream, then operations are performed.

When multiplying or dividing streams where both units are related by a dimension relationship in the DimensionMath table, the results are in the associated Result Dimension’s units.

If a dimension relationship is not defined, the results cannot be implicitly converted and the results are in “Unknown” units.

LOGICAL OPERATORS

AVEVA Historian Server supports the use of standard logical operators in expressions:

=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to

AVEVA Historian Server also supports the use of the following logical functions in expressions:

AND (arguments)	TRUE if all arguments evaluate to True, otherwise FALSE
OR (arguments)	TRUE if any arguments evaluate to True, otherwise FALSE
NOT (arguments)	Evaluates the arguments to a TRUE or FALSE value, then returns the opposite

SCALAR FUNCTIONS

AVEVA Historian Server also supports using scalar functions in expressions. These functions have a 1:1 relationship between values in the input and output streams.

ABS	Calculates the absolute value of a stream
CASTUOM	Changes the stream’s unit of measure without changing the values
IF	Produces a stream based on the results of a logical expression
IFBAD	Overrides “bad” quality values in a stream
LOG	Calculates the base 10 logarithm of a stream’s values
ROUND	Rounds a stream’s values to the nearest integer
SIGN	Replaces a stream’s value with 1 or -1 based on the current value’s sign
SQRT	Calculates the square root of a stream’s values
TIMESHIFT	Shifts the time stamps of a stream by a specified interval
TRUNCATE	Removes the fractional portion of a stream’s values
UOM	Converts the stream’s values to a specific unit of measure

TIME-SERIES FUNCTIONS

AVEVA Historian Server allows you to use the following time-series functions in expressions to compare and manipulate data:

AVERAGE	Calculates the time-weighted average for a stream
AVERAGES	Calculates the statistical average for a stream
DURATION	Calculates the duration of TRUE states in an expression
MIN/MAX	Selects the minimum/maximum value of a stream
PREV/PREVGOOD	Returns the last value from the previous time period
TOTAL	Calculates the time-weighted total for a stream
TOTALS	Calculates the statistical total for a stream

For more information on the proper syntax and arguments for any of the above functions, please refer to the AVEVA Historian Retrieval Guide.

QUERY EXAMPLES

In this section we will look at a few examples of using the wwExpression option in SQL Queries.

Example 1: Adding the values of two tags. In this example we will add the values of two temperature tags together for the past 10 minutes.

SELECT DateTime, Value, QualityDetail
FROM History
WHERE wwExpression = 'Temperature_001.PV + Tempeature_002.PV'
AND DateTime >= Dateadd(mi, -10, GetDate())
AND DateTime < GetDate()

Example 2: Using the ROUND function. In the next example, we will apply the ROUND() scalar function to round the results of the previous query to the nearest integer.

SELECT DateTime, Value, QualityDetail
FROM History
WHERE wwExpression = 'ROUND(Temperature_001.PV + Tempeature_002.PV)'
AND DateTime >= Dateadd(mi, -10, GetDate())
AND DateTime < GetDate()

Example 3: Using the DURATION function. This query will return the amount of time (in seconds) per hour that the tank’s level was above 80 litres.

SELECT DateTime, Value, QualityDetail, wwUnit
FROM History
WHERE wwExpression = 'DURATION( Mixer100.Level.PV > 80, hour )'
AND DateTime >= Dateadd(hh, -4, GetDate())
AND DateTime < GetDate()

Example 4: Converting Unit of Measure. We will take the results of the previous query and convert the results from seconds to minutes.

SELECT DateTime, Value, QualityDetail, wwUnit
FROM History
WHERE wwExpression = UOM( 'DURATION( Mixer100.Level.PV > 80, hour ), Minute )'
AND DateTime >= Dateadd(hh, -4, GetDate())
AND DateTime < GetDate()

Example 5: Using the IF statement. This query will return the value of the temperature tag if the pump is running, otherwise it will return a value of zero.

SELECT DateTime, Value, QualityDetail
FROM History
WHERE wwExpression = 'IF( Pump1_001.PV = 1, Temperature_001.PV, 0 )'
AND DateTime >= Dateadd(mi, -10, GetDate())
AND DateTime < GetDate()

Example 6: Using the AVERAGE function. This query will calculate the time-weighted average temperature each hour.

SELECT DateTime, Value, QualityDetail
FROM History
WHERE wwExpression = 'AVERAGE(Temperature_001.PV , 1 hour)'
AND DateTime >= Dateadd(hh, -8, GetDate())
AND DateTime < GetDate()

For more examples, please refer to the AVEVA Historian Retrieval Guide.

The post Retrieving Historical Data Using wwExpression appeared first on Industrial Software Solutions.

AVEVA Historians uses two potential authentication modes: Windows OS security and Microsoft SQL Server security.

SQL Server authentication is provided for backward compatibility – Windows authentication is recommended for improved security.

Since Historian uses a relational database, Runtime,  the Historian requires passing through both Windows and SQL Server security levels. The Historian Management Console within the System Platform Management Console adds an extra security layer, restricting access to only  authorized users for the starting/stopping of Historian processes. Some historian components also require Windows and SQL Server logins.

Security for AVEVA Historian is managed using these tools:

• Microsoft SQL Server Management Studio: Manage access to SQL Server and databases.
• Windows Local Users & Groups: Manage permissions for the Historian. It can be an alternative to configuring database permissions with Windows authentication.
• Change Network Account utility: Modify the Windows login for the historian services.

To access AVEVA Historian as a client, users must log on to the Windows operating system using a valid user account with a login ID and password. This Windows user account also grants access to network resources in the domain.

In this training document, we will cover essential aspects of managing Historian users and security in SQL Server Management Studio. We will also include some steps on common actions performed in SQL Server Management Studio:

• Verifying your Authentication Mode in SQL Server
• Viewing a Login
• Adding a Login

NOTE: SQL Server Management Studio is not installed by default with System Platform in later versions. The program can be downloaded from Microsoft.

In addition, you must be logged in as a SQL System Administrator (sysadmin) in order to perform modifications in SQL Server Management Studio. Should you encounter a scenario where no user is a sysadmin, please follow the guidelines from Microsoft on Connecting to SQL Server When System Administrators are Logged Out.

APPLIES TO

• AVEVA Historian Server

EXPLANATION

SQL Server Authentication Modes

Microsoft SQL Server authenticates users using individual login account and password combinations. After successful authentication, users can connect to a SQL Server instance. There are two types of authentication:

• Windows authentication
  • Users connect to the SQL Server using a Windows user account (Windows login ID and password provided during the Windows login session).
• SQL Server authentication
  • Users connect to the SQL Server using a SQL Server login account (SQL Server login ID and password).

SQL Server operates in either of two security modes, controlling the type of accounts used for server access:

• Windows authentication mode
  • SQL Server uses only Windows authentication.
• Mixed mode
  • SQL Server uses both Windows authentication and SQL Server authentication. Windows security mechanisms handle validation if the login name matches the Windows network username. If not, Microsoft SQL Server security mechanisms are used.

NOTE: SQL Server authentication is provided for backward compatibility. Microsoft recommends using Windows authentication whenever possible. For more information about authentication, refer to your Microsoft SQL Server documentation.

Windows Security Groups and SQL Server Roles

The following Windows security groups are created by default on the AVEVA Historian computer. Use these groups to assign different levels of database permissions to users. This user management is done through Local Users and Groups.

• aaAdministrators
• aaPowerUsers
• aaUsers
• aaReplicationUsers

Each group is automatically configured to be a member of the SQL Server database role with the same name. For example, the aaAdministrators Windows security group is a member of the default aaAdministrators SQL Server database role. If you add Windows users to the aaAdministrators security group, they will automatically be given permissions of the aaAdministrators SQL Server database role.

SQL Server Roles and Permissions

The following table describes the SQL Server database roles and what permissions they have in the Runtime DB.

IMPLEMENTATION

The following section explains how to do a number of key actions in SQL Server Management Studio.

Verifying your Authentication Mode in SQL Server

1. Go to Windows Start -> Microsoft SQL Server Tool # -> Microsoft SQL Server Management Studio.

2. Connect to your Historian Server name. In the Object Explorer console tree, locate and right-click on your SQL Server instance -> Properties.

3. Click on the Security tab in the Properties dialog box.

4. Verify the authentication mode displayed.

If it shows “SQL Server and Windows,” it means mixed mode authentication is enabled. If you want to change the authentication mode, remember to stop and restart the SQL Server after making the change. Also, note that modification tracking in the historian, if enabled, will not occur until you restart the historian.

The AVEVA Historian services log on to SQL Server using the ArchestrA user account, which is a Windows account. This comes from the Change Network Account utility.

5. Click “OK” to apply any changes and close the SQL Server Properties dialog box.

Viewing a Login

NOTE: For information on connecting to SQL Server Management Studio, please see steps 1 and 2 from the Verifying your Authentication Mode in SQL Server section

1. In SQL Server Management Studio, in Object Explorer, expand SQL Server instance associated with the AVEVA Historian.

2. Expand Security and then click the “+” by Logins to expand the folder. The default logins appear in the details pane.

3. Double-click the login (user or group) for which you want to see the properties. The SQL Server Login Properties dialog box appears.

4. Here you can access their server roles, database membership mapping, status, etc.

Adding a Login

NOTE: For information on connecting to SQL Server Management Studio, please see steps 1 and 2 from the Verifying your Authentication Mode in SQL Server section

1. In SQL Server Management Studio, in Object Explorer, expand SQL Server instance associated with the AVEVA Historian.

2. Expand Security and then right-click Logins.-> New Login

3. In the dialog box, enter the following information:

Name: Type the name of the new login. For Windows authentication, click “Search” to browse the network for a Windows user account.

Authentication: Choose between Windows authentication or SQL Server authentication. If you select SQL Server authentication, provide a password for the login.

Database: Select the default database that the login will use. You can choose “Runtime”.

Language: Choose a language or leave it as “<default>” to use United States English.

5. Click on the Server Roles tab. To assign the new login to an existing server role(s), select the appropriate checkbox(es) in the list. This is usually not required unless you are defining a power user with specific administrative capabilities on the server.

6. Switch to the User Mapping tab. The User column contains the username mapped to the login ID, if it exists. By default, the username is the same as the login name.

7. Select the databases accessible by the new login. Typically, AVEVA Historian users need access to the Runtime and Holding databases. Access to the master database is necessary only if administrative privileges are required.

Available database roles for the selected database(s) will appear in the Database Roles list. By default, all new logins are members of the Public database role, but you can choose an additional or different role for a specific database.

8. Once you have configured the login, click OK.

9. If you created a login with a SQL Server password, you will be prompted to confirm the new password. Confirm the password and click OK.

The post Working with Historian-related SQL Permissions in SQL Server Management Studio appeared first on Industrial Software Solutions.

AVEVA InTouch HMI 2020 and later now provides two ways to store historical data from InTouch tags. This training document will explain the two methods, the pros and cons of each, and provide implementation steps for them. The two methods are:

• InTouch Enable Storage to Historian 
• Tag Importer Wizard

This document assumes understanding of WindowMaker and Tagname Dictionary.

APPLIES TO

• AVEVA Historian
• AVEVA InTouch HMI (2020 and newer)

EXPLANATION

NOTE: Both methods allow you to store data from InTouch tags and then later retrieve it for furthering analysis and trending.

Enable Storage to Historian

The Historian Client Access is a built-in feature of InTouch HMI that allows WindowViewer to send historical data to Historian.  This feature is known as “Enable Storage to Historian” and can be configured in the Historical Logging Properties dialog box in WindowMaker -> Special -> Configure -> Historical Logging.

In this method, tag values are pushed over to Historian directly from the WindowViewer session over HCAL (Historian Client Access Layer). This is more similar to how Application Server pushes over data. The InTouch tags are imported to Historian as MDAS tags. Tags are only recorded into Historian when WindowViewer is running.

To enable the Enable Storage to Historian feature, go to the Historical Logging Properties dialog box. There, you need to specify the name of the Historian server in the Historian node name box. You can use the computer’s node name or IP address. Special characters like period (.), underscore (_), and hyphen (-) are allowed.

You can also enable “Store and Forward” which allows data to be locally stored should WindowViewer lose connection to Historian for a period of time. To do so, specify the storage location path in the History store forward directory field. Once the connection is reestablished, the Historian server syncs with these files and preserves all the information.

In addition, any tags you want to store to Historian via this method must have Log Data enabled.

Advanced Settings and Dealing with Conflicts

For more configuration options, you can access the Advanced Settings from the dialog box. These settings can be used to help optimize the transmission of data from WindowViewer to Historian.

There are also some settings to help handle multiple instances of WindowViewer storing data to Historian via the Always affix unique string option. A good practice is to use the node name or location name of each WindowViewer instance as the prefix or suffix, making all tags in Historian unique. This is especially important for built-in System tags in WindowViewer, as every instance has the same 34 built-in System tags.

Tag Importer Wizard

The Tag Importer Wizard allows you to easily import configuration information, such as tags and I/O Servers, from an InTouch HMI application. In this method, tags are pushed over to Historian from the I/O servers themselves. When a tagname dictionary file is imported, InTouch pulls the I/O configuration and creates IDASs, Historian Data Acquisition Services that accept data values from one or more I/O servers.

Tag Importer Wizard saves time by eliminating the need for manual tag configuration. This method allows you to import tag name databases from multiple InTouch nodes. It also automatically maps the data from the InTouch application to the appropriate tables in the Historian Runtime database during the import process.

If you need to update only the changed tags since the last import, you can perform a Delta Reimport. This is faster and retains existing storage settings for the tags.

Once the tags have been imported, you can view properties for imported InTouch nodes and the list of tags associated with each node in the System Management Console under Configuration Editor > System Configuration > Storage > Imported Nodes and Public Groups > InTouch Nodes.

Import Considerations

• Ensure you have administrative permissions for the Historian Runtime and Holding databases.
• Set the AllowOriginals system parameter to 1 before importing .lgh original data.
• Close InTouch WindowMaker before importing the Tagname.x file.

Considerations Between Both Methods

While Enable Storage to Historian is easier to configure, using Enable Storage to Historian instead of the Import Tags Wizard has some important differences to consider.

First, Enable Storage to Historian requires WindowViewer to be running continuously so that it can continuously provide data to Historian. On the other hand, the Import Tags Wizard creates IDAS which connect directly to the I/O servers and don’t require WindowViewer be running. So, if you use Enable Storage to Historian and WindowViewer closes, there will be gaps in the historical data during that downtime. For more continuous data storage that doesn’t require WindowViewer running, Import Tags Wizard would be the recommended method.

Another consideration is that Message tags in WindowViewer won’t be recorded in Historian when using Enable Storage to Historian. However, the Import Tags Wizard can record string data, including Message tags.

If you want to store Alarms and Events to History Blocks (2020 R2 and newer), you would need to use Enable Storage to Historian. If you use the Import Tags Wizard, historical alarms/events will still need to be stored in a database.

Finally, Enable Storage to Historian may cause issues with multiple WindowViewer sessions. Enabling the prefix requires access to WindowMaker which most client computers do not have. In addition, the affixed tags will add extra tag counts in Historian. If you have multiple WindowViewer clients, it’s recommend to only have one of them storing to Historian via this method, or to use Import Tags Wizard to pull directly from the I/O Servers.

IMPLEMENTATION

Enable Storage to Historian

1. Open WindowMaker and navigate to the Tools -> Configure -> Historical Logging, or go to Special -> Configure -> Historical Logging.

2. The Historical Logging Properties dialog box opens.

3. In the Historical Logging Properties dialog box, check the option “Enable Storage to Historian.”

4. In the Historian node name field, enter the name of your Historian Server. Specify a valid History store forward directory as well.

5. Click OK to save the changes and close the dialog box.

6. Make sure any tags you wish to store to Historian via this method have Log Data checked.

Tag Importer Wizard

1. Open the System Platform Management Console (SMC) and expand Historian -> Historian Group -> <HistorianName> -> Configuration Editor.

2. Right-click Configuration Editor -> Import Tags.

3. The Tag Importer Wizard opens. Click Next and choose the InTouch tagname database (Tagname.x) you want to import by clicking Add to browse to an InTouch application path.

4. Verify the InTouch computer name and application path during the import. Click Next.

5. Configure how the Tag Importer handles duplicate tags (if any) to ensure unique tag names in the Historian. Click Next.

6. The Tag Importer Wizard – Filter Tags dialog opens. Select the categories of tags you want to import (e.g., Plant I/O, Memory, System). You can also just choose specific Access Names to import or not import by clicking the Topics button. Then click Next.

7. The Tag Importer Wizard – Tag Storage dialog appears. Select storage options, such as cyclic or delta storage rates, for the imported tags. Click Next.

8. The Tag Importer Wizard – Final Confirmation dialog appears. Note that it is advisable to backup the Runtime database before continuing. Click Finish to import tags.

9. The import begins and provides a report. Click OK to close.

10. Under Configuration Editor -> System Configuration -> Data Acquisition, note that IDAS(s) have been created with the list of InTouch tags.

11. Under Configuration Editor -> Public Groups -> InTouch Nodes, note that the InTouch node has been created with a list of InTouch tags.

The post Using Historian with InTouch HMI appeared first on Industrial Software Solutions.

This training document provides an overview of the Configuration Export and Import utility, also known as Historian Database Export/Import Utility (aahDBDump.exe). It explains the benefits of exporting and importing AVEVA Historian configuration information to a text file. The utility allows bulk modifications to the configuration instead of just using the Configuration Editor for individual database entities. It also facilitates the transfer of configuration information between historians.

The document will also walk you through:

• Exporting a Configuration
• Importing a Configuration

APPLIES TO

• AVEVA Historian Server

EXPLANATION

Importing or Exporting Tag Information

The Historian Database Export/Import Utility allows exporting or importing various Historian configuration items, including analog, discrete, string, and event tags, system parameters, I/O servers, topics, and more. It is a versatile tool for managing Historian configuration. The utility does not export InTouch node information or tags with their current editor set to be AVEVA Application Server.

You can specify Unicode or ASCII as the preferred encoding format when exporting configuration information. Select Unicode when exporting data with double-byte characters (e.g., Japanese tag names).

Using the Utility

Use the Historian Database Export/Import Utility to export configuration information. You can choose to export all objects or specific entities from the Historian Configuration. Additional options are provided for tag-specific filtering. The export process generates a text file containing the configuration data. By editing the configuration text file, you can insert new objects, modify existing ones, or delete entities. The order of entities in the file is crucial for successful importing.

The import process requires a client connection to the SQL Server used by the historian. The utility allows importing text files, and you can choose to insert, update, delete, or ignore data based on mode indicators.

The utility also keeps track of errors encountered during an import, providing a detailed error log (aahDBDumpLog.Txt) containing information such as date, input file name, line numbers, and SQL Server error messages.

IMPLEMENTATION

Exporting a Configuration

To export configuration information, follow these steps:

1. Go to the Windows Start menu -> AVEVA Historian -> Configuration Export and Import. This will open the Historian Database Export/Import Utility Wizard.

2. Select “Export from Historian to a text file.” and click Next to proceed. The”Connect” dialog appears.

3. Provide the following information:

Server Name: type the name of the AVEVA Historian for which you want to export the configuration

Authentication: Provide login credentials for the historian. You can either use Windows authentication or SQL Server authentication.

Text File: Type the path for the text file to which you want to export the configuration. Alternatively, you can browse to the desired location.

NOTE: If you want to encode the data as Unicode when exporting, select the “Save file as Unicode” check box. This is useful for exporting data with double-byte characters like Japanese tag names.

Options: To export configuration information for all database entities (e.g., tags, engineering units, summary operations), select “Export all objects” and skip to step 6. Otherwise, do not check this option and then follow the proceeding steps.

4. Click “Next” to continue, and the “Select Objects” dialog will be displayed. Provide the following information:

Data acquisition and miscellaneous: Choose one or more groups of definitions to export. These include IDAS, I/O servers, topics, system parameters, storage locations, engineering units, extended property names, messages, snapshot tags, summary operations, summary tags, replication servers, replication schedules, extended property values, replication groups, replication tag entities, tag history, structure tags, auto-tags, and auto-tag history.

Analog Tags: To export analog tag definitions, select “Include Analog Tags.” Note that system tags are not included in the export.

Where tagname like: Use this box to filter tags by name. Leave it blank or use the wildcard symbol (%) to include all tag names. The exporter recognizes SQL Server wildcard characters.

Acquisition Type: Choose the filter for the source of tag values. You can select “All acquisition types” (no filter set), “IOServer only” (to include only I/O Server data source tags), or “Manual only” (to include MDAS, HCAL, or manually acquired tags).

Storage Type: Select the filter for the storage type, which determines how often the value for an analog tag is stored (either cyclic or delta).

5. Click Next to proceed. The”Select Objects” dialog is displayed. Configure the filters for discrete, string, and event tag definitions. System tags are not included in these options, and the process is similar to filtering analog tags.

6. Click Next to move to the “Confirm” dialog.

7. Click Next to begin the export process. The “Status” dialog box will appear, showing the results of the export and the number of objects exported.

8. Finally, click Finish to exit the wizard. The configuration data is now exported to the specified text file.

9. Browse to the path designated in step 3 and confirm the file exists. You can open this file to review the contents. A CSV editor is recommended.

Importing a Configuration

1. Go to the Windows Start menu -> AVEVA Historian -> Configuration Export and Import. This will open the Historian Database Export/Import Utility Wizard.

2. Select “Import from a text file to Historian.” and click Next to proceed. The “Connect” dialog appears.

3. Provide the following information:

Server Name: type the name of the AVEVA Historian for which you want to export the configuration

Authentication: Provide login credentials for the historian. You can either use Windows authentication or SQL Server authentication.

Text File: Type the path for the text file from which you want to import the configuration. Or, click the ellipse to browse to the location.

4. Click Next. The “Confirm” dialog appears.

5. Click Next to start the import process.

NOTE: If the text file includes delete mode indicators, the utility will prompt you to verify each entity to delete, unless you choose to turn off subsequent delete warnings.

6. The “Status” dialog will display the results of the import, including the number of objects imported.

7. Click Finish to exit the wizard and complete the import process.

The post Exporting and Importing Historian Configuration appeared first on Industrial Software Solutions.