How Refresh and Incremental Refresh Works in Bold BI

This article explains the difference between Full Refresh and Incremental Refresh in Bold BI and their impact on performance.

Configuring Refresh setting to data source

Bold BI uses an internal data store to process data from files and web-based connections. This data processing mode is called extract mode. All NoSQL databases, files, and REST API connections work in extract mode, where the data is extracted and moved into the data store. You can configure refresh settings to periodically update data in the intermediate data store from the actual source.

Full Refresh:

A Full Refresh in Bold BI updates the entire dataset from the data source at scheduled intervals. It re-imports all records from scratch, ensuring the dashboard reflects the complete and most accurate data.

You can configure refresh settings for a data source through any of the following ways:

https://www.boldbi.com/resources/blog/refresh-your-data-extracts-every-5-minutes/#scheduling-refresh-from-the-data-sources-listing-page

https://www.boldbi.com/resources/blog/refresh-your-data-extracts-every-5-minutes/#scheduling-refresh-from-data-source-designer-page

https://www.boldbi.com/resources/blog/refresh-your-data-extracts-every-5-minutes/#scheduling-refresh-from-new-data-source-window

How It Works:

When a full refresh is triggered:

• Bold BI connects to the original data source.
• It retrieves all the records, not just the new or updated ones.
• The previous dataset inside Bold BI is fully replaced with the freshly pulled data.
• This approach ensures that any records deleted from the source are also deleted in the Bold BI dataset, maintaining complete data integrity.

Example:

If you have 1 million rows in your database, a full refresh will reload all 1 million rows every time, even if only 10 rows changed.

Note:

Full refresh can be more time-consuming compared to incremental refresh, especially for large datasets.

Incremental Refresh

You can schedule incremental refreshes for any SQL-type data sources. To do so, connect the SQL data source in extract mode and choose the column with the time stamp as shown in the following image.

Incremental Refresh in Bold BI allows users to update or add only the rows that have changed based on a Last Modified column (or a similar marker). Instead of fetching the entire dataset each time, it retrieves only new or updated records since the last refresh, making it ideal for large datasets with frequent small updates.

How It Works:

• Bold BI identifies a Last Modified field (like a timestamp or updated date) in the source table.
• During each refresh, it checks which records have been added or changed since the last successful refresh, and it pulls only those changed rows instead of reloading the full dataset.
• This method reduces load on both the data source and Bold BI, improving refresh speed and system performance.

Example:

If yesterday you had 1 million rows and today 10 new rows were added, only those 10 rows will be fetched.

Benefits:

• Reduces load on the data source.
• Improves refresh speed and system performance.

Limitations:

• Supported only for SQL data sources like PostgreSQL, MySQL, and SQL Server.
• Requires a datetime column to track changes.
• Limited support for web data sources (e.g., Twilio, YouScan) with specific endpoints.

Performance Impact: Full vs. Incremental Refresh

• Without incremental refresh, Bold BI defaults to full refresh.
• While full refresh ensures data accuracy, it may impact performance for large datasets.
• Incremental refresh significantly improves performance by reducing data volume during each refresh.

Additional References:

https://support.boldbi.com/kb/article/13580/what-is-the-difference-between-the-live-and-extract-mode-data-sources-in-bold-bi
https://support.boldbi.com/kb/article/15123/optimizing-database-performance-with-indexed-views-in-sql-server