BI Apps Performance Optimization and Consideration - Part1

This topic may be used at a high level but covers most of the common aspects of the performance tuning and optimization considerations for OBIEE deployments. This topic does not detail much about handling ETL performance tuning. Couple of points are picked from Oracle's recommendations

At a high level the following needs to be reviewed and analyzed

• Data model and custom star schema's
• Physical Layer (including Joins and Keys configured)
• Business Model and Mappings Layer (including Joins and measures defined)
• Presentation Layer Layout
• Application Performance (including Joins, and aggregate tables configured)
• Caching options configured
• Security and Personalization
• Initialization Blocks and Variables configured
• Investigate the use of aggregate tables and mini dimension tables to increase performance
• Define data archive strategy and table partition strategy to manage data sizes in the database
• Database optimization
• Hardware setup

Partitioning

• Consider partitioning large Fact tables having more than 20 million rows
• Identify eligible columns of type DATE for implementing range partitioning
• Connect to the Oracle BI Server repository and check the usage or dependencies on each column in the logical and presentation layers
• Analyze the summarized data distribution in the target table by each potential partitioning key candidate and data volumes per time range, month, quarter or year
• Basing on the compiled data, decide on the appropriate partitioning key and partitioning range for your future partitioned table
• The recommended partitioning range for most implementations is a month, though you can consider a quarter or a year for your partitioning ranges

The following columns may be considered as partitioning keys

W_AP_XACT_F on POSTED_ON_DT_WID
W_GL_ACCOUNT_D may be a Date WID
W_AR_XACT_F on POSTED_ON_DT_WID
W_GL_REVN_F on POSTED_ON_DT_WID
W_GL_COGS_F on POSTED_ON_DT_WID
W_TAX_XACT_F on POSTED_ON_DT_WID
W_GL_OTHER_F on ACCT_PERIOD_END_DT_WID

Storage Considerations for Oracle Business Analytics Warehouse

• Setting excessive parallel query processes
• Running multiple I/O intensive applications, such as databases, on a shared storage
  choosing sub-optimal storage for running BI Applications tiers
• Make sure you carefully plan for storage deployment, configuration and usage in Oracle BI Applications environment
• Avoid sharing the same RAID controller(s) across multiple databases
• Set up periodic monitoring of your I/O system during both ETL and end user queries load for any potential bottlenecks
• Update optimizer statistics
• Consider implementing Oracle RAC with multiple nodes to accommodate large numbers of concurrent users accessing web reports and dashboards
• Check for the database configuration parameters as per the recommendations specified in the install guide

Managing Slow Running Reports

• Analyze the session SQL
• Run Trace or Explain plan on query
• Analyze the logical joins leading to the slow performance
• Analyze the database
• Increase the log level to 5 and analyze NQLQuery.log file and look at Query string,
  Logical request, Physical SQL query, Query outcome status, Physical query response time, Rows returned to client
• Key approach should be to address performance issues as close to the data as is possible, moving processing down the stack to the data

The next part will be about handling performance at metadata, reports & dashboards, presentation catalogs