Differenct ways to do performance tuning in OBIEE 11g

Below are the best practices to better performance of your OBIEE system.

• Aggregation

• Fragmentation

• Tend to star schema in physical model only

• Use hints in physical layer of the repository

• Avoid complex joins, opaque views, etc. Use an opaque view only if there is no other solution to your data model design.

• Do not enable log level (at least not in production) except Administrator role. Admin user can act as normal user o run the same query for diagnose  purpose.

• Do not have complicated formuleas for measures in Answers/ad-hoc reports. Do it from DB end or at max in BMM layer itself.

• If possible implement data level security to lower the data set pulled from DB

• Various configuration options can be used to limit the maximum number of rows that can be downloaded, processed, rendered, included in mail, etc.

• FILTER function in Edit Formula is always cost-saving that CASE statement.

• In connection pool, set the number of "Maximum Connestions" parameter very carefully.

• In case of huge data export, consider an option of ODBC or JDBC call into a BI server.

• Try to use cache seeding wherever possible. (Use EPT to purge stale data)

• If a number of users are huge, clustered environment is a must. (Vertical clustering)

• If users are from across the globe, load balancing with clustered environment should be there. (Horizontal clustering)

• Allocate more memory to JVM. Minimum heap and Maximum heap size parameters.

• Set the no. of elements for each dimesnion level

• Use the where clause in LTS if possible

• Reconfirm that aggregate/fragmented tables are getting used properly

• Set the Usage Tracking to get the usage, load, peak time of your system. (Usage tracking should be switched off if not needed as it will avoid extra DB Operations that run against each query.)

• Apply index on column from DB side only

• Consult with the DBA’s of your environment to improve performance from your DB end.

• Configure correct hardware for OBIEE and DB server according to Oracle’s recommendation.

• Get long running physical queries, check explain plan and make change accordingly to DB tables or from OBIEE.

Happy blogging.. :-)