Overview
Version
FineBI Version | Functional Change |
6.0 | / |
6.0.17 | Increased the fetchsize parameter configuration. |
Function Description
The function is commonly used to store business data in a database and continually update it. The function establishes a connection between FineBI and the database, allowing FineBI to directly access the data in the database.
Data Connection Creation
Log in to the decision-making system as the admin and choose System Management > Data Connection > Data Connection Management > New Data Connection.
Note:
Database Type Selection
You need to select the needed database type. This document takes the MySQL database as an example.
Data Connection Configuration
Database Information Configuration
Enter the relevant information for the database, as shown in the following figure.
The specific connection methods may vary with databases, so you can refer to specific documents for more information.
The following table describes the data connection information in detail. Different databases require different connection information settings, so some databases may have missing items.
Configuration Item | Introduction |
Data Connection Name | Name of the data connection (name it as you like.) |
Driver | Driver needed to connect to the database
|
Database Name | Name of the database to be connected to |
Host | Enter the IP address of the host (or server) where the database is located. |
Port | Port number of the host (or server) where the database is located |
Authentication Method | If the database supports multiple authentication methods, a drop-down list will appear.
|
Encoding | You are advised to select Default. |
Pattern |
Note: |
Data Connection URL | Automatically generated URL (Special configuration is not required.) |
Click Test Connection to test if the connection is successful.
If you do not need to perform any special configuration (see sections "Advanced Settings", "SSH Setting", "and SSL Setting"), you can click Save to finish the configuration.
Advanced Settings
When connecting to a database, you may need to perform some advanced settings, but in most cases, you do not need to do so. You can perform some settings as needed, as shown in the following figure.
Common Settings
The number of connections refers to the number of IO activities connected to the database. The more users simultaneously connect to this database to retrieve tables, the larger the number of connections.
A large number of connections will seriously affect the performance of the database, so you need to set a maximum number of active connections.
Name | Druid Scientific Name | Default Value | Definition |
Max Active Connection | maxActive | 50 | Represents the maximum number of active connections that the connection pool can allocate at the same time. |
Verification Statement | validationQuery | Default statement | Represents the SQL statement (a query statement, often in "select x" format) used to check whether a connection is valid. You can customize the statement. |
Test Before Connection | testOnBorrow | Yes | Executes validationQuery to check whether the connection is valid during connection application. |
Max Wait Time | maxWait | 10000 | Represents the maximum waiting time to obtain a connection. If the connection is not successful in this period, it is considered a connection failure. |
More Settings (not commonly used; you are advised not to modify the settings except for any special circumstance.)
Name | Druid Scientific Name | Default Value | Definition |
Number of Initial Connections | initialSize | 0 | Represents the number of physical connections established during initialization. |
Min Number of Idle Connections | minIdle | 0 | Represents the minimum number of connections. |
Detection Before Returning Connections | testOnReturn | No | Executes validationQuery to check whether the connection is valid during connection returning. |
Detection by Idle Connection Recycler | testWhileIdle | No | Indicates whether the connection is checked by the idle connection recycler (if any), and if the check fails, the connection will be removed from the pool. |
Sleep Time of Idle Connection Recycler | timeBetweenEvictionRunsMillis | -1 | Represents the sleep time value (unit: millisecond) of the idle connection recycler during the thread runtime. |
Number of Idle Connections Detected by Recycler | numTestPerEvictionRun | 3 | Represents the number of connections checked by the idle connection recycler (if any) during the thread runtime each time. |
Time Threshold for Recycling ldle Connection | minEvictableIdleTimeMillis | 1800s | Represents the minimum time to keep the connection idle without being evicted. |
Fetchsize Setting
The fetchsize parameter is used to control the number of records to be processed in batches for obtaining data from the database. A smaller value of the fetchsize parameter can reduce the memory consumption of each database query, but may require more database round trips to retrieve the complete result set; a larger value of the fetchsize parameter can reduce the number of database round trips but will increase memory usage. You can optimize the database query performance by setting fetchsize as required.
If the parameter value ≤ 0 (for example: -20), the fetchsize parameter is disabled.
1. The fetchsize parameter must be set for the Oracle, DB2, and Postgre databases. If the parameter value ≤ 0, the value will be automatically replaced by 128, 50, and 10000 respectively.
2. The fetchsize parameter is disabled for other databases by default.
SSH Setting
If you need high security, you are adivsed to use SSH. Tick Use SSH Tunnel and enter the corresponding information.
Note:
Click Test Connection. If the connection is successful, click Save to save the configuration.
SSL Setting
Note:1. Put the CA certificate, client certificate, and client secret key under the path %FineReport%/webapps/webroot/WEB-INF/resources/certificates.
2. Tick Use SSL Tunnel and add the file into the corresponding location.
3. Click Test Connection. If the connection is successful, click Save to save the configuration.
Data Connection Management
Management Scope
In the decision-making system, choose System Management > Data Connection > Data Connection Management, and click the icon ··· of any database, as shown in the following figure.
The following table describes the settings of data connection management.
Settings | Description |
Test Connection | Test whether the database configuration is successful. |
Edit | Modify data connection configuration items. |
Rename | Rename the data connection. |
Copy | Copy the database and add 1 behind the name of data connection automatically. |
Delete | Delete the database. |
Data Connection Information | Display the database type and its creator. |
You need to note the following points:
Icons will not display on the right side of unmatched database types.
You are not advised to modify the data connection name arbitrarily during data connection management as the table updated to local machines relies on the name of the data connection to match with the database. If the name of the data connection is modified, the tables retrieved from that connection will no longer be updated.
The Edit SQL button will not exist if the connection is based on an SQL dataset.
If you want to update the database of the locally saved table, such as changing from the development environment to the production environment, you only need to modify the URL of the data connection. In this way, the locally saved table can still update data according to the data connection name.
Connection Pool Status
You can view created data connections and their corresponding connection status, as shown in the following figure.
The following table describes Number of Active Connections and Number of Idle Connections.
Connection Pool Status | Description |
Number of Active Connections | Maximum connection number that the connection pool can create. If the maximum number is 10 (already 10 data connections in the pool), the system will pop up an error "active 10 maxActive 10" when you apply for the eleventh data connection. |
Number of Idle Connections | Connections that are in idle status and can change into active connections at any time. The idle status will be released automatically after exceeding the maximum idle connection time. Number of Active Connections + Number of Idle Connections = Number of Actual Physical Connections |
Common Errors
Failure to Preview Data
FineBI is connected to server datasets and an error message "find fieldinfo failed for table: XXX" occurs during the data preview.
The reason is that there are duplicate field names in the database table, and FineBI does not support duplicate field names and null values, as shown in the following figure. You need to modify the duplicate field names.
Failure to Add Data Connections
You may fail to add a data connection and receive an error message "The number of current data connections exceeds the registration LIC limit (1). All data connections are unavailable. Delete redundant data connections."
Solution: Disable the compatibility view in Internet Explorer.
