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.
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 items and dimensions that are
added, the greater is the risk for bad performance due to that the Oracle
optimizer might have a problem finding the best execution path.
Similar problems may appear when building a Data Warehouse or a MS Cube where a Star Schema query is built using one or more Information Sources. The time it takes to fetch the transactions via such a query can then be rather time consuming.
So what are the remedies? Here is a list of suggestions.
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:
A possible measure when it comes to performance tuning is to switch from On Line to Data Mart access.
This gives good opportunities to add performance tuning indexes depending on how the customer is accessing the information.
The general trick is to reduce the number of transactions handled. This can be done in the following ways:
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.
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.
If the Data Mart source type Incremental is used, this means that transactions are moved from core table via a view definition to an incremental snapshot table.
By default all transactions will be considered, especially when activating the entity (fact or dimension), i.e. when the first snapshot is created. 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.
For more information, read about Incremental Load Development
When building a Data Warehouse of a MS Cube, it is recommended to access Information Source information via so called Access Views. It is of course also possible to build an Information Source based on a Access View. 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:
Below follows some general tips have to handle design and performance of IFS Business Reporter 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 gives the designer an idea about the following:
SELECT?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 report in batch mode. There are two possibilities:
Learn more about how to performance trace and tune IFS Business Reporter reports.