Please use Custom SQL or create a view in your RDS to join all the required tables before creating a dataset in QuickSight.
You do not need to create one dataset per table unless you specifically need them isolated. Creating multiple datasets and then trying to combine them later requires using SPICE, which is not efficient for most use cases.
Use Custom SQL during dataset creation to define joins.
Or create a database view that pre-joins your required tables.
Please refer to the below documentation this might be helpful for you.
Thanks for the great response and resources @Xclipse.
Allow me to refine my scenario somewhat.
I have been to able create a successfully report using custom queries.
As a developer that works fine.
However for my use case I would like to facilitate non technical users to access and analyse data on our database. I would like to say: “here are all of our tables, you can view them and filter them and if you want the join them you can do so easily using the web UI”.
For this flow using an sql dataset or a predefined join view would defeat the purpose.
Any suggestion how I might accomplish this? Thanks again
However for my use case I would like to facilitate non technical users to access and analyse data on our database. I would like to say: “here are all of our tables, you can view them and filter them and if you want the join them you can do so easily using the web UI”.
This would mean all your non technical users are Authors. You can just give them access to the RDS datasource which you created in QuickSight. They can create datasets using the UI and within the data preparation, it can be a single table or joined tables.
The blog describes joining 2 different data sources. In your scenario, it’s 1 datasource which is RDS .
Makes sense. Seems like the way forward is a single data source (eg RDS)
Users can view the existing tables by using the pre-existing datasets (one per table) that I’ll set up.
To join tables they can create a new dataset.