Hi everyone, wishing you all a Merry Christmas and a happy holiday season!
I have a question regarding merging various data sources into one.
I currently have this analysis here
However, I also want the exact same analysis for 2 more countries. This means that I will have to use a total of 6 datasets in total, 2 for each country (for 3 countries). I have access to those datasets too. All datasets share the same field nomenclature and have a similar structure. Is there a way for me to get a drop down menu, allowing me to select between Country 1, 2 and 3 and show my analysis based on the selection? If so, could I get a detailed steps on how I could achieve it?
Edit: the reason i’m considering merging everything into a single analysis is to reduce the number of dashboards and allow the whole analysis to be conducted within a single dashboard, which makes it significantly easier for any user.
Thank you!
Wishing you Merry Christmas and Happy Holiday season !!!.
Could you please provide below additional info for us to better recommend solution .
Wondering where Orders & OrderItems transaction details are stored S3 / RDS/Redshift ? curious to know why opensearch datasource connectors are used
Are the Orders & OrderItems transaction details are stored in specific aws region pertaining to each country to meet government data residency requirements ?
The best approach is to create another data on S3, a db view or use ETL Glue/Athena some where else to combine both the datasets.
I tried this method first but finally do as suggested
@Naveed So a new dataset has to be created that’s inclusive of all 3 countries, with perhaps a field targeted towards naming the country (String: ‘Country 1’, ‘Country 2’, ‘Country 3’)?
Yes create a new dataset which should included data from all countries and ensure country column is also included in the dataset .
Since all the transaction details are stored in S3 and in one region , you will be able to easily combine data in Athena tables & Athena Views. And refer to these Athena tables in QuickSight dataset to get unified dashboard.
You can find more details on Athena basics & tables in the below workshop.
Thank you Vinod! Besides creating a new dataset, is there no other way to approach this (by perhaps joining datasets from the three countries) or creating some parameter to control which dataset is being used in the analysis?
By joining similar datasets will result in duplicated columns names for each country , and as of now cannot dynamically pick a dataset based on user selection . Since all the data are available in S3 would recommend to write custom SQL and combine (UNIONALL) for all the countries in one dataset .
