Tips and Tricks for IFS Business Reporter Designers and Developers¶
The purpose with this page is to supply some useful Tips and Tricks for developers of Information Sources as well as for IFS Business Reporter designers.
Performance - General¶
Using Information Sources for reporting purposes is a good choice due to the nicely packaged information. Connected dimensions enrich the reporting possibilities by adding lots of items that can be used for grouping/categorization or other presentation purposes. The backside of an Information Source with many connecting dimensions is the risk for increasing complexity. If e.g. an Information Source has 15 connected dimensions and one item is selected from each one of these dimensions along with measure items from the fact part of the Information Source, then the query that has to be built will contain one join for each one of the connected dimensions. When it comes to IFS Business Reporter, this means that a
SELECT statement in Oracle will be created with many joins and maybe also many selected items. The more sources and items that are part of the query, the greater the risk is for bad performance.
What are the remedies? Here is a list of suggestions.
On Line Access¶
If On Line access is used, it is rather likely that performance problems will appear sooner or later. If it is still necessary to use On Line access, some measures must be taken to reduce the complexity or the amount of involved transactions. Some possibilities are:
- Build new IFS Cloud functionality where the business logic creates grouped information and then create detail Information Sources based on this grouped data. The original Information Source. that represents the detail information, could then act as a Drill Down source to the new aggregated source.
- Open up joiners in the Information Source as visible items, enabling access directly on the Information Source (Fact part) instead of using dimensions. Another possibility is to add more items to the Information Source that replaces the dimension items. The flexibility might be reduced but on the other hand the number of joins can be reduced.
- Create snapshot functionality where as much information as possible is gathered in new snapshot tables. Build Information Sources based on the new snapshot tables. Creating new tables means that information can be collected from different sources and stored into one source table. A snapshot has the advantage that it contains calculated data instead of calculating it via the On Line view at execution time.
- New solutions as grouped tables or snapshot tables gives the flexibility to create performance indexes, something that is much more difficult in existing core tables due to the risk of introducing performance problems in other parts of the system.
Switch from On Line access to Data Mart access¶
A possible measure when it comes to performance tuning is to switch from On Line to Data Mart access.
- Data Mart access also means that a snapshot table is created. This gives good opportunities to add performance tuning indexes depending on how the customer is accessing the information.
Still Data Mart access can be rather costly. The general trick is to reduce the number of transactions handled. This can be done in the following ways:
- Data Filter for Materialized Views In order to reduce the number transactions stored in a Materialized View, one possibility is to introduce Materialized View filters. A typical implementation is to only include transactions from one specific date or year and onwards. How to implement this is described in the development documentation for Data Mart access.
- MV as source for another MV A rather nice possibility is to use the Materialized View concept to aggregate information from one Materialized View to another and so on. Then we get a chain of Materialized Views with different degree of aggregated information. These Materialized Views can then be used as sources for creating new Information Sources. By aggregating the data from the beginning, the access can be much more efficient than just building Materialized Views that more or less only mirrors the information in ordinary transaction tables.
- Reducing Transactions in Incremental Load Table If the Data Mart source type Incremental is used, this means that transactions are moved from a core table via a view definition to an incremental snapshot table. When activating the entity, i.e. when the first snapshot is created, all transaction all transactions will be considered. It is however rather easy to add filter conditions that reduces the number of transactions considered, e.g. only transaction created the last 2-3 years are of interest. Once the initial refresh has been done, subsequent refresh will only consider new or modified transactions. For more information, read about Incremental Load Development
Using Access Views¶
When creating a Tabular Model, it is recommended to access Information Source information via Access Views. The Access Views can then represent either Data Mart or On Line access depending on access type used when creating the views. There will however still be a risk that the number of transactions are too many, affecting the access performance. The natural way here is to reduce number of transactions. This can be done automatically by defining filters on the Access Views that are related to Information Sources. For more information, please refer to:
IFS Business Reporter Client Considerations¶
Below follows some general tips have to handle design and performance of IFS Business Reporter reports.
Reduce size/complexity of reports¶
Try not to create too large reports with huge amount of design. Both execution performance as well as maintenance might be affected. One possibility is to split the design into separate reports. The more measure item cells with different design instructions, the more
SELECT statement will have to be executed.
The Information Source navigator in IFS Business Reporter has performance indicators that gives the designer an idea about the following:
- Is an item indexed?
- Is an item derived, i.e. is the item rerieved by running a function call or a sub
- Is there an index definition covering one or more of the columns used to connect a fact with a dimension?
Try to keep common design criteria on as a high level as possible. This also applies to Advanced Filter Criteria, i.e. common criteria should be defined on highest possible level since it enables the IFS Business Analytics client to merge data sets and thus reducing the number of data sets sent to the server.
The repeater concept in IFS Business Reporter is rather powerful and should be used instead of repeating the design. Please note that the more items that are part of a repeater the, lager is the risk for bad client performance. This is more or less related to the size of the key matrix that has to be built by the client. So try to keep the number of repeater items low.
Also use sheet repeaters if the purpose is to create reports with the same design that has to be repeated on one specific item. A typical case is to present some financial result per cost center. Then instead of creating on design sheet per cost center it is much smarter to use a sheet repeater.
Running report manually via IFS Business Reporter is of course possible but the recommendation is to only do that during the design phase. The recommended way is to run reports in batch mode. There are two possibilities:
- Schedule published IFS Business Reporter reports in IFS Cloud Web without using IFS Business Reporter Execution Server. The data processing will be done in the server but the Excel/BR reports will not be rendered on the server side. This means that when the report is opened, the rendering will take place and that can be time consuming.
- Install, configure and startup IFS Business Reporter Execution Server (BRES) and then schedule published IFS Business Reporter reports in IFS Cloud Web. The benefit with this solution is that the BR reports will be completely rendered after being processed by BRES. Thus, if a report is scheduled to run early in the morning, then when opening the report a few hours later it will be completely ready and the startup time is very short. It is also possible to specify that the executed report should not contain any links to IFS Business Reporter, i.e. the executed report is an ordinary Excel report. This is suitable when the receiver of the report does not necessarily have IFS Business Reporter installed.
In addition to the above points, IFS Business Reporter designers can follow the tips given in this document to optimize the performance of IFS Business Reporter. This document contains some useful tips to improve the performance of Microsoft Excel as well. These tips would indirectly improve the performance of IFS Business Reporter.
Tracing Performance Problems in IFS Business Reporter Reports¶
Please use the following link >> to learn more how to trace/investigate performance issues related to IFS Business Reporter.