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.
Solution overview
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.
Prerequisites
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:
- Auto-discovered
- 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):
aws quicksight create-template --aws-account-id <DEVACCOUNT>--template-id QS-RS-SalesAnalysis-Template --cli-input-json file://parameters.jsonThe parameter.json file contains the following details (provide your source QuickSight user ARN, analysis ARN, and dataset ARN):
{
"Name": "QS-RS-SalesAnalysis-Temp",
"Permissions": [
{"Principal": "<QS-USER-ARN>",
"Actions": [ "quicksight:CreateTemplate",
"quicksight:DescribeTemplate",
"quicksight:DescribeTemplatePermissions",
"quicksight:UpdateTemplate"
] } ] ,
"SourceEntity": {
"SourceAnalysis": {
"Arn": "<QS-ANALYSIS-ARN>",
"DataSetReferences": [
{
"DataSetPlaceholder": "sales",
"DataSetArn": "<QS-DATASET-ARN>"
}
]
}
},
"VersionDescription": "1"
}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 describe-template command:
aws quicksight describe-template --aws-account-id <DEVACCOUNT> --template-id "QS-RS-SalesAnalysis-Temp"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:
aws quicksight update-template-permissions --aws-account-id <DEVACCOUNT> --template-id “QS-RS-SalesAnalysis-Temp” --grant-permissions file://TemplatePermission.jsonThe file TemplatePermission.json contains the following details (provide your target AWS account ID):
[
{
"Principal": "arn:aws:iam::<TARGET ACCOUNT>",
"Actions": [
"quicksight:UpdateTemplatePermissions",
"quicksight:DescribeTemplate"
]
}
]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
- CRISP DETAIL, CLEAR DISPLAY: Enjoy binge-watching...
- PRO SHOTS WITH EASE: Brilliant sunrises, awesome...
- CHARGE UP AND CHARGE ON: Always be ready for an...
- POWERFUL 5G PERFORMANCE: Do what you love most —...
- NEW LOOK, ADDED DURABILITY: Galaxy A54 5G is...
- Free 6 months of Google One and 3 months of...
- Pure Performance: The OnePlus 12 is powered by the...
- Brilliant Display: The OnePlus 12 has a stunning...
- Powered by Trinity Engine: The OnePlus 12's...
- Powerful, Versatile Camera: Explore the new 4th...
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:
- AWS::QuickSight::DataSource
- AWS::QuickSight::DataSet
- AWS::QuickSight::Template
- AWS::QuickSight::Analysis
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
- Host
- Port
- Database
- User
- Password
- 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:
Resources:
RedshiftBuildQSDataSource:
Type: 'AWS::QuickSight::DataSource'
Properties:
DataSourceId: "RS-Sales-DW"
AwsAccountId: !Sub ${AWS::ACCOUNT ID}
VpcConnectionProperties:
VpcConnectionArn: <VPC-CONNECTION-ARN>
Type: REDSHIFT
DataSourceParameters:
RedshiftParameters:
Host: "<HOST>"
Port: <PORT>
Clusterid: "<CLUSTER ID>"
Database: "<DATABASE>"
Name: "RS-Sales-DW"
Credentials:
CredentialPair:
Username: <USER>
Password: <PASSWORD>
Permissions: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):
aws quicksight list-data-sets --aws-account-id <DEVACCOUNT>The following code is the output:
Run the describe-data-set command, specifying the dataset ID from the previous command’s response:
aws quicksight describe-data-set --aws-account-id <DEVACCOUNT> --data-set-id "<YOUR-DATASET-ID>"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.
QSRSBuildQSDataSet:
Type: 'AWS::QuickSight::DataSet'
Properties:
DataSetId: "RS-Sales-DW"
Name: "sales"
AwsAccountId: !Sub ${AWS::ACCOUNT ID}
PhysicalTableMap:
PhysicalTable1:
CustomSql:
SqlQuery: "select sellerid, username, (firstname ||' '|| lastname) as name,city, sum(qtysold) as sales
from sales, date, users
where sales.sellerid = users.userid and sales.dateid = date.dateid and year = 2008
group by sellerid, username, name, city
order by 5 desc
limit 10"
DataSourceArn: !GetAtt RedshiftBuildQSDataSource.Arn
Name"RS-Sales-DW"
Columns:
- Type: INTEGER
Name: sellerid
- Type: STRING
Name: username
- Type: STRING
Name: name
- Type: STRING
Name: city
- Type: DECIMAL
Name: sales
LogicalTableMap:
LogicalTable1:
Alias: sales
Source:
PhysicalTableId: PhysicalTable1
DataTransforms:
- CastColumnTypeOperation:
ColumnName: sales
NewColumnType: DECIMAL
Permissions:
- Principal: !Join
- ''
- - 'arn:aws:quicksight:'
- !Ref QuickSightIdentityRegion
- ':'
- !Ref 'AWS::AccountId'
- ':user/default/'
- !Ref QuickSightUser
Actions:
- 'quicksight:UpdateDataSetPermissions'
- 'quicksight:DescribeDataSet'
- 'quicksight:DescribeDataSetPermissions'
- 'quicksight:PassDataSet'
- 'quicksight:DescribeIngestion'
- 'quicksight:ListIngestions'
- 'quicksight:UpdateDataSet'
- 'quicksight:DeleteDataSet'
- 'quicksight:CreateIngestion'
- 'quicksight:CancelIngestion'
ImportMode: DIRECT_QUERYYou can specify ImportMode to choose between Direct_Query or Spice.
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:
QSTCFBuildQSTemplate:
Type: 'AWS::QuickSight::Template'
Properties:
TemplateId: "QS-RS-SalesAnalysis-Temp"
Name: "QS-RS-SalesAnalysis-Temp"
AwsAccountId:!Sub ${AWS::ACCOUNT ID}
SourceEntity:
SourceTemplate:
Arn: '<SOURCE-TEMPLATE-ARN>'
Permissions:
- Principal: !Join
- ''
- - 'arn:aws:quicksight:'
- !Ref QuickSightIdentityRegion
- ':'
- !Ref 'AWS::AccountId'
- ':user/default/'
- !Ref QuickSightUser
Actions:
- 'quicksight:DescribeTemplate'
VersionDescription: Initial version - Copied over from AWS account.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.
Add the AWS::Quicksight::Analysis resource in the CloudFormation template as shown in the following code:
QSRSBuildQSAnalysis:
Type: 'AWS::QuickSight::Analysis'
Properties:
AnalysisId: 'Sales-Analysis'
Name: 'Sales-Analysis'
AwsAccountId:!Sub ${AWS::ACCOUNT ID}
SourceEntity:
SourceTemplate:
Arn: !GetAtt QSTCFBuildQSTemplate.Arn
DataSetReferences:
- DataSetPlaceholder: 'sales'
DataSetArn: !GetAtt QSRSBuildQSDataSet.Arn
Permissions:
- Principal: !Join
- ''
- - 'arn:aws:quicksight:'
- !Ref QuickSightIdentityRegion
- ':'
- !Ref 'AWS::AccountId'
- ':user/default/'
- !Ref QuickSightUser
Actions:
- 'quicksight:RestoreAnalysis'
- 'quicksight:UpdateAnalysisPermissions'
- 'quicksight:DeleteAnalysis'
- 'quicksight:DescribeAnalysisPermissions'
- 'quicksight:QueryAnalysis'
- 'quicksight:DescribeAnalysis'
- 'quicksight:UpdateAnalysis' 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
QS-RS-CF-Stack. - Choose Next.
- Choose Next again.
- Choose Create Stack.
The status of the stack changes to CREATE_IN_PROGRESS, then to CREATE_COMPLETE.
- 【Octa-Core CPU + 128GB Expandable TF Card】...
- 【6.8 HD+ Android 13.0】 This is an Android Cell...
- 【Dual SIM and Global Band 5G Phone】The machine...
- 【6800mAh Long lasting battery】With the 6800mAh...
- 【Business Services】The main additional...
- 【Dimensity 9000 CPU + 128GB Expandable TF...
- 【6.8 HD+ Android 13.0】 This is an Android Cell...
- 【Dual SIM and Global Band 5G Phone】Dual SIM &...
- 【6800mAh Long lasting battery】The I15 Pro MAX...
- 【Business Services】The main additional...
- 🥇【6.8" HD Unlocked Android Phones】Please...
- 💗【Octa-Core CPU+ 256GB Storage】U24 Ultra...
- 💗【Support Global Band 5G Dual SIM】U24 Ultra...
- 💗【80MP Professional Photography】The U24...
- 💗【6800mAh Long Lasting Battery】With the...
Verify the QuickSight objects in the following table have been created in the production environment.
| QuickSight Object Type | Object Name (Dev) | Object Name ( Prod) |
| Data Source | RS-Sales-DW | RS-Sales-DW |
| Dataset | Sales | Sales |
| Template | QS-RS-Sales-Temp | QS-RS-SalesAnalysis-Temp |
| Analysis | Sales Analysis | Sales-Analysis |
The following example shows that Sales Analysis was created in the target account.
Conclusion
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.