by Ross Sharman and Juliana Button
Part of the Oracle Fusion Middleware Patterns series.
Published August 2009
To establish competitive differentiation, organizations across the globe face similar issues - the need to simultaneously address situational questions ("What"), and forward-looking questions ("How"), as part of their every day organizational reporting and planning needs. Business Intelligence (BI)solutions can help with "What" analysis to provide past and present views of data and transactional reporting, whereas multi-dimensional Online Analytical Processing (OLAP) tools excel at "How" analysis, providing modeling and forecasting capabilities to determine how particular behavior will impact results. In the past, distinct BI solutions have addressed what really should be seen as a seamless BI continuum, as illustrated in Figure 1.
|Figure 1: Seamless BI Continuum|
However, this transformation process can only happen if users can access data in the correct manner and format. Organizations in search of fast and flexible data analysis ultimately need a unified platform to do both. With this unified platform in mind, there are three key principles we believe are relevant for any BI integration project that seeks to leverage the benefits of a combined relational and OLAP foundation.
We'll discuss each of these in greater detail elsewhere in this article.
Through the use of a real-world example, this article highlights a compelling business need to integrate relational and multi-dimensional data. We will discuss how Australia-based Knowledge Global has used the combined power of BI and OLAP to build a carbon/energy monitoring and measurement application.
Organizations can achieve significant, powerful insights and improved decision making by combining What and How analysis. The architecture to support this integration requires three important technologies (see Figure 2):
|Figure 2: BI, ETL and OLAP coming together to deliver integration solution|
Let's take a look at key architecture best practices to build such a solution.
Any BI integration project is all about the data. With the rise of sensors and event-driven technology, businesses are generating and collecting significantly more data than ever before. So, the first challenge is to get all of the data into a manageable source repository, where it can be accessed, reported on, analyzed and distributed.
Multiple BI repositories come into play while dealing with different data sources:
Data in these repositories could come from a variety of different data sources. An ETL tool helps load all different repositories using data-based, event-based or services-based integration. An integrated BI and OLAP environment can return strong dividends. A unified platform can enable Report to Source Lineage, that is, the ability to easily drill back from dashboard data to details about where the data came from and which transformations were applied.
BI and OLAP integration provides the ability to answer questions such as, "What is customer X's lifetime value for my enterprise." To accurately answer such a question, it is important to have one enterprise definition for 'lifetime value of a customer' across BI, OLAP, and other elements of the solution. A common enterprise information model is necessary to derive consistency, security, reuse and flexibility, across an integrated BI platform.
With an integrated BI platform an end user could be looking at OLAP information, do a single click, and quickly navigate into the relational world. In this scenario, the end user is completely oblivious to the source of the information. The unified metadata model needs to serve all end user tools, so every end user and every department has the same consistent view of information, tailored to each role. The model must account for all types of data sources that are assets of the enterprise, whether they are stored in relational or non-relational form. The model must also preserve the expressiveness of the data source.
Figure 3: Common Enterprise Information Model to drive consistency and reusability
How do you combine a sales revenue report (a "what" analysis) with a trend analysis showing how to improve sales in a particular region (a "how" analysis)? A flexible reporting and analysis framework is needed to address the diverse needs of different users across the BI continuum, from providing past historical views and present/current transaction and operational reporting to future-looking, predictive modelling and analysis. A reporting platform should support Microsoft Office and Outlook Integration so that modelling and forecasting can easily leverage this data. Support for reporting standards such as XBRL (a standards-based way to communicate business and financial information) can provide much needed flexibility.
Let's see how Knowledge Global used the power of OLAP, BI, and ETL, and applied the principles outlined above to build a carbon/energy management solution using Oracle Business Intelligence Enterprise Edition (OBIEE) and Oracle Essbase.
With a growing global groundswell in the carbon/energy economy, Knowledge Global, a specialized consulting organization, wanted to develop an application that can help organizations measure, monitor, forecast and reduce their carbon footprint. This application would help organizations answer "What" questions about past and present results including:
Also, in order to deliver a complete emissions solution, the application needed to address forward-looking "How" questions like:
The solution required a combination of ETL, BI, and OLAP technologies to accurately report on operational data and provide modeling and forecasting capabilities.
Knowledge Global chose Oracle BIEE, Oracle Hyperion Essbase and Oracle Data Integrator to build the Environmental Management and Measurement Application (EMMA).
Figure 4: EMMA Architecture
Let's quickly review three important aspects of this architecture.
The solution deals with a multitude of data formats. Data comes from smart meters for electricity usage, the Bureau of Meteorology for weather details, the Building Security System for personnel traffic, and various other systems.
This data flow ranges from the most granular operational data store level (measuring isolated devices and events in seconds), to the data mart star schema structure (measuring rooms and energy circuits, at a minimum of minutes but typically hours) and finally to Essbase OLAP (where we measure at buildings, demographic groups, and months).
The EMMA environment uses Oracle Data Integrator (ODI) to capture data from these different energy data sources and load data into appropriate BI stores, including the EMMA operational store, the star schema data mart, and the Essbase OLAP repository.
Figure 5: ODI is used to populate operational, BI, and Essbase data stores
ODI uses Web services for collecting data from internal data sources such as Smart meter data, as well as external data sources, such as weather and forecast data. For example, the weather data and forecasts are obtained by a custom Web service, which is wrapped around public websites. The output is XML data, which, using ODI, can be populated directly into the EMMA repository. On the other hand, integration with the Building Security System is file-oriented. The Building Security System exports data into comma-delimited files, which are mailed and copied to a processing directory. ODI processes files in this directory and loads them into the operational data store.
The data from all three of our data sources (EMMA OLTP, EMMA Data Mart, EMMA Essbase OLAP model) is exposed through Oracle BI Enterprise Edition interface.
The BI Repository stores the business intelligence metadata that is rendered inside the BIEE reports, Interactive Dashboard clients, and other clients. How is data represented in uniform fashion without any dependency on underlying data sources or presentation clients? Flexibility comes from separation between presentation, logical, and physical data. The BI Repository defines three layers of abstraction: Physical, Business Model and Mapping, and Presentation. The Physical layer contains information about the physical data sources to which BI Server submits queries. This physical layer gives us the ability to query analytical data coming from the BI Data Mart and multidimensional OLAP data coming from the Essbase cubes. The Business Model Layer, in turn, brings together data elements from the BI Data Mart and Essbase into a logical Star-Schema representation, creating uniformity in common enterprise definitions.
To integrate the Essbase OLAP data, we imported Essbase cubes into the Physical layer of the BI Repository using the BI Administration tool. The Essbase cube corresponds directly to our business model, so creating the Business Model and Mapping layer was easy because the metadata was implied by the structure of the Essbase cube.
Figure 6: Essbase Cubes are imported in the BI Repository using the BI Admin Tool
Full step-by-step instructions for this are provided in the tutorial, Integrating Oracle Essbase with Oracle Business Intelligence Suite Enterprise Edition.
With this seamlessly integrated architecture, the highly consolidated data in the Essbase OLAP data sources can be drilled down into to provide lower levels of granularity as stored in the data marts and even down to the EMMA operational data store.
Dashboards are used to align figures from all three of our data repositories - planning (targets and budgets), data marts for business intelligence and real time for alerting, and business rules against operational data.
Because carbon has become a financial entity, there will be the requirements to submit and receive financial information (budgeted emissions and steps to mitigate will be included). With the seamless integration between Essbase and BIEE, this type of information can come straight from the planning cube via the BI Server. The BI Server has interfaces through Web services and BI Publisher to fit any (XML-based) reporting methodology, which fits the XBRL reporting structure.
Now that we have seen the EMMA architecture, let's walk through a specific example in which the EMMA application has been put into practice. This will help you visualize how data varies across three different EMMA data stores and what the final dashboard looks like.
The example is based on a large University that occupies in excess of 400 large multi-story buildings. One CBD building has been selected for this example and a number of floors are examined in detail. Within this building, two very different groups — members of the student computer laboratory and university administration staff — operate on two floors.
We grow our understanding of the two occupant groups by measuring the consumption of energy (95% of which is electricity) on each of these two floors using two electrical sub-meters. For example, we can measure consumption against the security system to find out when people are on these floors, andor we can measure the consumption against student and staff timetabled space occupancies. In this manner we can slowly build profiles of energy consumption for occupant group traffic for various floors of the building. These behavioral profiles then provide the basis for new energy efficiency initiatives to reduce consumption. These initiatives can be profiled using the scenario capabilities in Essbase to determine their effectiveness.
In general, the majority of electricity consumption on each floor across the entire building is created from air conditioning, ventilation, lighting and personal computers. The lighting on each floor is manually switched on or off by people as they enter or exit the floor. The air conditioning, however, is automatically triggered when people enter the floor. Therefore, one person can incur a cost of tens of dollars by working alone. This illustrates where accountability and behavioral changes can create efficiencies.
In the example, we see that the students operate at very different hours as compared to the administration staff, often working through the night. However, the fact that one or two students choose to work in the early morning makes this a very inefficient use of energy per person. An education program to inform the students why this use of energy is inefficient is introduced, resulting in increasing awareness and a greater degree of accountability. This education initiative will be backed up with a security initiative to close the labs from 10:00 pm to 8:00 am during the week. All students have laptops, which means other working options are available. Students are also a demographic group that is keen to be a part of efforts to reduce energy consumption. The financial savings of this initiative can be modeled using the OLAP database.
Step 1: Data from Meter and Security Systems
The Meter Data shown below illustrates the data coming in from the smart meters that , monitor electricity usage for the whole floor (i.e. lighting, air conditioning, and circuits). This information is logged at 15-minute intervals. There is also an hourly feed from the security system, which logs people coming and going, so we can derive an average number of occupants for each hour.
Figure 8: Meter Data logged at 15-minute intervals
|Building||University Building, Campus A||University Building, Campus A||University Building, Campus A||University Building, Campus A||University Building, Campus A||University Building, Campus A||University Building, Campus A|
Figure 9: Hourly Feeds from Security System Data show no occupancy from 3am-7am
Step 2: EMMA OLTP Data Structure
By consolidating and combining the raw meter data with the raw security system data, the EMMA OLTP system provides a real-time view of how electricity consumption is dictated by personnel operations.
Figure 10: EMMA Operational Data Store Structure
Basic OLTP design does not allow for time series analysis of large sets of data. However, utilizing this data source is important for verifying information at the most granular level. You can roll up from this level to the Data Mart.
|Building Operator||University Property Services||University Property Services||University Property Services||University Property Services||University Property Services||University Property Services||University Property Services|
|Location||Floor 12, University Buliding, Campus A||Floor 12, University Buliding, Campus A||Floor 12, University Buliding, Campus A||Floor 12, University Buliding, Campus A||Floor 12, University Buliding, Campus A||Floor 12, University Buliding, Campus A||Floor 12, University Buliding, Campus A|
|Floor Tenant||Student (School of Economics)||Student (School of Economics)||Student (School of Economics)||Student (School of Economics)||Student (School of Economics)||Student (School of Economics)||Student (School of Economics)|
|Electricity Usage (kwh)||17||12||12s||12||14||18||22|
Figure 11: Security and Smart Meter data is combined in EMMA Operational Data Store
Step 3: EMMA Data Mart Structure
The EMMA OLTP data is then consolidated into the data marts where trends and benchmarks can be derived. Here, we can profile the energy usage per person type for a particular building floor. The benchmarks can be modelled over many floors and buildings to help model the complete picture of the university energy consumption and identify efficiency opportunities.
Figure 12: EMMA BI Data Mart Structure
The Data Mart consolidates OLTP data into a format where measurements can be analyzed by different dimensions at different levels. This also allows time series analysis - i.e. trending to be performed. From the dashboards you can to drill down from this level to the OLTP source for the originating values or roll up to the OLAP level to allow broader (and faster) analysis and forecasting.
Figure 13: EMMA BI Data Mart allows analysis at different dimensions
Step 4: EMMA OLAP Data Structure The OLAP cube consolidates data mart into a MOLAP model that allows quick ad-hoc analysis, high-level trend analysis and forecasting. From the dashboards you can drill down on consolidated figures for more detail in the data mart or even OLTP levels.
Figure 14: OLAP Model to scenario based modeling and multi dimensional analysis
We can then use these benchmarks for projections using the EMMA OLAP Model. Here we profile the typical usage of the two groups during a working day and then observe the savings if forced closure of the student lab occurs from 22:00 to 08:00. The students can be educated with this reasoning by demonstrating to them the cost per person of a few night owls working all hours (in red, below). When accumulated across all student labs across the university over a year, the savings can be very worthwhile.
Figure 15: Consolidated Data indicating energy cost/person is very high from 22:00 to 8:00.
Shutting the lab during those hours will save $17.25 per day per lab
Step 5: Consolidated Dashboards
In this simple example you can see the power of combining the data at different roll-up levelsand applying predictive modeling and forecasting, and how this can aid planning and budgeting to ensure the most efficient energy usage. Of course, this data is best displayed visually, as show below on the OBIEE dashboards.
Figure 16: Example of OLTP and Data Mart data sources working together
Figure 17: Example of OLAP output
Figure 17 (above) demonstrates the modeling example in the OLAP data set. We can show why (areas marked in red) and how the savings will be achieved. This data can be used to show any budgeted costs associated with this initiative and also the total expected savings. Of course the real benefit of an OLAP model is the way such small budgets can be rolled up to a larger projected budget over the whole University.
This article has illustrated how the EMMA application can be put into practice at a university to accurately report, monitor and mitigate carbon exposure. From a development perspective, the EMMA roadmap includes opportunities for:
As organizations try to synthesize the vast amounts of data they are amassing to help deliver competitive advantage, they are increasingly turning to Business Intelligence solutions. Companies are recognizing that by augmenting their traditional operational Business Intelligence tooling with multi-dimensional OLAP capabilities, they can cover the entire BI continuum to address both the situational ("What") questions and the forward-looking ("How") questions to help determine how a particular behaviour will impact results. While building solutions to perform combined analysis, it is necessary to account for varying data formats, multiple repositories and different presentation clients. A unified ETL, BI and OLAP platform can provide the required level of integration between the different moving parts.
The authors wish to thank Robin Hazel and Alan Lee for their assistance in preparing this article.
|Ross Sharman is the Technical Director for Knowledge Global, where his work architecting and building the EMMA sustainability solution helped him to win the 2009 Green IT Architect Award from Oracle Magazine. Ross has an extensive background in technology and in electrical and electronic engineering, and has worked in large integration and Business Intelligence projects in Australia, the US, and Europe.|
|Juliana Button is Director of Product Management for Oracle Fusion Middleware. Since 1992, Juliana has held various technical and management positions in Oracle Corporation in Australia and at Oracle Headquarters in Redwood Shores. Her responsibilities include showcasing worldwide customer success with Oracle Application Grid products, as part of the Oracle Fusion Middleware Strategic Customer Program.|