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

Oracle partners with BICG on BI Applications Hands-on Workshop

Pleasanton, California - December 09, 2009

BICG partnered up with Oracle to conduct a hands-on Oracle Business Intelligence Applications workshop for Oracle clients.

Oracle provided the meeting-space, latest and greatest view of the technology, and business value gained when implementing the technology presentation. This allowed all attendees to see first hand what is happening and what is coming. You had to be there!

As the only recipient of the coveted Oracle BI/EPM Partner of the Year designation in association with the Oracle Innovation Awards at this year's Oracle OpenWorld, Minneapolis based BICG was selected to provide the "hands-on" training. BICG has long been recognized as a top performer when it comes to executing "experience based" success in both product training and project design, development and implementation. BICG was also presented to the group on "Developing a Corporate Business Intelligence Strategy". The presentation directly addressed potential pitfalls and recommendations surrounding Business Intelligence projects. BICG chose 30 year BI veteran Steve Tinari to lead the training and make the BI Strategy presentation.

Registration for the workshop was required and limited to 40 seats. This is all the space there was in the meeting room. Registration was sold-out in days and a wait-list was quickly developed. Last look had 25 clients waiting to claim a seat.

BICG has been asked to continue conducting this hands-on Oracle BI Apps workshop at strategic locations across the US and abroad. Registration for upcoming workshops can be found at http://www.biconsultinggroup.com .

BICG Workshop Advice: register early for the upcoming events and don't forget to bring your laptop. BICG uses a virtual training method that allows you to connect to their environment using only a web browser!