Documentation Portal

The following sections will detail all the information needed for setting up a Microsoft Azure Data Explorer data source in TrendMiner.

ConfigHub configuration

To add a Microsoft Azure Data Explorer data source, in ConfigHub go to Data in the left side menu, then Data sources, choose + Add data source.  

Fill out 'Data source details' on the first step of the datasource creation wizard: type name of data source, select “Microsoft Azure Data Explorer” from the Provider dropdown.  Time series capability will be checked by default. 

Fill out additional properties, grouped in following sections:

• Connection details – lists properties related to the configuration of the connection that will be established with Microsoft Azure Data Explorer. 

  

• Time series details – lists properties relevant for the time series data.

  

Following properties need to be populated to establish the connection:

• “Name“ - а mandatory name needs to be specified.

• “Provider” - select the option Microsoft Azure Data Explorer from the dropdown.

• “Capabilities” - the only capability supported is the Time series capability. It will be selected by default.

• “Prefix” - optional text can be entered here that will be used to prefix the tag names in TrendMiner, if a value is entered. Warning: this cannot be changed. The datasource can however be deleted and you can start the configuration over again.

• "Cluster" - to be mapped with value of the cluster URI. https://<cluster>.<region>.kusto.windows.net

• "Application ID" - a mandatory application id. In Azure a new application “TrendMiner” needs to be registered. After completion of this registration the application id will be available. For more info please refer to Azure Active Directory application registration or consult with the Azure Data Explorer documentation.

• "Tenant ID" - a mandatory tenant id. In Azure a new application "TrendMiner"needs to be registered. After completion of this registration the tenant id is available.

• "Client secret" - after registration of the TrendMiner application in Azure a client id and a secret need to be generated in order to get application access. The client secret for this access needs to be entered here.

• "Database" – to be mapped with the name of the ADX database holding the time series data to be pulled.

• "Driver properties" - additional  optional driver specific properties can be configured here as key;value, where key is the name of driver specific property and value the desired value for this property. 

• “Tag list query” - a query used by TrendMiner to create tags. A query needs to be provided that given the database schema and tables used will return the following columns:

  • id - this is mandatory and must have a unique value for each row returned by the query.

  • name - this is mandatory and must have a value.

  • type - an optional type indication. If the type column is not in the query, TrendMiner will use the default type which is ANALOG. Custom types can be returned here not known to TrendMiner but then a type mapping must be added as well. See also type mapping below. Without additional type mapping it must be one of the following TrendMiner tag types: ANALOG, DIGITAL, DISCRETE, STRING

  • description - an optional description to describe the tag. Can return an empty value or null.

  • units - an optional indication of the units of measurement for this tag. Can return an empty value or null.

  The order of the columns in the query needs to be: id, name, type, description, units

• “Index query” - a query used by TrendMiner to ingest timeseries data for a specific tag. The query is expected to return a ts (timestamp) and a value. The following variables are available to be used in the WHERE clause of this query. The variables will be substituted at query execution with values. There should not be any NULL values returned by the SELECT statement so including "IS NOT NULL" in the WHERE clause is strongly recommended.

  • {ID}. The id as returned by the tag list query.

  • {STARTDATE} TrendMiner will ingest data per intervals of time. This denotes the start of such an interval.

  • {ENDDATE} TrendMiner will ingest data per intervals of time. This denotes the end of such an interval.

  • {INTERPOLATION}: Interpolation type of a tag, which can be one of two values: LINEAR or STEPPED.

  • {INDEXRESOLUTION}: Index resolution in seconds. It can be used to optimise a query to return the expected number of points.

• “Internal plotting” - if this option is selected TrendMiner will use interpolation if needed to plot the timeseries on the chart on specific time ranges. If this value is not selected and no plot query was provided, TrendMiner will plot directly from the index.

• “Plot query” – a query used by TrendMiner to plot timeseries data for a specific tag. The query is expected to return a ts (timestamp) and a value. The following variables are available to be used in the WHERE clause of this query. The variables will be substituted at query execution with values. There should not be any NULL values returned by the SELECT statement so including "IS NOT NULL" in the WHERE clause is strongly recommended.

  • {ID}. The id as returned by the tag list query.

  • {STARTDATE}. TrendMiner will plot data per intervals of time. This denotes the start of such an interval.

  • {ENDDATE}. TrendMiner will plot data per intervals of time. This denotes the end of such an interval.

  • {INTERPOLATION}: Interpolation type of a tag, which can be one of two values: LINEAR or STEPPED.

  • {INDEXRESOLUTION}: Index resolution in seconds. It can be used to optimise a query to return the expected number of points.

