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

Question & Answer
, , ,
1

Hi,

Due to client-level financial data-related restrictions, we have the same tables in 2 different database servers (2 different data sources) with different client-specific data in them. but while generating the dashboards in aws quick sight, with row-level security enabled dataset(direct query mode to the database views) , we would like to combine data in a single quick sight dataset so that we can manage single quick sight assets (dataset, analysis, themes, and dashboard) for those different clients.

Do we have some solution around to merge those data from different data sources in a single dataset? Considering the data amount can be large enough (4-5 Million records in the final merged dataset including data for all the clients), on top of this row level security will be enabled based on the client permission.

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
3

Hi @sandeepagrawal921 - When you have 2 different data sources and join, the query mode is always be SPICE. I agree with @SD_QS you need to think the solution out of QuickSight you have to do the join.

Regards - Sanjeeb

4

Hi Sandeep,
I hope the above suggestions helped. I will also check with our product team for updates on when UNIONs might be supported. For now I will mark this topHc as closed. Please let us know if you have any further questions.

Thank you.

5

Thanks for the suggestions @SD_QS. Good to know the alternate approaches, can give them a thought.

But sad to hear that the default union/append of the dataset is not allowed in Quikcisght yet.
Thanks for the quick help. I appreciate.

6

Thanks for the info @Sanjeeb2022. We do not have any issue to use query mode as spice, but yeah based on the suggestions from @SD_QS, will look into the further solution.

7

Thanks @Asem, Will be a great help if can discuss it with the product team to support dataset UNION out-of-the-box with quicksight.

1 Like
8

Hi again. The product team is aware of this need and has this on their list. We will update again when we have timelines. Thanks for your patience!

2 Likes
9

Thanks @Asem. will be great to hear back about this functionality in near future

1 Like