When the user creates a dashboard, the system will perform a static detection of the report performance, informing the user what will affect the report display and giving reasonable suggestions.
The reasons for report performance detection are categorized into three types, including system reasons, report reasons and dataset reasons, which are described in detail below.
1.2 Application scenario
Inform the person who creates the report what information may affect the performance of the dashboard.
2. Step
1)Function Entry: When there is a performance problem in the dashboard, a small red dot will appear on the "Performance Help" on the dashboard menu bar to remind the user, and the user can click Performance Help > Performance Test to view the specific reasons and suggestions, and click Download in Performance Test to export all the reasons for slow performance in Excel format, as shown in the figure below:
2)According tothe reasons for test, adjust the situation, for example, the first step in the commit can be adjusted to "batch commit", when the user will detect the performance of all the problems listed in the performance according to the recommendations given to solve all the red dots will disappear.
➢Note:
Dashboard performance testing is only prompted when the user to create a dashboard, in the preview dashboard will not be prompted tip.
3. Parameter configuration
•Frequency of performance test
The dashboard performance test frequency can be controlled by the db.performance.test.interval property in System Setting > System Parameter Configuration > Basic Parameter Configuration, as shown in the following figure:
The default value is 60s, that is, every minute on the dashboard performance test, users can also manually modify the value.
➢Note:
The first time you open a dashboard or open multiple dashboards at the same time after switching the selected dashboard will immediately test the performance of the dashboard, other operations will be tested according to the value of the property.
•Timeout check tip
The timeout for database/bazaar calculation and the timeout for BI memory calculation can be set through System Setting > System Parameter Configuration > Basic Parameter Configuration in pre.process.timeout and post.process.timeout respectively.
The default value is 10000 milliseconds. If the set value is exceeded, the following tip will pop up.
•Whether to perform performance test and usefulness checking of self-service dataset nodes when saving datasets.
Whether to perform performance checking when saving datasets and checking the usefulness of self-service dataset nodes can be set through dataset.save.check in System Setting > System Parameter Configuration > Basic Parameter Configuration .
The default value is true, which means the tip will be popped up when the performance test or usefulness test fails, and false, which means the tip will not be popped up when the dashboard is saved.
• Save dashboard performance test tip
After the user finishes making the dashboard, click "Save" or "Save As", the tip box will pop up by default to list all the performance problems in the dashboard, and the user can modify the dashboard according to the listed reasons and suggestions, as shown in the figure below:
Click "Keep Saving", the dashboard is saved successfully; click "Cancel" or "Close", users can continue to create the dashboard.
Whether or not to pop up the test performance Tip when saving the dashboard can be controlled by the db.save.performance.detail property in System Setting > System Parameter Configuration > Basic Parameter Configuration, as shown in the figure below:
The value of this attribute can be true or false, the default is true. when the value is true, the performance dashboard will pop up when saving the dashboard; when the value is false, the performance dashboard will not be popped up when saving the dashboard, the user can configure according to need.
4.Performance test reason
•System reason
When users modify some data volume related attribute values beyond the default value, it may cause too much pressure on the product calculation, which we will tip and collect into the system reasons.
1)The maximum number of rows set for the filter component or parameter component list.qry.maxrow exceeds the default value of 10000, a Tip will pop up, as shown in the following figure:
After the user clicks "OK" to force the value to be changed, if there is a filter component or parameter component in the dashboard, this information will be collected in the system cause, as shown in the following figure:
Solution: Modify the value of the list.qry.maxrow parameter in System Settings > System Parameter Configuration > Data Volume Parameter Configuration.
2)The set freestyle.max.size exceeds the default value of 500000
If there is a free-style form or a free-style fill form in the dashboard, it will collect the performance test information into the system reason.
Solution: Some of the grid calculation scenarios using free-form tables can actually be calculated just as well by calculating columns, so you can consider changing to calculating columns and then binding; you can also modify the default value of freestyle.max.size in bi.properties, but the default value is too large and may increase the dashboard load, resulting in lagging.
3)The maximum number of rows of the set parameter optional value browseParamlist.maxrow exceeds the default value of 5000
If there is a popup parameter in the dashboard and the optional value comes from the dataset, it will collect the performance detection information into the system reason.
Solution: You can go to System Settings > System Parameter Configuration > Data Volume Parameter Configuration to modify the browseParamlist.maxrow parameter value. The optional parameter value should not be too large, otherwise it may cause lagging.
4) The number of loaded data rows max.load.rows of the set component exceeds the default value 5000000
If there are tables, dashboards, free-form tables, charts, and bound data in the dashboard, it will collect the performance test information into the system reason.
Solution: You can go to System Settings>System Parameter Configuration>Data Volume Parameter Configuration to modify the max.load.rows parameter value, which cannot exceed the upper limit of 1000000000.
5)If the set number of rows of exported csv csv.max.row exceeds the limit 5000000
If there are tables, dashboards, free-form tables, charts, and bound data in the dashboard, it will collect the performance testing information into the system reason.
Solution: You can go to bi.properties and change the csv.max.rows parameter value and restart.
6)If the number of rows set for the pivot table pivot.max.row exceeds the default value of 500000
If there is a dashboard component or free-form table component in the dashboard with bound data, it will collect the performance detection information into the system reason.
Solution: You can go to bi.properties and change the pivot.max.row parameter value and restart.
7)The maximum number of axis labels set chart.axis.label.max.count exceeds the limit of 5000
If there is a chart component in the dashboard and data is bound to it, it will collect the performance test information into the system reason.
Solution: You can go to bi.properties and change the value of the chart.axis.label.max.count parameter and restart.
•Dashboard reason
When a user creates a dashboard to add a new column, if the column cannot be pushed down to the database for execution, it will be displayed as an orange logo and there is a performance detection Tip when the mouse is hovered over the column, as shown in the following figure:
It is collected in the dashboard reasons when the dashboard has the following conditions:
1)The columns in the SQL dataset used on the component cannot be pushed down to the database for execution, or the filter conditions set in the filter of the component cannot be pushed down to the database for execution, as shown in the following figure:
Solution: Before creating the dashboard, you can filter the dataset to leave only valid data and reduce the memory load.
2)The filter component or parameter component does not check the default value and the empty option policy is set to All Data, that is, all the data are displayed at once, as shown in the following figure:
Solution: Set default values for the filter component and parameter component.
3)The SQL dataset used on the component has columns that are detail expressions of logical judgment type (expressions with if statements), as shown in the following figure:
Solution: You can replace the calculated column with a filtered column.
4)The number of dimension bindings in the component exceeds 10, as shown in the following figure:
Solution: Remove the data fields that are not related to the analysis from the binding area. It is recommended that the number of bound dimensions does not exceed 10.
5) When there are multiple filter conditions in the dashboard, as shown in the following figure:
Solution: Add a batch commit button in the dashboard, check all the filter conditions and then submit them in bulk before performing the filtering.
6)For the database that has supported paging, paging is not used when creating the detail table, as shown in the following figure:
Solution: Check "Page" in the component settings to display an appropriate number of data rows per page.
•Dataset reason
1) When the dataset (SQL dataset/self-service dataset/combined dataset) that the dashboard component relies on has columns/operations that cannot be pushed down to the database, or the filter conditions in the filtering of the SQL dataset cannot be pushed down to the database, the information will be collected in the dataset reasons, as shown in the following figure:
Solution: Mongo calculates columns as much as possible using pipeline commands, datasets are filtered out of valid data before association calculations are performed, which can reduce the amount of data that needs to be concatenated, improve performance, and reduce the number of unnecessary fields to be calculated and so on.
2) In the static performance testing of the Dataset and Dashboard, do a test on the mpp execution. The number of rows in the execution plan exceeds a certain number.
Solution: This limit is controlled by the dc.perf.query.maxrow parameter and defaults to 10000000.
3) two into the Dataset of the Mart to do a joint query to determine whether to meet the map-side join, does not meet the performance tips.
Solution: To extract data, you need to first selec shard column and then import data into the data mart.
