Report Design Best Practices

Limit the number of Data Providers Used

Don not add more than 10 Data Providers per document.
Data Providers run serially, so runtimes add up
Refresh time and merging of dimensions can cause some big delays on the Processing Server side

Using a Data Warehouse to consolidate sources and ETL tools to produce better reporting sources is a better practice.

Retrieve aggregated data instead of aggregation within the document

Retrieve as much as possible pre-aggregated data from the data source meeting the business requirements directly from the consumer. Retrieving detail data while (high level) aggregation is required within the document, is considered a bad practice. Although Web Intelligence is very capable of aggregating data, your data source is stronger in these tasks.

If the required aggregation is not pre-calculated available, leverage aggregation commands in the query definition. This will request the source database to aggregate data before sending this back to the Web Intelligence Processing engines.

Do not (accidentally) disable the cache mechanism of Web Intelligence

Web Intelligence has a great caching mechanism for documents that have already been viewed. Using Cache improves the load time of documents, however there are a few functions within Web Intelligence that will prevent the use of the Cache. These functions are:
CurrentTime()
CurrentDate()
UserName()

The use of these functions will require documents to be regenerated every request, and by this bypassing the benefits of caching.

Avoid Auto-Fit When possible

Although Auto-Fit option for cells, tables, cross-tabs and charts is a wonderful option to have blocks being nicely adopting the size of the content on the fly, It also forces the document to be calculated during navigation. This will make navigating a report much slower. The maximum impact in slowing down the navigation is when jumping from the last page to the first page within a large report

Avoid Charts with a high number of Data Points

Within BI4.x a new charting engine has been added, the Common Visualization Object Model (CVOM). This engine is hosted within the Adaptive Processing Server (APS) as the Visualization Service.

CVOM enables you to generate compelling charts within the Web Intelligence Document, however is better at creating a large number of smaller charts than creating ones with many data points. It would be more efficient to use smaller, more specific charts than big ones.

Avoid Nested Sections

Nested Sections can contribute to performance degradation. This is especially true if conditions are used such as "Hide Section when following are empty"

Test Query Drill for Drill Down Reports

Within the report properties there is an option to enable the use of query drilling. The function Query Drill will leverage the performance of the underlying database instead of local data. If the Query Drill is not enabled, the query will load more and more data within the document. Once enable, a Drill request will modify the underlying query and fetches new data from the datasource.  By this process the amount of data stored locally for a drill session and could be beneficial for the performance of the document.

It is recommended to validate a report with both options to ensure Query Drill will provide benefits (as this is depending on the underlying database)

Limit use of "Scope of Analysis"

Scope of Analysis can support consumers of the document with an easy Drill Session. However once Scope of Analysis is enabled and defined, extra data will be retrieved from the database and stored in the cube of the document. Loading more data, will have a negative impact on performance.

Instead of using the Scope of Analysis / Drilling, Report Linking could be used as an alternative for on-demand data fetch of details. The advantage with Report Linking is that the detailed report will only fetch the required detail data (versus the whole set using drill)

Tip: Within the BI Launchpad Preferences of Web Intelligence, you can specify if a users is being prompted when drilling needs more data!

Formula Best Practices for Performance

The Web Intelligence Formula Engine has strong capabilities, however is depending on logic. Some statements in formula's will always cause a calculation over the whole data sets, while breaking down calculations into multiple steps (factorizing) will help the calculation engine working faster. Below known impactors for the Calculation Engine are recorded.

The Formula statements "ForEach"and "ForAll" should only be used when really necessary. It is recommended to use the "In" statement instead.
Where operator can also take longer to process documents behind the scenes. Using "If.. Then.. Else" may be better.
Factorizing variables reduces overhead for the calculation engine. for example
v_Sales = [MeasureA] + [MeasureB]
v_SalesEst = [v_Sales] + [MeasureC]

versus

v_SalesEst = [MeasureA] + [MeasureB] + [MeasureC]

By breaking down the number of steps, the calculation engine is faster in processing the results.

Other Blogs:

SAP Business Objects Business Intelligence
Universe Designing

SAP Design Studio

Big Data Concepts

Project Support:

If you are looking for consulting or project support drop an email.