Hybrid Aggregation Mode in Block Storage Databases
A new Essbase configuration setting, ASODYNAMICAGGINBSO, controls whether block storage databases use hybrid aggregation mode. Hybrid aggregation for block storage databases means that wherever possible, block storage data calculation executes with efficiency similar to that of aggregate storage databases.
The following are some scenarios where hybrid aggregation is highly likely to improve calculation performance:
- A block storage database has stored members that are not level 0, and are calculated according to hierarchy (rather than by calculation scripts).
- A Dynamic Calc member has more than 100 children.
- You are using a transparent partition between an empty aggregate storage target and a block storage source. If the formulas on the aggregate storage target are simple and translatable to block storage formula language, you can achieve fast results on block storage using hybrid aggregation.
- You are using a transparent partition between two block storage databases, and calculation performance is a concern.
Outline Structure for Hybrid Aggregation
To use hybrid aggregation most effectively:
- If there are non-level-0 stored members that are batch calculated based solely on their hierarchy, it’s recommended that you convert them to Dynamic Calc members.
- If the conversion to Dynamic Calc members affects solve order for dependent formulas, you may also need to adjust the outline’s order of dimensions, and/or their dense or sparse configurations, to align the solve order with the previous batch calculation order.
Faster Queries for MDX Aggregate and Sum Functions
In aggregate storage databases, performance is improved for MDX queries containing the Aggregate or Sum functions. Essbase performs dependency analysis and uses a formula cache to execute these requests dynamically.
Using FIXPARALLEL Parallel Calculation
Overview of FIXPARALLEL
Although parallel calculation can be performed using the CALCPARALLEL configuration setting, in certain cases it might be beneficial to use the FIXPARALLEL command block method.
In a FIXPARALLEL command block, you input some commands to be executed, along with a number of threads (numThreads) and a member list (mbrList) specifying the database regions (slices) to be calculated. Essbase creates a list of tasks from the combinations in the member list, and divides the tasks across the threads.
The FIXPARALLEL method can be advantageous in the following cases:
- If you need to use temporary variables during parallel calculation
- If you need to use the DATACOPY, DATAEXPORT, or CLEARBLOCK commands
- In conjunction with the @XREF or @XWRITE functions (You can now also use these with CALCPARALLEL; see SET FORCEPARALLELCALC)
- If you need to export regions of the database in parallel. See the Example in this topic.
- In cases where CALCPARALLEL is not meeting performance requirements, and your outline generates many empty tasks, or contains many task groupings with fewer tasks than threads made available to the calculation. See also “Task Selection Comparison of FIXPARALLEL and CALCPARALLEL.”
When considering converting FIX statements to FIXPARALLEL within a calculation script, follow these guidelines:
- Focus on FIX statements that do not meet your performance needs using CALCPARALLEL.
- Focus on FIX statements that require a substantial amount of work. Parallelizing a FIX statement requires some overhead, so trying to parallelize calculation passes with light workloads may not be beneficial. Heavier workloads, such as AGG and CALC DIM, are good candidates for FIXPARALLEL.
- First, try parallelism with a single large sparse dimension, or by restricting mbrList to one or more hierarchies with a limited stored member count. You may continue adding dimensions to the member list to see if the calculation time continues to improve.
Note that when “parallel” calculation of tasks occurs, it means that the tasks are divided and executed concurrently in any order. In other words, there is no guarantee that any task will be executed before any other tasks. This is why the regions you specify must not have any data or calculation dependencies. For example, assume there are two parallel threads, and there is a division of work into tasks A, B, C, and D.
The possible sequence of calculation might be:
Thread #1 executes A and then C.
Thread #2 executes B and then D.
Thread #1 executes A.
Thread #2 executes B, then C, then D.
Thread #1 executes C and then A.
Thread #2 executes D and then B.
For the optimized performance of these functions on aggregate storage databases, include in your query the following elements:
- Any of the following functions, used within the named set and/or as an argument to this function: Intersect, CurrentMember, Distinct, CrossJoin, PeriodsToDate. The use of any other functions (such as Members) disables the optimization.
- The second parameter, accounts_member, must be included for optimal performance.