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

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:

Driver Folder

ODBC

c:/inetpub/plantintegrations/odbc/connection_a

OLEDB

c:/inetpub/plantintegrations/oledb/connection_b

SQLite

c:/inetpub/plantintegrations/sqlite/connection_c

 

GCC1.png

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.:

Type Examples
Standalone connection string Driver={ODBC Driver 13 for SQL Server};Server=tcp:tm-vs,1433;Database=db-test;Uid=db-test@tm-vs;Pwd=pass123;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Driver={ODBC Driver 11 for SQL Server};Server=(localdb)\ProjectsV13;Trusted_Connection=Yes;Integrated Security=True;Initial Catalog=TestDB;Uid=user123;Pwd=pass123;
Using configuration from ODBC connection manager DSN=TestConnection;DSN=TestConnection2;PWD=pass123

OLEDB

Provider=iHOLEDB.iHistorian.1;Persist Security Info=False;USER ID=user123;Password=pass123;Data Source=vm-123;Mode=Read

SQLite

Data Source=C:\inetpub\Trendminer\sqlite\connection_c\test-database.sqlite;Version=3

 

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.

Screenshot_2021-07-01_at_15.53.25.png
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:

Parameter Type Mandatory

Tag name

string Yes
Tag description string Only if unit and/or type are returned (default is empty string)
Unit of measure string Only if type is returned (default is empty string)
Tag type string No (default is empty string which defaults to analog)

E.g.:

SELECT tagname, description, engunits, datatype FROM ihTags

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:

Parameter Type* Notes
Tag name string Name of the tag for which you want to retrieve data points
Start time datetime Timestamp indicating the start of the time period you want to fetch data points for
End time datetime Timestamp indicating the end of the time period you want to fetch data points for

The query must return:

Parameter Type* Notes
Timestamp datetime Timestamp of the returned data point
Value float/string Value of the returned data point: a number for analog and discrete tags, a string for string tags. More info on tag types can be found in the section about Mapping tag types

*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.:

Binding parameters SELECT timestamp, value FROM values WHERE tagname=? AND timestamp>=? AND timestamp<=? order by timestamp asc
select ts, value from Data where tagname = ? and ts between ? and ? order by timestamp asc
.NET formatting SELECT timestamp, value FROM values WHERE tagname='{0}' AND timestamp>='{1}' AND timestamp<='{2}' order by timestamp asc

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

Important note: The data points must be returned ordered by date, oldest first.

 

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.:

discrete;DISCRETE

string;STRING

VariableString;STRING

SingleFloat;DISCRETE

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.:

Without timezone (UTC) MM/dd/yyyy HH:mm
DDMMYYYY HH:mm:ss
With timezone yyyy-MM-ddTHH:mm:ssZ
yyyy-MM-ddTHH:mm:sszzz

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.:

Eastern Standard Time
Pacific Standard Time
Central Europe Standard Time

 

Important note: When the database stores date/times in a timezone which uses DST but the timezone is not stored along with the date/time, a 1 hour gap in the data will occur when entering summer time and 1 hour of data may be discarded when entering winter time.