If you use a complex formula, you can improve performance by applying the following guidelines:
If possible, apply the formula to a member in a dense dimension.
Use the FIX command in a calculation script to calculate only required data blocks. See Using the FIX Command.
Increase the density of the database (ratio of existing data blocks to possible data blocks).
A complex formula is one that meets any of the following requirements:
References a member or members in a different dimension (sparse or dense); for example, Product -> Jan.
Uses one or more range functions, for example, @AVGRANGE, @MAXRANGE, @MINRANGE, or @SUMRANGE.
Uses relationship or financial functions; for example, @ANCESTVAL, @NEXT, @PARENTVAL, @SHIFT, @ACCUM, or @GROWTH. For a complete list of relationship and financial functions, see the Oracle Essbase Technical Reference.
When applied to sparse dimension members, complex formulas create more calculation overhead and therefore slow performance. This problem occurs because the presence of complex formulas requires Essbase to perform calculations on all possible as well as all existing data blocks related to the member with the complex formula. The presence of a relationship or financial function on a sparse dimension member causes Essbase to perform calculations on all blocks, possible as well as existing, increasing the overhead even more.
Thus, a complex formula that includes a relationship or financial function creates a greater overhead increase than does a complex formula that does not include a relationship or financial function.
For a discussion about how complex formulas affect calculation performance, see Bottom-Up and Top-Down Calculation.
Two examples illustrate complex formula overhead:
If a database has 90 existing data blocks and 100 potential data blocks, the overhead for complex formulas is not large, not more than 10 extra blocks to read and possibly write values to.
If a database has 10 existing data blocks and 100 potential data blocks, the overhead is as much as ten times what it would be without the complex formula (depending on the outline structure and other factors), as many as 90 extra blocks to read and possibly write to.
In all cases, the lower the ratio of existing data blocks to possible data blocks, the higher the calculation performance overhead and the slower the performance.