Add SQL dataset

The SQL dataset belongs to the basic table. For concepts, please refer to: Basic table.

1.1 Version

1.2 Application scenarios

Users need to add SQL datasets to the decision-making system and perform subsequent data analysis and dashboard display.

1.3 Feature introduction

FineBI supports adding four types of data tables. This article describes the steps to add SQL dataset.

Note: Adding a SQL dataset cannot create a new table, nor can it modify the table structure. It only supports fetching data from tables in an existing database to BI through SQL statements.

1.4 Applicable people

Data processing users

2.1 Enter the package

Log in to FineBI and select a package under the data preparation, as shown in the following figure:

2.2 Enter the SQL editing interface

1) Click "Add Table" and select "SQL dataset". As shown below:

2) Enter the SQL dataset editing page to input SQL statement, table name, etc. As shown below:

Note 1: When adding a SQL dataset, the input SQL statement cannot contain ";". Otherwise, if the field type is modified in the field setting, the update of the dataset will fail.

Note 2: If you do not choose to enable real-time data for the successfully added SQL dataset, you need to update the data before you can use it, such as creating a self-service dataset, creating a component, and so on.

2.3 Add a SQL dataset

1) Select the data connection of the data source as FRDemo, and enter the SQL statement: select * from Employe. When entering the SQL statement, FineBI supports SQL statement keyword prompts. Click "Preview", and the obtained "Employe" table will appear, and modify the table name as "Employe", as shown in the figure below:

Note: Click "Preview" at this time, and a request for all SQL data will be sent to the database.

2) After the table configuration is completed, click "OK", and the SQL dataset just added will appear in the package. When the data is extracted, you need to click "Update Data", and then preview in the data preview, as shown in the figure below:

Note: The data preview of the data preparation interface displays 5000 rows of results by default. This number is only the amount of data displayed in the preview interface after saving, and all data is used after the dashboard is created.

Note: Fields recognized as a date type by BI will automatically complete the "year, month, day, hour, minute, and second" and display as yyyy-MM-dd HH:mm:ss (example: 2020-10-15 00:00:00)

2.4 Create SQL dataset with parameters

2.4.1 Create SQL dataset of text type parameter

1) Select the data connection of the data source as FRDemo, enter the SQL statement with parameters: select * from PRODUCT where SupplierID in ('${SupplierID}'), click "Refresh" in the parameter setting to get the parameter, and the parameter default value is 1, as shown in the figure below:

2) You can modify the default value of the parameter to "18", and click to preview the data, as shown in the figure below:

Note: The initial parameter type of all acquired parameters is text, and the default value is 1, which needs to be modified manually.

2.4.2 Create SQL dataset of date type parameter

1) If the parameter type is a date parameter, the required data can also be obtained by modifying the default value.

2) At the same time, you can set the dynamic default value through the date panel, and the initial default value is the current date.

For example, set the dynamic default value of the parameter to 10 days ago. After selecting the date for the parameter type, click the date panel of the default value, select the dynamic time, and set it to 10 days before the current time.

Note: The function of setting dynamic default values for date parameters requires the support of FineBI 5.1 version after 2019-01-15.

3) The default value of the parameter can be set to be empty, with the if function or <parameter> to achieve the effect of selecting all values when the parameter is empty. The SQL dataset supports adding parameters and passing them for use.

2.5 Modify SQL dataset

After adding SQL dataset, click "Modify SQL" to modify the SQL statement. As shown below:

Note: After adding the basic table, you can edit it. For details, see: Edit basic table.

3.1 Stored procedure introduction

A "stored procedure" is a set of SQL statements stored in the database. FineBI supports calling stored procedure in the database when adding SQL dataset, so as to obtain the tables after executing the stored procedure SQL statement set.

Types of database: You can use JDBC to connect, and you can use JDBC to execute SQL statements to call the database of stored procedure.

Users can use the self-check tool. After the SQL statement to be queried, enter the statement to call the stored procedure. If the execution is successful, it means that the stored procedure can be called in FineBI.

Note: The stored procedure is only effective for extracting data and does not support the use of stored procedure directly connected to the database.

4.1 Add adatabase view

Sometimes users need to add database views in BI, which can be achieved by adding SQL dataset. For example, you can create a SQL dataset to fetch the data to BI, as shown in the following figure:

You can also go to Manage > Data Connection > Data Connection Management, reconnect to the database and save, and you can add a view by adding a database table.