Smarter Metering for smart homes a real experience explained

1. Smarter Metering for smart homesa real experience explained Massimo Dore, IT Architect, IBM massimo.dore@it.ibm.com

2. 1 The challenge Buildings are responsible for a large fraction of the world�s total electrical consumption. More than 30% of all greenhouse gas emissions can be attributed to houses and buildings. Residents, usually, do not actively participate in the energy conservation initiatives. Energy awareness of residents, by means of timely electrical consumption feedback through smart metering, is believed to reduce significantly the waste of energy. According to a number of scientific studies, such as [1],timely electrical consumption feedback through smart metering is believed to reduce electrical consumption by a fraction of 5-15% Inhibitors: Residents do not possess the ability to automate their personal space and fully configure it to their own needs in order to save energy. Besides, every habitant has different behavior, specific preferences and varied habits

3. 2 The project The idea is to provide detailed, timely information of the environmental context of my home, such as temperature, light , together with real-time information of the consumption of electrical devices. The installation of solar cell photovoltaic panels to produce energy and the best use of this energy font compared to internal consumptions is also a target of measurement system. There are two existing options for household energy monitoring and control: whole-home and device-specific. Whole-home approaches place one smart meter where the home connects to the power grid. Google PowerMeter project uses energy information provided by utility smart meters and energy monitoring devices, to allow users to view their home�s energy consumption online. In [1], circuit-level power measurements are used to separate aggregated data into device-level estimates. Such approaches are mainly used for monitoring electrical data and not for directly interacting with the actual electrical appliances to control their operation.