• “Digital states query” - if tags of type DIGITAL are returned by the tag list query, TrendMiner will use this query to ask for the {stringValue, intValue} pairs for a specific tag to match a label with a number. The query expects to return a stringValue and an intValue and can make use of the {ID} variable in the where clause. The variable will be substituted at query execution with a value.

• “Tag filter” - an optional regular expression to be entered. Only tags with names that match this regex will be retained when creating tags (using the tag list query).

• “Type mapping” - if types differ from what TrendMiner uses and one wants to avoid overcomplicating the query, a type mapping can be entered here. It can contain multiple lines and expect per line the value to be representing a custom type followed by a ; and a TrendMiner type.  During the execution of the tag list query TrendMiner will then use this mapping to map the custom values returned in the query to TrendMiner tag types.

  1. customTypeA;ANALOG

  2. customTypeB;DIGITAL

  3. customTypeC;DISCRETE

  4. customTypeD;STRING

.create table MetaData (['id']: string, name: string, description: string, ['type']: string, units: string)
.create table Data (['id']: string, ts: datetime, value: string)
.create table DigitalStateData (['id']: string, intValue: long, stringValue: string)

MetaData | project id, name, type, description, units

Data
| where ts between (datetime('{STARTDATE}') .. datetime('{ENDDATE}'))
| where id == '{ID}'
| project ts, value| order by ts asc

Data
| where ts between (datetime('{STARTDATE}') .. datetime('{ENDDATE}'))
| where id == '{ID}'
| project ts, value| order by ts asc

DigitalStateData
| where id == '{ID}'
| project stringValue, intValue

.create table MetaData (['id']: string, name: string, description: string, ['type']: string, units: string)
.create table Data (['id']: string, ts: datetime, value: decimal)
.create table StringData ( ts: datetime, id: string, value: string)
.create table DigitalStateData (['id']: string, intValue: long, stringValue: string)

MetaData | project id, name, type, description, units

let StringValuesQuery = view() {
    StringData
    | where id == '{ID}'
    | where ts between (datetime('{STARTDATE}') .. datetime('{ENDDATE}'))
    | order by ts asc
    | where isnotempty(value)
    | project ts, value
};
let NumericValuesQuery = view() {
    let _Data = Data
    | where id == '{ID}'
    | where ts between (datetime('{STARTDATE}') .. datetime('{ENDDATE}'))
    | where isnotempty(value)
    | project ts, value
    | order by ts asc;
    let MyTimeline = range ts from datetime('{STARTDATE}') to datetime('{ENDDATE}') step {INDEXRESOLUTION} * 1s;
    MyTimeline
    | union  withsource=source _Data
    | order by ts asc, source asc 
    | serialize
    | scan declare (i_min:decimal, i_max:decimal, i_minTs:datetime, i_maxTs:datetime, startValue:decimal, endValue:decimal, startTs:datetime, endTs:datetime, min:decimal, minTs:datetime, max:decimal, maxTs: datetime) with 
    (
        step s1 output=none: source=="union_arg0";
        step s2 output=none: source!="union_arg0"=> startValue = iff(isnull(s2.startValue), value, s2.startValue),
                                 startTs = iff(isnull(s2.startTs), ts, s2.startTs),
                                 i_min = iff(isnull(s2.i_min), value, iff(s2.i_min > value, value, s2.i_min)),
                                 i_minTs = iff(isnull(s2.i_min), ts, iff(s2.i_min > value, ts, s2.i_minTs)),
                                 i_max = iff(isnull(s2.i_max), value, iff(s2.i_max < value, value, s2.i_max)),
                                 i_maxTs = iff(isnull(s2.i_max), ts, iff(s2.i_max < value, ts, s2.i_maxTs));
        step s3 output=none: source!="union_arg0" => i_min = iff(isnull(s3.i_min), value, iff(s3.i_min > value, value, s3.i_min)),
                                 i_minTs = iff(isnull(s3.i_min), ts, iff(s3.i_min > value, ts, s3.i_minTs)),
                                 i_max = iff(isnull(s3.i_max), value, iff(s3.i_max < value, value, s3.i_max)),
                                 i_maxTs = iff(isnull(s3.i_max), ts, iff(s3.i_max < value, ts, s3.i_maxTs));
        step s4: source=="union_arg0" => startValue=s2.startValue,
                                     startTs=s2.startTs,
                                     min = s3.i_min,
                                     minTs = s3.i_minTs,
                                     max = s3.i_max,
                                     maxTs = s3.i_maxTs,
                                     endValue= s3.value,
                                     endTs= s3.ts;
    )
    | project startTs, startValue, endTs, endValue, minTs, min, maxTs, max
    | mv-expand     ts  = pack_array(startTs, minTs, maxTs, endTs) to typeof(datetime),
                value = pack_array(startValue, min, max, endValue) to typeof(string)
    | order by ts asc
    | where isnotempty(value)
    | project ts, value
};
let type = toscalar(MetaData
| where id == '{ID}'
| project type);
union (StringValuesQuery() | where tolower(type)=='string'), (NumericValuesQuery() | where not(tolower(type)=='string'))
| where isnotempty(value)

