Importing a view from redshift in SPICE

Hello,

I have a theoretical question:
What happen if I create a dataset in quicksight using a view from Redshift and I save that in Spice?

My first approach was to create a materialized view in redshift, import as dataset the materialized view and then have a scheduled query in redshift to refresh the materialized view (was not possible to have the auto-refresh).

Is there any difference in performance using a view instead? will the view automatically update whenever I open the report and show the latest result?

Thank you

Hi @remba87 - Thanks for posting the question. This is an interesting question, couple of feedback from my side.

  1. When you have a view in Redshift and create a data set on top of view, make the data set SPICE, this is good when the multiple users are going to use same data set for reports. This will avoid the query in database and data will be fetched from SPICE and performance will be faster. You can schedule the refresh the SPICE in QuickSight and the management is easy .

  2. When you will create a materialize view, essentially the result of the view will be stored in the materialized view, one draw back every time when users will use this materialize view, every time the query will hit to database and also you have maintain out of QuickSight and ensure the data in the materialized view refreshed timely fashion.

If from my experience, suggestion option 1 is a better solution, create a view, bring the data to SPICE and do the reporting.

Tagging @Ashok for his feedback as well.

Regards -Sanjeeb

1 Like

Hi again @Sanjeeb2022, thank you for the precious help.

I think also that option 1 is better now. My missing knowledge was the autorefresh in Quicksight…I did not know I could have done that.

So my idea was to create a Materialized view in redshift, do the link RS-QS and refresh the MV in redshift…

but it is way better to connect a view in QS, and then refresh the data using QS refresh.I think it is way more efficient in this way.

Thank you!

1 Like

Hi @remba87 - Thanks, yes once you created the data set, during the import, you can select SPICE refresh so that data will come to SPICE. There are many options are available in SPICE, you can also do incremental refresh based on some column or Full refresh on time interval ( hour, daily, weekly etc). The details on SPICE can be found -
Refreshing SPICE data - Amazon QuickSight.

Hope these details will help you and please marked the suggestion as solution so that it will help to wider community.

Have a great week ahead.

Regards -Sanjeeb

1 Like

Just to be sure,
I noticed that when the dataset is refreshing, the data became unavailable in the analysis, but if I publish a dashboard with that data, the published dashboard will not be affected by this delay?

Hi @remba87 - It is a good practice to refresh the SPICE first ( possibly after all your daily etl done) and once data is available in SPICE, you can see the information in analysis and dashboard. During SPICE refresh ( if it is refreshed FULL mode), i guess you can not see the data in dashboard. Better to wait for spice to refresh first .

One of the option you can explore incremental refresh of SPICE where new changes will be updated but SPICE will have the data. For incremental, you need to understand which field can be used to identify the increments.

Regards - Sanjeeb

Hi @Sanjeeb2022, thank you for the fast answers as always!
the problem is that I need to refresh the data every 15 minutes…I did a test and it seems that while in the analysis the data was not showing during refresh, in the dashboard it looked everything ok!

doing additional test right now

Hi @remba87 - Ok, if your data refresh schedule is 15 mins, the SPICE may not be a solution. at present SPICE refresh interval is at least 1 hour.

@Ashok- Is there any road map to have SPICE refresh schedule in 15 mins , 30 mins ( near real time use cases). Please advise.

Regards - Sanjeeb

@Sanjeeb2022 it is possible to do a 15 minutes schedule for incremental update :slight_smile:

1 Like

Hi @remba87

SPICE dataset with customer SQL supports 15 mins incremental refresh. more information available here

2 Likes

Hello @Ashok,

I saw that, thank you.

You know what would be super cool? a crono shedule like in Redshift… right now I set up a schedule every 15 minutes but it will be cool if I could select specific times during the day and specific day of the week, maybe this can be a nice feature to add?

1 Like