Achieving Business Insight by Integrating Relational and Multi-Dimensional Data

by Ross Sharman and Juliana Button

Creating a unified platform for fast, flexible data analysis

Part of the Oracle Fusion Middleware Patterns series.

Published August 2009

 Oracle SOA Suite
 Oracle Business Intelligence


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.

  • A heterogeneous tool for data population to handle data input from a range of disparate physical devices and data stores;
  • A common enterprise information model that provides consistency, security, flexibility and re-use;
  • Flexiblity in reporting formats in order to accomodate the diverse needs of users across the BI continuum and rapidly changing reporting requirements.

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.

Integration Architecture

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
  • An ETL Tool to integrate data from disparate data sources and aggregate them into a common repository
  • A Business Intelligence Platform to perform flexible reporting on relational data and disseminate it to variety of end users across different channels
  • An OLAP Platform to enable business users to quickly model complex business scenarios to perform forecasting, discover trends and understand behavior patterns

Let's take a look at key architecture best practices to build such a solution.

1st Principle: Integrate Heterogeneous Data Population

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:

  1. OLTP to capture and store transactional operational data,
  2. Data Mart/Data Warehouse to consolidate into multi-dimensional data and
  3. OLAP for predictive modeling and forecasting

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.

2nd Principle: Build a Common Enterprise Information Model

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

3rd Principle: Enable Flexible Reporting

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.

Energy Efficiency Management And Monitoring With EMMA

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:

  • Emissions and mandatory energy efficiency reporting.
  • Ability to spot trends and learn from past trends on energy consumption.
  • Verification of energy sources.
  • Quantification of energy/carbon reduction measures.
  • Validation of energy/emissions data reported.

Also, in order to deliver a complete emissions solution, the application needed to address forward-looking "How" questions like:

  • Energy efficiency/carbon reduction project viability.
  • Effectiveness of efficiency/carbon reduction strategies.
  • Carbon pricing and building asset modeling.
  • Modeling of renewable and alternative energy supplies.
  • Notifications and alerting for abnormal energy consumption.
  • Changing behaviours in the workplace.
  • Orchestrating and measuring efficiency projects.

The solution required a combination of ETL, BI, and OLAP technologies to accurately report on operational data and provide modeling and forecasting capabilities.

EMMA Architecture

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

  1. Oracle Data Integrator (ODI), ETL tool, to aggregate data from energy sources like smart meters, security systems, electrical circuits etc
  2. Oracle BIEE to accurately report on energy measurements in standard formats prescribed by UN, industry and governmental bodies
  3. Oracle Essbase, analytical OLAP Engine, to plan for the future through modeling and predictive forecasting and to help mitigate carbon exposure and maximize potential revenue

Let's quickly review three important aspects of this architecture.

Heterogeneous Data Population

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.

  1. Energy Data Sources to Operational Data Store (EMMA Database)

    ODI is scheduled at regular intervals to process these files and Web services, and then populate the data into the EMMA database via staging tables adhering to a few business rules and validation rules. The data is then populated into the EMMA repository, applying several business rules (defined as knowledge modules) around this transformation to ensure the data is clean.
  2. EMMA Database to BI Data Mart

    The data is then consolidated into the data marts on a nearly constant basis, ensuring that the analytics layer is up to date. ODI uses Changed Data Capture in the EMMA OLTPdatabase to determine the data to be copied into the data marts. There is typically minimal information change on the data mart dimensions, with the majority of changes coming from the meters, which is an accumulation of the electricity and carbon data on an hourly basis.
  3. BI Data Mart to Essbase

    On a weekly basis ODI is scheduled to re-populate the Essbase cubes, for the planning and high-level analytics. We used the ODI adapter for Essbase, which makes mapping to the target cubes a straightforward process. The data granularity in the Essbase cubes is on a monthly basis. Data load problems in any of the three ELT processes are logged by ODI, and automatically flagged to the administrators who can then attend to the errors.

Common Enterprise Information Model

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.

Flexible Reporting

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.

Figure 7: BI Server can seamlessly report against all three data sources

University Energy Efficiency Management

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.

Ref Meter ID Timestamp KwH
10014 T0000241 5/05/2009 3:15 0.188
10014 T0000241 5/05/2009 3:30 0.188
10014 T0000241 5/05/2009 3:45 0.188
10014 T0000241 5/05/2009 4:00 0.188
10014 T0000241 5/05/2009 4:15 0.125
10014 T0000241 5/05/2009 4:30 0.125
10014 T0000241 5/05/2009 4:45 0.125
10014 T0000241 5/05/2009 5:00 0.062
10014 T0000241 5/05/2009 5:15 0.125
10014 T0000241 5/05/2009 5:30 0.188
10014 T0000241 5/05/2009 5:45 0.188
10014 T0000241 5/05/2009 6:00 0.062
10014 T0000241 5/05/2009 6:15 0.125
10014 T0000241 5/05/2009 6:30 0.062
10014 T0000241 5/05/2009 6:45 0.125
10014 T0000241 5/05/2009 7:00 0.125
10014 T0000241 5/05/2009 7:15 0.062
10014 T0000241 5/05/2009 7:30 0.125
10014 T0000241 5/05/2009 7:45 0.062
10014 T0000241 5/05/2009 8:00 0.125
10014 T0000241 5/05/2009 8:15 0.062
10014 T0000241 5/05/2009 8:30 0.375
10014 T0000241 5/05/2009 8:45 0.312
10014 T0000241 5/05/2009 9:00 0.188

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
Date 5/05/2008 5/05/2008 5/05/2008 5/05/2008 5/05/2008 5/05/2008 5/05/2008
Time 3:00 4:00 5:00 6:00 7:00 8:00 9:00
Floor 12 12 12 12 12 12 12
Current Occupants 0 0 0 0 10 2 8

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)
Date 5/05/2008 5/05/2008 5/05/2008 5/05/2008 5/05/2008 5/05/2008 5/05/2008
Time 3:00 4:00 5:00 6:00 7:00 8:00 9:00
Electricity Usage (kwh) 17 12 12s 12 14 18 22
Occupants 2 0 0 0 0 2 8
Meter ID T0000241 T0000241 T0000241 T0000241 T0000241 T0000241 T0000241

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:

  • Expanding the underlying service-oriented architecture to leverage Oracle Service Bus for real-time integration, and Business Activity Monitoring for real-time alerting. Knowledge Global is also looking at Complex Event Processing to provide an additional level of intelligence to the vast amounts of streamed data.
  • Oracle Governance, Risk, and Compliance Manager could be leveraged to achieve compliance with Australia's National Greenhouse and Energy Reporting System (NGER) and Energy Efficiency Opportunities (EEO) reporting, in order to avoid potentially significant fines for non-compliance.
  • E-Learning could help bring about behavioural change through employee interaction. Integrating an E-Learning tool to EMMA will provide staff with a real-time, interactive view of how they can help reduce energy consumption at their workplace.


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 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 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.