let StringValuesQuery = view() {
    StringData
    | where id == '{ID}'
    | where ts between (datetime('{STARTDATE}') .. datetime('{ENDDATE}'))
    | order by ts asc
    | where isnotempty(value)
    | project ts, value
};
let NumericValuesQuery = view() {
Data
  | where id == '{ID}'
  | where ts between (datetime('{STARTDATE}') .. datetime('{ENDDATE}'))
  | where isnotempty(value)
  | project ts, value=tostring(value)
  | order by ts asc;
};
let type = toscalar(MetaData
| where id == '{ID}'
| project type);
union (StringValuesQuery() | where tolower(type)=='string'), (NumericValuesQuery() | where not(tolower(type)=='string'))
| where isnotempty(value)

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

Plant integration configuration (Connector)

Create folders 

1. First step is to browse to the Plant Integration folder you have created during installation. This folder will most likely reside inside the inetpub folder on the c drive of your connector server, which is the default location:

   c:/inetpub/PlantIntegrations 

   

2. Create a folder (not case sensitive) that will host all your ODBC/OLEDB/SQLite configuration files. All configuration files for ODBC connections will go into the ODBC folder, for OLEDB connections into the OLEDB folder and so on.

   

3. Create a subfolder for each data source you want to configure inside the correct 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”, so we create the "Plant_LIMS" subfolder in the recently created "ODBC" folder.

   

Create 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 are not 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.	Required
  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.txt 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. Independent of the option that is chosen, the details should be added to the connection.txt file.

The following article contains additional information on the required format of a valid connection string and examples depending on the type of database being used:

https://www.connectionstrings.com/ 

Note that only one connection string is necessary to connect to a database. Different tables within one database will be accessible through the same connection.

Standalone Full Connection Strings 

ODBC example:

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 a minimum requirement, you shall specify the Server, Database, Uid, and Pwd.

OLEDB example:

Provider=iHOLEDB.iHistorian.1;Persist Security Info=False;USER ID=<myUsername>;Password=<myPassword>; Data Source=<data source>;Mode=Read

SQLite example:

For SQLite, the connection string shall contain the full path to the sqlite.db file as shown below.

Connection via named data source using System ODBC drivers 

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

2. Once the System Data Source is configured, create a “connection.txt” file and make reference to the System Data Source as shown below.

   Note

   The username and password must be included in the file.

   

You can find additional information 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?view=sql-server-ver15#odbc_connstring 

Configuring the queries

The data contained in your datasource now needs to be fetched in a way that TrendMiner can understand, to correctly digest and present back to the end users. There are a few steps required to make this work.

1. The first step is a query that will return unique tagnames (to identify the time series), its datatype, and optional metadata such as description and units. This query goes into the list.sql file.

2. The second step is a query that will return an ordered series of timestamp and value pairs for a given tagname, start date, and end date. This query goes into the values.sql file.

3. The third step is to map any custom datatypes to the TrendMiner datatypes. This mapping goes into the type.csv file.