4. 3 Metering Devices There is no a common standard for metering devices or data format, even for the metering devices used by energy providers. Zigbee and Bluetooth wireless technology enable an easy setup of distributed metering devices at home without the need of wired cabling. The smart metering devices selected for the project are Plogg, manifactured by the same name uk company (http://www.plogginternational.com). Ploggs have a high accuracy of electricity measurements,with an average error around 2%. Their main attribute is that they permit the control of a connected electrical appliance remotely, from anywhere in a building or over a network. They use a proprietary firmware based on the ZigBee protocol, forming a mesh network inside the smart home.

5. 4 Plogg protocol The Plogg interface uses a modem-like approach to query the measurements: Each command is in the form of ATxxx: Example: the command AT+SN queries the devices in the network. Each device answers with the string FFD: followed by the unique ID identifying the plogg device: AT+SN OK FFD:000D6F0000224328 FFD:000D6F0000262B19 AT+UCAST:000d6f0000224328=v +UCAST:00 OK ACK:00 UCAST:000D6F0000224328=~~SW Version 1.70~~Unit friendly name = Scaldabagno~~PCB Build R UCAST:000D6F0000224328=esistors DIO9=1 , DIO10=0 , DIO11=0 ~~Unit is Zigbee Enabled~~ UCAST:000D6F0000224328=- Unit is a configured as a router~~ - Current Pan = 31F4 unit UCAST:000D6F0000224328= is router~~Zigbee echo is on!!!~~TickCount = 9741087 ~~~~~~> While the hardware is excellent, only Microsoft Windows SDK is available to interface the device. To get the measure values it�s needed to parse the descriptive strings answered by the device. A java device driver and server has been developed to to it

6. 5 Metering architecture

7. 6 Architecture scheme

8. 7 Hardware � Use small, low power consumption devices The Plogg Linksys NSLU2 The NSLU2 has an Intel IXP420 as its processor (ARMv5TE architecture) @ 133 Mhz. The CPU is connected to 8MB of flash memory and 32MB of SDRAM on the PCB. SlugOS operating system (Linux kernel 2.6.27.8) 10W consumption For IDS Informix database tests I used a Zotac ION-ITX Intel Atom 230 CPU CPU Mini-itx motherboard 75 Gb Hard disk, 2 Gb RAM OS Microsoft Windows 7 IBM Informix developer edition IDS 10.70TC1CH Low power consumption (18W)

9. 8 Architecture software components IBM Really Small Message Broker: a very small footprint message broker by IBM implementing MQTT protocol. The RSMB decouples the metering application from the consumer applications. It runs on multiple OS (windows, unix, mac osx, SlugOS ) and in small appliance as Linksys NSLU2 Metering application: a custom java server application. Establish a zigbee mesh network, discover devices, poll every 5 minute in round robin all discovered device to get measures, push all devices measurements in one message to mq using a JSON format Real time display of consumptions - Java custom application: Read data from mq, calculates the current and daily accumulated kwh consumptions for each device, produces the graphs for web interface (using google chart api) History data application Use an Informix IDS 11.7 database to produce static graphical aggregations of consumptions (monthly, yearly) using JScharts tool

10. 9

11. 10 Informix TimeSeries Knocks Out The Competition Informix took ~ 18 minutes to load data for 1 million meters; The competitor took ~ 7 hours Informix took ~ 25 seconds (cached) to 6 minutes to run each utility commission report; The competitor took from 2 - 7 hours depending on the report Data space used by Informix is ~ 350GB; The competitor uses about 1.3TB Informix performance and storage comparison is linear with more meter data POC Benchmark Details: Data 90 days worth of meter data for 1 million meters 200 locations 500 feeders 34 substations Hardware used in POC Power7 with 2 sockets each with 8 cores 64 bit SUSE Linux 11 128 GB of memory Memory actually needed = 44GB, although could probably be less 6 disks dedicated to the database, 2 additional for OS and LSE staging Disk space actually used by the database = about 350GB Additional disks for the operating system and staging area for files Software Informix Ultimate edition Informix Time SeriesPOC Benchmark Details: Data 90 days worth of meter data for 1 million meters 200 locations 500 feeders 34 substations Hardware used in POC Power7 with 2 sockets each with 8 cores 64 bit SUSE Linux 11 128 GB of memory Memory actually needed = 44GB, although could probably be less 6 disks dedicated to the database, 2 additional for OS and LSE staging Disk space actually used by the database = about 350GB Additional disks for the operating system and staging area for files Software Informix Ultimate edition Informix Time Series

12. 11 Selling Informix TimeSeries for Smart Meters Informix Performance Delivers 23 times improvement over competitive DB Reports produced in seconds instead of hours Informix Space Savings Up to 75% space savings over standard relational layout Informix Usability Time series tool kit allows custom analytics to be written Handles operations hard or impossible to do in standard SQL No other RDBMS has native time series support

13. 12 Example of Systems Requirements that are Changing Utility Meter Transaction Background We�re working with Utility companies worldwide; Chart shows just the explosive growth expected in the number of transactions as today�s analog meters are replaced with smart meters. Consider a utility in California with roughly 10 million gas and electric meters, reads them once a month (120M transactions a year �). And looks at them as a group once. But if they could read these same 10M meters once a day � which is what they�re shooting for now � that�s 3.65B transactions a year -- generating more than 1 petabyte of data (equivalent to more than 500 billion pages of text) The goal: read meters every 15 minutes (or 96 times a day). It�s 960M transactions a day, 29B a month and nearly 350B a year. Now that's just one utility. We�re working toward benchmarks of 50M meters. Read every 15 minutes, and the transactions? 4.8B a day, 1.75T a year. As the planet becomes smarter � like you see here with the explosive growth of smart meters -- the number of transactions will continue to increase driving the need for more real time analytics and prediction requirements. Because transformation isn�t just being able to read meters every 15 minutes. The real breakthrough is being able to act on the data in real time; that�s the transformational capability. We now have the processing power and advanced analytics to make sense of it all. All of this to illustrate that smarter planet increases the scale and complexity of workloads. Utility Meter Transaction Background We�re working with Utility companies worldwide; Chart shows just the explosive growth expected in the number of transactions as today�s analog meters are replaced with smart meters. Consider a utility in California with roughly 10 million gas and electric meters, reads them once a month (120M transactions a year �). And looks at them as a group once. But if they could read these same 10M meters once a day � which is what they�re shooting for now � that�s 3.65B transactions a year -- generating more than 1 petabyte of data (equivalent to more than 500 billion pages of text) The goal: read meters every 15 minutes (or 96 times a day). It�s 960M transactions a day, 29B a month and nearly 350B a year. Now that's just one utility. We�re working toward benchmarks of 50M meters. Read every 15 minutes, and the transactions? 4.8B a day, 1.75T a year. As the planet becomes smarter � like you see here with the explosive growth of smart meters -- the number of transactions will continue to increase driving the need for more real time analytics and prediction requirements. Because transformation isn�t just being able to read meters every 15 minutes. The real breakthrough is being able to act on the data in real time; that�s the transformational capability. We now have the processing power and advanced analytics to make sense of it all. All of this to illustrate that smarter planet increases the scale and complexity of workloads.

14. 13 Traditional approach � using relational table schema About two years of measures for 5 devices. Sampling interval: 5 minutes Table details : consumi Loading samples from external file using external table feature Generated daily aggregated tables for kwh & device

15. 14 Using embedded Informix XML functions to generate graph JSGraph is a javascript-based tool to generate graphic charts. The data and attributes for the charts can be stored in a xml file Using Informix xml internal functions it�s easy to generate the graphic chart The xml files generated for all data series are statically stored in web server hosted by NSLU2 appliance

16. 15 Code to generate Graphic Charts CREATE function GenXmlYear (p_anno INT, tipo INT) RETURNING lvarchar(32732); DEFINE xmlretstr LVARCHAR(32732); DEFINE vXml LVARCHAR; DEFINE vanno, vmese INT; DEFINE descr_mese varchar(20); DEFINE descr_device varchar(20); LET xmlretstr = "<?xml version='1.0' ?><JSChart>"; LET vanno = p_anno; IF tipo = 1 THEN LET descr_device = "Generale"; LET vXml = (select genxml(row(unit , value, id),"data") from (select testo, sum(generale),id from t2, mesi_txt where anno=vAnno and id = month(tempo) group by 1,3 order by 3) as VT(unit,value,id)); END IF IF tipo = 2 THEN LET descr_device = "Scaldabagno"; LET vXml = (select genxml(row(unit , value, id),"data") from (select testo, sum(scaldabagno),id from t2, mesi_txt where anno=vAnno and id = month(tempo) group by 1,3 order by 3) as VT(unit,value,id)); END IF ........ return xmlretstr; END FUNCTION; Unload to �Anno2009.xml� select GenXmlYear(2009, 1) from mesi_txt;

17. 16 Using Informix TimeSeries extension Time Series is a set of data recorded as it varies over time. Although relational database management systems can store time series for standard types by storing one row per time stamped data entry, performance is poor and storage is inefficient. With the TimeSeries extension, the database management system is extended to �understand� time series data as a first-class type in a database. The time series entries are objects that the database can manipulate, rather than opaque large objects. The TimeSeries extension provides: High-performance storage and access architecture, including containers to hold time series data outside the database table A rich set of time series analysis routines Full support for time series data as an object data type, using the TimeSeries data type

18. 17 Steps to Create a time series supporting metering Define a Calendar Create a time series column Define a time series container Create and populate a time series Load data into time series

19. 18 Setup the Calendar Every time series is associated with a calendar. A calendar defines a set of valid times at which the time series can record data; it determines when and how often entries are accepted. For regular time series, calendars are used to calculate the mapping between offset and time stamp. Irregular time series do not have offsets, but they still use calendars to define valid entry times. Define a Calendar pattern named week_days (every day is ok for us): insert into CalendarPatterns values ( 'week_days', '{ 7 on }, day'); Define a Calendar with given pattern insert into CalendarTable(c_name, c_calendar) values ('energy_measured_cal','startdate(2009-01-01 00:00:00.00000), pattstart(2009-01-01 00:00:00.00000), pattname(week_days)');

20. 19 Create a time series column To create a column of type TimeSeries, you must first create a row subtype to represent the data held in each element of the time series. create row type plogg_daily ( timestamp datetime year to fraction(5), kwh decimal(4,2)) Now we are ready to define the table: create table daily_series ( plogg_name varchar(15), daily_kwh TimeSeries(plogg_daily) )

21. 20 Define a Container Time series data can grow too large to fit into the row of a table, currently limited to approximately 2048 bytes. When this happens, the database server moves the data portion of the time series into a container. A container is a structure to hold time series data. Different time series can use the same container if they are all regular or all irregular time series. execute procedure TSContainerCreate('plogg_cont', 'datadbs','plogg_daily', 0, 0);

22. 21 Create & Load Time series Prepare data for data loading unload to bulkld.txt select "row(" || tempo::datetime year to fraction(5) ||", "|| generale || ")" from t2 order by tempo (remove the �|� character at the end of each line !) Create an empty time series: insert into daily_series values('General', TSCreate('energy_measured_cal', '2009-01-01 00:00:00.00000',20,0,0, 'plogg_cont')); Load data into time series table update daily_series set daily_kwh=BulkLoad(daily_kwh, 'bulkld.txt') where plogg_name ='General';

23. 22 Creating a Virtual table Interface to time series table The virtual table interface takes the data encapsulated in the TimeSeries data type and produces a virtual relational table containing the same data. execute procedure TSCreateVirtualTab('daily_virtual_series', 'daily_series');

24. 23 Irregular Series Vs Regular Series An irregular time series stores data for a sequence of arbitrary timepoints. Measures from plogg devices are not equally time spaced. Use Irregular time series. insert into CalendarPatterns values ( 'days_minute', '{ 1 on }, minute'); insert into CalendarTable(c_name, c_calendar) values ('energy_min_cal','startdate(2008-01-01 00:00:00.00000), pattstart(2008-01-01 00:00:00.00000),pattname(days_minute)'); Create row type plogg_minute ( timestamp datetime year to fraction(5), kwh decimal(7,2) ); Create table kw_x_minute ( plogg_name varchar(15), daily_kwh TimeSeries(plogg_minute) ); execute procedure TSContainerCreate('plogg_cont_irr', 'datadbs','plogg_minute', 0, 0); insert into kw_x_minute values( 'fotov', TSCreateIrr('energy_min_cal', '2008-01-01 00:00:00.00000',20,0,0, 'plogg_cont_irr')); William Somerset Maugham - �Perfection - Nothing more than a complete adaptation to the environment; but the environment is constantly changing, so perfection can never be more than transitory.�

25. 24 Create regular series from irregular Example: Changing the sampling from minutes to 1 hour insert into CalendarPatterns values ( 'Hourly', '{ 24 on }, hour'); insert into CalendarTable(c_name, c_calendar) values ('energy_hourly','startdate(2009-03-19 00:00:00.00000), pattstart(2009-03-19 00:00:00.00000), pattname(Hourly)'); Create table kw_hourly ( plogg_name varchar(15), daily_kwh TimeSeries(plogg_minute) ); insert into kw_hourly values( 'General', TSCreate('energy_hourly', '2009-03-19 00:00:00.00000',20,0,0, 'plogg_cont')); update kw_hourly set daily_kwh = ( select SetContainerName(AggregateBy('avg($kwh)', 'energy_hourly', daily_kwh, 1), 'plogg_cont') from kw_hourly where plogg_name = �General') where plogg_name = �General'; execute procedure TSCreateVirtualTab('daily_virtual_hour', 'kw_hourly'); -- When the solar energy production is greater then the consumptions ? select d1.kwh,d2.kwh,d1.timestamp from daily_virtual_hour d1, daily_virtual_hour d2 where d1.plogg_name = 'fotov' and d2.plogg_name = 'General' and d1.timestamp = d2.timestamp and d1.kwh >= d2.kwh and d2.kwh > 50 order by 3

26. 25 Some statistics The Timeseries test has been done using more than 300.000 time samples The loading time has been about 6 secs (no optimization has been performed except the sizing of dbspaces needed to host logical log) for all data (unique transaction) All the queries under one second execution time. Informix could be embedded in very small devices: example the pico-itx system Artigo with 1 Gb of ram , SSD disk, Linux or Windows OS. Highlight consumptions outside the norm: Refrigerator was energy-hungry

27. 26 Abnormal consumption detection: reduced by 50% with a new A++ class refrigerator

28. 27

29. 28