Friday, May 16, 2008

How Oracle BI applications handle real time analysis?

First lets define what real time means:

"There is a wide variety of definitions for the different types of right-time BI processing that exist, but most real-time BI requirements can be placed into one of four main categories: right-time data integration, right-time data reporting, right-time performance management and right-time automated actions." Colin White DM Review Magazine, September 2004

Drill between dashboards and transaction applications.
You can click a link in an interactive dashboard or report to drill to a transaction page in a new browser window for more details, while maintaining the data and security.The following case scenarios explains the concept of drill through available since Siebel Analytics Applications 7.5.

Companies need to provide their sales force with the ability to interact with the data in a rich and meaningful manner – so they can diagnose issues and not just spot them. In short, sales force organizations need to be able to see both the forest and the trees if they wants to be able to understand why things are happening. They needs to ability to see something and drill into it (click) to understand what’s going on – like in this example where the user sees something he or she does not like in the pipeline and drills into it (click), eventually seeing the actual deals that comprise the segment he or she is most interested in (click).

Creating a Micro ETL Execution Plan using the Data Warehouse Application Console (DAC)
Micro ETL execution plans are ETL processes that you schedule at very frequent intervals, such as hourly or half-hourly. They usually handle small subject areas or subsets of larger subject areas. The DAC tracks refresh dates for tables in micro ETL execution plans separately from other execution plans and uses these refresh dates in the change capture process.

Caution: Micro ETL processes can cause issues with data inconsistencies, data availability, and additional load on the transactional database. Therefore, you should consider the following factors before implementing a micro ETL process:

  • For related star schemas, if one schema is omitted from a micro ETL execution plan, the cross-star reports may be inaccurate. For example, if the Person fact table is refreshed more frequently than the Revenue fact table, a report that spans the Person and Revenue dimensional schemas may produce inconsistent results.

  • If you omit dimension tables from a micro ETL execution plan, the foreign keys for the fact tables will point to Unspecified rows for the new dimension records. The foreign key references will be resolved when the Complete ETL execution plan is run, but users of the reports should be aware of such inconsistencies.

  • If you do not include aggregate tables in micro ETL execution plans, the reports that use data from these tables will be inconsistent with the reports that use data from the detailed fact tables. However, if aggregate tables are included in the micro ETL execution plan, the aggregate calculations are performed for each ETL process, which will take a constant amount of time and may be inefficient to perform at such frequent intervals.

  • Hierarchy tables are rebuilt during every ETL execution plan by querying the base dimension tables. This operation takes a constant amount of time. If the base tables are big, this operation may take a long time and may be inefficient if the micro ETL execution plan runs several times a day. However, if you avoid populating the hierarchy tables during micro ETL processes, data inconsistencies will occur.

  • With micro ETL execution plans, caching will occur more frequently, which may have performance implications.

  • Micro ETL execution plans will put more load on the transactional database because of the frequent extracts.

How Oracle BI Applications handle real-time is one of the many advantage that they have to offer. My advice is to work with your clients and first define what real-times means to them; then innovate in stead of reinventing the wheel. The out of the box functionality, in my experience, tends to over deliver the BI requirements of every project that I have worked. I always keep in mind that Siebel now Oracle has smart people working with the biggest organization in the world. Their business is to build software application. I rather reuse and learn new techniques than increase the risk by creating from scratch. I am in the business of implementing software solution. Data entities are constants what change is the context of their use; therefore, trying to understand business requirements from the data and process view will help in the gap analysis and mapping process.

Three distinct pieces of metadata: ETL, rpd and webcat.
One single model – not building separate marts, just extending the same central mart. This is why.

No comments: