Performance is always a bone of contention in the BI reporting space. BI Developers have many avenues to fine-tune their 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 the back end’. Logically, pushing resource-intensive tasks to the 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 do not allow all types of processing logic to be pushed to the ‘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 the database
- Report calculations are computed in the application
- Formatting is applied
- User interaction is managed (Slice & Dice)
Understanding Report Execution Sequence
Although the sequence of activities is simple, one has to spend time in understanding the activities behind the scenes for the toolset in the user. SAP Business Objects Crystal Reports is famous for its ‘Multi pass technique’, if utilized correctly can help developers to fine-tune the 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 tools 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.
Tool | Description |
---|
Tool | Description |
---|---|
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 the 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. |