Business Objects Interview Questions

1. How would you improve the performance of the reports.

Performance of the reports starts with analyzing the problem.
The problem could be with database, Universe or the report itself

Analyzing the database
-Run the SQL from the report on an oracle client like SQL Navigator or toad after passing in all the parameters.
-Identify if the SQL takes considerable less time than the report. If yes then the problem is with the Universe or with the report if no then
-Run an explain plan on the SQL
-Look to see if all the statistics are computed, indexes are built and the indexes are used
-Check to see if aggregate tables can be used ( Aggregate tables are useful if the data can be concised to 1/10th of fact data)
-Check to see if data has increased and usage of materialized views could help.

oCreating materialized views enable to pre -run the complex joins and store the data.
oMost of the DW environment has a day old data hence they don’t have lot of overhead.
oRunning a report against a single materialized table is always faster then running against multiple tables with complex joins.
oIndexes can be created on this materialized view to further increase the performance.
-Check to see if the performance of the SQL can be increased by using hints ,if yes then add a hint to the report SQL and freeze the SQL, this might have an additional overhead of maintaining the report

Analyzing the Universe

- Check is all the outer joins are properly created and remove unnecessary outer join

- Business Objects as such do not use Multi Pass SQL , Multi pass SQL is a technique a software use to break down a complex SQL into multiple smaller SQLs. Hence a query using one fact table and three dimension tables can be broken down into two, one between the dimension tables and the second between the first result and the fact table. This can be achieved in BO by creating Derived Tables. The derived table would be based on three dimension tables and the reports hence can use one derived table and one fact table instead of four tables.

- The Keys tab allows you to define index awareness for an object. Index awareness is the ability to take advantage of the indexes on key columns to speed data retrieval.

o In a typical data warehousing environment surrogate keys are used as primary keys instead of natural keys , this primary key may not be meaningful to the end user but Designer can take advantage of the indexes on key columns to speed data retrieval.
o The only disadvantage is it would not return duplicate data unless the duplicate data has separate keys

- Check to see if the size of the universe has increased recently

oTry to create a different universe for new requirements
oUnder extreme conditions the AUTOPARSE parameter in the param file can be turned off, this could be too risky if not handled properly.

Analyzing the Report
-Check to see if there are any conditions which could be pushed into universe as Filters
-Check to see if a formula has multiple usage ,turn this to a variable
-Check if there are any variables which are not used, remove them.
-Remove any additional filters or alerters on the report.