Pushing the Boundaries of Amazon QuickSight Through Automation

By Bhumika Sharma, Data Engineer – Quantiphi
By Rajas Walavalkar, Solution Architect – Quantiphi
By Sanchit Jain, Data Analytics & Cloud Practice Lead – Quantiphi
By Arturo Duarte, Sr. Partner Solution Architect – AWS

Quantiphi

Amazon QuickSight is an easy-to-use, cloud-powered analytics business intelligence (BI) tool that provides insights and analytics capabilities. QuickSight dashboards provide a visual representation of its graphical charts, which makes it easy to slice and dice data across different levels.

The conventional method to develop a QuickSight dashboard involves creating supporting resources which bind together in a single dashboard. In some cases, it could require significant time and manual work to create dashboards for each environment along with their data sources, datasets, and analytics.

In this post, we’ll share an approach to assist users in leveraging Amazon QuickSight as an enterprise-wide BI tool. The entire process, creation of data sources, datasets, analysis, and eventually dashboards, can be automated using AWS CodePipeline.

Along with dashboard deployment, we’ll explore QuickSight’s versioning capability with a CI/CD pipeline to deploy resources across various environments. This offers a better way of handling changes and implementing new features into production-sized dashboards.

Quantiphi is an AWS Premier Tier Services Partner and AWS Marketplace Seller with the Amazon QuickSight specialization. Quantiphi is pushing the boundaries of what QuickSight can offer and guiding best practices to use the offering.

A wide range of Quantiphi customers has analyzed QuickSight data to identify insights and patterns in the Amazon Web Services (AWS) environment through custom dashboards and solutions.

Drawbacks of the Conventional Method

Lack of automation: Each Amazon QuickSight resource will have to be manually created for all environments, which becomes a repetitive process and adds more complexity to the overall development process.
Increased time and effort: Due to the repetitive process and lack of automation in creating QuickSight resources, considerable time and effort go into this, which increases the overall cost of development.
Longer to integrate new features into existing dashboards: Dashboards must be updated manually in each environment, which can make managing versions of dashboards inefficient.
Increased chance of errors: As there are manual updates to the dashboard, it increases the chances of human error in the deployed dashboards.
Inefficient in scaling: Considering the implementation of an enterprise-level BI analytics platform with more than 100 productized dashboards, if you use the manual approach it can decrease the overall productivity.

Solution Overview

The approach here is to use a manually-created Amazon QuickSight analysis as a source entity to create QuickSight templates, and use these templates to create dashboards for the environments.

Figure 1 – Amazon QuickSight automation using CI/CD architecture.

Prerequisites

Amazon QuickSight account should be of the Enterprise Edition.
Manual Amazon QuickSight analysis using a QuickSight dataset only for the DEV environment.
AWS Systems Manager parameters:
- Parameter across different environments like DEV, UAT, and PROD to the following parameter:
  - Amazon QuickSight dataset id
  - Amazon QuickSight analysis id
  - Version number of templates and dashboards. The initial value must be 0.
Amazon QuickSight group name is required to share the QuickSight resources.
Amazon DynamoDB table to maintain the history of the dashboard versions. It stores the following details:
- versionID (partition key) – Version number of the QuickSight dashboard.
- inserted_time (sort key) – When the record has been inserted in the DynamoDB table.
- environment – The environment name for which the dashboard is updated.
- last_updated_time – The last time the manual analysis was updated.

Figure 2 – Amazon DynamoDB table with history.

Step 1: Add IDs into AWS Systems Manager Parameter

Once manual analysis and datasets are created, you can get the respective IDs and store them into different AWS Systems Manager parameters.

Step 2: Create AWS Lambda Function to Update Dashboard Versions

The following diagram shows the working of the AWS Lambda function:

Figure 3 – Flow diagram for updating versions of QuickSight dashboard.

Steps performed:
1. Call the Amazon QuickSight API to get the last updated time of the QuickSight manual analysis.
2. Get the value from AWS Systems Manager (which stores the dashboard version number).
3. If the value of the Systems Manager parameter is 0:
  - Create a new record and store the version_id, last_updated_time, inserted_time, and environment in the DynamoDB table.
  - Increment the version number in the SSM parameter by 1.

else:

- - If last_updated_time of analysis > last_updated_time stored in the DynamoDB table:
    1. Update the DynamoDB table with the new version and other details.
    2. Update the parameter with the new version number, enabling AWS CloudFormation templates to detect changes when “Release Change” hits AWS CodePipeline.

Step 3: Create a Data Source

To create a QuickSight data source, refer to the documentation.

Step 4: Create a Dataset

To create a QuickSight dataset, refer to the documentation.
Create two QuickSight datasets:
- RLS dataset – This is created to implement row-level security on the QuickSight dataset.
- Dataset – This is the primary table with the QuickSight dashboard data.

Step 5: Create a Template for DEV Environment

Create a QuickSight template from the manually created QuickSight datasets and analysis.
The source entity for QuickSight template:
- Source analysis – ID of the manually created QuickSight analysis.
- DatasetReferences – ID of the manually created dataset.
Below is the code to create the QuickSight template:

AWSTemplateFormatVersion: 2010-09-09
Description: Automated deployment of QuickSight Template
Parameters:
 env:
    Description: Name of the environment
    Type: String
 QuickSightGroup:
    Description: QuickSight Group name
    Type: String
 SsmParameter:
    Description: Name of the ssm Parameter that contains version no.
    Type: 'AWS::SSM::Parameter::Value<String>'
 SsmParameteranalysis:
    Description: Name of the ssm Parameter that contains analysis id
    Type: 'AWS::SSM::Parameter::Value<String>'
 SsmParameterdataset:
    Description: Name of the ssm Parameter that contains dataset id
    Type: 'AWS::SSM::Parameter::Value<String>'
Conditions:
 CreateDevResources: !Equals
  - !Ref env
  - develop
Resources :
 Templatedev:
   Type: AWS::QuickSight::Template
   Condition: CreateDevResources
   Properties:
     AwsAccountId: !Ref 'AWS::AccountId'
     TemplateId: !Sub ${env}-template-id
     Name: !Sub ${env}-template
     Permissions:
       - Actions:
            - 'QuickSight:CreateTemplate'
            - 'QuickSight:DescribeTemplate'
            - 'QuickSight:ListTemplates'
            - 'QuickSight:DescribeTemplatePermissions'
            - 'QuickSight:DeleteTemplate'
            - 'QuickSight:UpdateTemplate'
         Principal: !Join
            - ''
            - - 'arn:aws:QuickSight:'
              - !Ref 'AWS::Region'
              - ':'
              - !Ref 'AWS::AccountId'
              - ':group/default/'
              - !Ref QuickSightGroup
     SourceEntity:
       SourceAnalysis:
         Arn: !Join
           - ''
           - - 'arn:aws:QuickSight:'
             - !Ref 'AWS::Region'
             - ':'
             - !Ref 'AWS::AccountId'
             - ':analysis/'
             - !Select [ 0, !Split [ ",", !Ref SsmParameteranalysis ] ]
         DataSetReferences:
           - DataSetArn: !Join
               - ''
               - - 'arn:aws:QuickSight:'
                 - !Ref 'AWS::Region'
                 - ':'
                 - !Ref 'AWS::AccountId'
                 - ':dataset/'
                 - !Select [ 0, !Split [ ",", !Ref SsmParameterdataset ] ]
             DataSetPlaceholder: Dataset1
     VersionDescription: !Select [ 0, !Split [ ",", !Ref SsmParameter ] ]

Step 6: Create a Template for UAT and PROD Environments

