Report design best practices

This topic describes key factors to consider when using Application Studio to write reports over OLAP cubes.

Front end considerations

This table shows key considerations for front end report design:


Consideration	Description
Do your reports contain nested hyperblocks?	A report based on a slice, and where hyperblocks are filtered to, for example, exclude null values, performs better. There is a learning curve associated with using slices and some desirable functions are missing but the available functions meet 80% of use cases.
Do reports require vertical scrolling?	If Yes, then incorporate a paging web extension in your report footers. It improves the user experience and performance.
Do reports make use of conditional formatting?	Conditional formatting is a powerful feature, but use it sparingly because of its effect on performance when conditions are evaluated. You avoid conditional formatting when you use the number format and rely on the number formats, defined in the database. Application Studio clusters the data requests of formulas, which are sent to the OLAP database. The result is one overall query instead of multiple single cell queries. Clustered queries are faster than single cell access. Data read formulas within conditional formats and report jumps cannot be clustered and are always queried as single cell request.
Are the style sheets reports over engineered?	Think about simplicity when developing new style sheets and resist the urge to keep adding content to existing style sheets where possible. The number of styles add to the size of a report.
Have you specified initial selections for report entities?	Reports load faster if the last entity that a user visualized is stored. If a report is loaded at the All Entity level for example, much more data must be fetched and report takes more time to load.
Are you using stateless controls such as Lookup objects, rather than controls based on list views?	Controls based on list views, such as combo boxes, retrieve lists of elements when a report loads. Stateless controls do not retrieve lists of elements, which makes them faster to load.
Do you thoroughly test your reports?	You can build reports which concatenate strings to derive element IDs, but if you send the request to the OLAP server for elements that do not exist, you risk errors and timeouts. Testing should always consider end-user security settings.
List filters	Some filter types are slower than others, for example attribute filters are slower than value filters. Filters in general are slower than structure selections. Avoid large data areas in filters. The more elements you select per dimension, the slower the filter.
Global Actions	Consider using global actions to take calculation time away from single reports. Global actions are run during a user's log-in, after a repository model is created and before any engine is created. Global variables values are already set before any report starts loading.
Report Variable	Avoid large strings in variables.

Back end, environmental, and interpersonal considerations

On successful project there is a feedback loop between the report developers and the authors of the cubes. Bad cube design is probably the biggest factor in poor performance. This table shows important factors to consider:


Consideration	Description
Writing and testing reports with a realistic data set	Reports where dimensions include up to 30 elements, and cubes include several hundred data points can perform well, but are difficult to maintain, because of the large data set. It is important to make tests with realistic data sets.
Writing correct OLAP rules. An example of a poorly written rule: [Revenue] = [Price] * [Quantity] or [Account 1] = [Account2} + [Account3] + [Account4]	Familiarize yourself with the best practices for writing OLAP rules. Refer to the online documentation for guidance.
Dimensions which are badly structured	Consider a product dimension with a top element of All Products, but 10,000 children. At some point the user is likely to trigger a request to the OLAP server that requires all the elements to be retrieved and, potentially, displayed. Intermediate levels make a report based on the hierarchy more performant.
High number of users	Avoid changes to dimensions during office hours, when the peak number of users is online.
Missing parent elements in dimensions, for example, a year total element	A report that retrieves daily data in hidden columns to add up and display as a year total is much slower than one where the total is calculated in the OLAP database.
Misuse of attributes	Attributes can be freely defined and used to filter reports, but filtered reports perform less well than a hierarchy that groups elements by potential attribute values.
Ensure the OLAP server has enough RAM	OLAP is an in-memory database. Poorly written queries can consume a significant amount of RAM. If your OLAP server is paging, you can expect poor performance.
Do not put transactional data in cubes	Infor OLAP is optimized for balance level reporting. You can build a report that is not performant, without being prevented by the application. For example you can build a detailed cube over a Purchase Order line file, but it is likely to be non performant.
Writing a bad report as a short-term fix	Resist project pressure, to do something that creates a non performant report. Put your concerns in writing and suggest alternatives.
Do not store zeros in your cube	A zero is a number that must be stored in OLAP memory and evaluated in rule calculations whereas the alternative of null does not. You can create an Application Engine process that can replace zeros in a cube with nulls.
The best place to do calculations	This depends on multiple factors, including how often the calculated values are required. These are example options of where to do calculations, in order of preference: In the source system As part of a data load process Calculated and stored in OLAP by Application Engine In OLAP using dynamic rules As a calculation (session member) in your repository In Application Studio as part of a report
Set specific default elements	If you do not select specific default elements for hierarchical dimensions, the top element is selected as the default. This requires more time to reset the default elements. Currently default elements are set in the extended properties of the dimension.
The XML interface usage is slower than the communication through the built-in interface in version 11	To speed up communication, we recommend that you do not read values by single-cell requests, but put them into one or few requests to OLAP. This can be done with the Cube Read request, by adding multiple `CellCoordinate` tags.
Use server-side paging to make a report to query only visible values	The paging in hyperblocks pages is on the client side. All cells are returns from the server but only a page is drawn. In OLAP lists use subset function to page on the server side. In relational lists use advanced methods in query to create subsets.
Filter your queries to ensure you do not query all elements from a dimension	Use null suppression to show only filled cells, show all elements under a certain parent instead of a whole dimension. Decrease initial expand level in slices and lists.
Avoid putting big amount of dimensions to axis	Optimal amount is three dimensions per rows axis, one dimension per columns axis.