Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« OBIEE 11G Performance Optimization Strategies - Part I | Main | Customer Experience - Expectations »

OBIEE 11G Performance Optimization Strategies - Part II

In the first part of the blog, we discussed the basic performance expectations from a reporting application and the reporting design considerations. In this part we continue the conversation and discuss further on the RPD design considerations and the approach to troubleshoot performance at the database level.

RPD Design Considerations

While looking at the OBIEE RPD from the point of view of performance, there are a number of basic questions that need to be kept in mind which are illustrated below

1. De-normalizing Objects De-normalize dimensional objects, combine several dimensional attributes into one flat table to reduce joins

2. Aggregate Tables Minimize the cost of aggregations using Aggregate tables though this has a trade-off resulting in additional ETL, storage and complex mapping efforts

3. Trimming the OBI RPD Best practice is to keep the RPD trimmed by disabling the projects, initialization blocks, variables and redundant objects

4. Push Complicated Calculations to ETL Complex logic that involves complex SQL can be considered to be handled in ETL reducing runtime processing

5. Cross Database Joins and Opaque Views Cross Database joins should be eliminated as much as possible and opaque views used as sparingly as possible

6. Caching Caching helps especially for complex queries that are reused many times. Implementing caching can be a little tricky as cached entries may become specific to users

7. Connection Pools Dedicated connection pools are advised for initialization blocks and session variables. Maximum number of connections advised for a connection pool is 20% of the concurrent user

8. Timeout Parameters Providing certain timeout parameters in the RPD to control long running queries enables OBI to track and cancel runaway queries

Database Layer Troubleshooting

1. SDE Queries Source Dependent Extract queries in the ETL can be optimized by adding hints which force the database query optimizer to execute the statement more efficiently

2. Lean and Streamlined Mappings Use of redundant transformations which utilize system memory and temp space could pose as another bottleneck and hence should be done away with

3. Incremental Data Ensuring incremental records are picked in daily loads is essential and can be done by configuring prune days

4. Load Plan Scheduling Proper care should be given while scheduling the loads so that deadlocks and unconditional waits aren't introduced

5. Database Optimization Oracle database features like proper indexing, partitioning of tables and pushing the star transformations to the DB level should be used. Configuration of DB parameters as per Oracle recommendations should be followed

The above are the most common and basic optimization techniques that can be employed to tackle any problem associated to performance. The main thing to remember is to break the problem into pieces and take it a battle at a time rather than try to fight a singular losing war.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles