IFS Business Reporter - Performance Related
This page provides some information on how to investigate performance issues related to IFS Business Reporter (BR) report execution.
Experiencing bad performance happens either during the design phase or when reports are executed by end users. In both cases, one obvious option is to investigate the design. This is done by a BR designer that needs to have access to the correct version of IFS Business Reporter. For some general design guidelines, please refer to the High Level Design Guide.
Another or complementary option is to investigate number of involved data sets and the performance for each data set.
If the report is already published to IFS Reporting or published for BR Access Only, it is possible to find out how long time it took for the latest execution of the report. For his purpose, use the BR Report Level Execution Parameters page in Aurena client. The same approach also applies even if the report has not been published yet. If e.g. bad performance is experienced during the design phase, published the report for BR Access Only, before investigating execution details.
Investigation Data Set and Timings
Execution of a published BR report leads to that data set information will be made available in the BR Report Level Execution Parameters page.
There are two types of publishing:
- Publishing a BR report for BR Access Only
- Executing the report in Report Mode in BR using Order Report.
- Publishing for end user access
- Executing the report in Report Mode in BR using Order Report.
- Execution via Order Report page in Aurena, either online or scheduled.
One example is given below. Execution of a published report is performed am then the BR Report Level Execution Parameters page is opened and the report is selected.
The page can be used to find out:
- The total execution time for the report
- Number of executed server data sets
- The identity of each server data set
- Time taken to execute a single data set
It is now possible to find the data sets that consumes most of the total execution time. The associated SQL statements can be investigated and it is possible to do some server side parameter settings that might improve the performance. This is described more in detail in the section about BR Execution Parameters.
Many times it will however be necessary to also investigate the design of the related IFS Business Reporter report, since the design might cause the performance problem. If the server data sets that consumes most of execution time are known, the Data Set ID in the BR Report Level Execution Parameters page is useful, since it can be used to find the associated client data sets and and the corresponding design.
Taking a closer look at the corresponding IFS Business Reporter report.
At a first glipmse the design seem to describe a standard case with one design row and one design column that will lead to generation of one server data set.
However, we could see i the server trace taht there were 3 data sets.
To figure out more about involved data sets, it is necessary to enable display of data sets. This is done in Design Mode before running the report by in the IFS Business Reporter ribbon click on Report Options and then on Debug and then select the option Display Dataset Info Before Executing.
Click OK to save the settings.
When executing the IFS Business Reporter report, after supplying necessary input parameters, the dialog Data Set Information is displayed:
The dialog shows the data sets to be sent to the server and for each data set the corresponding client data sets are displayed via the Design row references of server sets grid. Thus, it is possible to figure out how many client data sets that are merged into each server data set and also the origin of each client data set in the design.
Click Cancel to avoid executing the report.
Note: If a BR report has been executed then it is recommended to use the Cached Data option when it is executed the next time, since it leads to that only server data sets related to modified client data sets will be executed. Caching of data only applies in Design Mode.
If a IFS Business Reporter report has bad performance it is of interest to figure out what part of the design that leads to the slow execution. As mentioned before it is possible to find the execution time for each server data set in the BR Report Level Execution Parameters page. This however only works if it is a published report. For a non-published report it will be up to the designer to investigate the performance. The designer still has the possibility to publish the report for BR Access Only but in order to get information about the execution time, the report must be executed at least once and that process can be time consuming in some cases. The option is to start investigating the performance in IFS Business Reporter by using the following options:
- Disable Design Sheets
- Disable Server Datasets
If the report has many Design Sheets, one approach is to disable all sheets except one and then execute the report. If the report execution time is acceptable, then disable the sheet and enable another one and then test again until the most time consuming sheet has been found. Of course it might also be so that all sheets require a fairly equal execution time and then it is the sum of the time for all design sheets that is the problem.
The other possibility is to disable server datasets via the dialog Data Set Information in order to find the most time consuming dataset.
In the following example one out of three server data sets in a IFS Business Reporter report has been disabled and thus only two server data set will be executed.
Note: The report design does not have to be modified.
If the report option Display Dataset Info Before Executing has been defined in Design Mode it leads to that the DReport Mode. So once the report has been executed it is possible to find out the timings for each data set by clicking on the Data Set Info button in the options page.
Once the most time consuming part of the investigated report has been found, then focus on this part and try to find a suitable solution.
The best solution might be different from case to case, i.e.:
- Consider changing the report design to reduce number of server data sets
- Consider general performance tips
- Consider general IFS Business Reporter client tips
- High level BR Design Guidelines
- Investigate server SQL statements and try using the BR Report Level Execution Parameters page in Aurena client. For more information read about BR Execution Parameters.
- Introduce new indexes.
- This is preferrably made in the Data Mart, i.e. adding indexes to Materialized Views or incremental tables.
- Adding index in core table is a risk, since new indexes might affect ordinary core functionality in an unwanted way.
- If the Data Mart storage gets new indexes, also make sure that Data Mart is used as access type for reports with problems.