The source entity for the template contains the Amazon Resource Name (ARN) of the template of the DEV environment.
Below is the code to create the QuickSight template for UAT and PROD environments. Except for the source entity part, all properties are the same as those of the DEV template.
Conditions in this template are:
- CreateUatResources – Creates resources for only UAT environment.
- CreateProdResources – Creates resources for only PROD environment.

SourceEntity:
      SourceTemplate:
        Arn: !Join
          - ''
          - - 'arn:aws:QuickSight:'
            - !Ref 'AWS::Region'
            - ':'
            - !Ref 'AWS::AccountId'
            - ':template/'
            - !If [CreateUatResources,!Sub "${env1}-template-id",!Ref "AWS::NoValue"]
            - !If [CreateProdResources,!Sub "${env2}-template-id",!Ref "AWS::NoValue"]

Step 7: Create a Dashboard for DEV Environment

To create a QuickSight dashboard, refer to the documentation.
The source entity for the dashboard:
- Source template – Includes the ARN of the DEV template.
- DatasetReferences – Includes the ARN of the QuickSight dataset created using the CloudFormation template in Step 4.
This dashboard uses the VersionDescription property to get updated.

Step 8: Create a Dashboard for UAT and PROD Environments

The source entity for the dashboard:
- Source template – Includes the ARN of the template for the UAT and PROD environments, respectively.
- DatasetReferences – Includes the ARN of the QuickSight dataset created using the CloudFormation template in Step 4.
Below is the source entity part for the QuickSight dashboard of UAT and PROD environments.
Conditions for this dashboard are:
- CreateUatResources – Creates resources for only UAT environment.
- CreateProdResources – Creates resources for only PROD environment.

SourceEntity:
       SourceTemplate:
         DataSetReferences:
           - DataSetPlaceholder: Dataset1
             DataSetArn: !Join
               - ''
               - - 'arn:aws:QuickSight:'
                 - !Ref 'AWS::Region'
                 - ':'
                 - !Ref 'AWS::AccountId'
                 - ':dataset/'
                 - !Sub ${env}-dataset-id
         Arn: !Join
           - ''
           - - 'arn:aws:QuickSight:'
             - !Ref 'AWS::Region'
             - ':'
             - !Ref 'AWS::AccountId'
             - ':template/'
             - Fn::If: [CreateUatResources, !Sub "${env}-template-id", !Ref "AWS::NoValue"]
             - Fn::If: [CreateProdResources, !Sub "${env}-template-id",!Ref "AWS::NoValue"]

Integrating CloudFormation Templates Within CI/CD Pipeline

Once all of the AWS CloudFormation templates are developed for the Amazon QuickSight resources, you can integrate these templates into the AWS-native CI/CD pipeline.

The approach to creating this pipeline is described below:

AWS CodePipeline: Consists of seven stages to deploy the CloudFormation stacks for DEV, UAT, and PROD environments.
AWS CodeCommit: The CloudFormation templates and other files can be pushed to the AWS CodeCommit repository.
Manual approval stage: Added for each of the environments. After reviewing the changes pushed to the CodeCommit, an approver will approve the pipeline to deploy the changes.
AWS CodeBuild: The AWS CodeBuild project is used by CodePipeline as a “TestAndDeploy” stage.

Conclusion

This post discussed automating the manual process of creating Amazon QuickSight dashboards and other resources. We also covered using CI/CD pipelines to deploy QuickSight dashboards with the most recent changes and features across all environments.

This approach boosts overall productivity and significantly reduces the time and effort needed to create business dashboards for data analysis, decision making, and tracking key performance indicators (KPIs).

You can also learn more about Quantiphi in AWS Marketplace.

.

.

Quantiphi – AWS Partner Spotlight

Quantiphi is an AWS Competency Partner and AI-first digital engineering company driven by the desire to solve transformational problems at the heart of the business.

Contact Quantiphi | Partner Overview | AWS Marketplace | Case Studies

AWS Partner Network (APN) Blog