Skip to content

Implementing Complex Real-Time Analysis by Leveraging Oracle BI Applications


by Puneet Aggarwal

Many customers have real-time reporting requirements that are analytic in nature. Essentially, they have a real-time report as a starting point for a more detailed real-time analysis which can go a few levels deep. Shown below is a generic example of one such requirement for Account Analysis.


Here, the business wants the ability to do the following:

  1. Compare current period balance real-time with previous period account balances, etc.(Top report.)

  2. Drill down to details on the accounts of interest. (Navigate from the top report to the more detailed report.)

  3. Analyze real-time, especially during period close, how manual adjustments, etc. impact the General Ledger balances.

  4. Reconcile General Ledger Account Balances with Sub Ledger details by drilling down to Subledger details report.

Potential Solutions

There are three approaches to address the above problem. Each presents their own unique challenge.

Approach 1

Business Intelligenc Applications SolutionUse micro Extract-Transform-Load and make the reports near real-time as opposed to absolute real time.

Design and build relevant aggregates and custom tables per best Oracle Business Intelligence Data Warehouse practices.


Approach 2

Oracle E-Business Suite Solution. Use a combination of materialized views and non-materialized views.


  • Materialized views for Prior Period Summary and 12 Month Moving Average.
  • Real-Time Views in E-Business Suite for all other information.

Approach 3

Hybrid Solution. Use Oracle Business Intelligence Applications and Oracle E-Buiness Suite.


  • Use Business Intelligence Applications Data Warehouse for Historical Data
    - Summary Reports, Prior Period
    - Summary Reports, 12 Month Rolling Average
  • Use Oracle E-Business Suite for Real-Time Data
    - Summary Reports, Current Period
    - Detail Reports, Current & Prior Period
  • Use OBIEE Conformed Dimensions to Merge Summary Report (Important aspect of implementing the solution correctly).
  • Additional Aggregates in Data Warehouse for Performance

Options Comparison

BI APPS Only Solution

EBS Only Solution

Hybrid Solution

Leverages already built Oracle BI APPS code
Easy to integrate other GL Segment hierarchies

•  No additional effort on integrating real time and Historical data


Real time


Meets Real-Time Needs
Leverages BI Apps infrastructure where possible
In line with KPI’s Real-Time Analytics solution
- Conformed Dimensions, Shared Security setup
Aggregates built for this report will improve performance on other dashboards/reports
No Micro-ETL Design and Tuning
Design is not Report Specific

Does not meet Real-Time need

Report Specific OBIEE Design
Materialized View design & maintenance
Does not use existing BI Apps warehouse
Greater load on EBS Production database

Somewhat complex OBIEE modelin


Concluding Thoughts

Approach 1 - Oracle Business Intelligence Applications (OBIA)
This solution is an attractive one if absolute real-time is not a key requirement. However, additional aggregates might be needed to meet performance expectations.

Approach 2 - Oracle E-Business Suite (EBS)
This is not a recommended approach in most of the cases, as it does not leverage any existing Business Intelligence Applications content and is not flexible, because of being a report centric approach.

Approach 3 - Hybrid Sustainable Organization Library (SOL)
This is the best approach if real-time is an absolute requirement. KPI Partners has had great success in implementing this approach at customer sites and in achieving results that were considered not attainable.


Watch: Real-Time Analytics for Oracle BI



Puneet Aggarwal is Consulting Manager at KPI Partners.  His project work for KPI has been recognized by industry experts with an Oracle Excellence Award.  Before joining KPI, he was member of the Siebel Analytics Core Engineering Development team responsible for the original development of Oracle Human Resource (HR) Analytics.   Check out Puneet's blog at


Comments not added yet!

Your future starts today. Ready?