My source tables have changed and the dataset gives an error that one column is missing which is expected but I cant seem to find a resolution for it. Should it not ideally take whatever is the latest schema on its own?
Second part of my question is to check if there is a way I can convert it into a custom SQL?
QuickSight does not automatically detect or adapt to schema changes in underlying data sources. The dataset will continue to look for the original schema.
Handle missing columns: When a previously used column is no longer present, you will get an error as you observed. To resolve it, you can edit the dataset and remove the reference to the missing column. This will allow the dataset to load again.
Use custom SQL: Yes, you can use custom SQL in the dataset to adapt to schema changes if and only if, obviously, the data source is SQL-based. When using custom SQL, you have full control over the query and can modify the SELECT clause to adapt to structural changes in the tables.
To convert to custom SQL:
– Edit the dataset
– Switch to the “Edit/Preview data” tab
– Click on “Switch to custom SQL”
– Write a SQL query to handle the new schema
– Save the dataset
The key advantages of custom SQL are the flexibility to handle structural changes and having full control over the final query.
Let me know if you have any other questions!
Did this solution work for you? I am marking this as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
Hi Gillepa, I wonder if you could show screenshots of you converting a dataset to custom SQL, I can’t find any tab for edit/preview data, there is however an option to edit/preview data when creating a dataset but as far as I can tell there is no option if you’ve already created the dataset.
Hi @gillepa, I’m running into the same issue as JJBD here. I attempted to follow your instructions and I’m stuck on step 2. I just don’t see the “Edit/Preview data” tab in the dataset editor (I see the table queries on the grid view and can access the previews for each table, but otherwise there are no other tabs).
Is it possible this is role restricted? Would I need to reach out to my org’s BI team/admins for permission?
I have done some digging around and I believe “Switch to custom SQL” was part of old UI.
I hope the understanding is not that QuickSight will automatically convert your existing dataset into Custom SQL. QuickSight does not provide this capability. You can use custom sql to create new datasets or modify existing datasets
@Koushik_Muthanna , Yes indeed, my understanding is NOT that QuickSight actually try to come up with some SQL.
Thus two questions remain:
Is there a way to invalidate the solution in this thread?
Not that not having this feature, or (apparently) having lost this feature can cause a lot of work. It means that you need to create a new dataset that IS custom SQL, rebuild every custom fields and replace the non-custom dataset with the custom one in every downstream object. Which is tedious, thus:
is there a way to get this back? do you keep a list of change requests?