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?
Hi @remba87 - Thanks for posting the question. This is an interesting question, couple of feedback from my side.
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 .
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.
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.
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.
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!
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?
