Postgres / Timescaledb¶

Normal makes it easy to build a durable, complete, and scalable repository of all of your BAS and other datasets by storing it in a SQL database. Currently, Normal supports PostgreSQL with the Timescaledb extension installed. Becasue BAS systems can generate a substantial amount of time-series data, it is important to use a database with good support for this data type; otherwise the system will scale poorly.

When Normal is configured with Timescale connection information, it will automatically insert all point data and metadata into the database, making it easy to build dashboards and other analytics products using any dashboard tool. Popular dashboarding tools include Tableau, Grafana, and PowerBI.

Setup¶

Before starting, you must install and configure a Timescale instance somewhere. The easiest way to do this is using the Timescale Cloud offering, but the Community Edition is also available, open-source, and free to use.

After installing, you will need to create a database with the timescale extension installed for your BAS data. Collect the hostname, username/password, and database name. Enter these into the Timescale settings page in the Normal Framework console.

If using a self-hosted timescale node, make sure to install the timescale extension on the database you create. For instance, this will create a new role and database in postgres, and add the extension to it:

CREATE USER tsdb WITH PASSWORD 'password';
CREATE DATABASE tsdb WITH OWNER tsdb;
\connect tsdb
CREATE EXTENSION timescaledb;

Using the Database¶

The database connector will create three tables in the database you have:

• instances: each copy of Normal Framework will create a single row in this table, to distinguish data coming from different sites.
• points: this table has metadata for every point in the Normal point database. If the metadata changes (for instance, due to a BACnet discovery), it will be updated here.
• data: timeseries data are added to this table.

In addition to these table, the adaptor also creates views to make it easier to explore the point database. A view is created for each layer in the system. For instance, the BACnet view makes it easy to query based on device ID or object type.

Example Queries¶

1. Count points by bacnet device ID

   select device_id, count(*) from layer_hpl_bacnet_1 group by device_id
   

2. Get the latest value of points from a device. Somewhat more complex, but shows good use of the layer views to display a useful summary.

   select 
       points.name,
       layer_hpl_bacnet_1.object_id,
       vals.time,
       vals.value
   from layer_hpl_bacnet_1
       left join points on points.id = layer_hpl_bacnet_1.point_id
       inner join lateral (
           select * 
           from data 
           where data.point_id = layer_hpl_bacnet_1.point_id
           order by time desc
           limit 1
       ) vals on TRUE
   where device_id = 260001
   order by time asc