1. Using Oracle Essbase
  2. Adopt Hybrid Mode for Fast Analytic Processing
  3. Optimize the Cube for Hybrid Mode

Optimize the Cube for Hybrid Mode

To use hybrid mode most effectively:

  • Avoid using two-pass calculation in hybrid mode. Use solve order instead.

  • Convert non-level-0 stored members to Dynamic Calc wherever this is feasible.

  • If the conversion to Dynamic Calc members affects solve order for dependent formulas, you may need to adjust the outline's order of dimensions to align the solve order with the previous batch calculation order and two-pass calc settings.

    The default solve order for hybrid mode cubes is similar to the calculation order of block storage cubes, with some enhancements. If you wish to use a non-default solve order, you can set a custom solve order for dimensions and members.

  • A dynamically calculated formula processed in the wrong solve order can cause too many formulas to be executed by a query, degrading performance. When possible, a dynamically calculated sparse formula should have a higher solve order than hierarchically aggregated sparse dimensions.

    In some applications, this is not possible, as a different solve order is necessary to get the correct formula results. For example, an application with units and prices needs to have a sales value executed before the sparse aggregations in order to get the correct sales value at upper levels.

  • You may need to adjust the dimensions' dense or sparse configurations (applies only to block-storage engine utilization in cases where the hybrid engine cannot be used).

  • Minimize the size of blocks, if possible.

Essbase administrators can use the following tools to monitor and optimize query performance in hybrid mode:

  • To limit how much memory may be consumed by any single query, use the MAXFORMULACACHESIZE configuration setting.

  • If your cube has complex member formulas with cross-dimensional operators and multiple IF/ELSE statements, performance concerns may be related to formula execution. If this is suspected, you can activate bottom-up query processing for formula calculation. This optimizes query times by identifying the required intersections for calculation, making the query time proportional to input data size.

    To make these query optimizations for Release 21C, use the QUERYBOTTOMUP configuration setting, as well as the @QUERYBOTTOMUP calculation function. For Release 19C, use the IGNORECONSTANTS configuration setting with BOTTOMUP syntax, as well as the @NONEMPTYTUPLE calculation function.

  • Use query tracing to monitor and debug query performance. Multiple application-level configuration settings are available, depending on your use case. Use QUERYTRACE for short term debugging of a single query that you think might be problematic. Use TRACE_REPORT for statistics collection about concurrently running queries (ideal for debugging in a development environment). Use LONGQUERYTIMETHRESHOLD in production environments to print statistics to the application log file about any queries that run longer than a set time.