22 Oracle Business Intelligence Performance Tuning

22.1 About Oracle Business Intelligence

Oracle Business Intelligence (BI) Enterprise Edition (or Oracle Business Intelligence) provides a full range of business intelligence capabilities that collects up-to-date data from the organization, presents the data in easy-to-understand formats (such as tables and graphs), and delivers the data quickly to the members of the organization.

These capabilities enable the organization to make better decisions, take informed actions, and implement more-efficient business processes.

22.2 Oracle BI Server Query Performance Tuning

This section describes some important considerations for improving query performance with the Oracle BI Server.

The following list summarizes methods that you can use to improve query performance:

• Tuning and indexing underlying databases: For Oracle BI Server database queries to return quickly, the underlying databases must be configured, tuned, and indexed correctly. Note that different database products have different tuning considerations.

  If there are queries that return slowly from the underlying databases, then you can capture the SQL statements for the queries in the query log and provide them to the database administrator (DBA) for analysis. See "Managing the Query Log" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about configuring query logging on the system.

• Aggregate tables: It is extremely important to use aggregate tables to improve query performance. Aggregate tables contain precalculated summarizations of data. It is much faster to retrieve an answer from an aggregate table than to recompute the answer from thousands of rows of detail.

  The Oracle BI Server uses aggregate tables automatically, if they have been properly specified in the repository. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for examples of setting up aggregate navigation.

• Query caching: The Oracle BI Server can store query results for reuse by subsequent queries. Query caching can dramatically improve the apparent performance of the system for users, particularly for commonly used dashboards, but it does not improve performance for most ad-hoc analysis.

  See "About the Oracle BI Server Query Cache" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about query caching concepts and setup.

• Setting parameters in Fusion Middleware Control: You can set various performance configuration parameters using Fusion Middleware Control to improve system performance. See "Setting Performance Parameters in Fusion Middleware Control" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

• Setting parameters in NQSConfig.INI: The NQSConfig.INI file contains additional configuration and tuning parameters for the Oracle BI Server, including parameters to configure disk space for temporary storage, set virtual table page sizes, and several other advanced configuration settings. See "NQSConfig.INI File Configuration Settings" Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

22.3 Oracle BI Server Query Cache Performance Tuning

You can configure the Oracle BI Server to maintain a local, disk-based cache of query result sets (query cache). The query cache allows the Oracle BI Server to satisfy many subsequent query requests without having to access back-end data sources (such as Oracle or DB2). This reduction in communication costs can dramatically decrease query response time.

As updates occur on the back-end databases, the query cache entries can become stale. Therefore, you must periodically remove entries from the query cache using one of the following methods:

• Manually. In the Oracle BI Administration Tool, in the Manage menu, select Cache to open the Cache Manager. The Cache Manager provides the most flexibility in choosing which cache entries to purge and when to purge them, but it requires manual intervention. See "Using the Cache Manager" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

• Automatically. In the Administration Tool, you can disable cache for the system, set caching attributes for a specific physical table, and use Oracle Business Intelligence event tables to purge cache automatically. See "Monitoring and Managing the Cache" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for additional information.

• Programmatically. The Oracle BI Server provides ODBC-extension functions for purging cache entries programmatically. These functions give you the choice and the timing flexibility of the Cache Manager with the automation of event tables. You can write your own scripts to call these functions at times that fit your needs. See "Purging and Maintaining Cache Using ODBC Procedures" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

22.4 Oracle BI Web Client Performance Tuning

You can improve the performance of the Oracle BI Web client by configuring your Web server to serve up all static files, as well as enabling compression for both static and dynamic resources. By enabling caching and content expiration on the Web server, Web browsers can determine how often to reload the static files from the server.

In Oracle BI EE, static files are located in ORACLE_HOME/bifoundation/web/app. Follow the instructions for your Web server to set up static file caching and compression for the files located in this directory.