In this article you will find best practices when designing reports to increase overall performance for retrieving and displaying data from BW.
One of the simplest ways to desing a fast running report is by minimizing the amount of data that comes back from BW. This can be accomplished by creating reports where the users specify the data they would like to see. In the case of reports from BW queries by having SAP variables where the user respond to a prompt in order to specify the exact data they want to see. If your report requirements can't be fulfilled wiht simple parameterized reports it is still posible to desing more complex reports that allow users to drive the data they see.
A subreport is an excellent way to include additional data in a main report. Performance issues relating to subreports vary depending on the type of subreport used. Every time a subreport is executed a new connection is established to the database and a new query is executed. So, if a subreport is placed in a group header, and the report has 10 groups, then the 10 separate connections will be made and 10 separate queries will be executed (plus the query for the main report). The use of subreports can seriously impact the performance of a report. Consider using on-demand subreports rather than regular subreports.
An on-demand subreport appears as a hyperlink within the body of the main report. The on-demand subreport will only be executed if the user clicks on the hyperlink. Use on-demand sub-reports to minimize the initial amount of information that is returned in a report. For example, place an on-demand subreport in a group header to allow a user to retrieve additional information related to that specific group. In this example an on-demand subreport that returns information for a particular group is only executed when the user clicks the hyperlink for that particular group. If a regular subreport was used then it would automatically be executed for each group that appears within the report - whether the user is actually interested in the additional information for that group or not
When a subreport is linked, Crystal Reports coordinates the data in the subreport with the matching records in the primary report. If you need to use regular linked subreport (not on-demand subreport):
• If the additional data provided by a regular linked subreport is useful to relatively few users, create a linked on-demand subreport instead. Users who need to see the extra data click a hyperlink to view the subreport; users who don't need to see the extra data won't have to download it from the database server.
• In some cases, placing regular linked subreports in the Details section of a main report may hinder performance---especially when your main report contains many records. (This is because you're creating a separate subreport for each record, and a separate query must be run for each database record in the main report.) As an alternative, consider using linked on-demand subreports in the Details or Group sections of your main report.
Multiple data sources in the same report
Creating reports with more than one datasource can result in slow performance. This is because when you have more than one datasource you are doing a cross data source join, this means that any data filtering trough the join is done on the client side, since Crystal Reports will have to retreive all data from the data soruces first then filter out the data based on the link locally.
If you need to create a report with multiple data sources consider consolidating all the data you need in one data source rather than having two or more. For example if you have multiple BEx queries, consider creating one. If this is not an option you can use subreports, create a main report with one datasource and a linked subreport for the other. With subreports performance will be better as Crystal Reports will send the query for the main report, and then based on the records on the main report, send to the database the query for the subpreport, requesting only the data that matches the record in the main report.
"Page N of M" or "Total Page Count"
If the special fields "Page N of M" or "Total Page Count" are included in a report, the report needs to finish processing entirely before it can calculate this value and display the first page. If these fields are not used the first page of the report can often be displayed while the rest of the report is still being processed. Avoid using these special fields in your report unless the report is very small or you absolutely need this data in the report.
Live Data vs Saved Data
Live Data (On-Demand)
Live reporting gives users real-time access to live data, straight from the database server. Use live data to keep users up-to-date on constantly changing data, so they can access information that's accurate to the second. For instance, if the managers of a large distribution center need to keep track of inventory shipped on a continual basis, then live reporting is the way to give them the information they need.
Crystal Reports supports live reporting. However, you should first consider whether or not you want all of your users hitting the database server on a continual basis. If the data isn't rapidly or constantly changing, then all those requests to the database do little more than increase network traffic and consume server resources. In such cases, you may prefer to use reports with saved data.
Saved Data (Historical Instances)
Reports with saved data are useful for dealing with data that isn't continually updated. When users navigate through reports with saved data, and drill down for details on columns or charts, they don't access the database server directly; instead, they access the saved data. Consequently, reports with saved data not only minimize data transfer over the network, but also lighten the database server's workload.
You can schedule these reports within BusinessObjects Enterprise, so they automatically refresh from the database on a predetermined basis. For example, if your sales database is only updated once a day, or once a week, then you can run the report on a similar schedule and save it with data. Sales representatives then always have access to current sales data, but they aren't hitting the database every time they open a report. Alternatively, you can refresh reports with saved data on an as-needed basis.
Additional guidelines for reports based on BW Queries
In order to serve as a data source and become available through the OLAP interface to Crystal Reports, BEx Queries must be released for OLE DB for OLAP. You allow external access to the BEx Query in the BEx Query Designer, on the Extended tab of the Query Properties dialog box. The option in BEx Query Designer is "Allow External Access to this Query".
- Crystal Reports and BEx are 2 different tools and handle BW Queries differently, so you should design your BW Queries with Crystal Reports in mind and not BEx.
- Maximize filters and restrictions in the BEx query, in order to reduce the datasets returned to Crystal Reports
- Design your BEx query appropriately with limiting number of KeyFigures and Characteristics.
- Consider if your really need navigational attributes in your BW query and design them if possible as display attributes. Usage of navigational attributes leads to additional joins at BW level and therefore reduces performance.
- Let Crystal Reports handle the formatting needs of the report output. You can do all the formating more efficienty in Crystal Reports.
SAP variables vs Crystal Parameters
When looking into reducing the dataset, besides filters and restrictions, you may want the users to select which data they want. This can be accomplished with SAP variables or Crystal Parameters. When deciding which one to use you should understand where the filtering of records takes place as this affects performance.
With a variable defined in BW all filtering happens in BW. If you instead have a Crystal Parameter all the records are returned to Crystal Reports and the filtering happens within Crystal Reports, so it is a best practice to create SAP variables rather than Crystal Reports parameters.
Time spend in BW
When looking at the time it takes for the data to come back to Crystal Reports consider the following:
- Where is the data comming from InfoProvider vs MultiProvider? MultiProviders have additional joins which usually affect performance.
- Is your query written against a remote cube or virtual Infocubes? These cubes can be stored anywhere (i.e another BW system, a third party system) this adds time to accesing the data.
- Are you reporting from ODS? Make sure to use Crystal Reports ODS driver that allows you to connect directly to ODS.
- Have you applied filters when possible? Applying filters reduces the dataset returned and processing time
- Have you created efficient BW aggregates? Aggregates optimize the performance of the query.
- Compression of data requests at BW side should be performed regularly
- Check the flag USE SELECTION OF STRUCTURE ELEMENTS available for all BW query in transaction RSRT.
- Performance tunning within the BW system itself affects reporting perfromance.