Adding SQL Datasets

Version

Application Scenario

You can process tables in the database through simple SQL statements and then add the tables to Public Data.

Function Description

You cannot create tables or modify table structures in FineBI through SQL statements. You can only retrieve data of tables from the existing database to FineBI through SQL statements.

The syntax rules of SQL statements need to be compatible with the connected database.

Choose Public Data and select a folder on which you have management permissions. For details, see Public Data Management Permission. Click Add Dataset and select SQL Dataset from the drop-down list, as shown in the following figure.

Creating a SQL Dataset Without Parameters

After selecting the needed data connection, enter the SQL statement to retrieve data. Click Preview to view whether the data is correct and click OK to add the table, as shown in the following figure.

The SQL statement is SELECT * FROM Balance_sheet.

Note:

The entered SQL statement cannot contain semicolons when you add the SQL dataset. If you modify the field type in the field setting area, the dataset will fail to be updated.

Creating a SQL Dataset with Parameters

1. Enter an SQL statement with parameters.

2. Click Refresh.

3. Set Parameter Type and Default Value for parameters in the SQL.

4. Click Preview to view the data retrieval result. All data (later than 2016-12-12 14:15:25) is displayed through filtering.

Note:

You can set Default Value to empty for the parameters. By working with the IF function or <parameter> in this case, you can select all values when the parameters are empty. SQL datasets allow you to add and pass parameters.

You can set a dynamic time (like yesterday/last month) when setting the default value for time parameters.

Updating a SQL Dataset

SQL datasets with direct connection data can be used directly after being added. SQL datasets with extraction data can be used after being updated, as shown in the following figure.

Modifying a SQL Dataset

After adding an SQL dataset, you can modify the SQL statement and parameter setting through two entries, as shown in the following figure.

Note:

1. Stored procedures are only effective for extraction data and cannot be used in databases with direct connection data.

2. Oracle cannot call stored procedures in FineBI.

Introduction to Stored Procedures

Stored procedures are SQL statement sets stored in the databases. FineBI allows you to call stored procedures when you add SQL datasets to obtain tables after SQL statement sets of stored procedures are executed.

Databases that can call stored procedures are ones that can be connected through JDBC and can call stored procedures by executing SQL statements through JDBC.

You can enter the statement for calling stored procedures after the SQL statement to be queried in the self-test tool. If the execution is successful, you can call stored procedures in FineBI.

Calling Example

Different databases have different calling statements. You need to use the corresponding statements for different databases to call stored procedures.

Calling Stored Procedures in MySQL Databases

Enter call Stored procedure name() in the SQL statement box, click Preview to view the calling result, and click OK to save the dataset.

If parameters exist in stored procedures, the calling methods are as follows:

• If you use the default parameter value, you can directly enter call Stored procedure name() (for example, call test1()) to call stored procedures.

• If you pass parameters without using the default parameter value, you can enter call Stored procedure name ('Parameter value 1', 'Parameter value 2') (for, example, call test('a', '100')).

Calling Stored Procedures in SQL Server Databases

Enter execute Stored procedure name in the SQL statement box, click Preview to view the calling result, and click OK to save the dataset, as shown in the following figure.

If parameters exist in stored procedures, the calling methods are as follows:

• If you use the default parameter value, you can directly enter execute Stored procedure name (for example, execute StuCouIN) to call stored procedures.

• If you pass parameters without using the default parameter value, you can enter call Stored procedure name 'Parameter value 1', 'Parameter value 2' (for example, execute StuCouIN '7001','2').

Blank Data Preview Page After Field Adding

Add the Contract Payment Type field and click OK to modify the SQL statement of the SQL dataset, as shown in the following figure.

In this case, the added field is not updated in the data table. You need to click Edit in the right corner of the base table, manually select the added field, and click Save, as shown in the following figure.

Adding the Database View

You can add the database view in FineBI by adding the SQL dataset.

For example, if database views exist in MySQL databases, you can retrieve data to FineBI by creating SQL datasets, as shown in the following figure.

Alternatively, you can choose System Management > Data Connection > Data Connection Management, reconnect databases, and save the operation. That is, you can add views by adding database tables.