I often feel the need of recording my experiences, thoughts and lessons learnt both in work and personal life. It improves your learning process. I believe blogging is a good beginning and here it is my first blog. I thought "performance optimization of BO reports" would be a good start. The disclaimer as always would be that these are all my personal views, experiences and knowledge gained in discussions with BO gurus.
One of the key advantages of a BI solution is the quick response time to open and view a report. The challenge is the huge amount of data that needs to be processed and presented to the end user. A datawarehouse whose database is designed using dimensional modelling and denormalization is one of the key factors in achieving the quick response time to open and view a report. The other key factors are basically how one utilizes and applies the BI solution tool and it's features. From a BO perspective the performance of a BO report depends on the following factors:
1) Universe Design
2) Report Design
3) Server Architecture and Administration
While designing a Universe:
a) Try to avoid usage of derived tables.
b) Create short-cut joins wherever appropriate
c) Apply appropriate aggregate functions on measure objects
d) Apply appropriate value to the table weight parameter.
e) Apply appropriate value to the Array fetch size parameter.
f) Disable the Associate a List of Values property for measure objects and dimension objects that are not used for prompts.
g) Complex calculations for custom objects should as much as possible be accomodated into the ETL design.
While designing a report:
a) Try to use Universe condition objects.
b) Minimize the usage of report filters.
c) Try to maximize the no. of prompts used to restrict the pulling of data records from database.
d) Try to reduce the no. of variables created at report level.
e) Study the execution plan of the SQL generated from the BO query. Identify the tables on which full table scan is happening and implement indexes on appropriate table fields.
f) Implement indexes on the table fields that are being used as condition objects and prompts.
While designing the Server Architecture and Administration:
a) Follow the Bo sizing guide to provide for vertical and horizontal scalability.
b) Identify the reports which do not require real time updates and schedule them for execution in off-peak hours.
c) Set appropriate and optimal values for properties like connection and cache size of various internal servers in BO.
These are a few which I recorded in my learning process. Please feel free to comment and add on the above.
Wish you all a happy, healthy, prosperous and successful new year ahead!!!
