Oracle Business Intelligence Application 7.9.6.3 Customization Types, Tips and Best Practice Guidelines – Part A

I would like to share some of the Oracle Business Intelligence Application (OBIA) customization tips and best practices guidelines from my knowledge base. This document is based on my experiences of BI Apps implementation over the period of last 10/11 years since Siebel days and extract from Oracle’s standard documentation around BI Apps.

OBIA Architecture

OBIA Architecture has following components i.e. ETL Architecture, Data Model, Metadata (Repository), Dashboard/Webcat along with security.

OBIA Pre-Built Contents

Oracle Business Intelligence Applications 7.9.6.3 are pre-built solutions from source to dashboard. The pre-built solution contents are:   

Pre-built Analytics Data Warehouse Model

The Business Analytics Data Warehouse is the persistent layer where information is stored for retrieval when required. The data warehouse resides in a relational database platform like Oracle, IBM DB2 LUW, Teradata or Microsoft SQL Server. It consists of transaction grain tables used to capture facts from the key business processes across the organization. The model consists of current snapshots, periodic snapshots and aggregate tables to support complex analysis requirements along with the hundreds of dimension tables conformed across the fact tables which contribute to enrich the analyses.

Pre-built Data Integration Components (ETL/E-LT)  

Data Integration components ( data extraction routines) are used to integrate data from various operational systems such as E-Business Suite, PeopleSoft Enterprise, Oracle's Siebel CRM Applications, Oracle JD Edwards Applications, and third party legacy systems, and load the information in the Business Analytics Data Warehouse.

The data integration components are built using either the Informatica Power Center data integration platform (ETL) or Oracle Data Integrator (E-LT). It uses a layered approach based on data warehouse best practices, which is open and flexible which provides ease of configuration and customization to enable changing the out of the box transformation rules, adding additional attributes, facts and dimensions.

Pre-built Metadata (BI Repository)

The metadata layer includes the definition of role based views, dimensions, metrics, hierarchies, calculations, aggregation rules, canonical dime dimensions, time series metrics, roles and security. The metadata layer is built on the Oracle Business Intelligence Enterprise Edition platform using the Admin Tool, a core component of the BI server, which is designed to meet the requirements of the Common Information Model to deliver consistent and integrated information to business users, by capturing the data from multiple source systems. Oracle Business Intelligence Applications provides ready to use pre-built metadata covering dimensions, metrics and hierarchies. In addition, the pre-built metadata includes the capability to leverage the security profiles inherited from operational systems such as Oracle EBS, PeopleSoft and JD Edwards.

Pre-built OBI-EE dashboard and report content (Catalog)

The OBIEE Presentation Catalog contains pre-built metrics, analytic decision workflows, reports, dashboards and alerts used to deliver information, stored in the Business Analytics Data Warehouse, to information consumers. These services are built on the Oracle Business Intelligence Enterprise Edition delivery platform, which is designed to meet the requirements of an enterprise business intelligence solution. Pre-built reports and dashboards are built on industry best practices, and provide rich content covering functional areas including sales, service, marketing, finance, supply chain, order management, procurement and human resources.

Customization Best Practice – Keep it to Minimum

In line with other Oracle applications products in general, customization should be kept to minimum and wherever possible out-of-the-box (OOTB) functionality should be employed.  This should be done in such manner that future upgrades to the product will not require significant amount of rework, i.e. where there is a good match between the requirements and the out-of-the-box functionality. However, in reality it is often unavoidable. 

Customization Reality – It is Unavoidable

It is often seen that certain key business requirements are not covered by the pre-built contents (Out of Box contents), For example the OOTB Data Warehouse may not include certain attributes customer would like to report on from the source system, or more likely, require an external secondary data store (file, RDBMS, XML, and OLAP) to include additional reporting matrices.

For a rapid development we must re-use as much of the OOTB data model as possible to avoid “re-inventing the wheel” by developing data objects that have already been populated by BI-Apps. There are number of situations where there is a need to modify the pre-built contents.

In these cases it is important to follow certain guidelines and techniques when developing non-standard BI elements. This blog is an attempt to detail guidelines on how to develop custom objects to integrate into BI Apps. The idea is to follow the same design techniques and guidelines that BI Apps uses to build OOTB contents for customization. This approach would be helpful to maintain the oracle’s high standard development practices and keep “look and feel” like standard BI-Apps content.

Types of Customization and Impacts

There are 6 types of customizations that will be implemented.  These customizations can be as simple as exposing already existing attributes in the DW to the Presentation Layer of OBI-EE.  They can also be as complex as creating new dimension, facts and external data sources. The new report creation is not in scope of the this discussion.

The following are the main extensions covered in this blog.

TYPE A- Simple Extensions and Adding Simple/Complex Logical Definition

The Data Warehouse has a vast amount of attributes within its data model.  However, not all this data is exposed to the repository and dashboards.  One of the simplest customizations is to expose the attribute top the Presentation Layer of OBI-EE.