4. The fourth step is to configure the returned timestamp format from step 2 so TrendMiner can parse it correctly. This step is optional and only required if the timestamp format returned in step 2 is not "yyyy-MM-dd HH:mm:ss.fff”

5. The fifth step is to configure the timezone of the returned timestamps from step 2 so TrendMiner can align it correctly. This step is optional and only required if the timestamps returned in step 2 are not in UTC. This is also important to correctly handle Daylight Savings Time (DST).

Details about all steps are documented in the sections below.

list.sql query

The list.sql file will contain a SQL SELECT statement and shall return all the tags with additional metadata as shown below. All your returned types should be mapped onto one of the supported tag types with the type.csv file.

The query should return a result with column names "tagname" (mandatory), "type" (mandatory), "units" (optional), and "description" (optional). All values for the tagnames returned by the query should be unique. Duplicates will lead to synch errors later on when connecting the datasource to TrendMiner in later steps. Stored procedures can be used for retrieving tags as long as they return a result set.

An example of a very simple query is given below. Note that the type has been hardcoded to be "analog". For additional examples, refer to the following article: Examples of SQL queries for ODBC connections.

SELECT
    tagname, description, units, "analog" as type
FROM Table_1

A correct output of the query is shown below:

values.sql query

The values.sql file will contain a SQL SELECT statement and shall return all the values and timestamps for a given tagname and date range. Note that these parameters (tagname, start date, and end date) will be automatically passed on by TrendMiner when in production as binding parameters.

These binding parameters are specified in the query by using question marks "?". 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.

The query should return a result with column names "ts" (mandatory) and "value" (mandatory). The timestamps in the ts column should be ordered in ascending order (oldest point first, newest point last). Stored procedures can be used for retrieving tag data as long as they return a result set.

Since this is a crucial step, here is a summary of the output requirements:

• The columns returned should be named "ts" (for the timestamp) and "value". Timestamps should be in ascending chronological order, meaning oldest first, newest last.

• All timestamps that are returned should fall inside the requested time interval. If they don't, then indexing will fail later on.

• The complete response should be returned within a reasonable timeframe. Typically recommended is +- 1 second per month of data, with user experience noticeably dropping off when response time exceeds 5 seconds per month of data. The response time can be influenced somewhat by tweaking settings such as the index granularity in ConfigHub, although a slow datasource will always lead to bottlenecks, no matter the settings. Check the user documentation for more info.

• An acceptable amount of data points within the given timeframe. The number of data points returned can be influenced by tweaking the query, or by tweaking settings such as the index granularity setting in ConfigHub. Check the user documentation for more info.

An example of a simple query is given below. For additional examples, refer to the following article: Examples of SQL queries for ODBC connections.

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

An example of the correct output is shown below.

type.csv mapping

The types returned in the list.sql must be mapped to the types supported by TrendMiner, also if the returned type is the same as a TrendMiner supported type. You need to generate your own mapping. It is best practice to explicitly map each possible type returned by list.sql to one supported by TrendMiner. If no mapping/type.csv is provided all tags types will default to analog.

In the type.csv file, create one line per data type, with the output of the list.sql types as the first value, followed by a semicolon (";"), followed by one of the supported datatypes in TrendMiner. The example below ensures the results are returned in the right case, and a DIGITAL result is explicitly returned as a STRING result.

datetime_format.txt configuration

In case the timestamps returned in the values.sql query are not in the format "yyyy-MM-dd HH:mm:ss.fff” (e.g. 2022-12-03 14:28:23.357), then the format can be configured by setting it in the datetime_format.txt file. Simply edit the txt file and paste the format in the file. The format is interpreted following the .NET formatting specification.

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.

Some examples are specified below.

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

timezone.txt configuration

It is advised to always use timestamps in UTC. This will avoid confusion when troubleshooting, especially when datasources from different timezones are connected since TrendMiner uses UTC by default. If timestamps are stored in the datasource in local time, it is necessary to provide the timezone information.

Specify the time zone name in the timezone.txt file, using "Time zone name" as listed on https://docs.microsoft.com/en-us/previous-versions/windows/embedded/gg154758(v=winembedded.80).

Table 17. Timezone examples

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

