Automate deployment of an Amazon QuickSight evaluation connecting to an Amazon Redshift information warehouse with an AWS CloudFormation template

Amazon Redshift is essentially the most extensively used information warehouse within the cloud, finest suited to analyzing exabytes of knowledge and operating advanced analytical queries. Amazon QuickSight is a quick enterprise analytics service to construct visualizations, carry out advert hoc evaluation, and shortly get enterprise insights out of your information. QuickSight supplies straightforward integration with Amazon Redshift, offering native entry to all of your information and enabling organizations to scale their enterprise analytics capabilities to a whole bunch of 1000’s of customers. QuickSight delivers quick and responsive question efficiency by utilizing a sturdy in-memory engine (SPICE).

As a QuickSight administrator, you should use AWS CloudFormation templates emigrate belongings between distinct environments from growth, to check, to manufacturing. AWS CloudFormation helps you mannequin and arrange your AWS assets so you possibly can spend much less time managing these assets and extra time focusing in your purposes that run in AWS. You not must create information sources or analyses manually. You create a template that describes all of the AWS assets that you really want, and AWS CloudFormation takes care of provisioning and configuring these assets for you. As well as, with versioning, you could have your earlier belongings, which supplies the flexibleness to roll again deployments if the necessity arises. For extra particulars, discuss with Amazon QuickSight useful resource kind reference.

On this put up, we present learn how to automate the deployment of a QuickSight evaluation connecting to an Amazon Redshift information warehouse with a CloudFormation template.

Resolution overview

Our resolution consists of the next steps:

1. Create a QuickSight evaluation utilizing an Amazon Redshift information supply.
2. Create a QuickSight template on your evaluation.
3. Create a CloudFormation template on your evaluation utilizing the AWS Command Line Interface (AWS CLI).
4. Use the generated CloudFormation template to deploy a QuickSight evaluation to a goal setting.

The next diagram exhibits the structure of how one can have a number of AWS accounts, every with its personal QuickSight setting linked to its personal Amazon Redshift information supply. On this put up, we define the steps concerned in migrating QuickSight belongings within the dev account to the prod account. For this put up, we use Amazon Redshift as the information supply and create a QuickSight visualization utilizing the Amazon Redshift pattern TICKIT database.

The next diagram illustrates move of the high-level steps.

Stipulations

Earlier than establishing the CloudFormation stacks, you will need to have an AWS account and an AWS Id and Entry Administration (IAM) consumer with adequate permissions to work together with the AWS Administration Console and the providers listed within the structure.

The migration requires the next stipulations:

Create a QuickSight evaluation in your dev setting

On this part, we stroll by way of the steps to arrange your QuickSight evaluation utilizing an Amazon Redshift information supply.

Create an Amazon Redshift information supply

To connect with your Amazon Redshift information warehouse, it is advisable to create a knowledge supply in QuickSight. As proven within the following screenshot, you could have two choices:

• Auto-discovered
• Handbook join

QuickSight auto-discovers Amazon Redshift clusters which can be related together with your AWS account. These assets have to be positioned in the identical Area as your QuickSight account.

For extra particulars, discuss with Authorizing connections from Amazon QuickSight to Amazon Redshift clusters.

You may also manually join and create a knowledge supply.

Create an Amazon Redshift dataset

The subsequent step is to create a QuickSight dataset, which identifies the particular information in a knowledge supply you wish to use.

For this put up, we use the TICKIT database created in an Amazon Redshift information warehouse, which consists of seven tables: two reality tables and 5 dimensions, as proven within the following determine.

This pattern database utility helps analysts monitor gross sales exercise for the fictional TICKIT web site, the place customers purchase and promote tickets on-line for sporting occasions, exhibits, and live shows.

1. On the Datasets web page, select New dataset.
2. Select the information supply you created within the earlier step.
3. Select Use customized SQL.
4. Enter the customized SQL as proven within the following screenshot.

The next screenshot exhibits our accomplished information supply.

Create a QuickSight evaluation

The subsequent step is to create an evaluation that makes use of this dataset. In QuickSight, you analyze and visualize your information in analyses. While you’re completed, you possibly can publish your evaluation as a dashboard to share with others in your group.

1. On the All analyses tab of the QuickSight begin web page, select New evaluation.

The Datasets web page opens.

2. Select a dataset, then select Use in evaluation.

3. Create a visible. For extra details about creating visuals, see Including visuals to Amazon QuickSight analyses.

Create a QuickSight template out of your evaluation

A QuickSight template is a named object in your AWS account that accommodates the definition of your evaluation and references to the datasets used. You’ll be able to create a template utilizing the QuickSight API by offering the small print of the supply evaluation through a parameter file. You should use templates to simply create a brand new evaluation.

You should use AWS Cloud9 from the console to run AWS CLI instructions.

The next AWS CLI command demonstrates learn how to create a QuickSight template based mostly on the gross sales evaluation you created (present your AWS account ID on your dev account):

aws quicksight create-template --aws-account-id  <DEVACCOUNT>--template-id QS-RS-SalesAnalysis-Template --cli-input-json file://parameters.json

