How to Merge two datasets(direct query mode) with same fields from 2 different datasources into a single dataset

Question & Answer
, , ,
2

Hi @sandeepagrawal921 - when you say merge, I understand you’re trying to union / append rows from two different tables having the same columns but are from two different data sources. Unfortunately, I don’t think you can do that with QuickSight today. You could have used custom SQL if the data source was the same, but unfortunately that’s not the case either from what you have described.

The workarounds that I can think of would of course be limited by the specific constraints you may have, but here are two suggestions:

  1. Create a data pipeline / ETL job outside of QuickSight to combine the data from the different sources; you could make use of Athena and/or Redshift

  2. If you were to maintain two different QuickSight assets, I understand the only difference would be the second dashboards pointing to a different dataset from a different data source having separate client-specific data. You could look at automating that process (and hence shouldn’t require a lot of efforts on an ongoing basis) using the new assets API that QuickSight launched recently, details here: Automate and accelerate your Amazon QuickSight asset deployments using the new APIs

Hope this helps, thanks!

1 Like