Data Management Guideline

This document introduces how to integrate curated data sources into the BI system and ensure their sustained availability.

The following figure shows the main content.

Target group: platform administrators and O&M personnel

• Permission control: The permission to create data connections is open to only super administrators and sub-administrators.

• Duplicate verification: Check if a connection with identical parameters (such as database address, database name, and username) already exists before      creating a data connection. Creating duplicate connections is prohibited.

• Naming convention: Establish a unified convention for data connection names. Recommended formats include Business system/Data theme_Database type (for example, BI platform operation_MySQL and ERP system transaction data_GP) and Business system/Data theme_Department_Operator/Responsible person (for example, BI platform usage record_Operation department_Peter). Super administrators need to monitor adherence to the naming convention.

• Fixed data connection name: Avoid changing the data connection name as it may cause the system to fail to find the database. Determine the data  connection name in advance. If a data connection name is changed, you must modify related SQL datasets accordingly by selecting the correct data connection in the SQL statement. If the data connection name is very likely to be changed, add base tables via SQL queries rather than directly using database tables.

FineBI supports two data retrieval modes:

Direct Connection: Calculations are performed by using the computational power of the database.

Extraction: Data is extracted and saved in the engine of FineBI for calculation. For details, see Difference Between Direct-Connected Data and Extracted Data.

A system can use both modes to fetch data, but each component can use data obtained in only one mode.

• If an enterprise has a relatively professional big data platform with high data quality, or if it has stringent requirements for real-time data (T+1 data latency not allowed), or if data security is a critical concern (storing data separately into a BI system not allowed), the company can select the direct connection mode.

• In addition, if the maximum amount of data records for the enterprise exceeds 100 million and frequent analysis of tables with large data volumes is required, the direct connection mode is recommended to shift the calculation to the database and reduce the pressure on the FineBI server.

• However, the direct connection mode does not support the operations involving data tables of different sources such as establishing associations, joining, and union all. In this case, if cross-table association is required in the actual business, FineDataLink can be used for synchronizing data across databases.

• The calculation in the data extraction mode is performed using the computational power and resources (mainly the memory) of the FineBI server, and the calculation performance is greatly affected by the server resource occupancy and resource using ability of FineBI. The calculation performance in the direct connection mode is strongly related to the database engine. FineBI engine converts the front-end calculation logic into SQL statements and send them to the database for execution, and then FineBI fetches and presents the results returned by the database. To use the direct connection mode, confirm that the business database can support the highly concurrent SQL calculations issued by FineBI. This includes accessing query speed and resource usage under high concurrent loads.

1. Use the built-in global memory control function: If the memory usage reaches the alert threshold due to the use of business personnel, thread exporting will be interrupted by the memory monitoring and protection mechanism of FineBI.

2. Set restrictions for Excel export. You can configure the following two parameters to set restrictions for Excel export under System Management > System Setting > BI Parameter.

• Data Volume Limit on Excel Export: This parameter is not set by default and the recommended value ranges form 0 to 1,000,000,000.

• Concurrent Thread Quantity Limit Upon Detail Table Export: The default value is 3, and the recommended value ranges from 1 to 5. (You are advised to use the default value.)

3. If Nginx is used, you can enable the HTTP/2 protocol, which can increase the number of concurrent browser requests (Using the HTTP/2 protocol will increase the number of concurrent database requests. Evaluate the database performance before use.)

4. Install the FINE_CONF_ENTITY Visualization Configuration plugin.

Use the FINE_CONF_ENTITY Visualization Configuration plugin to configure the SystemOptimizationConfig.queryConditionCountRestriction parameter: In this case, the system can interrupt queries of which the filter conditions exceed the limit and throws an error saying that "condition count out of restriction: xxxx." Recommended value: 30 (You can modify it to be a value smaller than 30 according to the actual filter condition.)

5. Install the BI Linkage Restriction plugin: If multiple components in a dashboard are linked to each other, linkages of three or more layers are removed and the last two layers of linkages of the last component to be linked are retained, thus avoiding slow loading of the dashboard and ensuring its performance.

6. Limit component query duration. You can choose System Management > System Setting > BI Parameter, and set the two query timeout parameters to limit the duration of component queries.

• You can set the Direct Connection Query Timeout and Extracted Query Timeout parameters when a dashboard contains too many components and the query of components takes long, or the query of a component in the dashboard takes too long and leads to the subsequent requests being blocked and the product being mistaken as experiencing downtime. All real-time and data extraction queries that exceed the specified duration will be terminated, preventing abnormal slow queries from blocking other normal queries.

• Recommended value range: from 10 to 300 (You can modify the value based on actual business and acceptable waiting time.)

Note:

These parameters apply to the Polars engine. For the Spider engine, you can set the DistributedOptimizationConfig.spiderConfig.spider_query_timeout_open and DistributedOptimizationConfig.spiderConfig.spider_query_timeout_limit parameters. Configuring these parameters helps terminate long-running queries in high concurrency, ensuring overall system availability.