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

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.