Best practices to improve performance

This section describes best practices which improve performance.

Rewrite rules to produce a limited number of results.

A calculation of an area where a rule calculates many values can take a significant amount of memory, even more than is available on the computer. This slows down overall performance.

You can write rules in a form that produces cell values. For example:

['Number of Actual values']
=
if(
   ['Actual'] = 0,
   0,
   1
)

This example produces on each cell a value, either 0 or 1. While the 1 values are necessary for the calculation, the 0 values get created as well and OLAP calculates with them. But as a cell with 0 has no meaning in most of the cases, this wastes processor and memory resources and can lead to very slow calculations.

This is an example of a better rule:

['Number of Actual values']
=
if(
   ['Actual'] = 0,
   #NA,
   1
)

This example produces an empty result on those cells. There is no value that OLAP cares about in aggregations.

Use mass data calculation

The pre-fetching Rules Engine internally retrieves the data for calculation in big blocks. If values can be reused, then they are retrieved only once. For example, currency conversion factors are the same for all products. Therefore, it is important to use mass operations instead of single cell operations.

There is a second reason to use mass operations over single cell operations. When working in a network environment, each single cell request travels on its own over the network. In the case of 100 cells, the network latency between the client and the server is multiplied 100 times. With mass operations, network latency has only minimal effect. For example:

  • Use CellGetC or MDXExecute instead of CellGet.
  • Use ad-hoc reports or Alea ad-hoc reports.
  • Do not loop and retrieve single cell values. Instead, create a data area and loop over its results.

Monitor memory consumption

There might be peaks in the memory consumption based on the calculations of the user. We recommend that you monitor the virtual bytes and their peak on the MIS.Alea.Server64 process.

The performance counters might be named differently in different versions of the Windows operating system.

You can influence the memory needed in the calculation.

Rule debugging support

You can retrieve information on how a given cell is calculated through cell notes. If a cell note contains the content "value?", then calculation information is returned in the cell note. Do not add any other characters or new lines when you specify "value?".

On a consolidated cell, the information may look like this: 

value?
---Cell Coordinates -------------------------------
YEARS	2002
ACTVSBUD	Actual
REGIONS	World
PRODUCTS	Total
MONTHS	Year
MEASURES	Units
Internal Key:	1	1	34	61	13	1
External Key:	1	1	1	1	1	1
---------------------------------------------------------
Cell Type: Calculated
Consolidated by:
REGIONS:'World' 33 - Base Descendants
PRODUCTS:'Total' 60 - Base Descendants
MONTHS:'Year' 12 - Base Descendants
Total consolidated cells: 23760

The first part gives you the coordinates of the cell, with the dimension and element names. It also contains an internal and an external key. The external key relates to the ID of the element in the dimension.

The second part indicates that this is a calculated element. It also states from how many base descendants it is calculated. This cell value is consolidated from 23760 base cells.

For a cell calculated by rule, the information has some differences: 

value?
---Cell Coordinates -------------------------------
YEARS	2002
ACTVSBUD	Actual
REGIONS	World
PRODUCTS	Total
MONTHS	Year
MEASURES	Average price
Internal Key:	1	1	34	61	13	2
External Key:	1	1	1	1	1	2
---------------------------------------------------------
Cell Type: Calculated
Rule: ['Average price']=['Sales']/['Units'];
---Optimized Rule References-----------------------
['Sales']
['Units']
---------------------------------------------------------

Again, the second part indicates that the value is calculated, this time by a rule. The rule is displayed.

Performance testing

An important part of improving the performance of your rules is to implement reliable performance measurement. Only if this is available can the performance or the improvements be judged. These are some of the steps in performance testing:

  • Make the performance reproducible.

    Ensure that you always measure performance by using the same report with the same selections, running under the same user credentials. This is the most important step.

  • Ensure that the values are not cached.

    If the values were calculated previously, then they are cached, and the server responds much faster. Consider emptying the cache before you do the measurement. But do not disable the cache, as this will change the calculation behavior.

  • Identify relevant rules for the report.

    To work on the correct rule, use the "value?" functionality to understand how a value is calculated. Sometimes it helps to disable rules entirely to understand if they have a significant effect on the calculation time.

  • Record implemented changes and their effects.

    This helps to understand how your changes affect performance.

  • Implement one change after another.

    By implementing the changes one by one and testing their effects, you can understand the results of the changes.

  • Check the results.

    After changing your rules, compare the results on your report with the calculation from the original rules to ensure that you have not accidentally introduced an error into the rule definition.

  • Be patient.

    Performance testing can take some time. The more experience you gain, the easier it is.

Related topics