The following sections will detail all the information needed to set up an AWS IoT SiteWise data source in TrendMiner.

Communication flows 

You can configure AWS IoT SiteWise to store data in following storage tiers: a hot tier optimized for real-time applications, and a cold tier optimized for analytical applications.

The cold tier requires additional configuration in AWS with regards to S3 and a Glue crawler. For more information about this, please consult the AWS documentation.

Setup an AWS IoT SiteWise data source 

To add an AWS IoT SiteWise data source, in ConfigHub go to Data in the left side menu, then Data sources, choose + Add data source.

Fill out 'Data source details' on the first step of the datasource creation wizard: type name of data source, select “AWS IoT SiteWise” from the Provider dropdown. 

Selecting the capabilities for the data source with the given checkboxes will open additional properties to enter, grouped in following sections:

As the time series and asset capabilities go hand in hand in AWS IoT SiteWise, the checkboxes for Asset and Time series capabilities must be selected together.

To pull AWS IoT SiteWise alarm data, select the Context capability checkbox.

Asset capability 

With this capability enabled the datasource can pull assets from AWS IoT SiteWise to be synced into a TrendMiner asset structure.

Following properties need to be populated:

Time series capability 

With this capability enabled the datasource can sync Metrics, Transforms and Measurements from AWS IoT SiteWise into TrendMiner tags.

Following properties need to be populated:

Context capability 

With this capability enabled the datasource can sync Alarms from IoT SiteWise into TrendMiner context items.

Following properties need to be populated:

Setting up S3, Glue, Athena 

The following sections will detail all the information needed top set up an Amazon Timestream data source in TrendMiner.

To add an Amazon Timestream data source, in ConfigHub go to Data in the left side menu, then Data sources, choose + Add data source.  

Fill out 'Data source details' on the first step of the datasource creation wizard: type name of data source, select “Amazon Timestream” from the Provider dropdown. Time series capability will be checked by default. 

Fill out additional properties, grouped in following sections:

Following properties need to be populated to establish the connection:

Example queries 

The following exemples are based on the sample data setup in Amazon Timestream – containing a sample database with IoT sample data. Amazon Timestream will then create a sample database that contains IoT sensor data from one or more truck fleets to streamline fleet management and to identify cost optimization opportunities such as reduction in fuel consumption. For more information, please consult https://docs.aws.amazon.com/index.html.

This query treats the location measurements as tags of type STRING.

SELECT distinct 
CONCAT(truck_id, '--',measure_name) as id,
CONCAT(truck_id, '--',measure_name) as name,
CASE measure_name
    WHEN 'load' THEN 'DISCRETE'
    WHEN 'location' THEN 'STRING'
    ELSE 'ANALOG'
 END
 AS type,
CONCAT(make, ' ', model, ' (', fleet , ')') AS description,
 CASE measure_name
    WHEN 'load' THEN 'kg'
    WHEN 'speed' THEN 'km/h'
    WHEN 'fuel-reading' THEN '%'
    ELSE ''
 END
 AS unit
FROM sampleDB2.IoT

Alternative query treats the location measurements as tags of type DIGITAL. This is a somewhat less realistic case, but it serves the purpose of also demonstrating tags of type DIGITAL. Location measurements are given a random number between 1 and 10 in the matching alternative index query.

SELECT distinct 
CONCAT(truck_id, '--',measure_name) as id,
CONCAT(truck_id, '--',measure_name) as name,
 CASE measure_name
    WHEN 'load' THEN 'DISCRETE'
    WHEN 'location' THEN 'DIGITAL'
    ELSE 'ANALOG'
 END
 AS type,
CONCAT(make, ' ', model, ' (', fleet , ')') AS description,
 CASE measure_name
    WHEN 'load' THEN 'kg'
    WHEN 'speed' THEN 'km/h'
    WHEN 'fuel-reading' THEN '%'
    ELSE ''
 END
 AS unit
FROM sampleDB2.IoT 

select time as ts,
CASE WHEN measure_value::double  is not null
               THEN cast(measure_value::double as varchar)
               ELSE measure_value::varchar
          END AS value
from "sampleDB2"."IoT" where
 concat(truck_id, '--',measure_name) = '{ID}'
 and time >= '{STARTDATE}'
 and time <= '{ENDDATE}'
 order by time asc

Alternative index query for digital location tags. Where we for the purpose of this demo case assign a random number to a location value for a timestamp.

select time as ts,
CASE SPLIT('{ID}', '--')[2]
    WHEN 'location' THEN RANDOM(10)
    ELSE "measure_value::double"
END AS value
from "sampleDB2"."IoT" where
 concat(truck_id, '--',measure_name) = '{ID}'
 and time >= '{STARTDATE}'
 and time <= '{ENDDATE}'
 order by time asc

This query is applicable when the alternative tag list and alternative index query is used.

SELECT 'STATE0' as stringValue, 0 as intValue UNION
SELECT 'STATE1', 1 UNION
SELECT 'STATE2', 2 UNION
SELECT 'STATE3', 3 UNION
SELECT 'STATE4', 4 UNION
SELECT 'STATE5', 5 UNION
SELECT 'STATE6', 6 UNION
SELECT 'STATE7', 7 UNION
SELECT 'STATE8', 8 UNION
SELECT 'STATE9', 9 UNION
SELECT 'STATE10', 10
ORDER BY 1

Aspen Production Record Manager is an information management system for batch type data. A batch in Production Record Manager is subdivided into subbatches and contains attributes called characteristics.

Characteristics represent most types of attributes, including start and end times, batch header data, units, recipe information, batch end data, and so on. In addition, Production Record Manager can generate certain attributes dynamically, such as tag statistics, when requested. The batches are grouped by area and one or more batch areas exist in a Production Record Manager server.

Our provider for Aspen Production Record Manager implements the asset capability and uses data from the area definition to populate the asset hierarchy in TrendMiner. The root node of the asset hierarchy is the area itself. The children nodes of an area node are created using “Unit Definitions". The “Tag Definitions“ are the children of the unit nodes, and the leaf nodes of the asset hierarchy. The asset hierarchy has a fixed depth of two or three.

Area, Unit and Tag Definitions in Aspen Production Record Manager Administrator

Asset hierarchy in TrendMiner

Our provider for Aspen Production Record Manager also implements the context capability and uses data from the area definition to populate context items in TrendMiner.

Context Item Types 

The provider will create Context Item Types for every subbatch definition in the area definition, and that type will be linked to the Context Items.

Components 

The link between the asset hierarchy and the context items is established using components in TrendMiner. The provider uses the Unit Characteristic from the subbatches of Aspen Production Record Manager to determine the component of a Context Item. The subbatch level where the Unit Characteristic is defined in Aspen Production Record Manager can be configured by the customer. Since a unit is generally a physical piece of process equipment, every child subbatch of the subbatch level which defines the Unit Characteristic is also conceptually linked to that unit. This is why the following logic is implemented in the provider:

This comes into play when the user starts visualizing multiple subbatch levels at once. Together with the possibility to include the children of a component during filtering it would render the following Gantt, without the users knowing which units belong to the area.

Gantt 

Combining the Context Item Types and the implementation of the above component logic, will give the following result in the Gantt view of TrendMiner:

Context Item properties 

The provider generates Context Item properties based on the numeric Characteristics that are available on the subbatches of Aspen Production Record Manager. Only the numeric characteristics are included as Context Item properties, this is by design.

Since numeric characteristics are decimal values in Aspen Production Record Manager, Context Items properties are also formatted decimal numbers.

Context item properties

The Start Time, Duration and End Time Characteristics are available as Events. The existence of characteristics and the level on which they are defined is completely dependent on the configuration of the Subbatch Definition implemented on the customer environment.

Multiple occurrences (instances) of the same characteristic can be created during the execution of a subbatch. If a subbatch has multiple instances for a specific characteristic, the characteristics are suffixed with the instance number of the characteristic e.g., BATCH_NO_1 and BATCH_NO_2.

Provider generated property - Lineage 

To make the lineage of a context item more apparent, the provider generates the Lineage property, using the same semantics as the Batch Detail Tool.

Batch Detail Tool

Lineage property in TrendMiner

AtBatch21ApplicationInterface 

Our APRM provider uses the AtBatch21ApplicationInterface library which is a component of the AspenONE Manufacturing Execution Systems. This library is installed when you install the Aspen Production Record Manager Server. If Plant Integrations is deployed on the same server, no additional software / configuration is required to use the provider. If it is required to install Plant Integrations of a different server, the Aspen Desktop Application must be installed on the Plant Integrations Service.

