Hi!
I need to use multiple fact tables in a single dataset, all connected to the same two dimension tables. Doing this via left joins with separate datasets in quicksight is taking too much space and generating huge final dataset. I am looking to model this via relationships as I would in Power BI or Tableau, is that possible within quicksight or is this roadmapped?
Thank you!
Hello @joana, I would say that if you are trying to control the number of rows you are ingesting a little more, you would have more luck creating the joins through a custom SQL query. This way you can build out where clauses in SQL and only retrieve the column values you want more specifically. The alternative option would be to limit the number of fields you bring in by importing each table into their own dataset, removing the fields you do not want, then joining the ingested dataset, but that likely won’t eliminate any rows of data.
I agree with @DylanM , custom sql is one of the efficient approach to restrict rows and do the join on the data sets. Bringing too much data to QS and then do the join is not a good idea as it will affect performance and your network traffic cost.
Regards - Sanjeeb
@DylanM @Sanjeeb2022 agreed about the custom sql - or creating a view via left joins of all of the facts. For this specific case I was looking at an alternative directly in a BI tool as it would be faster and probably less problematic with the aggregation certain metrics needed at sql level.
So for now, treatment needs to come before getting the data into quicksight.
Thanks to both!
Hello @joana, I completely understand. I wish I could give you a plug-and-play option that would be perfect for this scenario, but managing it through SQL is going to be the way to receive your desired output. If you have any questions when building out that custom SQL query, always feel free to post a new topic and we will do our best to assist you. Thank you!