Quicksight: Changing data source for a dataset

Question

My organization is looking to change BI tools and I'm working on duplicating the currently implemented data models and dashboards over in Quicksight.

In our case, the data is modeled exactly the same across clients. Each client has their own separate database (data source) which is the exact same structure. In our current tool, I'm able to copy a 'Dataset' and then update the duplicate Dataset's data source with the correct database information for that specific client. Once the new dataset is created from the template, we go in and update the new data set to adjust for client specific customizations.

I've figured out how to duplicate Datasets and Dashboards, however I'm unable to figure out how, or if it's even possible, to change the data source for any of the duplicated datasets.

Answer 1

This is not possible AFAIK in the Quicksight UI, sadly. In the Quicksight UI it is possible to create a new Data Source manually and then create Datasets from that Data Source and if the Dataset was a simple SQL query etc you could just copy the query across. But this will not copy across any filters, calculated fields etc and that is what I suspect you want to do.

We use the AWS Quicksight CLI version 2 to programmatically deploy new and updated Datasets and Data Sources to our Production environment from my BI development environment using Python scripts inside AWS Lambda functions. You can however simply do it all from your own terminal / coding environment as long as your user account has all the relevant AWS permissions for Quicksight.

The CLI has the commands you would need e.g. create-data-source and create-data-set. The CLI also support for a variety of languages in addition to Python.

Using the Quicksight UI I would set up my first Data Source, Dataset(s) and also create a base Analysis (with common Filters, Calculated Fields, Visualisations etc) and save that as a Dashboard. Once all that is done you can use the AWS Quicksight CLI to create-template:

A template is an entity in Amazon QuickSight that encapsulates the metadata required to create an analysis and that you can use to create s dashboard. A template adds a layer of abstraction by using placeholders to replace the dataset associated with the analysis. You can use templates to create dashboards by replacing dataset placeholders with datasets that follow the same schema that was used to create the source analysis and template.

Then, I would use the AWS CLI to get the metadata for the Dataset you created above (list-data-sets -> describe-data-set). Then using (Python) script with some dictionaries of all your customers Database details to create-data-source for each. Then use list-data-sources, to get all the DataSources -> Arn, and finally use a script to create-data-set for each customer, replacing the DataSourceArn each time (we use dictionaries which are 'easy' to update in a loop).

Hope this gives you a start, Cheers