The parameter.json file accommodates the next particulars (present your supply QuickSight consumer ARN, evaluation ARN, and dataset ARN):

{
    "Title": "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 should use the AWS CLI describe-user, describe_analysis, and describe_dataset instructions to get the required ARNs.

To add the up to date parameter.json file to AWS Cloud9, select File from the device bar and select Add native file.

The QuickSight template is created within the background. QuickSight templates aren’t seen inside the QuickSight UI; they’re a developer-managed or admin-managed asset that’s solely accessible through the AWS CLI or APIs.

To examine the standing of the template, run the describe-template command:

aws quicksight describe-template --aws-account-id <DEVACCOUNT> --template-id "QS-RS-SalesAnalysis-Temp"

The next code exhibits command output:

Copy the template ARN; we want it later to create a template within the manufacturing account.

The QuickSight template permissions within the dev account must be up to date to offer entry to the prod account. Run the next command to replace the QuickSight template. This supplies the describe privilege to the goal account to extract particulars of the template from the supply account:

aws quicksight update-template-permissions --aws-account-id <DEVACCOUNT> --template-id “QS-RS-SalesAnalysis-Temp” --grant-permissions file://TemplatePermission.json

The file TemplatePermission.json accommodates the next particulars (present your goal AWS account ID):

[
  {
    "Principal": "arn:aws:iam::<TARGET ACCOUNT>",
    "Actions": [
      "quicksight:UpdateTemplatePermissions",
      "quicksight:DescribeTemplate"
    ]
  }
]

To add the up to date TemplatePermission.json file to AWS Cloud9, select the File menu from the device bar and select Add native file.

Create a CloudFormation template

On this part, we create a CloudFormation template containing our QuickSight belongings. On this instance, we use a YAML formatted template saved on our native machine. We replace the next totally different sections of the template:

• AWS::QuickSight::DataSource
• AWS::QuickSight::DataSet
• AWS::QuickSight::Template
• AWS::QuickSight::Evaluation

Among the info required to finish the CloudFormation template will be gathered from the supply QuickSight account through the describe AWS CLI instructions, and a few info must be up to date for the goal account.

Create an Amazon Redshift information supply in AWS CloudFormation

On this step, we add the AWS::QuickSight::DataSource part of the CloudFormation template.

Collect the next info on the Amazon Redshift cluster within the goal AWS account (manufacturing setting):

• VPC connection ARN
• Host
• Port
• Database
• Person
• Password
• Cluster ID

You’ve the choice to create a customized DataSourceID. This ID is exclusive per Area for every AWS account.

Add the next info to the template:

Sources:
  RedshiftBuildQSDataSource:
    Kind: 'AWS::QuickSight::DataSource'
    Properties:  
      DataSourceId: "RS-Gross sales-DW"      
      AwsAccountId: !Sub ${AWS::ACCOUNT ID}
      VpcConnectionProperties:
        VpcConnectionArn: <VPC-CONNECTION-ARN>      
      Kind: REDSHIFT   
      DataSourceParameters:
        RedshiftParameters:     
          Host: "<HOST>"
          Port: <PORT>
          Clusterid: "<CLUSTER ID>"
          Database: "<DATABASE>"    
      Title: "RS-Gross sales-DW"
      Credentials:
        CredentialPair:
          Username: <USER>
          Password: <PASSWORD>
      Permissions:

Create an Amazon Redshift dataset in AWS CloudFormation

On this step, we add the AWS::QuickSight::DataSet part within the CloudFormation template to match the dataset definition from the supply account.

Collect the dataset particulars and run the list-data-sets command to get all datasets from the supply account (present your supply dev account ID):

aws quicksight list-data-sets  --aws-account-id <DEVACCOUNT>

The next code is the output:

Run the describe-data-set command, specifying the dataset ID from the earlier command’s response:

aws quicksight describe-data-set --aws-account-id <DEVACCOUNT> --data-set-id "<YOUR-DATASET-ID>"

The next code exhibits partial output:

Based mostly on the dataset description, add the AWS::Quicksight::DataSet useful resource within the CloudFormation template, as proven within the following code. Observe that you could additionally create a customized DataSetID. This ID is exclusive per Area for every AWS account.

QSRSBuildQSDataSet:
    Kind: 'AWS::QuickSight::DataSet'
    Properties:
      DataSetId: "RS-Gross sales-DW" 
      Title: "gross sales" 
      AwsAccountId: !Sub ${AWS::ACCOUNT ID}
      PhysicalTableMap:
        PhysicalTable1:          
          CustomSql:
            SqlQuery: "choose sellerid, username, (firstname ||' '|| lastname) as identify,metropolis, sum(qtysold) as gross sales
              from gross sales, date, customers
              the place gross sales.sellerid = customers.userid and gross sales.dateid = date.dateid and 12 months = 2008
              group by sellerid, username, identify, metropolis
              order by 5 desc
              restrict 10"
            DataSourceArn: !GetAtt RedshiftBuildQSDataSource.Arn
            Title"RS-Gross sales-DW"
            Columns:
            - Kind: INTEGER
              Title: sellerid
            - Kind: STRING
              Title: username
            - Kind: STRING
              Title: identify
            - Kind: STRING
              Title: metropolis
            - Kind: DECIMAL
              Title: gross sales                                     
      LogicalTableMap:
        LogicalTable1:
          Alias: gross sales
          Supply:
            PhysicalTableId: PhysicalTable1
          DataTransforms:
          - CastColumnTypeOperation:
              ColumnName: gross sales
              NewColumnType: DECIMAL
      Permissions:
        - Principal: !Be part of 
            - ''
            - - 'arn:aws:quicksight:'
              - !Ref QuickSightIdentityRegion
              - ':'
              - !Ref 'AWS::AccountId'
              - ':consumer/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_QUERY

You’ll be able to specify ImportMode to decide on between Direct_Query or Spice.

Create a QuickSight template in AWS CloudFormation

On this step, we add the AWS::QuickSight::Template part within the CloudFormation template, representing the evaluation template.

Use the supply template ARN you created earlier and add the AWS::Quicksight::Template useful resource within the CloudFormation template:

QSTCFBuildQSTemplate:
    Kind: 'AWS::QuickSight::Template'
    Properties:
      TemplateId: "QS-RS-SalesAnalysis-Temp"
      Title: "QS-RS-SalesAnalysis-Temp"
      AwsAccountId:!Sub ${AWS::ACCOUNT ID}
      SourceEntity:
        SourceTemplate:
          Arn: '<SOURCE-TEMPLATE-ARN>'          
      Permissions:
        - Principal: !Be part of 
            - ''
            - - 'arn:aws:quicksight:'
              - !Ref QuickSightIdentityRegion
              - ':'
              - !Ref 'AWS::AccountId'
              - ':consumer/default/'
              - !Ref QuickSightUser
          Actions:
            - 'quicksight:DescribeTemplate'
      VersionDescription: Preliminary model - Copied over from AWS account.

Create a QuickSight evaluation

On this final step, we add the AWS::QuickSight::Evaluation part within the CloudFormation template. The evaluation is linked to the template created within the goal account.

Add the AWS::Quicksight::Evaluation useful resource within the CloudFormation template as proven within the following code:

QSRSBuildQSAnalysis:
    Kind: 'AWS::QuickSight::Evaluation'
    Properties:
      AnalysisId: 'Gross sales-Evaluation'
      Title: 'Gross sales-Evaluation'
      AwsAccountId:!Sub ${AWS::ACCOUNT ID}
      SourceEntity:
        SourceTemplate:
          Arn: !GetAtt  QSTCFBuildQSTemplate.Arn
          DataSetReferences:
            - DataSetPlaceholder: 'gross sales'
              DataSetArn: !GetAtt QSRSBuildQSDataSet.Arn
      Permissions:
        - Principal: !Be part of 
            - ''
            - - 'arn:aws:quicksight:'
              - !Ref QuickSightIdentityRegion
              - ':'
              - !Ref 'AWS::AccountId'
              - ':consumer/default/'
              - !Ref QuickSightUser
          Actions:
            - 'quicksight:RestoreAnalysis'
            - 'quicksight:UpdateAnalysisPermissions'
            - 'quicksight:DeleteAnalysis'
            - 'quicksight:DescribeAnalysisPermissions'
            - 'quicksight:QueryAnalysis'
            - 'quicksight:DescribeAnalysis'
            - 'quicksight:UpdateAnalysis'      

Deploy the CloudFormation template within the manufacturing account

To create a brand new CloudFormation stack that makes use of the previous template through the AWS CloudFormation console, full the next steps:

1. On the AWS CloudFormation console, select Create Stack.
2. On the drop-down menu, select with new assets (commonplace).
3. For Put together template, choose Template is prepared.
4. For Specify template, select Add a template file.
5. Save the supplied CloudFormation template in a .yaml file and add it.
6. Select Subsequent.
7. Enter a reputation for the stack. For this put up, we use QS-RS-CF-Stack.
8. Select Subsequent.
9. Select Subsequent once more.
10. Select Create Stack.

The standing of the stack modifications to CREATE_IN_PROGRESS, then to CREATE_COMPLETE.

Confirm the QuickSight objects within the following desk have been created within the manufacturing setting.

The next instance exhibits that Gross sales Evaluation was created within the goal account.

Conclusion

This put up demonstrated an method emigrate a QuickSight evaluation with an Amazon Redshift information supply from one QuickSight account to a different with a CloudFormation template.

For extra details about automating dashboard deployment, customizing entry to the QuickSight console, configuring for group collaboration, and implementing multi-tenancy and shopper consumer segregation, try the movies Digital Admin Workshop: Working with Amazon QuickSight APIs and Admin Degree-Up Digital Workshop, V2 on YouTube.

In regards to the writer

Sandeep Bajwa is a Sr. Analytics Specialist based mostly out of Northern Virginia, specialised within the design and implementation of analytics and information lake options.