Creating new logical definitions (simple-moderate-complex) based on physical/logical columns already exposed should also be included into the Type A category. Time Series Logical columns are an example of this type of customization.  

Impact:

·     Changes only in the OBI-EE repository in the Physical, Logical and Presentation Layer are needed.

TYPE B- Attribute Extensions

Adding attributes to existing dimensions and measures to existing facts.  The complexity of this customization depends on the SQL required to populate the attribute.  If the attribute is part of an existing table in an existing query then the inclusion is very simple.

Impact: 

• Additional column(s) to the physical staging and target tables.
• Changing existing SDE source mapping to add the additional column(s) to the SQL query and the target table.
• Changing existing SIL target mapping to add the additional column(s) to the SQL extraction query to the target table.
• Changes required to the OBI-EE repository to add the column to the Physical and modeled through to the Business and Presentation Layers.

TYPE C- Additional Fact, Parent Child Hierarchies Table, Bridge Table

Adding new Facts, Parent Child Hierarchies (value hierarchies) or Bridge tables (a technique for modeling Many-to-Many relationships) will require new SDE mappings for extraction to the DW staging area and new SIL loading routines from the staging area to the DW 

Impact:

• Additional physical tables created in the staging and DW areas.
• Add new SDE source mapping to populate the target staging table
• Add new SIL target mapping to populate the DW target table
• Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
• Optionally applying security filtering.
• Adding new SDE and SIL to the DAC dependency

TYPE D- Localization of Oracle Business Intelligence (Repository Metadata, Oracle BI Data or Report and Dashboard)

Localization of Oracle Business Intelligence requires three configurations i.e. localization of Oracle BI Repository Metadata, localization of Oracle BI Data and localization of Reports and Dashboards. These configurations require configuration file changes, creating metadata objects to support it which includes initialization block, externalize metadata objects etc , DW tables and routine to populate the language dependent data.

Impact:

• Additional translation tables/ Modification to existing columns in staging and DW areas.
• Add/Modify SDE source mapping to populate the target staging table.
• Add/Modify SIL target mapping to populate the DW target table.
• Session Variables and Configuration Settings and creation of supporting metadata objects
• Import the data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
• Adding new SDE and SIL to the DAC dependency if required  

TYPE E- Fact Granularity Change

Add new Dimensions to an existing Fact requires changes to existing SDE and SIL mappings for the Fact table.  In addition, SDE and SIL mappings are required to implement new dimensions.

Impact:

• Additional physical dimension table created in the staging & DW areas.
• Additional key fields to staging and DW fact tables.
• Add new SDE source mapping to populate the new target staging table.
• Add new SIL target mapping to populate the DW target table.
• Changing existing SDE source mapping to add the additional keys staging fact table.
• Changing existing SIL target mapping to add the additional key column(s) to the DW target fact table.  Also, include the key lookup to the new dimension.
• Create SDE & SIL workflows.
• Create DAC tasks with required dependencies.
• Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
• Changes required to the OBI-EE repository to model new Physical model and modeled through to the Business and Presentation Layers.
• Optionally applying security filtering.

TYPE F- New Star Schema

New facts and some new dimensions integrated with existing dimensions.

Impact:

• Additional physical dimension and fact table(s) created in the staging & DW areas.
• For each new physical dimension table and fact table a new SDE source mapping to populate the new target staging tables.
• For each new physical dimension table and fact table a new SIL target mapping to populate the DW target table.
• Create SDE & SIL workflows
• Create DAC tasks with required dependencies.
• Import the new data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.
• Optionally applying security filtering.

TYPE G- Additional Source Systems or Using Universal Adaptors

Adding a second home-grown or non-supported data source.  OOTB BI-Apps has one supported data source.  However, it is possible that you want to integrate other data sources into the Business Analytic Warehouse (BAW).  Additional sources can include simple spreadsheets to interlacing data into existing data models. 

Interlacing is a method of loading data into the existing data model without matching & merging into the supported data source.  For example, there might be Purchase Orders from a different ERP system that you want to provide integrated reporting with eBS.  In this case you will need to integrate dimensional and fact data.

Another example is to use the universal adaptors to push the data into the BI Apps. The universal adaptors implementation lists following impacts.

Impact:

• Additional columns created for additional attributes in dimension and fact table(s) created in the staging & DW areas.
• Modify the universal adaptor SDE mapping &  SIL mappings to include additional attributes if any
• Create SDE workflow
• Create DAC tasks with required dependencies.
• Import the new columns data objects to the OBI-EE repository and model the physical through to the Business and Presentation Layers.

In the next blog of this series I would like to cover the naming conventions and development guidelines that should be followed when developing new attributes, tables, dimensions and facts in the Business Analytics Warehouse (BAW) and new/changed repository objects in the OBIEE repository.

Read 2nd Article on topic :

http://bidirect.blogspot.co.uk/2013/01/oracle-bi-apps-7963-tips-and-best.html