Uncovering energy waste in buildings with streams and tables

By Pilgrim - October 22, 2019

The challenge

The CFO of a large retail chain suspects that the heating/cooling systems on her sites are sometimes running even when the sites are unoccupied - wasting energy and money and contributing to climate change. 

Across several sites most have a smart energy meter for the whole building which report energy used in the past half-hour, and several occupancy sensors within the building which give a True/False report on occupancy every 15 minutes.

The CFO wants a report showing energy wasted in unoccupied buildings over the past 7 days.

Joining the streams

Every IoT device has a unique ID which is reported with every reading that streams from that device. If our problem was entirely about energy (say we wanted to sum the energy from each meter over the past day) then all our calculations could be done within the stream from each meter.

But here we have a stream of energy readings from each energy meter and a stream of occupancy readings from each occupancy sensor. The only way we know which energy streams are related to which occupancy streams is that each stream has a “site” property.

two sites-1

In the picture above, and table below, we can see that Meter 1234 is reporting energy for site 1, and Occupancy Sensor 2222 is reporting occupancy also for site 1:

Energy Meter 1234

Energy Meter 5678

Occupancy Sensor 2222

Occupancy Sensor 4444

ID=1234, site=1, kW=2

ID=5678, site=2, kW=2

ID=2222, site=1, occupied=0

ID=4444,site=2, occupied=1

ID=1234, site=1, kW=3
ID=5678, site=2, kW=2
ID=2222, site=1, occupied=0
ID=4444,site=2, occupied=1
ID=1234, site=1, kW=1
ID=5678, site=2, kW=2
ID=2222, site=1, occupied=1
ID=4444,site=2, occupied=1

For simplicity above we’ve shown devices all reporting simultaneously (in reality they won’t) and the site property being sent repeatedly with every message (in reality it may just get set once when the device is first configured).

We need to re-organise the streams to collect the energy and occupancy readings for each site into a single stream, so that we can compare that site’s energy and occupancy side-by-side:

merging the streams

In Excel terms we might describe this as “building a pivot-table by site”. In SQL terms we might describe this as “joining by site”. 

In this example site happens to be a static property of each device, so in principle we could select devices according to their site and then merge the streams from those devices forever more without any further reference to site. However in other examples here we’ll see streams that need to be merged by properties which change dynamically, and the solution we present here will work even in that case. For example, if our devices were mobile then their site could change from time to time, and this site-based query will still work.

Now we can do a query with (GROUP BY 1h, site) to get the total energy consumption and average occupancy of each site, in each hour. E.g. below we see that only one site is consuming energy on a Saturday:

$ts

site

occupancy

kWh

0h

Hitchin

0.8000

4.48

0h

Leeds

0.8300

4.99

0h

London

0.8311

5.04

0h

Cambridge

0.8321

5.05

0h

Southampton

0.4545

5.14

0h

Oxford

0.3684

5.28

0h

Birmingham

0.5123

4.67

1h

Hitchin

0.4285

4.19

1h

Leeds

0.3333

5.26

1h

London

0.3844

5.08

1h

Cambridge

0.2232

4.89

...

...

...

...

energy use

Calculating the final metric

Now we need to define a threshold above which the fractional occupancy must be in order for a building to be considered worth heating - then we can measure the energy consumed when occupancy is above and below that threshold. Having done that, we can then produce totals and ratios. 

This effectively adds two relational queries around our streaming query:

RELATIONAL: Work-out the percentages from...

RELATIONAL: energy-when-occupied & energy-when-not-occupied from...

STREAMING: the stream-merging query to calculate energy and occupancy

We discover that over the last week the Hitchin site is by far the biggest culprit, wasting 5% of its energy:

hitchin is culprit

Parsimonious though the Scots are reputed to be, you may find it suspicious that the Edinburgh site is reporting zero inefficiency. On closer inspection we discovered that this is because there is no smart meter fitted at the Edinburgh site.

Conclusion

Combining streaming and relational queries can enable IoT data to deliver energy savings in buildings. Next, read about using it to deliver service assurance for a device estate.

Request access to private alpha

 

Comments

See how DevicePilot can make the difference

 

Industry leaders trust DevicePilot to help them analyse and manage their devices at scale.
Book your personalised demo now and discover how DevicePilot can help you 

  • Get to insight faster 
  • Improve operational efficiencies 
  • Enable business users to find the answers they need 
  • Change behaviour using IoT
Erik in a circle-1

Erik Fairbairn, CEO at POD Point:
Achieved 99% uptime across device estate

"We're totally data driven at POD Point, and if we can answer a question using data then we think that’s the best way - there’s no guesswork and you can use the facts.

Our DevicePilot dashboards have really let us get that actionable insight out of our devices."