Generic Connector Configuration_2021.R1

This document outlines the configuration of generic ODBC, OLEDB and SQLite data sources in Plant Integration servers, making time series data available in TrendMiner. The data sources are connected to the TrendMiner Plant Integrations Server, an IIS runtime that can be installed on any Windows server close to the archive or even on the machine itself. A Plant Integrations Server can connect to multiple sources. Data sources are expected to allow the generation of tags list and to allow data queries.

• Step up
• Connector configuration
• ODBC
• OLEDB
• SQLite
• Add connection to TrendMiner
• Retrieve tag list
• Retrieve tag data
• Mapping tag types (Optional)
• Specify timestamp format (optional)
• Specify time zone

Step Up

Making your SQL datasource available in TrendMiner requires two actions:

• Configure an ODBC/OleDB/SQLite connector: server-side configuration which consists of creating the necessary configuration files at a specific location in the TrendMiner Plant Integrations Server.
• Add the connection to TrendMiner: client-side configurationin the TrendHub settings page.

Connector Configuration

The configuration that follows, needs to be done on the TrendMiner Plant Integrations server.

Location

First step is to create a folder for each data source within a folder of the connection:

<plant integrations website directory>\<generic datasource type>\<datasource name>. Where:

• ‘plant integrations website directory’: the installation directory of the plant integrations website
• ‘generic datasource type’: one of the currently supported generic datasources in TrendMiner(odbc, oledb or sqlite)
• ‘datasource name’: the name of the datasource that will be used later in the client-side configuration of the TrendMiner configuration pages, to identify this datasource.

For example, a default installed location of c:/inetpub/plantintegrations:

Configuration files

The following configuration files need to be provided in these directories to configure the generic connectors:

• Connection string specification: connection.txt
• Retrieve tag list: list.sql
• Retrieve tag data: values.sql, values-STRING.sql
• Mapping tag types: type.csv (Optional)
• Specify timestamp format: datetime_format.txt and/or input_datetime_format.txt(Optional)
• Specify time zone: timezone.txt (Optional)

Connection string specification

The connection string should be provided in a file with name connection.txt

ODBC

To create the connection string for an ODBC database you can take the steps in the following article:

https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-odbc-data-source-sql-server-import-and-export-wizard#odbc_connstring

E.g.:

OLEDB

SQLite

Add connection to TrendMiner

Once the connection.txt is correctly set-up on the server, the connection needs to be added to TrendMiner to render tags available for analysis. The data source configuration guidelines can be found here.

First go to ConfigHub and click on 'Data sources' in the menu. Click 'Add data source' and provide the details of the connection:

The ‘Name’ field should contain a descriptive name for the historian. It will be visible to end users and used in Access Control lists.
The 'Provider' dropdown will give an option to choose ODBC|OLEDB|Sqlite as connectivity options.
At 'Connect via' you should choose the connector which you would like to make the connection with.
The 'Host' field contains the name of the folder created inside odbc/oledb/sqlite folder. For example: You want to create an odbc connection to your datasource. So you will create a folder for the data source within a folder of the connection. For a default installed location, this would be: c:/inetpub/plantintegrations/odbc/connection_a
Username and Password are passed in connection.txt either in the connection string or the DSN. So this can be left empty.
The 'Prefix' field should help avoid duplicate tags in TrendMiner. When you want to add more than one historian to TrendMiner, we strongly advise the use of a Prefix name. It is visible to end users. For example: if the Prefix name is Hist1 then all the tags will appear as [Hist1]tagname. Try to keep them as short as possible for readability.

Retrieve tag list

The query for fetching tag metadata should be stored in list.sql. It is executed as provided.

The query should return:

E.g.:

To validate the correctness of this query use the following connector endpoint:

GET /api/v2/tags?historianName=<name of data source>

<name of data source> being the Name field we filled in earlier when creating the connection in ConfigHub. If the response from this call is as expected, the content of your list.sql is correct.

Note that stored procedures can be used for retrieving tags as long as they return a result set.

Retrieve tag data

The query for fetching points for analog and discrete tags should be stored in values.sql and in values-STRING.sql for string tags. It is executed as provided.

Both configuration files are optional but at least one should be provided.

Queries to retrieve points use binding parameters (specified in query by ?). If binding parameters are not supported then .NET string formatting can be used.

Parameters:

The query must return:

*Type when using binding parameters. When .NET formatting is used parameters are passed as string. When using .NET formatting the default format for datetimes is 'yyyy-MM-dd HH:mm:ss.fff'. If a different datetime format is required, it can be specified in the 'datetime_format.txt' configuration file (more details in the 'Specify timestamp format' section).

E.g.:

Note:that stored procedures can be used for retrieving tag data as long as they return a result set.

Mapping tag types (optional)

The tag type returned by a data source, as specified in the section 'Retrieve tag list' does not always map one on one with the TrendMiner supported data types (ANALOG, DISCRETE, STRING). Instead of complicating the metadata query, a mapping can be provided in a CSV file type.csv. Entries are separated by a semicolon.

TrendMiner reads this mapping and searches for a match of the type returned in a metadata query in the first value of each line. The second value on the line should be a type known by TrendMiner:

• ANALOG
• DISCRETE
• STRING

If a type returned by the 'Retrieve tag list' query cannot be mapped to a value described in type.csv, ANALOG is assumed.

E.g.:

To validate which values are used by your data source, use the following connector endpoint:

GET /api/v2/tags?historianName=<name of historian>

Specify timestamp format (optional)

For .NET parameters, when creating queries and reading the response out of these queries, it is possible to define the format of the datetime by creating a file datetime_format.txt in the connection folder.

If the format of the input parameters when creating the queries is different from the datetime format in the response, it is possible to redefine it in the file input_datetime_format.txt, in the connection folder.

If datetime_format.txt is available, it will be used to configure both the .NET parameters, when creating the queries and the reading of the response.

If input_datetime_format.txt is available, it will override the configuration for the .NET parameters, whencreating the queries.

If no file exists the format is the default one: "yyyy-MM-dd HH:mm:ss.fff"

The format follows .NET formatting specification (https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings)

E.g.:

Alternatively the file can contain the string “EPOCH”, in which case dates are converted to the equivalent number of seconds since the Epoch and the converted values are used in the query.

Specify time zone (optional)

It is advised to have timestamps inUTC. If timestamps are stored in local time it is possible to provide the time zone in a separate config file.

In case timestamps do not contain timezone information and no timezone is specified by the user in a separate config file, timestamps are assumed to be in UTC timezone.

To configure manually the timezone add a timezone.txt file in the connection folder andspecify the timezone name as listed on https://msdn.microsoft.com/en-us/library/gg154758.aspx in the column "Time zone name".

E.g.:

• Attachment: 2021.R1 - TrendMiner - Generic Connector Configuration.pdf 400 KB Download