Incremental Update Overview

Application Scenario

There is a table in the database, and it will receive new data every day. In this case, you can use the Incremental Update function to only update the newly-added data to FineBI every day. You do not need to update the entire data table, saving the time and resources you will consume on the update.

Function Description

The data table that meets the following conditions is more suitable for the Incremental Update function.

There is a timestamp field in the table, which can be used to compare with Update Time to achieve the Incremental Update function.

The historical data in the table does not change.

The Incremental Update function is commonly used for the table that requires frequent updates and has a large data volume. If the data volume of a single table is relatively small, or if the single table is only updated once a month, there is no problem with using the Full-Volume Update function.

Notes

The following are the tables do not support the Incremental Update function: server datasets, the base tables that have been performed the operation of row-column conversion or self-looping column, Excel datasets, and self-service datasets.

The Incremental Update function can only be used after you perform a full-volume update, and the SQL statements used for the incremental update cannot be empty.

The Full-Volume Update function synchronizes all database data to the data to the data table, while the Incremental Update function updates specific data by using SQL statements.

Select the table you want to update incrementally, select Update Information, and click Single Table Update, as shown in the following figure.

Go to the Single Table Update setting page, as shown in the following figure.

There are two methods under Incremental Update Mode: Incremental Increase and Incremental Deletion.

Incremental Increase

When you enter the SQL statement into the box below to extract data from the database, the data you have extracted will be added to the FineBI engine through the Incremental Increase function, as shown in the following figure.

The Incremental Increase function is commonly used for scheduled data addition. For details, you can refer to Incremental Increase Example.

Incremental Deletion

When you enter the SQL statement into the box below, the system will extract the corresponding data from the FineBI engine to delete, as shown in the following figure.

You can delete the data that is unnecessary in the FineBI engine through the Incremental Deletion function. However, the Incremental Deletion function is rarely used alone, and it is usually used to modify data. For details, see section "Incremental Modification."

Incremental Modification

You can perform in conjunction with both Incremental Increase and Incremental Deletion. For example, you can first delete some data in FineBI, and then add data from the database to the FineBI engine. For details, you can refer to Incremental Modification Example.

Setting Scheduled Incremental Update

FineBI supports the function of Scheduled Incremental Update. For details, you can refer to section "Setting Scheduled Incremental Update" in Incremental Increase Example.

Duplicate Data Caused by Repeated Execution of Incremental Update

If you accidentally repeat the update when using the Incremental Update function, you may get into the trouble of duplicate data in FineBI.

In this case, you only need to use the Full-Volume Update function once, and the data in the FineBI engine will be overwritten by the data in the database to maintain consistency.

Disk Space Remaining Unchanged After Incremental Deletion

After you use the Incremental Deletion function, your disk space may not decrease accordingly.

FineBI does not immediately release your disk space after you use the Incremental Deletion function, FineBI will mark the data as deleted instead.

When the volume of the marked data reaches a certain quantity, FineBI will delete the marked data together to release your disk space.

Inconsistent System Time of Database and FineBI Server

When the system time of the FineBI server is inconsistent with that of the database, the repeated update is prone to appear.

You are advised to set the statement of Incremental Update to Where Time > Last Update Time and Time <= Current Update Time. (You can click the Last Update Time icon and the Current Update Time icon right next to the Parameter to add your statement.)