Monday, February 11, 2013

The Three Most Common Mistakes Made in Oracle BI Application projects

For a variety of reasons, completing a successful Oracle BI Applications project is not as straightforward as one might think considering that the BI Applications are touted as a pre-built, end-to-end solution out of the box.

Based on our experience with either implementing new Oracle BI Applications projects or following on to failed projects, there are three common mistakes made that can determine the success or failure of the project:

1.   Failing to follow the installation and configuration guides

This may seem difficult to believe but there are many cases where projects have been implemented without following the specific instructions in the installation guide and/or the configuration guide. 

The installation guide is critical for setting up the infrastructure for Informatica, DAC, and the OBIEE  platform.

Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users

Some of the more common steps missed include:
  • Setting up the SSE_ROLE for the target data warehouse user
  • Configuring the proper Code Page settings for data movement between source and target
  • Failing to review and apply the supplied Oracle database parameter settings in the parameter template file (for example the one for Oracle 11g named init11g.ora)
  • Not setting PowerCenter Integration Services custom properties - specifically the  overrideMpltVarWithMapVar parameter which enables Informatica to evaluate parameters within mapplets.
The configuration guide provides instructions on how to set up both common Oracle BI Application areas and dimensions as well as functional area configuration steps.

Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users

Under common dimensions, it is critical that the exchange rate and calendar configuration is followed correctly and relates to the specific source system environment that will be used.

For the functional areas, there are a series of configuration files that must be reviewed and edited to conform to the source system.   For example, the Human Resources functional area requires that the 
band dimension files for Person Age, Job Requisition Age,  Performance Ratings, and Period of Service are configured before the data is loaded.

Also domain CSV files for Ethnic Group, Sex, Employment, and other HR attributes should be reviewed prior to the data load,  For HR Analytics, the most critical domain file that must be configured is the one that populates the Workforce Event dimension.  This file (named domainValues_Wrkfc_EventType_psft.csv for Peoplesoft implementations) maps each employee assignment event from the PS_JOB table to a standard set of values for Hires, Terminations, and other job related activities.   This file should be reviewed with knowledgeable HR subject matter experts to properly categorize each Action/Reason Code combination into the standard event types.

2.   Developing dashboards without continuous user involvement

The four words that strike fear into the heart of any Oracle BI applications consultant are "I have a spreadsheet".    In many implementations, dashboard development requirements are taken directly from one or more existing spreadsheets that are passed among various business organizations.   This approach more often than not leads to a disappointed user base when a final dashboard is delivered because OBIEE, while quite powerful, cannot always replicate the form and function that is easily built into a spreadsheet.

A far better approach is to take the existing spreadsheets and work through a fit-gap analysis to understand the business requirements and metrics that drive the spreadsheet.  After that is completed, the OBIA data model should be modified to reflect those requirements before any actual dashboard and analysis configuration is started.    Once the data model is ready and available with either actual or test data, workshops should be scheduled with users to demonstrate the capabilities of OBIEE on top of that data model.   Rather than duplicating spreadsheets, focus on the data model and the flow of an analysis.  Many spreadsheets have thousands of rows that are filtered by the user and then pivoted to create other summary analyses.   

Start with a top down approach on the dashboards, focusing on: 
  • dashboard prompts to filter reports automatically 
  • drilling and navigation 
  • conditional highlighting
  • ranking reports to identify outliers and top performers
  • charts that visually display trends
  • multiple view types of the same data using view selectors
  • column selectors
  • filters and calculations driven by presentation variables 
The key is to get users to think about interactive analysis instead of data dumps and scrolling through long table format reports.   

It is important to push back on users when they ask for features that are not easily achieved in the OBIEE tool or require significant modification to the data model just to meet a very specific reporting requirement.  Balancing the development and maintenance of any OBIEE code with what can be occasionally excessively specific user report requirements should be considered before heading down a path that can lead to project delays. 

Involve users throughout the development process to get their input and feedback.  With the rapid development capabilities of Answers, it is very easy to modify the layout of dashboards and analyses on the fly to get buy-in from the users.

3.   Implementing the RPD without modification

The delivered metadata repository (RPD) that comes with the Oracle Business Intelligence Applications should not be considered a final product.    On every OBIA project, one of the first tasks that should be performed is an RPD Review with the business users to develop a list of customizations that will make the Presentation Layer of the RPD a more effective representation of the business.    Performing this process early on will greatly reduce the development time later on when reports are developed.   It also is very helpful in improving the user adoption experience if they are new users to the OBIEE Answers tool.

The three R's of the RPD review process are:  Rename, Remove, and Reorder.

Rename any presentation column or table to reflect the business definition.  It is far easier to rename a column than to get user's to convert their known business vocabulary to match that of OBIA.  For example, rename the Out of the Box Employee Organization table and columns to be Department.

Remove any presentation columns and tables that are not required for analysis.  This includes any columns that may be exposed in the Presentation Layer but are not populated by the ETL for the particular source system for the implementation.   Work under the assumption that any column exposed in the Presentation Layer must be populated by ETL, unit tested for accuracy, and useful for creating analyses.    Simplicity yields project success.

Reorder presentation tables and columns to be more effective for users.   Put most frequently used columns at the top of presentation tables.    Put dimension tables at the top and facts at the bottom of the subject area.    Group similar metrics together either by purpose or by time series.    Make good use of presentation table foldering to minimize the number of attributes and metrics displayed.


There are no guarantees of success when implement a BI application.   But there are certainly ways to increase the possibility of attaining the ultimate goal:  satisfied users with a useful business analysis tool delivered on time and on budget.    It can be done.


  1. Thanks . Useful Post!

  2. Thank you an interesting post. HR has seen much technological advancement in the past decade. They are much more responsible than any other post. They must be well versed in all the departments. Nothing wrong in appointing those persons with powerful post.
    HR innovation