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 host>. 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 host’: the host 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 |
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:
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. |
![]() |
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. |
- Attachment: 2021.R1 - TrendMiner - Generic Connector Configuration.pdf 400 KB Download