Entry-Level Data Connection Configuration

Version

Function Description

The function allows you to establish a connection between FineBI and the database, enabling FineBI to directly access the data in the database.

1. Log in to a FineBI project as the admin and choose System Management > Data Connection > Data Connection Management. Choose Create > Folder, and you can use folders to classify and manage data connections, as shown in the following figure.

Note:

If you are not an admin, you need to have the Using Permission of Data Connection to create a data connection.

 Create a data connection in a folder, as shown in the following figure.

2. In the pop-up box, customize a data connection name, as shown in the following figure.

3. Select the database type. For example, if you want to connect to a MySQL database, you need to select MySQL and then click the Next button, as shown in the following figure.

4. Enter the relevant information of the database, as shown in the following figure.

The following gives details about data connection configurations. Different databases have different connection information to set. For details, see specific documents. For example, for details about how to configure MySQL connections, see MySQL Data Connection.

Configuration	Introduction
Data Connection Name	Name of the data connection (Name it as you like.)
Driver	Driver needed to connect to the database Default: For some of the databases that have built-in database drivers, you can select the default option. Custom: You need to customize drivers that do not exist in FineDataLink. For details, see Driver Management
Database Name	Name of the database to be connected to
Host	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 radio group will appear. Username Password: username and password of the database Kerberos: client name and Keytab key path registered with KDC. For details, see Kerberos Authentication in Data Connection.
Encoding	You are advised to select Default.
Schema	Some databases require you to select a schema after they are successfully connected. You do not need to select a schema if there is no schema option on the configuration page. The first schema is selected by default if no schema is selected. Use the schema prefix in SQL statements to search in a specific schema when a multi-pattern database is connected. Note: You need to correctly select the corresponding mode of the database, otherwise a prompt "No table is available in the current data connection." will occur when you add database tables. In this case, you can only retrieve data through SQL datasets.
Data Connection URL	Automatically generated URL (No manual configuration is required.)

Click Test Connection to test if the connection is successful.

If you do not need to set a connection pool, you can click Save to complete data connection configuration.

When connecting to a database, you can perform some advanced settings as required, as shown in the following figure.

Fetchsize settings

Problems such as slow extraction from base tables and from direct-connected SQL datasets can be tuned through fetchsize.

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 the fetchsize parameter as required.

If the parameter value is less than or equal to 0 (for example: -20), the fetchsize parameter is disabled.

1. The fetchsize parameter must be set for the Oracle, DB2, and Postgre databases. Therefore, if the value of the fetchsize parameter is set to be a value less than or equal to 0, it will be automatically replaced by 128, 50, or 10000 in the three databases, respectively.

2. The fetchsize parameter is disabled for other databases by default.

• SocketTimeout settings

Set the socket timeout duration (the longest time for connection waits or data transmission), exceeding which the connection will be forcibly disconnected.

If the value is null or 0, no timeout duration is set.

Connection Pool Settings

Set the connection pool and select the Connection Pool Setting button, as shown in the following figure.

• Common Settings

• More Settings (not commonly used; you are advised not to modify the settings except for any special circumstance.)

SSH Settings

If you require high security, you are advised to use SSH. Check UseSSH Tunnel and enter the corresponding information.

Note:

There are two options in Authentication Method: Password and Public Key. If you select Public Key, you need to put the file under the path %FineReport%/webapps/webroot/WEB-INF/resources.

Click Test Connection. If the connection is successful, click Save to save the configuration.

SSL Settings

Note:

SSL Setting is only available in MySQL databases.

1. Put the CA certificate, client certificate, and client key under the path %FineBI%/webapps/webroot/WEB-INF/resources/certificates.

2. Select UseSSL Tunnel and add the file to the corresponding location, as shown in the following figure.

3. Click Test Connection. If the connection is successful, click Save to save the configuration.

Single Data Connection Management

After the data connection is successfully created, the page is shown in the following figure.

Note:

By default, a user has the use and management permissions for folders and data connections created by the user.

Users can edit, rename, copy, delete and test the data connections in the list, as shown in the following figure.

Notes:

• The xxx icon will not be displayed on the right side of incompatible database types.

• You are not advised to modify the data connection name during data connection management. Because the table updated to local computers relies on the name of the data connection to match the database,if the name of the data connection is modified, the tables retrieved from that connection will no longer be updated, and the Modify SQL button will not exist when the connection is based on an SQL dataset.

• If you want to update the database of the locally saved table, for example, when you changed from the development environment to the production environment, you only need to modify the URL of the data connection when the table name and the table structure are consistent. In this way, data from the the locally saved table can still be updated according to the data connection name.

Management of Data Connections in Folders

1. Click the folder or root directory to search for data connections by the data connection name (supported case-insensitive and fuzzy search), data source type (multiple selections allowed, which are existing data source types in the table), and creator (multiple selections allowed, which are existing creators in the table), as shown in the following figure.

The following table describes Number of Active Connections and Number of Idle Connections.

2. You can rename or delete folders, as shown in the following figure.

3. The data connection can be dragged from folder A to folder B. You cannot drag a folder to another folder, A maximum of one layer of folders is supported.

Data Connection Timeout Detection

You can use the Data Connection Timeout Detection function by clicking Data Connection Management > Global Setting to customize the timeout period of a data connection.

When a data connection times out, an error pops up: 31300010 kdc server is not connected. You can modify the timeout period manually.

Data Preview Failure

When fineBI is connected to server datasets, 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.