Analytics Software

Hi Chandu,

Below the Steps to improve the performance.

1. implement caching mechanism

2. use aggregates

3. use aggregate navigation

4. limit the number of initialisation blocks

5. turn off logging

6. carry out calculations in database

7. use materialized views if possible

8. use database hints

9. alter the NQSONFIG.ini parameters

Note:calculate all the aggregates in the Repository it self and Create a Fast Refresh for MV(Materialized views).

and you can also do one thing you can schedule an IBOT to run the report every 1 hour or some thing so that the report data will be cached and when the user runs the report the BI Server extracts the data from Cache.

http://blogs.oracle.com/pa/resource/Oracle_OBIEE_Tuning_Guide.pdf

http://www.peakindicators.com/index.php/knowledge-base/98-obiee-and-database-performance-tuning

http://obiee101.blogspot.com/search/label/CACHE

http://obiee101.blogspot.com/2010/01/obiee-performance-tuning.html

http://www.slideshare.net/themoff/performance-testing-and-obiee-4247838

http://www.peakindicators.com/index.php/knowledge-base/96-20-golden-rules-for-the-obiee-11g-rpd

Mark if helps.

Thanks,

Some comments:

• ad 1. implement caching mechanism: This should come way back at the end of this list and should NEVER be the first thing you turn to. Caching isn't something you "just turn on" since without a proper understanding and a sound caching strategy (refreshs vs stale data anyone?) caching does more harm than good.
• ad 4. limit the number of initialisation blocks: This ahs absolutely nothing to do with the stated problem.
• ad 5. turn off logging: The ancient myth of logging slowing down operations. This is nonsense.

With regards to the OP @user5388961 :

"In Sql it is expected as the data is huge. But why OBIEE is taking such time."

^-- So you do expect your query to be slow in SQL. OBIEE is emitting SQL agaisnt the data base...how do you expect a speed-up to happen? If an (same) SQL statement takes 10 seconds on SQL Developer/Toad/whatever, it will not take any less when emitted by OBIEE.

In general: Most performance problems are best tackled at the source - i.e. the actual database. And the means for this are legion: from simple indexing to partitioning stategies, DB parametrization, actual aggregate modelling etc etc.

Hi Chandu,

Check the Execution plan in database whether the tables are using full scan or index scan.

Check whether using cast functions in the front end of the report.

Check whether any null values are going to filter or in metric column, bec null values will take longer time in obiee.

let me know .