Problem solve Get help with specific problems with your technologies, process and projects.

Implementing business intelligence with Oracle's E-Business Suite, part 3

This tip covers the merits of using Oracle's Discoverer and Reports features for business intelligence tasks.

This tip is brought to you by the International Oracle Users Group (IOUG), a user-driven organization that empowers Oracle database and development professionals by delivering the highest quality information, education, networking and advocacy. IT is excerpted from the paper "Using Business Intelligence with Oracle's E-Business Suite," by David Fuston . Become a member of the IOUG and gain access to thousands more tips and techniques created for Oracle users by Oracle users.

This tip follows from part 1, which discussed Oracle's Embedded Data Warehouse, Business Intelligence System and Daily Business Intelligence features, and part 2, which covered Sales Analyzer, Financial Analyzer and Enterprise Planning and Budgeting.

Implementing business intelligence with Oracle's E-Business Suite

Along with the different modules in Oracle's E-Business Suite, some analysis of the data may need to be done with other Oracle BI/DW software or third-party tools. This tip covers the merits of using Oracle's Discoverer and Reports features for the tasks.

Discoverer is the ad-hoc query tool used to analyze data on the fly from Oracle's RDBMS. Discoverer integrates with Oracle Warehouse Builder, Oracle Reports, Oracle Express Products, and Oracle Designer. Discoverer uses drill-down and pivoting, just like FA and SA. In fact, the Discoverer administrator has a choice of defining the following four types of drills:

  • Item to item drills (item hierarchies)
  • Date to date drills (date hierarchies)
  • Drill to detail item class (hyperdrills)
  • External application drills (hyperdrill plug-ins)
By now, you should be asking yourself, "If SA, FA, and Discoverer all three share drill down and pivoting, what else do the have in common?" SA, FA, and Discoverer share the following features and functions:
  • Tables and graphs
  • Drag-and-drop rotation
  • Drill-down/up
  • Easy to use interfaceERP, CRM, SFA, and SCM
  • Cache data
  • Conditional formatting (red, yellow, green and other color-coding)

Discoverer also has advantages over SA and FA, as follows:

  • Simple SQL custom calculations can be accomplished in Discoverer
  • More flexible reporting (subtotals, etc.) than Express
  • Operates directly against relational data source (and 9i OLAP analytical workspaces in newer versions)
  • Transactional view available
  • Record-based selection
  • More intuitive for users that know SQL

Oracle Discoverer can integrate with data warehouses by using OWB to generate Discoverer metadata. OWB and Discoverer Bridge can access OWB metadata layer, and create a Discoverer export file (*.EEX). Discoverer then imports the .EEX files into a Discoverer EUL. Most, if not all, of this work can be done by the Discoverer administrator, and does not generally required DBA resources to manage the application. The Discoverer administrator is the person who defines the options for the display of the data joins as follows:

  • Outer join on detail
    • Example: Dept exist that do not have employees
  • Outer join on master
    • Example: Employees exist that are not in a dept
  • Details foreign key can have null values
    • Example: Employees exist that are not in a dept
  • One-to-one join relationship between master and detail
    • Example: An employee belongs to only one dept

Discoverer, like all products, has its limitations. The first disadvantage is the potential need for users to know underlying DB structures and/or SQL. The second disadvantage is the weak security, either public or private, unless installed as part of Oracle Apps.

Discoverer has four possible integration methods, some of which are integrated with Oracle Apps, and some of which are not. The four possible methods are:

  • Create EUL against Business Views
  • Oracle BIS
  • Create Data mart
  • Enterprise Data Warehouse

The Discoverer administrator can create a EUL against the Oracle Apps business views. This method is generally regarded as the easiest and fastest Do-It-Yourself (DIY) approach, or in other terms, the "build" approach defined by the TDWI. You will need to capture your FlexFields information, and come up with ERP, CRM, SFA, and SCM routines that keep your data up-to-date. Beware that performance may be an issue here, if you have installed Discoverer on the same box as the Oracle Apps. This approach is very similar to BIS. BIS is a collection of pre-defined workbooks and a EUL built using Discoverer and then sold as a packaged application, saving you some of the work.

If instead, you decide to create a Data mart, you will need to distill critical Oracle Apps tables into a data mart, which is the most difficult DIY approach. This strategy will require extensive knowledge of Oracle Apps schema, taking snapshots of data that needs to be refreshed, and can be challenging to preserve your FlexFields during each patch and/or upgrade cycle. You may, however, get, better performance since you will only be presenting the data that your end-users need, and not all of the data defined in a pre-built application like BIS.

Assuming that you pursue the DIY approach for the Data mart, here are some dimension table design requirements and recommendations to consider:

  • Primary key in relational table must represent lowest level dimension value in the data model
  • Each level in data model must be represented by a column in the dimension table
  • Child dimension values cannot have multiple parent dimension values
  • Dimension values from multiple levels of summarization must not be stored in the same column
  • DATE and TIMESPAN attributes must be included in time dimension
  • Avoid multiple-part primary keys
  • If natural keys are lengthy, use surrogate keys
  • Consider descriptive labels in place of numeric keys, if it makes sense
  • Use logical naming conventions for readability
  • Dimension table periodic update options

Using standard star schema architecture, here are some fact table design requirements and recommendations to consider:

  • Each database must have at least one fact table
  • The primary key must be a composite of all key columns in fact table
  • Primary key must contain all dimensions included in data model
  • Include summary level data in your data warehouse for performance
  • Avoid vertical partitioning by grouping facts that share the same dimensionality in the same fact table
  • Define and index tables in order of slowest –varying to fastest-varying dimension
  • Use level names in fact table names

Also, you will need to consider which of several possible fact table styles are best suited to your business, such as:

  • Detail-level fact table only—summarize data at runtime
  • Detail-level and summary-level fact tables—partition by level of summarization
  • Embedded-total fact table—multiple levels in a single fact table

In contrast, if you decide to pursue a Discoverer integration strategy and want Oracle Apps certification, then you will want to consider these issues in your DIY or BIS approach:

  • Discoverer 3i, 4i and 9.0.2 work against Oracle Applications 11i, 11.0 and 10.7
  • 11.5.7 rapid install contains Discoverer 4i (as part of iAS
  • Customers who use BIS/EDW are certified to use 4i against 11i if IAS installed on separate machine (metalink note 139516.1, ARU 1834171)
  • Customers who use BIS/EDW and want IAS in same Apps Oracle Home can use the 11.5.7 rapid install


Oracle Reports is really the relational reporting identified in the OLAP Report classifications as the BI "want-to- be" but is not really BI at all. Oracle Reports does allow users to create complex reports from Oracle's Relational or Multidimensional Databases, including unlimited data formatting and high-quality data presentation. The end-users usually can only view the reports, and not drill down, rotate, or pivot. An additional disadvantage is that typically only developers can create the complex reports.

Dig Deeper on Oracle E-Business Suite

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.