OBIEE 11G: Perfomance Tuning

Dear All,

Most of us, who is/are familiar with the Business Intelligence Reporting Tools , may be familiar with Oracle Business Intelligence Enterprise Edition (OBIEE) 11g version. OBIEE 11G is widely used in industry as the reporting tool. OBIEE 11g provides many new features compared to its previous versions.

But the important factor, when we develop the logic and build the analysis is to keep tracking of its performance. Building the report is not the big task, but to build good performing dashboards is the ultimate need of this dynamic industry. A well timed reports could help the users to take the decisions in time.

Please find below some of the steps which can comparatively reduce the execution timing, hence improving the dashboard performance.

• Turn off query logging in OBIEE
• PERF_PREFER_MINIMAL_WITH_USAGE Database Feature
• Setting number of elements for hierarchies in RPD
• Timeout and Max Connection Settings for Connection Pooling
• Foreign key joins by varchar fields to be replaced by surrogate keys
• Using aggregate tables for performance improvement
• Redundant views to be removed from reports
• Pivot Table vs. Table View

Turn off query logging in OBIEE

Where the log level is set at a high value in a production env, the performance of the application will be impacted as a large number of entries are added to the log file.

High query logging levels adds a very high overhead, especially for dashboards as all of the requests are issues/processed in parallel. Logging forces serial execution in the logger. 

“System Logging Level” has to do only with the BISystemUser interaction with the RPD.  This option determines the default query logging level for the internal BISystem user. The BISystem user owns the Oracle BI Server system processes and is not exposed in any user interface. A value of 3 within a production environment means that there will be a significant amount of logging being generated.

For testing purposes, query logging should be enabled for independent custom users created and not for all users. Also, ensure that the query logging options is disabled by setting these to 0 for this user prior to application rollout and within the production environment (Note that it has to be turned off in multiple locations.) to prevent excessive writes to the log files from initialization blocks etc.

If there is a requirement to troubleshoot a particular query in the production system, note that the LOGLEVEL does not need to be set for all users. It can be set on a per query basis as follows:

1.    Open the report to troubleshoot in OBIEE Answers

2.    Go to the "Advanced" tab

3.    Enter SET VARIABLE LOGLEVEL=2;  (or any level required) *Note that the ";" is required in the "Prefix" form box 

4.    Navigate to Results tab

It is recommended to set the logging level to 0 whenever analyzing performance in any environment.

PERF_PREFER_MINIMAL_WITH_USAGE Database Feature

The PERF_PREFER_MINIMAL_WITH_USAGE allows the OBI Server to use WITH clauses in SQL queries, which generally has a strong impact on performance as Oracle database optimizer is not efficient when WITH clauses are used.

Also ensure any performance testing is done with logging turned off to ensure that this is not impacting the statistics gathered.

Note that this setting will affect all OBIEE queries, therefore it is important that the development team test a number of other selected queries within the implemented to ensure that there are no significant performance increases in other areas.

Setting number of elements for hierarchies in RPD

When there is more than one query path to satisfy a particular query executed, the Oracle BI server may not choose the most optimal path as the number of elements has not been configured to define the ratio of data. 

The Number of Elements when set appropriate is used by the Oracle BI Server optimizer to calculate the best access paths to satisfy a given query, such as determining the best table to use when a query involves aggregating data to the selected level. The row counts available within the Administration Tool can be used to assist in setting this property. This setting does not have to be precise, just an estimate of the number of elements is required. The Total Level should always be defined as having just the one element.

Dimensional Hierarchies when created in the logical layer have their Number of Elements set as 1. This should be set to a more appropriate value to represent the number of distinct values at each level. 

Timeout and Max Connection Settings for Connection Pooling 

Connection Pooling allows the Oracle Business Intelligence Server to multiple physical SQL requests over a single physical connection and should be set to a reasonable value. 

Unreasonable number of connection pooling and timeout may cause performance issues.

A connection pool contains information about the connection between Oracle Business Intelligence Analytics Server and a data source. The Physical layer in the Administration Tool contains at least one connection pool for each database.  Multiple connection pools can be configured for a database.  Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database.  

For each connection pool, the maximum number of concurrent connections allowed must be specified.  Once this limit is reached, Oracle Business Intelligence Analytics Server routes all other connection requests to another connection pool or, if no other connection pools exist, the connection request waits until a connection becomes available. 

Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. 

In addition to the potential load and costs associated with the database resources, Oracle Business Intelligence Analytics Server allocates shared memory for each connection upon server startup. Therefore, raising the number of connections increases OBI Analytics Server memory usage.

The “Enable connection pooling” timeout specifies the amount of time, in minutes, that a connection to the data source will remain open after a request completes. During this time, new requests use this connection rather than open a new one (up to the number specified for the maximum connections). The time is reset after each completed connection request.  If the timeout is set to 0, connection pooling is effectively disabled; that is, each connection to the data source terminates immediately when the request completes. Any new connections either use another connection pool or open a new connection. 

The following formula is a rule of thumb to calculate maximum connection pool.

Max connections = 10-20% of concurrent users X # logical queries per page

For deployments with dashboard pages, consider estimating this value at 10% to 20% of the number of simultaneous users multiplied by the number of requests on a dashboard. This number may be adjusted based on usage. The total number of all connections in the repository should be less than 800. To estimate the maximum connections needed for a connection pool dedicated to an initialization block, use the number of users concurrently logged on during initialization block execution.

Foreign key joins by varchar fields to be replaced by surrogate keys

Using the ID varchar columns does not conform to the data warehouse best practices, and may result in potential performance issues as these columns may be alphanumeric columns, and Indexes on alphanumeric columns do not work the same way than indexes on numeric columns and can be much less efficient.

All foreign keys between fact and dimension tables should be surrogate keys, not reused keys from operational data. A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.

Using aggregate tables for performance improvement

The creation of aggregate tables is the one of the most common techniques in improving the performance of queries. Aggregate tables allows for queries to be run on a higher grain of data than that in the base table. For example, if a number of queries are run on the Retail Audit Fact table on the Monthly level (where the grain of the fact table is stored at the day level), then it would be worthwhile reviewing the options of creating an aggregate table to store this data.  Ideally the compression ratio between aggregation: base table should be at least 10:1.

Aggregate tables are commonly modeled with the base data warehouse table from which is was aggregated from within the logical layer. Content levels are commonly used to identify which table is an aggregate table, by specifying the grain of data which it contains.

When running queries, the Oracle BI Server reads the contents levels along with the dimensions which are included within the query, and determines whether the aggregate table should be used. Building the aggregate table as a separate business model removes this functionality and decisions from the Oracle BI Server. 

Redundant views to be removed from reports

Redundant views increase the overall maintenance of such reports, and may also impact the query of such OBIEE queries executed against such saved reports. 

Look to delete all redundant views, using the highlighted option below:

Pivot Table vs. Table View

Pivot tables generally require more time to process than standard Table views, and can potentially increase the performance of such queries. 

For any queries, please post as a comment.

Thanks,

Angad