OBIEE Performance Management

Performance Management in OBIEE for optimal Query Execution time

After the implementation of OBIEE project completes, then the real world issues datawarehouse team faces. The issues reported by end user & client are mostly based on performance issue or to say report execution time for most common business reports having huge data. Other than Table Indexing at database level, here are some of the common technique’s which may help you in metadata repository development (rpd) to overcome performance related issues.

Caching :
Caching / seeding important reports before working hours on daily basis, reduces query execution time and report executes in optimal time.

There can be various technique for caching, some of which are :

      Seeding cache from delivers / alerts (IBots)

Setting Event Pooling Table

Aggregate Table : (Aggregate Persistence Wizard)

Go to: OBIEE Admin > tool> Utilities > Aggregate Persistence Wizard

Aggregate Table: Aggregate tables store precalculated measures that have been aggregated over a set of dimensional attributes.

Note : You must create aggregate table only in the situation when you can suppress huge number of rows of in the record of detail table.

The Ratio should be :

Detail Table : Aggregate Table >= 30 : 1

Turning off Loging :

Rpd -->Manage --> security --> users -->(Select appropriate user)--> right click --> properties

Logging can affect in performance of Oracle BI Server and can create large log files. By default logging level is 0.

Note : Logging can be used in problem solving and troubleshooting problematic queries. Do not change or update logging level until required.

Connection Pools – Best Practices for Performance Enhancement:

Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

• 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.
• Create a separate connection pool for execution of session variables.
• Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables.
• If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.

Constructing result using where clause :

With the help of where clause in content tab in logical table source you can filter the data (limits the rows) returned from database.Here, you can see the where clause and restrict the data using Expression Builder( marked in Blue).

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.

Setting Query limits : 
We can enable oracle BI Server to track and cancel run way queries by placing various limits on the repository for a given user or group. For each user or group it is possible to limit queries by various condition:

1) maximum number of rows a query can retrieve from a database.

2) maximum time a query can run on a database.

3) restricting access to a database during particular time periods from Analytic server.

Modelling Dimension Hierarchies Correctly : 
Dimension Hierarchy must be modelled accurately to ensure that Oracle BI optimizer chooses the most economical source. The Number of element for each level must be specified. The Number does not have to be exact, but ratio of number from one parent to child logical level should be accurate.

Setting NQSConfig Parameters : ( General Section )

Home : OracleBI\server\Config\ NqsConfig.ini (Open in Notepad to edit) à General Section à Edit :

• Sort Memory size
• Sort buffer Increment size
• Virtual Table Page Size