Hello there. This question could be summarized as “should I still use Quicksight for this?”
I have several datasets from RDS databases imported in Quicksight using the “custom SQL query” option. The problem that emerged recently is that sometimes the datasets could change their structure (adding or deleting columns, basically).
This is not a problem when the changes are adding a new column (just editing the SQL query works fine). But recently some datasets were also needing to have some columns removed. This lead to errors in some analysis when I updated the query reflecting the changes (for example, deleting 2 columns and adding other 3). This is not a problem in a simple analysis (just build another) but in complex ones it is a problem (lot of time replicating them).
My questions are basically:
Which are the limits of changing the structure of a dataset? I checked that adding columns at the end works, but deleting columns breaks them.
Is Quicksight the correct tool for “dynamic” dataset? For “dynamic” I mean one that could have many columns removed or added (be it from a custom SQL query from RDS, for example)
In case the previous answer is “yes, but…”. Should I use another type of dataset instead of a custom SQL query?
Sorry, dynamic dataset structure changes in QuickSight is not supported yet with custom query datasets or static view/table datasets.
Here are some workarounds you can try,
When your custom query removes any columns, simply add a dummy column with the removed column name. When publishing the dataset, exclude the dummy columns so it won’t be visible to users.
Alternatively, create a new dataset from the updated custom query and replace it in your analysis. Once done, you can safely delete the old datasets.
Thanks for the reply Xclipse. I have another question.
When you said
dynamic dataset structure changes in QuickSight is not supported yet with custom query datasets or static view/table datasets
Does that mean that dynamic datasets (with changing structure) are not supported only for that type of dataset (custom query or static view/table from database) or in general?
I’m not that familiar with non-RDS/S3 data sources in Quicksight. If there is a data source that supports that capability that would be helpful.
Changing dataset structure is not supported across all data sources in QuickSight, including S3, RDS, Redshift, and others. This applies regardless of whether the dataset is based on a custom SQL query or a static view/table from the database.
