Documentation Portal

The first step in setting up an ODBC connector is to retrieve a unique list of tags. This query is entered into the list.sql file. The resulting query should return at least the "tagname" column and the "type" column. The query can optionally also return additional metadata in the "units" and the "description" columns, to help users recognize the data they're working with.

The query should not return duplicate entries for the tagname column. This will cause synch errors.

The simplest possible query, if the table you're working on already only has unique tagnames (no duplicates are present), and all tags are "analog" tags:

SELECT
    tagname, "analog" as type
FROM Table_1

If your database table contains different descriptions, units, and types for a single tag name, you can use the SQL function FIRST_VALUE() to get the first value in an ordered partition of a result set. The following query will return the first description, unit, and type it encounters for every distinct tagname.

SELECT DISTINCT 
    tagname,
    FIRST_VALUE(description) OVER (PARTITION BY tagname ORDER BY tagname) as description,
    FIRST_VALUE(units) OVER (PARTITION BY tagname ORDER BY tagname) as units,
    FIRST_VALUE(type) OVER (PARTITION BY tagname ORDER BY tagname) as type
FROM Table_1

Use a UNION statement to combine the output of two different tables (Table_1 and Table_2) into a single list.sql query.

SELECT
    tagname,
    description,
    type,
    units
FROM Table_1  
UNION
SELECT
    tagname,
    description,
    type,
    units
FROM Table_2 

Depending on the table schema, you might want to concatenate (combine) multiple columns to generate a unique tagname as shown below. You might also want to do the same for the description as well. Note that this will make it somewhat harder to reconcile data in TrendMiner with data in the SQL database, e.g. during troubleshooting. Therefore, it could be advised to create a table in the database that performs these steps, and have TrendMiner query that table directly.

SELECT DISTINCT
    (CAST(COMPONENT_NAME as varchar) + CAST(TANK_NUMBER as varchar)) as tagname,
    description,
    type,
    units
FROM Table_1

You might need to combine (join) multiple tables to generate a query output with all the necessary information for the list.sql file. In the query shown below, “TagName” and “Description” are both part of the table “_Tag”. However, the type and units are part of the “TagType” and “EngineeringUnit” tables respectively, pulled using the corresponding keys. Once again, for troubleshooting purposes, it might be better to construct a table like this in the data source immediately and use a simpler query in this list.sql setup.

SELECT DISTINCT
    TagName as tagname,
    Description as description,
    Unit as units,
    TagTypeName as type 
FROM _Tag
LEFT JOIN TagType ON _Tag.TagType=TagType.TagTypeKey
LEFT JOIN EngineeringUnit ON _Tag.EUKey=EngineeringUnit.EUKey 

Once the tagnames are known to TrendMiner through the list.sql query, a query needs to be constructed that can retrieve data using three inputs: the tagname, the starts date for the query and the end date. These inputs are passed through as question marks in the query and should be in this order. The query should return an ordered list of timestamps and value pairs, with column names "ts" and "value" respectively. NULL values and other unexpected values such as NaN or other typical error flags should be filtered out.

The simplest possible query, if data is already preprocessed and column names already match the expectations, is shown below:

SELECT
    value, ts
FROM Table_1   
WHERE tagname = ? AND timestamp IS BETWEEN ? AND ? ORDER BY ts ASC  

If the values table contains entries (rows) with an empty (null) value, these can be filtered out with a values.sql query as follows.

SELECT
    value,
    timestamp as ts  
FROM Table_1   
WHERE tagname = ? AND timestamp IS BETWEEN ? AND ? AND value IS NOT NULL ORDER BY timestamp ASC

In case there is more than one value for the same timestamp available (which, in itself should physically not be possible, but could happen due to rounding errors), it is required to let TrendMiner know which one to pick to avoid unpredictable results. The following query will return the first “value” encountered for a given tag and date range.

SELECT DISTINCT
    FIRST_VALUE(value) OVER (PARTITION BY timestamp ORDER BY value desc) as value,
    timestamp as ts  
FROM Table_1  
WHERE tagname = ? AND timestamp IS BETWEEN ? AND ? ORDER BY timestamp ASC

In this case, assuming the values and timestamps originate from the same table, the values.sql query might look as follows

SELECT
    value2,
    FORMAT(value2, 'N', 'en-us') as value2_US_Format,
    FORMAT(value2, 'N', 'de-de') as value2_DE_Format
FROM Table_1

The output will look as follows: