FIXPARALLEL...ENDFIXPARALLEL

Enables parallel calculation on a block of commands by using up to a specified number of parallel threads.

The ENDFIXPARALLEL command ends a FIXPARALLEL command block.

Syntax

FIXPARALLEL (numThreads, mbrList)
COMMANDS ;
[ POSTFIXPARALLEL ( [ varName = ACCUMULATEVAR ( threadVarName ); ]* ); ]
ENDFIXPARALLEL
Parameter Description

numThreads

A positive integer specifying the number of threads to be made available for parallel calculation.

mbrList

A selection of slices for restricting the calculation. These slices become the task members for the FIXPARALLEL calculation. Can be one of the following:

  • A member name or list of members. Note: If mbrList is a single member from one or more sparse dimensions, then it only generates one task, and cannot benefit from parallel execution. Multiple members from one or more sparse dimensions generate multiple tasks.

  • Member set functions, which are used to build member lists based on other members.

The database regions (slices) you specify must be independent of one another.

From mbrList, Essbase generates tasks to be calculated in parallel.

Essbase uses only non-dynamic, non-shared, sparse members to create the tasks, which in turn determine the blocks to be calculated. Therefore, mbrList must contain at least one non-dynamic, non-shared, sparse member. In order to use multiple threads, mbrList should contain two or more members from each sparse dimension. mbrList should indicate at least as many tasks as the numThreads you specify.

To avoid setting too many tasks in a FIXPARALLEL calculation, only those member combinations that are to be used for tasks should be in the mbrList. All other sparse member combinations belong in an inner or outer FIX.

COMMANDS

The commands you want to be executed for the duration of the FIXPARALLEL. These commands are applied to the database regions described by mbrList. May includeTHREADVAR commands.

POSTFIXPARALLEL

Optional block of operations to copy THREADVAR variables to VAR variables. Essbase executes POSTFIXPARALLEL block once, before the FIXPARALLEL command finishes. See POSTFIXPARALLEL.

varName

Name of a VAR variable.

threadVarName

Name of a THREADVAR variable.

ACCUMULATEVAR

Used within optional POSTFIXPARALLEL. Add up all the thread values of a given THREADVAR variable. The sum is then assigned to a specified VAR variable.

ENDFIXPARALLEL

Closes the FIXPARALLEL command block.

Notes

You control thread activity by using:

  • The numThreads parameter

  • The THREADVAR command

  • The ACCUMULATEVAR command (inside POSTFIXPARALLEL)

  • The mbrList parameter. The member list is an important tool for optimizing calculations, because it tells Essbase how to divide the calculation regions into tasks. As mbrList becomes larger, each task becomes smaller. When tasks become too small, calculation memory overhead could slow down performance. However, when tasks are too large, there might not be enough tasks for parallel calculation threads to work on.

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.

  • 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.

Or,

  • Thread #1 executes A.

  • Thread #2 executes B, then C, then D.

Or,

  • Thread #1 executes C and then A.

  • Thread #2 executes D and then B.

Task Selection Comparison of FIXPARALLEL and CALCPARALLEL

CALCPARALLEL creates tasks from the last sparse dimension first, then the second from the last, and so on, until it has enough tasks. FIXPARALLEL can choose from any sparse dimension that is not in its COMMANDS block. For example (as is true with FIX), you cannot FIXPARALLEL on (Level 0, Product) and also AGG (Product).

FIXPARALLEL can help you customize task selection, but it also assumes no interdependencies when generating tasks from the selected region. CALCPARALLEL must consider sparsity, outline order, dependencies, and member formulas in generating a task list.

Calculator Cache Sizing Notes for FIXPARALLEL Parallel Calculation

For FIXPARALLEL, calculator cache sizing is as important as it is for CALCPARALLEL, and follows the same methodology as described in the Oracle Essbase Database Administrator's Guide, in the chapter "Optimizing Essbase Caches," and in the section "Calculating the Calculator Cache Size."

When selecting dimensions to include in the calculator cache, Essbase always begins with the first sparse dimension, and progresses in outline order, selecting bitmap dimensions until no more will fit in the calculator cache.

The following is unique for FIXPARALLEL calculations:

  • Task dimensions may be scattered throughout the sparse dimension list, rather than clustered at the bottom of the outline as is the case with CALCPARALLEL.

  • For task dimensions included in the bitmap, the appropriate stored member count to use for calculator cache size estimation is 1, rather than the total stored members in the dimension. This is because each FIXPARALLEL task contains a single member from each task dimension.

For all other bitmap dimensions, the stored member count is the appropriate value to use to estimate calculator cache (same as for CALCPARALLEL).

Limitations of FIXPARALLEL Parallel Calculation

  • FIXPARALLEL is not supported on partitioned databases.

  • The following calculation commands are not supported in a FIXPARALLEL block:

    • DATAEXPORT with options other than flat files

    • DATAIMPORTBIN

    • EXCLUDE...ENDEXCLUDE

  • FIXPARALLEL supports up to 8 threads (more if Essbase is running on Oracle Exalytics In-Memory machine). The data structures created in each thread and the algorithms used for scheduling and executing tasks require significant CPU and memory resources. Executing highly parallelized activities on servers with limited resources might have a negative impact on performance and system stability. Therefore, using FIXPARALLEL with more than 8 threads, when the ORACLEHARDWAREACCELERATION configuration setting is set to FALSE, is not supported.

Example

FIXPARALLEL used with DATAEXPORT enables you to export restricted regions of database in parallel. The following example uses two threads to export data relating to [California], [Oregon], [Washington], [Utah], and [Nevada].

FIXPARALLEL (2, @CHILDREN("West"))
   DATAEXPORT "File" " " "dataOfWest.txt" "#MI";
ENDFIXPARALLEL

See also the example for POSTFIXPARALLEL.

See Also