Usage Recommendation
The number of DEF nested levels should not be too large.
You are not advised to set too many nested levels in the DEF function, That is because the backend calculations will generate complex SQL statement, and the large amount of data will affect the performance, resulting in long calculation time and lowering the dashboard loading speed.
Limiting the Number of Nested Levels in DEF Formula
To avoid this situation as much as possible, the admin can modify Number of Nested Levels in DEF Formula (4 by default) under System Setting > BI Parameter, and click Save after finishing, as shown in the following figure.
You are not advised to have too many nested levels in the multi steps of the DEF function.
For example, if you add a DEF formula column DEF1, subsequent DEF formula columns will rely on DEF1 for their calculations, which is the two levels of nesting. Too many nested levels can increase the performance burden, leading to slow loading and even downtime.
You can adjust parameters to detect the number of nested levels in the DEF formula for all steps in the self-service dataset, and report an error when the number exceeds the limit. For details, see section "Adjusting Limiting Parameters."
No Join class steps should be interspersed between DEF steps in the self-service dataset.
Reason: In FineBI version 6.1.5 and later versions, the DEF calculation speed can be increased if Join class steps are not added between DEF steps.
Join class steps include: Join, Union All, Group Summary, Row to Column, Column to Row, Sort, Filter, Delete Duplicate Row, Split Field, Field Settings, and Column from Other Tables.
Features of Join class steps: Change the number/order of rows of data, resulting in an increase in the complexity of subsequent DEF calculations and a drop in BI performance.
Function Description
The Calculation Logic of the DEF Function in the Self-Service Dataset
A DEF formula column is added in a self-service dataset, as shown in the following figure.
In the calculation, the DEF step can be split into two parts:
Create a DEF view based on the parameters in the DEF formula (parameter 1 for aggregated metrics, parameter 2 for grouping, and parameter 3 for filtering) for group summarization as well as filtering.
Based on the grouping information, merge the original view in the data editor with the DEF view to get the final result.
Complexity Increased by the DEF Step
Two views, the original view and the DEF view, are mentioned in the previous description of DEF. A new DEF formula column will create a DEF view based on the original view for group summarization and merges with the original view left and right.
Then the complexity of the whole calculation will increase. The scenarios are as follows:
Scenario1: Nested relationships exist between DEF steps.
For example, DEF2nestDEF1, as shown in the following figure.
Scenario2: Join steps exist between DEF steps.
Other steps are those that affect subsequent DEF calculations (changing the number/order of data rows):
Join, Union All, Group Summary, Row to Column, Column to Row, Sort/Filter, Delete Duplicate Row, Split Field, Field Settings, and Column from Other Tables
For example: Since filtering results in a change in the number of rows of data, DEF2 cannot share the main view with DEF1 and needs to perform calculations based on the filtered view.
Product Optimization
In FineBI 6.1.5, the calculation in some DEF scenarios is optimized to avoid an increase in the complexity.
Scenario description: No nested relationships or Join steps exist between DEF steps.
The steps, Formula Column, Summary Column, Assignment Column, Condition Label Column, Time Interval, Get Time, and Split Field, will not increase the complexity of the DEF calculation.
In this scenario, multiple DEF steps can be calculated based on the same view. The performance is thus improved.
Adjustment and Optimization of DEF in the Self-service Dataset
Problem Description
If there are large number of nested DEF steps with some Join steps in between in the self-service dataset, the generated calculation description will be very complex, which may lead to high load or even downtime of the system during the editing/updating phase.
Solution
Updating the Parent Table
If the dataset has a parent table, update its parent table to avoid generating additional DEF complexity due to the unupdated parent table.
Adjusting the Order of Steps
Check the number of steps of DEF formulas in the self-service dataset. If the calculation results are not affected, you can adjust and optimize the steps according to the following methods.
1. Originally, the Join steps were interspersed with the DEF steps, as shown in the following figure.
2. Put all the Join steps together in front of the DEF calculation steps, as shown in the following figure.
Adjusting Limiting Parameters
If you need to use more than the limited number of DEF steps in a self-service dataset, you need to adjust the value of the self-service dataset DEF nested levels number parameter SystemOptimizationConfig.etlDefComplexityLimit in the FineDB database.
For example, if you adjust the value of the added DEF formula column in the self-service dataset to 5, which triggers an error if the DEF steps are nested more than five levels. When the limit is triggered, the error is reported as shown in the following figure.
