Selection Options in Procedures

1.   Abstract

Board technology allows different types of select, static, or dynamic queries. In particular, in procedures, it’s possible to choose between three methods that can seem very similar:

• Selection (fixed)
• Select Entity based on Cube
• Dynamic selection (from the “Select” step)

Although they can give the same result in many cases, these are fundamentally different features and not interchangeable.

2.   Context

During the setup of a procedure, there are different ways to define a selection, so developers will need to choose one based on the pros and cons of each method.

3.   Content

3.1 Selection (Fixed)

This is the most commonly used method. It can be set up using the “Selection” procedure step. It’s simply applying a fixed selection at a specific point of the procedure execution. This will affect all the Cube-based actions which follow this step.

This step is often hard coding business logic into the procedure, which, if used too often, can lead to increased maintenance requirements for the following reasons:

• Changes to the business logic require repointing of fixed selections manually
• Changes to the technical setup require repointing of fixed selections manually
• Maintenance activities that involve the clearing of Entities can require repointing of fixed selections manually

For those reasons, the best practice is to avoid fixed selections on leaf levels and limit them to :

• Parent Entities: these are related to the leaf level; they are less granular and less likely to change. By keeping parent Entities as a fixed selection, you limit the likelihood of having to review them individually. In fact, in the scenario that the leaf level is changing, the update in the relationship will be sufficient to enable the procedure to work on the new range of elements
• Technical Entities: these have been created to manage a specific functionality and are entirely managed in BOARD. These Entities are also less likely to change over time. Since they are specific to certain functionality, it is evident that it will also require a rebuild of the dedicated procedures (e.g., a new status is added to the “Workflow status” Entity).

3.2 Select Entity based on Cube

This method calculates a selection based on the content of a Cube.

Compared to a fixed “Selection,” this method generates a dynamic selection. It’s dynamic because the selection is calculated every time the procedure is executed.

When the procedure is executing this step, it will first consider all active selections to filter the content of the chosen Cube and then select the desired Entity. This means that the active selection will filter the chosen Cube before the “select based on Cube” is executed, allowing the developer to influence the end result by changing the order of the different selection steps.

This step allows you to minimize the maintenance of a procedure. No selection is hard-coded; the steps will return the expected selection if the chosen Cube is populated correctly.

The best practice is to use this step in procedure selection as much as possible when the required selection does not fall in the previous best practice (paragraph 3.1). The following limitations/characteristics need to be considered:

• Recalculated once: this step is recalculated at each execution of the procedure, but it’s only recalculated once and at a specific point of the process; after that point, the selection remains the same unless the procedure includes additional selection-related steps
• Requires documentation: since it’s based on the content of a Cube, it’s essential to document how/where this Cube(s) should be populated; otherwise, there will be more significant maintenance of the procedures required and potentially complex reverse-engineering needed
• Greater resources required: since it is not a fixed selection, but it’s instead based on a Cube, it requires that this Cube is queried every time to calculate the elements that it contains—essentially, it’s like a layout with one Entity by row. This means that compared to a fixed selection, it will use more resources
• Selection is done on Total level:  as explained above, this selection is calculated with a method similar to a layout with one axis by row (i.e., the Entity of interest.) If you look at it in this way, it’s also clear that it will ignore elements for which the total sum is exactly zero; therefore, they are not considered of interest. For example, we’re selecting the Entity “product” based on the Cube “Net Sales,” and we have the following data available:

As you can see in the above example, the “select based on Cube” will return two selected products, not three, because of the zero value in the cell. 

• Consistency check required: to avoid unexpected results, you should always check the content of a Cube before executing the “select based on Cube,” using an “If then else” procedure step; in practice, it is suggested to check that the Cube is indeed populated before it is used for the selection and, if not, redirect the procedure on a fallback step or an error message to alert the user that the behavior will be different. In particular, a selection with zero (0) elements will invalidate other selections or dataflows using this dimension which may not be what the end user expects. Of course, it is advised to use this method only with critical selection steps or “selections based on Cubes,” which could potentially be recalculated simultaneously by another procedure.

Note: since the 'select based on' is a step which executes a layout, the number of selectable elements can be affected by the 'Reports rows upper limit' setting (into Board Server Configuration Tool).

3.3 Dynamic Select

This method is activated from the typical “selection” procedure step. Once you’ve opened the selection step, from within the selection window, a developer can choose the “dynamic selection” option as they’d typically do in a Screen selection.

The way a Dynamic Select is defined is similar to the “select based on Cube” since you’re also prompted to choose a Cube for the selection, and the result is, therefore, dynamic.

However, this step is executed fundamentally differently from the “select based on Cube.” This selection is not “calculated once” but recalculated at each and every procedure step. This will continue to be the process until there is a reset selection or another selection step that overwrites the previous one.

This process causes two effects that are important to consider when using this method:

• Calculation intensive: since it is recalculated at each step, it can affect the procedure performances in a negative way
• Difficult to manage and debug: since the selection is recalculated at every step, it can also change at every step, either because the underlying Cube has changed or because other selections affect the result. This makes it very difficult to manage or debug, especially on lengthy procedures;

The best practice is to use this method only when it’s strictly required that the selection is recalculated at every following step by the nature of the calculation; otherwise, the previous two methods should be used.

3.4 Attention point: Large dimensions (≈100k+ elements)

Specific methods are better when performing a selection on large dimensions. In particular, the “fixed selection” method is best for these situations.

However, it is essential to mention that the order of selection is significant for these “large” dimensions.

In these situations, the best practice is to:

• Rely as much as possible on “fixed selection” as parent Entities: this will give the best balance between maintainability and performance. Board will exploit the relationships and be faster than ‘select based on Cube’.
• Carefully evaluate the order of consecutive selections on the same tree: regardless of the method used, the order that selections are performed considerably impacts large dimensions. You should first apply the selections that reduce the range of elements the greatest; this will exponentially reduce the effort required by any following selection step.