How to set up an ODBC/OLEDB/SQLite data source?
The following sections will detail all the information needed for setting up an ODBC/OLEDB/SQLite data source in TrendMiner.
Conventional SQL servers like MSSQL, MySQL, OracleDB and others are all supported data sources that allow you to pull in data into TrendMiner. It is important to know that process historians like Aveva PI, Wonderware and others might also support ODBC/OLEDB connectivity as their back-end databases are still SQL.
SQLite is a database engine that allows the creation of a file that serves as a relational database.
Overview
Configuring your SQL data source in TrendMiner requires these actions:
Plant Integration configuration on the connector side which consists of creating necessary files on a specific location.
Adding the data source on the client-side from the TrendMiner ConfigHub page.
Plant integration configuration (Connector)
Create folders
First step is to browse to the Plant Integration folder inside of inetpub, most likely located in the c drive of your connector server.
Default Plant Integration (yours might be different) folder name and location:
c:/inetpub/PlantIntegrations
Create a folder (not case sensitive) that will host all your ODBC/OLEDB/SQLite configuration files.
Create a folder for each data source you want to configure inside the ODBC/OLEDB/SQLite folder, depending on which connection type you are using. In the following example we will configure an ODBC data source called “Plant_LIMS”.
Configuration files
Create the following configuration files needed to configure the generic data sources.
For ODBC/OLEDB:
For SQLite:
File name and type
Description
Required / Optional
sqlite.db (only for SQLite)
This is the sqlite.db file created that contains the data.
Required
connection.txt
Will contain the necessary information (e.g. full connection string) to establish the connection with the data source.
Required
list.sql
Query select statement that returns the list of tags with additional metadata.
Required
values.sql
Query select statement that returns the timestamps and values for a given tag.
Required
values-STRING.sql
Query select statement that returns the timestamps and values for a given string tag. Only include if necessary. If any string tags aren’t in the same database you could use the query here or decide to join with your values.sql query.
Optional
type.csv
Specifies information for mapping all tag types available in the data source to those recognized by TrendMiner.
Optional
datetime_format.txt
Specifies the date-time format of the timestamps returned by the values query
Optional
timezone.txt
Specifies the timezome associated to the timestamps if not already in UTC.
Optional
Connection file
The information provided to establish the connection with the data source can be provided as part of a full standalone connection string or by referencing a ‘data source’ already identified as a data source name using the ODBC drivers.
The following article contains information of the required format and examples depending on the type of data base being used:
https://www.connectionstrings.com/
Note that one connection string is used to connect to one database. Different tables within one database will be accessible through the same connection.
Standalone Full Connection Strings
ODBC
Driver={ODBC Driver 13 for SQL Server};Server=<ipAddress,port>;Database=<database_name>;Uid=<myUsername>;Pwd=<myPassword>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;As minimum requirement you shall specify the Server, Database, Uid and Pwd.
OLEDB
Provider=iHOLEDB.iHistorian.1;Persist Security Info=False;USER ID=<myUsername>;Password=<myPassword>; Data Source=<data source>;Mode=Read
SQLite
For SQLite, the connection string will contain the full path to the sqlite.db file as shown below.
 |
Connection via named data source using System ODBC drivers
Set up a new System Data Source using the corresponding SQL Server driver.
Note
Make sure the username being used to access the SQL database has read access.
Once the System Data Source is configured, create a “connection.txt” file and making reference to it as shown below.
Note
The username and password must be included in the file.
You can find additional information in the following article:
List file
The list.sql file will contain a SQL SELECT statement and shall return all the tags with additional metadata as shown below. If the “type” property doesn't return one of the supported tag types, you shall generate your own mapping with the type.csv file.
 |
Example query output:
 |
Only the tag name is strictly required and needs to be unique per tag (the tag name is used as unique identifier within TrendMiner). Description and units can be left empty. The type is required if you want to support different interpolation types for different tags. If not, the type can be hardcoded in the queries.
For additional examples, refer to the following article: Examples of SQL queries for ODBC connections
Note that stored procedures can be used for retrieving tags as long as they return a result set.
Values file
The values.sql file will contain a SQL SELECT statement and shall return all the values and timestamps for a given tag name and date range.
Queries to retrieve points use binding parameters (specified in query by ?). If binding parameters (?) are not supported then .NET string formatting can be used so use '{0}', '{1}' and '{2}' (include single quotes) instead of the question mark.
 |
Example query output:
 |
Note that stored procedures can be used for retrieving tag data as long as they return a result set.
Output requirements:
Timestamps should be in chronological order, oldest first
All timestamps returned should be inside of the requested time interval
The complete response should be returned within a reasonable timeframe of 5-30 seconds. A well performing data source returns 1 month of data in around 1 second. The response time can be influenced by tweaking the index granularity setting in ConfigHub. Check the user documentation for more info.
Acceptable amount of datapoints within the given timeframe. The number of data points returned can be influenced by tweaking the index granularity setting in ConfigHub. Check the user documentation for more info.
Type file
In case that the types returned in the list.sql file aren’t one of the supported ones you can generate your own mapping as shown below. Even though it isn't require it is good practice to map each type to one supported by TrendMiner.
 |
Datetime file
When creating the values.sql query, it is possible to define the format of the datetime by creating a file datetime_format.txt
If no file exists the format is "yyyy-MM-dd HH:mm:ss.fff”
The format follows .NET formatting specification.
Example:
Without timezone (UTC) | MM/dd/yyy HH:mm |
DDMMYYY 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.
Timezone file
It is advised to have timestamps in UTC. If timestamps are stored in local time it is advised to provide the time zone. 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.
Specify the time zone name as listed on https://docs.microsoft.com/en-us/previous-versions/windows/embedded/gg154758(v=winembedded.80) in the column “Time zone name”.
Example:
Eastern Standard Time |
Pacific Standard Time |
Central Europe Standard Time |
Important
When the database stores date/times in a timezone which uses DST but the timezone is not stored along with the date/time then 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.
Adding data source in ConfigHub
Follow the steps for adding a new data source as detailed in the user documentation.
The “Host” name shall be the name of the folder previously created containing all the configuration files as in the example below. The data source name is free to choose.
TrendMiner will consider the username and password provided in the “connection.txt” file and not the one provided through the interface. You do not need to provide the username and password here.
