OBIEE reports performance

OBIEE Reports performance

I am assuming you are using a data warehouse (DWH) environment for the reports, rather than a live OLTP.

Now in normal circumstances, Bitmap indexes are best suited for DWH because there is huge amounts of data to handle.
Bitmap indexes work best where the change in data (transactions) is slow. Bitmap indexes consume very little space too, compared to the common b-tree index. And with tables containing millions, billions, or more rows of data, this becomes an important aspect.
A common assumption also says that bitmap works best with columns having lesser distinct values or cardinality. But it is best to look for the amount of data updated from concurrent systems. If that's low, always use bitmap.

Remember that we talk about indexes when we mean performance tuning of the database or DWH.

Best practices include - 

Using star schema model wherever possible. Snow-flaking affects performance. So if you can convert a snow flake to star by using some alias tables or multiple LTS, go for it.

Using aggregate tables wherever possible. You probably won't be able to create aggregate tables for every possible combination of levels in all the heirarchies, but if you check your usage statistics you'll see which are the most common (or longest running) queries and you can plan accordingly.

Avoid excessive use of functions like cast, nvl, lpad, rpad, trim etc. or push them to the ETL phase. Functions may kill the indexes and remember that every foreign key of your tables would also be acting as an index in the star or snowflake schema. Actually unless you build function based indexes, assume that functions in join conditions will destroy performance.

Avoiding unnecessary calculations or again push them to the ETL phase if possible. Get a proper DWH built by the ETL team, in accordance to the reporting requirements.

Using filter functions instead of case when statements. Filter functions include an internal where clause which helps them to use less data for comparison and hence perform better.

Using Union All instead of Union wherever possible.

Using materialized views (for query rewrite) when some complex queries are needed. Assuming that your database is setup to allow query rewrite, materialized views can significantly improve performance, without rework in the repository or front-end. Remember as always, that this is a band-aid solution and at some stage you will need to migrate your repository to use the materialized view, and materialized views bring their own complications and administration.

Some of the NQSConfig.ini parameters may also be altered for a better tuning.

And always check the NQQuery.log file to see the final query that is being executed for a report. That may also tell you what alterations might be needed for a better performance.

In some cases, you are probably looking to run a few reports faster which are taking some overly long time to open. So the best/quickest suggestion would be to use a proper cache mechanism. There are various ways in which you can manage the cache for your reports to run faster. The easiest way to pre-fill the cache with the report would be to have an agent 'run' the report into the cache, thus seeding the cache for the following users.