Sunday, June 20, 2010

Understanding Oracle BI Applications

Oracle BI Applications are a complete, end-to-end BI environment covering the Oracle BI EE platform and the prepackaged analytic applications. The Oracle BI Applications discussed below is solely pertaining to its use with Informatica PowerCenter as the ETL tool. The current version of Oracle BI Applications which is intended for use with Informatica PowerCenter 8.6.1 is

                                                           Courtesy: Oracle Corporation

As shown above, the packaged ETL mappings consume operational data from sources comprising J D
Edwards, Oracle, PeopleSoft and Siebel with the aid of source-specific adaptors as well as universal adaptors, which are used for legacy or other data sources. The data is then loaded into a data warehouse comprising of pre-built schemas, based on different subject areas, readily available for use with a reporting tool.

The main constituents of the Oracle BI Applications setup are the Informatica PowerCenter pre-packaged repository 'Oracle_BI_DW_Base.rep' that contains the pre-packaged ETL's, the metadata for the pre-built data warehouse referred to as the Oracle Business Analytics Warehouse (OBAW), the pre-built Oracle BI repository 'OracleBIAnalyticsApps.rpd' file that contains the pre-designed data models for different subject areas and the ready-to-use OBIEE web catalog 'EnterpriseBusinessAnalytics' that consists of pre-built dashboards and requests. The Oracle BI Applications provides a complete solution for enterprises needing a data warehouse having either one or more of the following source applications - J D Edwards, Oracle EBS, PeopleSoft and Siebel. The other installables needed for use with Oracle BI Applications includes Oracle Business Intelligence Enterprise Edition (OBIEE), Informatica PowerCenter and Data warehouse Administration Console (DAC).

Oracle BI Applications supplies the Informatica PowerCenter Standard Edition license in the Oracle_All_OS_Prod.key file. This license is non-expiry and supports a variety of platforms and databases and also offers PowerExchange licenses to access PeopleSoft, Oracle E-Business Suite, Siebel et al. The Informatica Administration Console is shown below that showcases the license information for different platforms, databases, source applications etc.

Oracle BI Applications provides a pre-built Informatica repository Oracle_BI_DW_Base.rep consisting of shared folders that contain packaged ETL mappings that extract data from sources comprising J D Edwards, Oracle, PeopleSoft and Siebel and load the data into the pre-built warehouse referred to as the Oracle Business Analytics Warehouse (OBAW). The image below shows the global repository Oracle_BI_DW_Base.

In the image, the  SDE_PSFT_89_Adaptor consists of mappings that extract data from PeopleSoft 8.9 application tables and loads them to the staging tables in the OBAW. SDE is Source Dependent Extract as the mapping depends on the source application for extraction of data and the mapping names in the SDE folders are pre-fixed with 'SDE_' like 'SDE_PSFT_EmployeeDimension' as shown below.

The SILOS folder comprises of mappings that load data into the dimension, fact and aggregate tables in the OBAW from the staging tables. The mappings in the SILOS folder are pre-fixed with 'SIL_' and SIL is referred to as Source Independent Loading.

The OBAW is administered by the DataWarehouse Administration Console (DAC). DAC not only facilitates the creation of the pre-built OBAW schemas, scheduling and monitoring the Informatica ETL process but also allows creation of customised tables, indices in the OBAW and also registering custom ETL tasks that are created in Informatica. DAC is an ETL orchestration tool that is used by warehouse developers and ETL administrators for application configuration, execution and recovery and monitoring. The DataWarehouse Administration Console is shown below.

Oracle also offers Oracle Data Integrator (ODI) as the middleware ETL tool as part of the Oracle BI Applications package, but it still continues to provide Informatica as the ETL solution as per the user's choice of middleware.

Oracle BI Applications provide a pre-built Oracle BI repository that comprises of different warehouse data models for a host of different applications that include Human Resources, Sales, Financials, Supply Chain and Order Management, etc. The contents of the Oracle BI repository file 'OracleBIAnalyticsApps.rpd' is shown below.
The image below shows the pre-built logical model for CRM - Revenue Fact comprising of one logical fact and several dimension tables. The ready-to-use model forms the basis for physical queries to be executed on the OBAW, when processing requests from Oracle BI Answers and Dashboards.

The pre-designed Oracle BI web catalog contains ready-to-use dashboards and requests, pertaining to different applications or subject areas. The image below shows the pre-built dashboard for Sales - Customers subject area's Account Summary page.

Thus, the Oracle BI Applications package is a complete, ready-to-use solution. However, the Oracle BI Applications implementation will either be a smooth ride if all the existing business logic is as per desired with minimal customizations or it may come with its own set of perils if there are data issues and major customizations in either the packaged ETL's or the pre-built OBIEE repository, requests or dashboards. But then everything that is readily delivered comes with its own set of pros and cons.


  1. I'm curious about your comment saying

    " Oracle have planned to do away with the use of Informatica PowerCenter as the ETL tool for Oracle BI Applications".

    Where did you get that information from and is that still a valid comment

  2. Good posting.Very useful information.

  3. Hi Garivaldo,

    Oracle still provides Informatica as part of its solution for Oracle BI Applications. The current Oracle Business Intelligence Applications has Informatica 9.0.1 being offered as the ETL tool.

    Thanks for pointing this out.


  4. are informatica power center licenses needed as part of obiapps setup?