Make sure you select the following components from the AspenOne Manufacturing Execution Systems-installer from AspenTech:

Installing the Aspen Desktop Applications

Connector (Plant Integrations) 

Plant Integrations must be configured to run under an identity that has access to Aspen Production Record Manager. This user must be a member of the 'Distributed COM Users ’group on Aspen Production Record Manager server.

Firewall 

If a customer uses a firewall between Plant Integrations and the Aspen Production Record Manager Server, additional ports must be open to allow the DCOM library to communicate with the server.

In ConfigHub go to Data in the left side menu, then Data sources, choose + Add data source. Fill out 'Data source details' on the first step of datasource creation wizard: type the name of a data source, select “Aspen Production Record Manager (via SDK)" from Provider dropdown, then select the configured connector from Connect via dropdown.

Data source details step in creation wizard

Connection details 

To establish a connection 2 optional properties to be filled in:

Connection details step in datasource creation wizard

Provider details 

The APRM provider implements both assets and context capability. These capabilities have two required provider properties to be filled in:

Provider details step in datasource creation wizard

The following are the optional context properties:

Context details step in datasource creation wizard

Creating Generic JDBC provider 

To integrate with and ingest data from a database that supports JDBC, a datasource of provider type Generic JDBC can be added and configured in ConfigHub. 

To add a Generic JDBC data source, in ConfigHub go to Data in the left side menu, then Data sources, choose + Add data source. Fill out 'Data source details' on the first step of the datasource creation wizard: type the name of the data source, select “Generic JDBC” from the Provider dropdown. Selecting the capabilities for the data source with the given checkboxes will open additional properties to enter, grouped in following sections:

Following properties need to be populated to establish the connection to the JDBC provider:

Following properties are shared by multiple capabilities:

Following properties need to be entered to enable time series capability:

Following properties need to be entered to enable asset capability:

Following properties need to be entered to enable context capability:

 Time series capability 

Tag list query

select id, name, type, description, units from source

Index query

select ts, value from source where id={ID} and ts between TO_TIMESTAMP({STARTDATE}, 'YYYY/MM/DD HH24:MI:SS') and TO_TIMESTAMP({ENDDATE}, 'YYYY/MM/DD HH24:MI:SS') and value IS NOT NULL order by ts asc

Plot query

select ts, value from source where id={ID} and ts between TO_TIMESTAMP({STARTDATE}, 'YYYY/MM/DD HH24:MI:SS') and TO_TIMESTAMP({ENDDATE}, 'YYYY/MM/DD HH24:MI:SS') and value IS NOT NULL o rder by ts asc

Digital states query

This query is applicable when there are tags of type STRING returned through the tag list query.

select stringValue, intValue from source where id={ID}

Asset capability  

Root asset query

select id, type, name, parentId, description, dataType, dataValue from source where parentId is NULL

Asset query

select id, type, name, description, dataType, dataValue from source where id in ({IDS})

Asset children query

select id from source where parentId={ID}

Context capability  

Events query

select id, name, type, occurred_at, modified_at, components, state, description, keywords from source where occurred_at > TO_TIMESTAMP({AFTER}, 'YYYY/MM/DD HH24:MI:SS') and occurred_at < TO_TIMESTAMP({BEFORE}, 'YYYY/MM/DD HH24:MI:SS')

Event by id query

select id, name, type, occurred_at, modified_at, components, state, description, keywords from source where id = {ID}

Event types query

select id, name, color, startstate, endstate, states, fields from source

Event type by id query

select id,name, color, startstate, endstate, states, fields from source where id = {ID}

Fields query

select id, name, type, placeholder, options from source

Field by id query 

select id, name, type, placeholder, options from source where id = {ID}

Event fields linked to event query

select fieldId, value from source where eventId = {ID}

Time series capability  

Tag list query

select id, name, type, description, units from source

Index query

select ts, value from source where id={ID} and ts >= STR_TO_DATE({STARTDATE}, '%Y-%m-%d %H:%i:%S') and ts <=STR_TO_DATE({ENDDATE}, '%Y-%m-%d %H:%i:%S') order by ts asc

