I am creating a Quicksight dashboard that uses several tables all from the same AWS Postgres database. Most tables are set to “Direct Query” and are used by themselves, but one is set to “SPICE” and consists of 3 joined tables, two of which are also “Direct Query” tables that stand alone. The SPICE dataset is scheduled to refresh once a day and in theory the direct query tables should refresh once every 24 hrs as well.
For some reason, the data in the dashboard is not updating automatically like it should. It is several days behind and only adjusts to the correct value if I mess with the dashboard (ie create a random filter, apply it, and then delete it). This is true for the analysis that I am currently building as well as the drafts that I have published as dashboards.
I have no idea why this is happening or how to fix it. Any advice / clarification would be greatly appreciated.
Hello @RoboK, welcome to the QuickSight community! This is an interesting discussion. Direct Query datasets will not be included in any kind of data refresh because any time you view that dataset, it pulls the most current data from the datasource. The refresh only updates datasets that are set to SPICE because that data is being stored in QuickSight and must query the original data source to update.
Are you doing anything in the datasets that are set to direct query that would not allow them to be set to SPICE, like using a parameter to set a value in the SQL query? If that is the case, it might be storing a single snapshot of the joined tables in SPICE and is unable to query those data sources again.
I would say the main course of action would be to set up the base datasets that you are joining with SPICE and adding them to a refresh schedule as well to make this work as expected. Let me know if that helps!