SPICE Incremental Refresh in Amazon QuickSight

Overview

Amazon QuickSight is a scalable business intelligence (BI) service built for the cloud that lets you share insights with everyone in your organization.

You can refresh the spice data at any time. Refreshing imports the data again into SPICE so that the data includes all changes since the last import. You can fully refresh your SPICE data whenever you want for Amazon QuickSight Standard Edition. You can always perform a full or incremental refresh for Amazon QuickSight Enterprise Edition (only for SQL-based data sources).

Introduction

With the incremental refresh, you can update SPICE datasets much more quickly than a full refresh, giving you access to the most up-to-date information much sooner. But this feature is only available for SQL-based data sources, such as Amazon Redshift, Amazon Athena, PostgreSQL, or SnowFlake. You can Schedule an incremental refresh to run up to every 15 minutes.

Customers can perform an incremental refresh by selecting a timestamp column from the database corresponding to an event, such as creation time, update time, or published date. They can also specify a look-back window, such as 30 days or 3 hours. If the data size in the look-back window is significantly less than the size of the entire data set, Amazon QuickSight can refresh the data changed within that look-back window in minutes rather than hours. Readers can access new data for large datasets in minutes instead of hours by allowing more frequent refreshes with smaller amounts of data.

Prerequisites

1. Amazon QuickSight enterprise edition with admin permissions.
2. Dataset imported from Athena data source into the SPICE capacity.

Steps to setup incremental refresh for a SQL-based SPICE Dataset

Step 1: Login into the Amazon QuickSight Enterprise Edition. Navigate to the left side of the pane and select the Datasets icon. Here you can see the input dataset imported from Amazon Athena data source into spice capacity. Click on the input dataset to open.

• The below screenshot contains the data in the input dataset.

Step 2: Click on the REFRESH NOW button.

Step 3: Configuring the incremental refresh.

• For Refresh type, choose Incremental refresh. Next, click on Configure Incremental Properties

• For the Date Column, select the timestamp column of the dataset from the drop-down list on which you want to base the look-back window.
• For Window size, enter a number for size, and then choose the time you want to look back for changes. You can set the window size here to hours, days, or weeks.
• Next, click on CONTINUE.

• Click on CONTINUE.

• Next, Click on REFRESH to confirm refresh.

Step 4: Checking the status of the refresh

• In the summary tab, you can see the refresh status as completed and how many rows are imported during the refresh. If the status fails, then the refresh is not successfully performed.

• In the refresh tab, under the History section, you can see at what time the spice refresh started, the status, the amount of time the refresh took from start to stop, several rows were ingested successfully, and the total number of rows in the spice capacity after refresh and refresh type.

Conclusion

This blog shows how to set up incremental refresh for SQL-based data sources in Amazon QuickSight. Incremental refresh saves time by updating records incrementally instead of refreshing the entire dataset. And it also reduces the overall consumption of resources needed. Here you can also schedule incremental refresh when the data must be refreshed.

About CloudThat