Entry-Level Data Connection Configuration

Version

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.

Log in to the decision-making system as the admin and choose System Management > Data Connection > Data Connection Management > New Data Connection.

Note:

If you are not an admin, you need to have the using permission of data connections to create a data connection.

You need to select the needed database type. This document takes the MySQL database as an example. 

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 Default: built-in database drivers in some databases (You can use them directly.) Custom: You need to customize drivers that do not exist in FineReport. For details, see Driver Management.
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. Username and Password: username and password of the database Kerberos Authentication: client name and Keytab secret path registered with KDC. For details, see Kerberos Authentication in Data Connection.
Encoding	You are advised to select Default.
Pattern	Some databases require you to select a pattern after they are connected. You do not need to select a pattern if there is no Pattern option on the setting        page. The first pattern is selected by default if no pattern is selected. Use the pattern prefix in SQL statements to search a certain table or view in a specific pattern when a multi-pattern database is connected. Note: You need to correctly select the corresponding mode of the database, otherwise a prompt "No tables available in 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 (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.

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

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:

There are two options in Verify 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 Setting

Note:

SSL Setting is only available in MySQL database.

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.

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.

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.

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.