Performance Tuning in OBIEE

The first thing you should start with is analyzing the execution plans of your queries to see where most of the cost is spent.

From OBIEE side, you can think about using BI server cache and presentation server cache.
- BI server cache : http://download.oracle.com/docs/cd/E12096_01/books/admintool/admintool_QueryCaching2.html
- BI Presentation cache : http://download.oracle.com/docs/cd/E12096_01/books/AnyWebAdm/AnyWebAdm_CustAnyWeb19.html

Use of aggregate tables: Tables that contain data at different level of aggregation

Usually the time that the queries take to run is spent on the database so to improve performances you can think about:

- use of indexes
- correct sizing of temp table space (very important for sorts and groupings)
- use of materialized views (query rewrite)
- use of hints (star transformation) if you are using a star schema


Performance tuning from OBIEE perspective can be 2 to 3 types.Decide on what you wanna go with
1) Check the SQL's generated and the realted joins are correct and run them at the backend DB and check the time taken.
2) In the SQL if some table has huge data or due to that table/column it is taking lot of time then use Hints in the physical layer of the repository(if you have knowledge abt them u can go ahead and do it,If not take ur DBA help)
3) One is ibot seeding i.e. scheduling the report nightly basis...so when the user comes in the morning he sees data in a go ( for your scenrio i dont think this is required cz it is taking 80-100sec only)

Until Next Post ... :)