Plot query

select ts, value from source where id={ID} and ts >= STR_TO_DATE({STARTDATE}, '%Y-%m-%d %H:%i:%S') and ts <=STR_TO_DATE({ENDDATE}, '%Y-%m-%d %H:%i:%S') order by ts asc

Digital states query

This query is applicable when there are tags of type STRING returned through the tag list query.

Select stringValue, intValue from source where id={ID}

Asset capability  

Root asset query

select id, type, name, parentId, description, dataType, dataValue from source where parentId is NULL

Asset query

select id, type, name,description, dataType, dataValue from source where id in ({IDS})

Asset children query

select id from source where parentId={ID}

Context capability  

Events query

select id, name, type, occurred_at, modified_at, components, state, description, keywords from source where occurred_at > STR_TO_DATE({AFTER}, '%Y-%m-%d %H:%i:%S') and occurred_at < STR_TO_DATE({BEFORE}, '%Y-%m-%d %H:%i:%S')

Event by id query

select id, name, type, occurred_at, modified_at, components, state, description, keywords from source where id = {ID}

Event types query

select id, name, color, startstate, endstate, states, fields from source

Event type by id query

select id,name, color, startstate, endstate, states, fields from source where id = {ID}

Fields query

select id, name, type, placeholder, options from source

Field by id query 

select id, name, type, placeholder, options from source where id = {ID}

Event fields linked to event query

select fieldId, value from source where eventId = {ID}

Time series capability  

Tag list query

select id, name, type, description, units from source

Index query

select ts, value from source where id={ID} and ts>={STARTDATE} and ts<={ENDDATE} order by ts asc

Plot query

select ts, value from source where id={ID} and ts>={STARTDATE} and ts<={ENDDATE} order by ts asc

Digital states query

This query is applicable when there are tags of type STRING returned through the tag list query.

select stringValue, intValue from source where id={ID}

Asset capability  

Root asset query

select id, type, name, parentId, description, dataType, dataValue from source where parentId is NULL

Asset query

select id, type, name, description, dataType, dataValue from source where id in ({IDS})

Asset children query

select id from source where parentId={ID}

Context capability  

Events query

select id, name, type, occurred_at, modified_at, components, state, description, keywords from source where occurred_at >{AFTER} and occurred_at <{BEFORE}

Event by id query

select id, name, type, occurred_at, modified_at, components, state, description, keywords from source where id = {ID}

Event types query

select id, name, color, startstate, endstate, states, fields from source

Event type by id query

select id,name, color, startstate, endstate, states, fields from source where id = {ID}

Fields query

select id, name, type, placeholder, options from source

Field by id query 

select id, name, type, placeholder, options from source where id = {ID}

Event fields linked to event query

select fieldId, value from source where eventId = {ID} 

Time series capability  

Tag list query

select id, name, type, description, units from source

Index query

select ts, value from source where id={ID} and ts between convert(DATETIME, {STARTDATE}, 127) and convert(DATETIME, {ENDDATE}, 127) order by ts asc

Plot query

select ts, value from source where id={ID} and ts between convert(DATETIME, {STARTDATE}, 127) and convert(DATETIME, {ENDDATE}, 127) order by ts asc

Digital states query

This query is applicable when there are tags of type STRING returned through the tag list query.

select stringValue, intValue from source where id={ID}

Asset capability  

Root asset query

select id, type, name, parentId, description, dataType, dataValue from source where parentId is NULL

Asset query

select id, type, name, description, dataType, dataValue from source where id in ({IDS})

Asset children query

select id from source where parentId={ID}

Context capability  

Events query

select id, name, type, occurred_at, modified_at, components, state, description, keywords from source where occurred_at > {AFTER} and occurred_at < {BEFORE}

Event by id query

select id, name, type, occurred_at, modified_at, components, state, description, keywords from source where id = {ID}

Event types query

select id, name, color, startstate, endstate, states, fields from source

Event type by id query

select id,name, color, startstate, endstate, states, fields from source where id = {ID}

Fields query

select id, name, type, placeholder, options from source

Field by id query 

select id, name, type, placeholder, options from source where id = {ID}

Event fields linked to event query

select fieldId, value from source where eventId = {ID}