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

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)

• 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)

• 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

• 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 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

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

• 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

• 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

• 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 1.0.2.2.2)
• Customers who use BIS/EDW are certified to use 4i against 11i if IAS 1.0.2.2.2 installed on separate machine (metalink note 139516.1, ARU 1834171)
• Customers who use BIS/EDW and want IAS 1.0.2.2.2 in same Apps Oracle Home can use the 11.5.7 rapid install

Reports

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.