How to Configure Incremental Refresh in Tableau?
Before we dive into the steps to configure incremental refresh in Tableau, let’s first understand what incremental refresh is, why to use it, when to use it, and when not to use it!
What is Incremental Refresh?
Incremental refresh is a type of data extract that adds new data records to the already existing data extract. The word incremental suggests that the number of rows in the data extract which is already in use increases without making any changes to the original data extract. In simpler words, incremental refresh adds new rows to the previous data extract.
The below image is a high-level representation of the Incremental Refresh concept in Tableau!
When we perform an incremental refresh, Tableau sends a request to the data extract. In case new rows are added to the dataset, it pulls only the newly added rows from the data source into the data extract. The data extract then sends the data back into Tableau, and changes are reflected in your tables and graphs.
Why use Incremental Refresh?
Now to address the big question: why use an incremental refresh? Why not just use live connections or full extracts? There are two reasons someone would prefer using incremental refresh:
It takes much less time to pull the newly added data from a data source. Instead, if you were to perform a full extract, it can take hours for the changes to take place in case you are connected to a huge data source.
Less load on the server
If you are working for a company that runs Tableau on a server, then you should avoid overloading the server by performing an incremental extract. Incremental refresh, in this case, is the most preferred way of pulling new data from data sources.
When to use an incremental refresh?
Incremental refresh can be very useful in some cases. In fact, it could be just what you need to make your Tableau workflow more efficient.
Dynamic Data Source
The first and the most critical use case of Incremental Refresh is when you are connected to a data source that updates frequently. For instance, you work for a web analytics company that records the number of visits to a website. Each user visit is stored in a database, and the number of these visits can be insanely high. Using incremental refresh, in this case, is a must; otherwise, you will have no option but to wait for the entire data set to load into Tableau before you start analyzing the data. The load time can take hours in some cases. The best option, therefore, is to use Incremental Refresh, which will load only the newly added rows rather than loading the entire data into Tableau.
Fixed Data Structure
Use incremental refresh when you are sure that the underlying data structure or database schema will not change. This is essential for an incremental refresh to work properly because if the underlying database schema changes, the incremental refresh will no longer work. Incremental refresh only pulls new rows, not new columns.
When not to use Incremental Refresh?
Incremental Refresh is an excellent technique for pulling new records from a dataset without loading the entire dataset all over again. But what happens when a row(or record) is deleted or updated in a dataset? Let’s see some scenarios where you should avoid the Incremental Refresh approach!
Deletion of Data from the data source
If you go ahead and delete one of the rows from the dataset that we have been working with and then perform incremental refresh following step 4, you will notice that the bar graphs stay the same. This is because incremental refresh only pulls new records added to a dataset and does not pull any changes made.
Update in data in the data source
The same explanation holds when we update values in a dataset. If you’d like, go ahead and change the Mobile Phones price to 70, perform an Incremental Refresh, and you’ll see that the bars stay the same. Incremental refresh does not make changes to the existing data extract.
In both cases above, you’ll have to perform a Full Extract to make the Incremental Extract function again.
Step-by-Step Configuration of Incremental Refresh in Tableau
Now that we understand what Incremental Refresh is, let’s go ahead and see how we can configure Incremental Refresh in Tableau.
The dataset that we’ll use for demonstration purposes is shown below. Here’s the link to the dataset we use in this post. Please feel free to download the dataset and practice yourself. The dataset contains sales data for an electronics store. We’ll be making multiple iterations to this dataset throughout this tutorial.
Step 1: Open up Tableau, and load the dataset provided into it.
Step 2: Select Extract as the connection type. Then click on Edit next to the Extract option.
Step 3: When you click edit, a window named Extract Data will appear on your screen. This is where we will configure our incremental refresh.
Check the Incremental Refresh box.
Next, in the Table to refresh, select the sheet name containing your data.
Then, in the Identify new rows using column, select Order ID. You can select any other column as well. For instance, if you would like to refresh your data based on dates, then go ahead and select the Order Date column. In our example, we will use Order ID.
Once done with the above steps, simply click OK.
When you click Sheet 1 in the Tableau workbook, a window will appear asking you to save your extract file. You can save this file anywhere you like. The best practice is to keep the file in the same directory which contains your dataset.
Step 4: The above steps complete the configuration of Incremental Refresh in Tableau. Whenever you want to pull in new data from the data source or perform Incremental Refresh, simply click on Data > Data Extract File > Extract > Refresh (Incremental).
Testing Incremental Refresh in Tableau
Now that we have configured Incremental Refresh, let’s test it out. I’ll make a simple bar graph from the data we loaded into Tableau.
Currently, we have six rows of data in our dataset. Let’s add a new row to the dataset and see if Incremental Refresh will fetch the new rows from the dataset.
After adding the new row, our dataset looks similar to the image shown below:
Now, let’s perform an incremental refresh on our data. Keep an eye out for how the mobile phone bar changes when the refresh is completed. To perform an incremental refresh, refer back to Step 4.
We see that the bar representing Mobile Phones moves from 100 to 150. Behind the scenes, the Incremental Refresh pulls only the new data row from the data source. It adds it to the data extract without importing the entire dataset into Tableau.