AWS Big Data Blog
Automate deployment of an Amazon QuickSight analysis connecting to an Amazon Redshift data warehouse with an AWS CloudFormation template
Amazon Redshift is the most widely used data warehouse in the cloud, best suited for analyzing exabytes of data and running complex analytical queries. Amazon QuickSight is a fast business analytics service to build visualizations, perform ad hoc analysis, and quickly get business insights from your data. QuickSight provides easy integration with Amazon Redshift, providing native access to all your data and enabling organizations to scale their business analytics capabilities to hundreds of thousands of users. QuickSight delivers fast and responsive query performance by using a robust in-memory engine (SPICE).
As a QuickSight administrator, you can use AWS CloudFormation templates to migrate assets between distinct environments from development, to test, to production. AWS CloudFormation helps you model and set up your AWS resources so you can spend less time managing those resources and more time focusing on your applications that run in AWS. You no longer need to create data sources or analyses manually. You create a template that describes all the AWS resources that you want, and AWS CloudFormation takes care of provisioning and configuring those resources for you. In addition, with versioning, you have your previous assets, which provides the flexibility to roll back deployments if the need arises. For more details, refer to Amazon QuickSight resource type reference.
In this post, we show how to automate the deployment of a QuickSight analysis connecting to an Amazon Redshift data warehouse with a CloudFormation template.
Our solution consists of the following steps:
- Create a QuickSight analysis using an Amazon Redshift data source.
- Create a QuickSight template for your analysis.
- Create a CloudFormation template for your analysis using the AWS Command Line Interface (AWS CLI).
- Use the generated CloudFormation template to deploy a QuickSight analysis to a target environment.
The following diagram shows the architecture of how you can have multiple AWS accounts, each with its own QuickSight environment connected to its own Amazon Redshift data source. In this post, we outline the steps involved in migrating QuickSight assets in the dev account to the prod account. For this post, we use Amazon Redshift as the data source and create a QuickSight visualization using the Amazon Redshift sample TICKIT database.
The following diagram illustrates flow of the high-level steps.
Before setting up the CloudFormation stacks, you must have an AWS account and an AWS Identity and Access Management (IAM) user with sufficient permissions to interact with the AWS Management Console and the services listed in the architecture.
The migration requires the following prerequisites:
- A QuickSight enterprise account in the source and target accounts. For instructions, see Setting up for Amazon QuickSight.
- A connection between QuickSight and the Amazon Redshift instance. For instructions, refer to Authorizing connections from Amazon QuickSight to Amazon Redshift clusters.
- An Amazon Redshift cluster with sample data loaded. For instructions, see Using a sample dataset.
- You can use AWS Cloud9 or AWS CloudShell from the console to run AWS CLI commands.
Create a QuickSight analysis in your dev environment
In this section, we walk through the steps to set up your QuickSight analysis using an Amazon Redshift data source.
Create an Amazon Redshift data source
To connect to your Amazon Redshift data warehouse, you need to create a data source in QuickSight. As shown in the following screenshot, you have two options:
- Manual connect
QuickSight auto-discovers Amazon Redshift clusters that are associated with your AWS account. These resources must be located in the same Region as your QuickSight account.
For more details, refer to Authorizing connections from Amazon QuickSight to Amazon Redshift clusters.
You can also manually connect and create a data source.
Create an Amazon Redshift dataset
The next step is to create a QuickSight dataset, which identifies the specific data in a data source you want to use.
For this post, we use the TICKIT database created in an Amazon Redshift data warehouse, which consists of seven tables: two fact tables and five dimensions, as shown in the following figure.
This sample database application helps analysts track sales activity for the fictional TICKIT website, where users buy and sell tickets online for sporting events, shows, and concerts.
- On the Datasets page, choose New dataset.
- Choose the data source you created in the previous step.
- Choose Use custom SQL.
- Enter the custom SQL as shown in the following screenshot.
The following screenshot shows our completed data source.
Create a QuickSight analysis
The next step is to create an analysis that utilizes this dataset. In QuickSight, you analyze and visualize your data in analyses. When you’re finished, you can publish your analysis as a dashboard to share with others in your organization.
- On the All analyses tab of the QuickSight start page, choose New analysis.
The Datasets page opens.
- Choose a dataset, then choose Use in analysis.
- Create a visual. For more information about creating visuals, see Adding visuals to Amazon QuickSight analyses.
Create a QuickSight template from your analysis
A QuickSight template is a named object in your AWS account that contains the definition of your analysis and references to the datasets used. You can create a template using the QuickSight API by providing the details of the source analysis via a parameter file. You can use templates to easily create a new analysis.
You can use AWS Cloud9 from the console to run AWS CLI commands.
The following AWS CLI command demonstrates how to create a QuickSight template based on the sales analysis you created (provide your AWS account ID for your dev account):
parameter.json file contains the following details (provide your source QuickSight user ARN, analysis ARN, and dataset ARN):
You can use the AWS CLI describe-user, describe_analysis, and describe_dataset commands to get the required ARNs.
To upload the updated
parameter.json file to AWS Cloud9, choose File from the tool bar and choose Upload local file.
The QuickSight template is created in the background. QuickSight templates aren’t visible within the QuickSight UI; they’re a developer-managed or admin-managed asset that is only accessible via the AWS CLI or APIs.
To check the status of the template, run the
The following code shows command output:
Copy the template ARN; we need it later to create a template in the production account.
The QuickSight template permissions in the dev account need to be updated to give access to the prod account. Run the following command to update the QuickSight template. This provides the describe privilege to the target account to extract details of the template from the source account:
TemplatePermission.json contains the following details (provide your target AWS account ID):
To upload the updated
TemplatePermission.json file to AWS Cloud9, choose the File menu from the tool bar and choose Upload local file.
Create a CloudFormation template
In this section, we create a CloudFormation template containing our QuickSight assets. In this example, we use a YAML formatted template saved on our local machine. We update the following different sections of the template:
Some of the information required to complete the CloudFormation template can be gathered from the source QuickSight account via the
describe AWS CLI commands, and some information needs to be updated for the target account.
Create an Amazon Redshift data source in AWS CloudFormation
In this step, we add the AWS::QuickSight::DataSource section of the CloudFormation template.
Gather the following information on the Amazon Redshift cluster in the target AWS account (production environment):
- VPC connection ARN
- Cluster ID
You have the option to create a custom
DataSourceID. This ID is unique per Region for each AWS account.
Add the following information to the template:
Create an Amazon Redshift dataset in AWS CloudFormation
In this step, we add the AWS::QuickSight::DataSet section in the CloudFormation template to match the dataset definition from the source account.
Gather the dataset details and run the
list-data-sets command to get all datasets from the source account (provide your source dev account ID):
The following code is the output:
describe-data-set command, specifying the dataset ID from the previous command’s response:
The following code shows partial output:
Based on the dataset description, add the
AWS::Quicksight::DataSet resource in the CloudFormation template, as shown in the following code. Note that you can also create a custom
DataSetID. This ID is unique per Region for each AWS account.
You can specify
ImportMode to choose between
Create a QuickSight template in AWS CloudFormation
In this step, we add the AWS::QuickSight::Template section in the CloudFormation template, representing the analysis template.
Use the source template ARN you created earlier and add the
AWS::Quicksight::Template resource in the CloudFormation template:
Create a QuickSight analysis
In this last step, we add the AWS::QuickSight::Analysis section in the CloudFormation template. The analysis is linked to the template created in the target account.
AWS::Quicksight::Analysis resource in the CloudFormation template as shown in the following code:
Deploy the CloudFormation template in the production account
To create a new CloudFormation stack that uses the preceding template via the AWS CloudFormation console, complete the following steps:
- On the AWS CloudFormation console, choose Create Stack.
- On the drop-down menu, choose with new resources (standard).
- For Prepare template, select Template is ready.
- For Specify template, choose Upload a template file.
- Save the provided CloudFormation template in a .yaml file and upload it.
- Choose Next.
- Enter a name for the stack. For this post, we use
- Choose Next.
- Choose Next again.
- Choose Create Stack.
The status of the stack changes to
CREATE_IN_PROGRESS, then to
Verify the QuickSight objects in the following table have been created in the production environment.
|QuickSight Object Type||Object Name (Dev)||Object Name ( Prod)|
The following example shows that
Sales Analysis was created in the target account.
This post demonstrated an approach to migrate a QuickSight analysis with an Amazon Redshift data source from one QuickSight account to another with a CloudFormation template.
For more information about automating dashboard deployment, customizing access to the QuickSight console, configuring for team collaboration, and implementing multi-tenancy and client user segregation, check out the videos Virtual Admin Workshop: Working with Amazon QuickSight APIs and Admin Level-Up Virtual Workshop, V2 on YouTube.
About the author
Sandeep Bajwa is a Sr. Analytics Specialist based out of Northern Virginia, specialized in the design and implementation of analytics and data lake solutions.