Documentation Portal

This first example will only implement minimal logic to create a functioning data source. It will not consider Analytics Optimization or implement logic to handle different data types. It is recommended to start with this simple example to ensure the connectivity with ADX is functioning well and to check whether any constraints performance-wise would already pop up. Also, note that ADX will limit the number of output rows. This truncation default is typically 500,000 rows, but can be overridden. If the limit is hit however, ADX will consider this query as FAILED, which causes indexing in TrendMiner to stop.

To create a simple working set of tables with the schemas used in this documentation, the following queries can be executed. Note that this considers data entered as strings in the tables, even though they might represent numeric values (e.g. "5.01").

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

Based on these tables, a tag list query to fetch all tags and their details is a simple project query.

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

The index query this simple example selects the data based on the parameters {ID}, {STARTDATE}, and {ENDDATE}. TrendMiner will fill in these variables when sending the query to ADX. Note that this query does not perform any analytics optimization or interpolation. It will simply return all rows that match the conditions. Due to the data schema (the value was defined as string type), this query works for ANALOG, DIGITAL, and STRING tags.

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

The plot query will be the same as the index query in this simple example. Once again, all rows that match the conditions will be returned without any further processing.

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

To map STRING values to a numeric value (to define the TrendHub y-axis plotting order), the following digital states query can be defined.

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

This example will elaborate and will consider different datatypes in the queries and implement the Analytics Optimization. Note that implementing this in the query itself may come with a performance penalty as the logic needs to run when index data is fetched. Especially if the raw data contains many data points, it should be considered whether data can be pre-processed before it is fetched by TrendMiner.

The table schemas for this second example include one table (Data) for tags with decimal values and one table (StringData) for tags with non-decimal values.

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

Based on these tables, same as above, a tag list query to fetch all tags and their details is a simple project query.

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

The index query in this example is a lot more complete as it handles different scenarios but at the cost of query complexity. Analytics optimization is implemented for numeric tags. This means numeric tags and non-numeric tags need to follow different query paths. This is done by using the union statement near the end of the query, which uses the output of the type filter to query the different tables.

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)

The plot query looks similar conceptually, but just returns raw data so is a lot shorter. Note again the conversion from decimal to string for the NumericValuesQuery to avoid a type mismatch in the union statement.

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)