Follow

How do I create a Definition for a database-type Data Source?

This article will be updated in the near future! In the meanwhile, if the following information is insufficient, contact support@flow-software.com.

It is possible to build a json definition file for a database-type Data Source. The definition file defines the queries required to build a namespace and generate retrieval queries automatically based on the concept of placeholders in subsequent queries.

The generator works on the concept of an item path and item value queries. A namespace definition file must be created to generate the namespace and parameters that can be used in subsequent queries. The definition file is a JSON file and the context will be discussed in the latter part of this document.

It allows for mass population of measures without having to manually change query parameters. If no definition file is supplied, the Data Source connector will, as per normal, browse the database to retrieve tables, views and stored procedures.

Definition file

The definition file is a JSON formatted file to specify the namespace / schema of the database.

The file consists of 3 main parts:

  • Item Path Query
This will generate the folder structure of your namespace.
  • Item Query
This creates the item in a specific folder. It will also specify items such as descriptions, unit of measure and item data types.
  • Item value Query
This is the query that will be used to retrieve raw or aggregated values by the Flow engine.
   

Path Query

The path query specifies the file structure of your namespace. These queries can also be defined as an array of queries in the JSON file if you need to generate more than one folder structure for different items. There are some rules that must be adhered to for the query to build a namespace successfully:

  1. The query must return a column specifying the path of an item. The column must be called “path”
  2. Levels of derivation in your folder structure is separated in the result of the path query by the” \\” characters.
  3. The query can return other field to be used in subsequent queries
  4. It must return a link to resolve your items to the folder structure you created.

It is good practice to order your query results by the resolving path.

Item Query

The item query specifies the detail of your item and can include items such as unit of measure, description and the data type of the item. The query must resolve the item to only one path structure. These queries can also be defined as an array of queries in the JSON file if you need to generate more than one item type per path.

DataSources-SQLDBItemQuery.png

There are some rules that must be adhered to for the query in order to build add an item successfully:

  1. The special characters “[*,”,”*]” are deemed as placeholders. The resulting row value from previous queries will be replaced within the query.
  2. The query must return the values in the following columns:
  • Name
Required. The name of the item.
  • Description
Optional. The description of the item.
  • UOM
Optional. The unit of measure of the item.
  • Type
Optional. The data type of the item. By default, the “Analog” type will be selected if nothing is specified.
   

Valid types include:

  1. Analog
  2. String
  3. Boolean

Item Value Queries

The item value query specifies the query that will be executed by the Flow engine to return the actual values of the item for a specific time period. The values can either be aggregated by the query, or it could be the raw values and the Flow engine will do the aggregation method selected in the properties section of the file.

There are some rules that must be adhered to for the query to get item values successfully:

  1. The special characters “[*,”,”*]” are deemed as placeholders. The resulting row value from previous queries will be replaced within the query
  2. The query must return the values in the flowing columns:
  • Value
The actual item value for a specific timestamp.
  • Quality
Quality data representing the quality data of the item. If no quality data is available, hardcode this field to 192.
  • Timestamp
Timestamp of the data. This is required if the Flow engine needs to perform an aggregation method on the data.
  • Time Delimiters
[PeriodStart], PeriodEnd]. These placeholders will be replaced by the Flow engine with the correct time period based on the retrieval that must be performed.
   

Item Properties

Other properties that can be defined, but are optional include:

Measure Aggregation – Aggregation method to be applied if the raw data is returned by the item value query (result must include a timestamp field).

Valid values include:

DataSources-SQLDBItemProperties.png

  • Measure Rollover
Applicable if the Counter retrieval style is selected.
  • Measure Deadband
Applicable if the Counter retrieval style is selected.
  • Measure State Comparator
Applicable if the Time in State retrieval style is selected.
  • Measure State
Applicable if the Time in State retrieval style is selected.
   
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments