In spite of the huge amount of energy information that is shared by cross-functional areas of companies, they aren't taking better decisions towards energy saving. Based on the existing literature, energy management systems and data warehouse architectures for energy management, a research model identi es several problems that a ect intelligent building data integration. The aim of this article is to point several complexity factors that a ect energy and building data integration and present a solution that could ease the integration process and applied in di erent environments to conform with di erent business requirements. To achieve it, is de ned a generic prototype, which can help to de ne Extract-Transform-Load processes in building and energy management contexts. The prototype is intended to ease the process of extracting and transforming building and energy data by integrating speci c ETL modules to an existent ETL tool.
Business data analytics is becoming a key tool for managing nearly any kind of
business enabling for instance analysing customer pro tability, asset
optimization and operation analysis to identify cost-reduction opportunities [
Data integration assumes an important role in business data analytics
because it is responsible for combining multiple and heterogeneous sources of data
(which may reside in di erent locations) and store them under a global schema,
giving a uni ed view over that data. The main goal of this process is to help
extracting knowledge that is scattered in di erent data sources [
Given the exponential growth of Intelligent Building we think that business analytics should be applied to building management as well. Therefore, building data should also be integrated, to ease the process of decision making and identify optimization opportunities among which, energy saving is chief. To achieve that, building data must be collected and consolidated, analysed and aggregated in proper formats (such as reports) allowing the drilling and mining of building data. Combining data in this way is crucial for example to understand the energetic behaviour of a building and to clarify the relation of each device and appliance to energy consumption.
This paper addresses the urgent need of integrating Intelligent Building data to identify possible energy savings and improvements in the energetic behaviour of a building. Monitoring and analysis of building performance is a key mechanism to pro le consumption patterns, to detect abnormal energy use and to reduce energy consumption. This document is organized into ve sections. After the introductory section, Section 2 explores the di erent types of Intelligent Building data and the underlying complexity of integration this type of data. Section 3 details the Extract, Transform and Load (ETL) process which is responsible for integrating data in a global uni ed schema and also the pros and cons of applying ETL methodologies in a energy and building domain. Section 4 presents a prototype that integrates an ETL tool with protocols and standards of Intelligent Buildings and the advantages of this synergy, Section 5 presents some conclusions about this work. 2
The concept of Intelligent Buildings (IB) is related with the usage of Information
Technology in building operations to face the progressive demand of comfort
environment, the requirements for occupant control of the environment and the
reduction of energy usage [
An IB encompasses several site-speci c systems that control well de ned
areas and aspects of a building. An Energy Management Systems (EMS) aims at
identifying energy-savings opportunities through continuous monitoring of
energy consumption and equipments. Building Management System (BMS) which
has to control and monitor mechanical and electrical equipments of a building [
{ Building Structure, refers to the type of building, the internal infrastructure and the physical layout details. To understand the energy consumption in the building we have to break up di erent areas physically (i.e., space breakdown and functionality) to pro le the energy for each location. External Sources
Type of information Energy Management System Continuous monitoring of energy EMS speci c soft(EMS) consumption. ware Automated Meter Reading Read, tranport and store meter en- AMR, AMI speci c (AMR), Advanced Metering ergy data. software Infrastructure (AMI) Building Management Systems Control and monitor building Standard Protocol (BMS), Building Automation equipments(e.g. HVAC, Lighting (e.g. BacNet, LonSystem (BAS) System, Fire protection system). Works). Proprietary Software (e.g. TAC Vista, Metasis).
Computer-aided Facility Man- Supports operational management CAFM speci c softagement (CAFM) and activities related with Facility ware Management (FM). Provides functional space information.
Building Information Model Provides information about build- gbXML, ifcXML (BIM) ing envelope.
Organizational Information Information about the organization Database Manand the way it is structured to per- agement System form the core activities. (DBMS) Energy Pricing Data Information about tari s from dif- Paper form (nonferent energy service providers. structured data) Billing Information Keep record of information about Paper form, Billing energy expenditures (i.e. energy Information System cost, taxes, billing date) (BIS) Weather Data Provides information about cli- Provided by weather matic conditions in the facilities stations connect to and surroundings. the BMS or from external sources.
{ Operational Data, refers to data that has a direct relation with the business process, with the space, the occupant and how they perform their core activities. This is important so that energy use can be traced to activities. { Commissioning data provides details about the operation of IB data systems, showing temperature, pressure levels and setpoints, helping to determine the cause o peak-demands and abnormal situations. { Sensor data captures environmental and occupancy data related with the information that can be measured using sensors (luminance sensors, occupancy sensors). { Equipment status data is essential to fully understand the energetic behaviour of a building. Equipments may be grouped as a stand-alone device or operate as a system (e.g. HVAC). Each device as a speci c electric-load, an associate activity (e.g. air conditioning, lighting) and a working period.
Accordingly IB data integration is an activity that gives support to Energy Management (EM) and Facility Management (FM). A building should be understood as a whole system and the interaction of each system to energy consumption and building maintenance must be de ned. Then, building data must be correlated, aggregated according to di erent dimensions and hierarchies in order to process and analyse information. 2.2
There are intricate factors that a ect the integration of IB data making it harder to provide an homogeneous and uni ed view. The data integration in this context is hindered mainly due to: { Heterogeneity of data sources. As explained before, the information required in IB comes from di erent sources. The buildings, devices, occupants, external factors such as weather conditions have to be brought together. This variety of sources requires the communication with several external sources in order to extract useful knowledge. IB data sources present data in di erent data structures (structured, semi-structured, non structured), with di erent sampling periods (data that is constantly being updated such as energy information provided by meters and data that is most likely not to change like building information). { Data storage. IB data is stored in databases of proprietary systems known as \informational silos", which makes it harder to extract and access information. { The amount of data produced by external sources systems is often very large, since energy meters are constantly performing readings of energy consumption. { Mapping problems. Due to the heterogeneity of data sources more e orts are need for schema matching (to identify that di erent schemas share similar semantics) and for schema mapping (performing transformations to integrate di erent schemas). { Data Quality. In the IB context the quality of the information provided relies heavily in the accuracy of systems and devices (e.g. accuracy of data acquisition meters, sensors) and also to his fault tolerance capacity (e.g. communication losses with meters causes the storage of incorrect energy values). { Large Data Models. The source data models are frequently very large due to the number of entities and instances requiring an additional e ort to integrate these sources. Moreover, the documentation is often poor or absent. { Organization-dependent data. In this context some information is hard to infer because is not explicitly stored in any system. This knowledge is stored and shared by the people that compose that organization and is not stored in any physical format.
Nevertheless, collecting, archiving and analysing energy and building data
requires signi cant computational resources with the ability of processing and
analysis, making this a costly and cumbersome task and usually the information
provided to the end-user is very di cult to interpret [
Building and energy data must be stored and integrated with a global uni ed
schema enabling an easy access to information to speci c users. This data can
be integrated into a Data Warehouse (DW) which is a repository of
information collected from multiple sources and integrated [
Nevertheless, ETL is more than loading data from the operational source systems to the data presentation area. ETL processes are responsible for enforcing data quality and consistency, conforming and for mapping data from di erent data sources. 3.1
To design ETL processes it is important to de ne the system requirements, to
identify the data sources and the DW schema and determine the set of
transformations that are needed for the project. The complexity factors of developing
ETL process for building operations are due to di cult access to data sources,
data source characteristics and lack of a reference model. Di cult access to data
sources is related with the lack of drivers for IB protocols and with the existence
of undocumented energy data models. Heterogeneous data sources have di
erent characteristics, there are real-time data sources (that require real-time ETL)
and data quality issues due to intermittent data sources (meters and equipments
that disconnect). The lack of a reference integrated model, means that data has
to be integrated against a model that no one knows how to build it (no such
model has been proposed until now). Moreover, autonomous and heterogeneous
data should be integrated in an uniform way and consolidated through the data
cleaning activity to assure data quality. Poor data quality as a strong impact in
enterprise strategies, because the foundations of his success and of the process
of decision-making relies heavily on this data [
The ETL activity is crucial for designing a DW for Energy Management. This activity replaces the manual task of analysing and correlating energy data, which is very di cult and error prone since the amount of information is large and heterogeneous. 3.2
There are several reasons for using an ETL tool to integrate heterogeneous energy data sources.
Data Source abstraction, eases the process of creating an ETL transformation because data can be handled and accessed in a uniform way. There are several factors that depict the advantages of using an ETL tool to e ectively integrate energy data: { Logic access to data is location-independent and implementation independent. The abstraction level hides the complexity of extracting data and the location of the data source, the developer just needs to focus in the transformation process. { Since the architecture is source-independent, raw data can be handled in a generic way because it is independent of the extraction process.
Provides a uniform access to data sources in a common representation. { Connectivity, provides connection with a wide range of source systems (from relational databases, XML les among other formats). This feature is important because it transforms heterogeneous data into primary data, which is easier to manipulate.
Declarative transformations, with this paradigm it is only required to express the logic of the transformation without describing the entire execution ow.
{ Algorithms are chosen based in the context conditions that can be modi ed, allowing the solution to evolve to face new demands. { The strategy to implement a transformation is de ned through the speci c context. For each transformation it is necessary to evaluate the best algorithm to face the context characteristics. { Improve scalability, it is possible to use computing techniques to improve the transformation process, for instance using parallelism, partitioning or clustering.
Re-usability, since ETL process can be disaggregated into loosely-coupled components it is possible to modify and reuse ETL components to t in new solutions.
{ Domain speci city is low, which means that ETL transformations can evolve to solve other problems in di erent domains. Eases the process of reproducing a new ETL solution. { Modularity, allows the separation and recombination of ETL components. Reduces complexity and increases the exibility of creating an ETL transformation. { Extensibility, allows the extension and creation of new functionalities.
Since ETL is a wide explored area and several ETL tools are opensource solutions it is natural to take into consideration future growth. Explicit knowledge, fruitful information about ETL process is easily stored and transmitted. It is focused on the \essential" data.
{ Easy to understand and control ETL transformations, the user doesn't need to concern with implementation tasks. { Business-oriented, it is possible to evaluate only the business logic, to identify transformation rules and constraints, determine the execution ow and necessary steps to complete a transformation. 4
To validate our ideas we implemented an ETL data ow using an open-source ETL tool (Pentaho Data Integration - Kettle1) to create a set of extra-features
that allows the communication with standards and protocols of intelligent buildings. Keetle consists of an ETL engine and GUI applications that allows the developer to de ne data integration process using jobs and transformations.
To extract data from building management and energy management systems
there are two possible solutions: (1) build a speci c data extraction software
that must interact with each data source, (2) create data source adapters for
an ETL tool that share some features (such as similar API, similar metadata
format, among others) allowing the developer to focus only in the extraction
process. The rst option present several drawbacks, which makes this solutions
infeasible to integrate building data. With speci c extraction software there is
no abstraction level between the transformation and the data source in that way
the extraction driver must be embedded in the software, on the other hand the
missing extensibility a ects the exibility since is not possible to add or modify
a component to address a speci c business need [
We propose to implement a module with drivers for extracting data from
energy and building data sources. The prototype already has a connection with
an Automated Meter Reading (AMR) system which is responsible for collecting,
transporting and storing energy meter data from di erent types of meters
(electricity, gas, heat) and with building control standards (e.g. KNX2) to verify the
status of sensors and actuators to evaluate the operation of building
management equipments, such as lighting, shutters, HVAC systems [
By integrating this components of building management we are able to directly extracted data from devices, sensors, meters and actuators located in any point of the building. This direct extraction decreases latency time, since energy and building data can be faster modi ed and loaded to the nal schema and increases the exibility because data can be cleaned and conformed using the available steps of the Kettle framework. 5
Energy management is a fast growing area along with smart grids, smart metering infrastructures and intelligent buildings. Energy managers and building owners have realized the potential of this area but they are not totally aware of the fact they are not taking the most of these systems: they have a lot of information but little knowledge about their energy consumption. Thus, integration of energy data is of utmost importance.
Nowadays data warehousing and ETL are very popular tools in business analytics to e ectively integrate data residing in di erent data sources. However these tools have not yet been adopted to energy management and more speci cally to integrate energy-related data.
The aim of this article has been to discuss the superiority of using ETL tools to integrate energy data. We presented a prototype ETL implementation that extracts data directly from building management and energy management systems. The solution is highly modular and isolates the data transformation logic from the process of communicating with meters, sensors and actuators since it reduces the number of steps in the communication and allows the interaction of other steps in the extracted data because the steps are loosely-coupled.
The advantages of this solutions compared to build up a speci c software from scratch are: exibility to create di erent steps based in minor modi cations with minor e ort, re-usability to reuse existing components to create new components and connectivity to interact with a multiplicity of source systems. We expect the results of this work to contribute to streamline the engineering practice concerning data warehouse projects in energy contexts and integrating energyrelated data sources.