Performance is always a bone of contention in BI reporting space. BI Developers have many avenues to fine tune the performance, but always should undertake a holistic approach by improving both back end and front end options. When it comes to performance optimization, the golden rule always seems to be “delegate to back end“. Logically, pushing resource intensive tasks to back end is a novel idea, but it is practically not plausible to implement for all scenarios. There are either functional or technical constraints that simply does not allow all types of processing logic to be pushed to “back end”.
A key feature to be understood in all BI reporting tools is the sequence of events that happen when a report is launched. In simple terms, the stages of execution are
- Data is fetched from database
- Report calculations are computed in application
- Formatting is applied
- User interaction is managed (Slice & Dice)
Although the sequence of activities are simple, one has to spend time in understanding the activities behind the scenes for the tool set in user. SAP Business Objects Crystal Reports is famous for its “Multi pass technique”, if utilized correctly can help developers to fine tune performance of a report as well as address many different development bottlenecks.
The list below gives quick look into different strategies employed by different reporting tool and one can observe that tools almost follow a scripted method of execution, but each tool has its own variation when it comes to actual implementation
|SAP Business Objects Crystal Reports||Multi Pass technique has four phases of processing the data. This different phases happen before and during data fetch from database and one can explicitly force computations to be performed at different levels.|
|Microstrategy||Multiple SQL statements are generated to fetch data. Intermediate processing data might be materialized into temporary tables and dropped later.|
|SAP Business Objects Web Intelligence||Depending on complexity of Universe structure, SQL is generated and data is fetched. Report level logic is processed in various processing servers. Multiple round trips to database can happen.|
|Cognos||A series of SQL statements are generated based on report design. All custom logic is processed in the processing server.|
|SAP Business Explorer||Single SQL is used to fetch data into OLAP engine. All calculations are performed in OLAP engine.|
- Database Delegation – Click Here