Monday, 13 May 2013

Oracle BI Applications 11g Architecture – ODI and Golden Gate is it a dream Data Integration Combination?

Oracle BI Applications built on the Oracle BI Suite Enterprise Edition is one of the leading pre-packaged BI Application offering from Oracle for many years to gain insight from a range of data sources and applications including Siebel, Oracle E-Business Suite, PeopleSoft, and third party systems such as SAP.

Oracle pre-packaged BI Applications offers followings 

·         A prebuilt data warehouse schema, ETL metadata and data-movement infrastructure to support data transformation from transactional sources
·         A pre-built Oracle BI Applications metadata repository.
·         A suite of metrics for selected line of business
·         Role based analytics and dashboards

Oracle BI Apps 7.9.6.X

Oracle BI Apps 7.9.6.X applications components are as follows

·         Source Systems
·         Oracle Business Intelligence Enterprise Edition
·         Oracle Business Analytics Warehouse
·         Oracle Data Warehouse Application Console
·         Informatica
The data integration tool used is Informatica while Oracle Data Warehouse Application console is used to manage the full/incremental load and life cycle management of ETL codes and procedures.

Though there are number of shortcomings of pre-packaged solution still I believe this is one of the best pre-packaged solutions to report against ERP and CRM applications available in the market. With 20-30 % customization any organization can realize the real potential of the solution within limited time frame.

Data Integration Limitations - Oracle BI Apps 7.9.6.X

Some of the short comings around data integration ( Informatica and DAC) are as follows

·         Informatica OEM version does not provide many of the features for improving performance of ETL
·         Performance Issues around lookup and temporary file creations
·         Does not support real time reporting data extraction though Micro ETL is supported by DAC
·         Multiple Execution Plan is not supported by DAC
·         DAC could not support to handle parallel bitmap index creation processes
·         DAC - It does not have advanced features expected from a sophisticated dependency management tool in morden area of data integration

In short there are significant limitations with Informatica (OEM version) and DAC based data integration option provided.  Recently oracle announced Oracle Business Intelligence (BI) Applications 11.1.1.7.1 which leverage the Oracle Data integration option with ODI and Golden Gate instead of using Informatica and DAC based ETL solution. This is the first major release on the 11g code line leveraging the power of ODI, and certified with the latest version of Oracle BI Foundation 11.1.1.7.

This blog is attempt to describe the Oracle BI Apps 11.1.1.7.1 Architecture and Oracle Data integration option with Data Integrator and Golden Gate


Oracle BI Apps 11.1.1.7.1  Architecture

Oracle Business Intelligence Applications 11.1.1.7.1   has following components:
·         Source Systems
·         Oracle Business Intelligence Enterprise Edition
·         Oracle Business Analytics Warehouse
·         Oracle BI Applications Configuration Manager and Functional Setup Manager
·         Oracle Data Integrator
·         Oracle GoldenGate
The logical architecture for Oracle Business Apps 11g  is shown below.
 


The diagram is presented in 3 main layers.
The bottom layer includes the transactional application database, the Oracle Business Analytics Warehouse Database, and the Oracle BI Applications Repository Database. The transactional application database contains transactional application schemas and can optionally be hooked up to the Oracle Business Analytics Warehouse with Oracle Golden Gate. The Business Analytics Warehouse Database contains the optional BI Applications Source-dependent Data Store (SDS) Schema and the BI Applications Data Warehouse Schema. The Oracle BI Applications Repositories Database system includes the Oracle BI Applications Oracle Data Integrator (ODI) Repository and the Oracle BI Applications Components Repository.
The middle layer shows the BI Domain. This layer includes the ODI Console and ODI agent, Oracle BI Applications Configuration Manager and Functional Setup Manager, and the load plan generator, all of which are hosted on the Oracle Weblogic Server. This layer also includes the Oracle BI Presentation Server and the Oracle BI Server, and the Oracle BI Applications Presentation Catalog and repository (.rpd) file.
The top layer includes the user connections to Oracle BI Applications. The BI Applications Client for Business User Roles and the BI Applications Client for BI Applications ETL Administration and Functional Setup Roles both use a web browser to access their part of Oracle BI Applications. Business users access dashboards and analyses created from Oracle BI Applications. Administrative users or users performing configuration tasks access Oracle BI Applications Configuration Manager and Functional Setup Manager from their web browser. Development users use a web browser as well as ODI studio, which gives them access to the load plan generator plug-in and other utilities. They also use the BI Administration Tool, which is part of Oracle BI EE.

Oracle Data Integration – Data Integrator (E-LT) and Golden Gate Replications

On data integration front of BI Apps 11.1.1.7.1 are completely re-architected to leverage Oracle Data Integrator and Oracle Golden Gate 
  • BI Applications is now available with Oracle Data Integration. Oracle Data Integrator (ODI) delivers Extract Load and Transform (E-LT) technology that improves performance and reduces data integration costs—even across heterogeneous systems, and improves productivity.
  • Functional Setup Manager provides a Web based Administrative User Interface to track and manage implementation projects and their required functional setup steps
  • Configuration Manager provides a centralized Web based administrative user interface to simplify the setup and maintenance of Oracle Business Intelligence Applications.
  • A new optional pre-packaged solution, that leverages GoldenGate, eliminates the need for an ETL batch window and minimizes the impact on OLTP systems, while providing near real-time reporting and enabling more reliable change data capture and delete support.
The solution looks promising on paper, but need to pass a tough test with large data volumes and complex project life cycle management and operational activities monitoring and controlling with Configuration Manager and Functional Setup Manager.
On a lighter note someone asked me do we really need sophisticated data integration tool to carry out insert into select type of statements as most of the logic of data extraction/load is designed within the SQL Overrides only and with no significant transformation designed within ETL or ELT tool .. J

No comments:

Post a Comment