QuickSight - Incremental refresh

Introduction:

Amazon QuickSight stands as a powerful cloud-based business intelligence service, empowering users to craft interactive dashboards and insightful reports. Within its arsenal of features, the incremental refresh functionality stands out, allowing users to efficiently update datasets with only the changed or new data, minimizing processing time and enhancing overall performance. This guide takes you through the step-by-step process of setting up incremental refresh for your dataset in QuickSight for Redshift (SQL).

Step 1: Access Your Datasets

Begin by navigating to your QuickSight account and accessing the datasets section.

Step 2: Choose Your Dataset

Select the dataset you want to enable incremental refresh for and click on the dataset to proceed.

Step 3: Add new schedule

Within the dataset go to Refresh tab, locate and click on the three dots in the upper right corner. From the dropdown menu, select “Add new schedule.”

Step 4: Choose Incremental Refresh

In the scheduling options, opt for “Incremental refresh” as your refresh type. Set the start date for the incremental refresh and choose the frequency — whether it’s hourly, daily, weekly, etc. and column that would be then used as limit in SQL generated Query generated by QS as WHERE TIMESTAMP MINUS XY DAYS

Step 5: Perform Full Refresh

After configuring the incremental refresh schedule, QuickSight will execute a full refresh of the dataset. Unfortunately, there is currently no way to avoid this initial full refresh.

Step 6: Schedule/Modify Refresh Time

Once the full refresh is complete, navigate to the “Actions” section below to schedule or modify the refresh time. This provides flexibility in aligning with your workflow.

Step 7: Configure Date Field and Window Size

To ensure seamless incremental refresh, configure the date field that QuickSight will use to identify changes. Also, set the window size, specifying the number and unit.

• Window Size (Number): Determines how far back in time data is erased and reloaded for each incremental refresh.
• Window Size (Unit): Specifies how often the incremental reload is executed.

By properly configuring these settings, you optimize QuickSight to accurately identify and update only the necessary data during incremental refreshes, enhancing overall efficiency.

Addressing the Aggregation Challenge:

While incremental refresh offers significant benefits, it’s crucial to note a common pitfall related to the choice of the incremental refresh field. When designating a field as the incremental refresh field, it’s advisable to avoid selecting an aggregated column.

Aggregated fields, when chosen for incremental refresh, can inadvertently lead to a process similar to a full refresh. QuickSight may need to scan over the entire dataset to identify missing data, resulting in diminished efficiency gains.

Best Practice: Use Non-aggregated Columns for Incremental Refresh Fields:

To harness the full potential of QuickSight’s incremental refresh, it is recommended to use non-aggregated columns, especially when dealing with date-based fields. This strategic choice ensures that the incremental refresh process can efficiently pinpoint and add only the missing data, steering clear of unnecessary data scanning.

Conclusion:

Incorporating incremental refresh into your QuickSight dataset is a straightforward process that involves a few key steps. This guide provides a comprehensive understanding of QuickSight’s incremental refresh feature, from its mechanics to best practices. By following these steps and recommendations, users can harness the power of incremental refresh to keep their analyses up-to-date with minimal disruption and improved overall performance.

Generated by AI with human guidance.

Clap follow for more