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

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:

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.

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:

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:

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: