OBIEE Performance Tuning

Following are the few points which can improve the performance of Oracle Business Intelligence Enterprise:

1) Using Cache

2) Connection Pool

3) Aggregation navigation

4) Turning off log level

5) Where Clause

6) Limited Number of Initialization Block
7) Avoiding Opaque views
8) Avoid Complex join in Physical layer

Using Cache: When data is accessed, a copy is stored in separate memory called cache , Next time CPU looks for information, it first checks the cache. If data is there called a hit , retrieval is faster from the  cache memory .If it is not a hit, then it accesses system memory, puts a copy of the new data in the cache, and processes the information. Disk caching and memory caching significantly improves the overall speed of the computer.

 So,to use the cache we need to edit the NQSConfig.ini file

To enable cache set the ENABLE parameter to “YES” as shown in below image.

Connection Pool: It contains information about the connection to the database, not the database itself.

Improperly defined connection pool would affect the OBIEE performance .Here are some of the things to consider while defining connection pool.

Create seperate connection pool for session variable

Create dedicated connection pool for aggregate persistence wizard. You need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables.

Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage.

Using Aggregate Tables: Aggregate tables store pre computed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for speeding up query response times in decision support systems. This eliminates the need for run-time calculations and delivers faster results to users.

Turn off Log Level: Logging can affect in performance of Oracle BI Server and can create large log files By default logging level is 0. Though query logging has immeasurable development value, do not use this for regular production users as the runtime logging cost is extremely high. Every log item is flushed to the disk, which in turn hurts query response. 
Administration tools->manage->Security->users->right click on appropriate user->properties->set logging level

Where Clause:  With the help of where clause in content tab in logical table source we can filter the data(limit the rows).  Filter applied in WHERE Clause will applied on physical table that will be result in restricted or required data, other data which is not necessary, will not be fetched each time when there is query on that table.

Limiting Number of Initialization block: Initialization block are the only means to initialize dynamic repository, system session and non-system session variables. We should be very careful about not to create too many init blocks.
As we know, In the case of system and non-system session variables, the initialization blocks get executed every time a user logs in to the server. And In the case of Dynamic repository variables, the SQL in the Initialization blocks get executed every time the server is started or periodically if a schedule is set up to refresh the value of the variable.

Avoiding Opaque Views: An opaque view is a Physical layer table that consists of a SELECT statement. It should be used only if there is no other solution to your modeling problem. Opaque
views prevent the Oracle BI Server from generating its own optimized SQL,
because they contain fixed SQL statements that are sent to the underlying data
source.

Avoid Complex join in physical layer: If we create complex join in physical layer then there is probability of unnecessary tables in the final query, fired on the database.

Please share your feedback and keep me posted.
have a good day!!

Thanks