Post Syndicated from Srikanth Baheti original https://aws.amazon.com/blogs/big-data/enable-federation-to-multiple-amazon-quicksight-accounts-with-microsoft-azure-active-directory/

Amazon QuickSight is a scalable, serverless, embeddable, machine learning (ML)-powered business intelligence (BI) service built for the cloud that supports identity federation in both Standard and Enterprise editions. Organizations are working towards centralizing their identity and access strategy across all of their applications, including on-premises, third-party, and applications on AWS. Many organizations use Microsoft Azure Active Directory (Azure AD) to control and manage user authentication and authorization centrally. If your organization uses Azure AD for cloud applications and multiple QuickSight accounts, you can enable federation to all of your QuickSight accounts without needing to create and manage users multiple times. This authorizes users to access QuickSight assets—analyses, dashboards, folders, and datasets—through centrally managed Azure AD.

In this post, we go through the steps to configure federated single sign-on (SSO) between a single Azure AD instance and multiple QuickSight accounts. We demonstrate registering an SSO application in Azure AD, creating roles in Azure AD, and assigning these roles to map to QuickSight roles (admin, author, and reader) These QuickSight roles represent three different personas supported in QuickSight. Administrators can publish the QuickSight app in the Azure App portal to enable users to SSO to QuickSight using their Azure AD credentials.

Prerequisites

To complete this walkthrough, you must have the following prerequisites:

• An Azure AD subscription
• One or more QuickSight account subscriptions

Solution overview

The walkthrough includes the following steps:

1. Register an AWS Single Sign-On (AWS SSO) application in Azure AD.
2. Configure the application in Azure AD.
3. Add Azure AD as your SAML identity provider (IdP) in AWS.
4. Configure AWS Identity and Access Management (IAM) policies.
5. Configure IAM roles.
6. Create roles in Microsoft Graph Explorer.
7. Assign the newly created roles through Graph Explorer to users in Azure AD.
8. Test the application from Azure AD.

Register an AWS SSO application in Azure AD

To configure the integration of an AWS SSO application in Azure AD, you need to add AWS SSO to your list of managed software as a service (SaaS) apps.

1. Sign in to the Azure portal using a Microsoft account.
2. Under Azure services, choose Azure Active Directory.

3. In the navigation pane, under Manage, choose Enterprise Applications.

4. Choose All applications.
5. Choose New application.

6. In the Browse Azure AD Gallery section, search for AWS Single Sign-On.
7. Choose AWS Single Sign-On from the results panel and add the application.

8. For Name, enter Amazon QuickSight.
9. After the application is created, copy the Object ID value from the application overview.

You need this object ID in the later steps.

Configure an AWS SSO application in Azure AD

Follow these steps to enable Azure AD SSO in the Azure portal.

1. In the Azure portal, on the AWS SSO application registered in first step, in the Manage section, choose single sign-on.
2. On the Select a single sign-on method page, choose SAML.
3. Choose the pencil icon.
4. For Identifier (Entity ID), enter URN:AMAZON:WEBSERVICES.
5. For Reply URL, enter https://signin.aws.amazon.com/saml.
6. Leave Sign on URL blank
7. For Relay State, enter https://quicksight.aws.amazon.com.
8. Leave Logout URL blank.
9. Choose Save.

10. On the Set up Single Sign-On with SAML page, under User Attributes & Claims, choose Edit.

11. In the Additional Claims section, configure SAML token attributes by using the values in the following table.

12. In the SAML Signing Certificate section, choose Download to download the federation metadata XML file.

You this XML document later when setting up the SAML provider in IAM.

Add Azure AD as your SAML IdP in AWS

To configure Azure AD as your SAML IdP, complete the following steps:

1. Open a new tab in your browser.
2. Sign in to the IAM console in your AWS account with admin permissions.
3. On the IAM console, under Access Management in the navigation pane, choose Identity providers.
4. Choose Add provider.

5. For Provider name, enter AzureActiveDirectory.
6. Choose Choose file to upload the metadata document you downloaded in the earlier step.
7. Choose Add provider.

8. In the banner message that appears, choose View provider.

9. Copy the ARN to use in a later step.

10. Repeat these steps in other accounts where you want to enable SSO.

Configure IAM policies

In this step, you create three IAM policies for mapping to three different roles with permissions in QuickSight (admin, author, and reader).

Use the following steps to set up the QuickSight-Admin-Account1 policy. This policy grants admin privileges in QuickSight to the federated user.

1. On the IAM console, choose Policies.
2. Choose Create policy.
3. Choose JSON and replace the existing text with the code from the following table for QuickSight-Admin-Account1.

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "quicksight:CreateAdmin",
"Resource": "*"
}
]
}

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "quicksight:CreateUser",
"Resource": "*"
}
]
}

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": " quicksight:CreateReader",
"Resource": "*"
}
]
}

4. Choose Review policy
5. For Name, enter QuickSight-Admin-Account1.
6. Choose Create policy.
7. Repeat the steps for QuickSight-Author-Account1 and QuickSight-Reader-Account1.
8. Repeat these steps in other accounts where you want to enable SSO.

Configure IAM roles

Next, create the roles that your Azure AD users assume when federating into QuickSight. Use the following steps to set up the admin role:

1. On the IAM console, choose Roles.
2. Choose Create role.
3. For Select type of trusted entity, choose SAML 2.0 federation.
4. For SAML provider, choose the provider you created earlier (AzureActiveDirectory).
5. Select Allow programmatic and AWS Management Console access.
6. For Attribute, choose SAML:aud.
7. For Value, enter https://signin.aws.amazon.com/saml.

8. Choose Next: Permissions.
9. Choose the QuickSight-Admin-Account1 IAM policy you created in the previous step.
10. Choose Next: Tags.
11. Choose Next: Review
12. For Role name, enter QuickSight-Admin-Role.
13. For Role description, enter a description.
14. Choose Create role.

15. On the IAM console, in the navigation pane, choose Roles.
16. Choose the QuickSight-Admin-Role role you created to open the role’s properties.
17. Copy the role ARN to the notepad.
18. On the Trust Relationships tab, choose Edit Trust Relationship.
19. Under Trusted Entities, verify that the IdP you created is listed.
20. Under Conditions, verify that SAML:aud with a value of https://signin.aws.amazon.com/saml is present.

21. Repeat these steps to create your author and reader roles and attach the appropriate policies:
    1. For QuickSight-Author-Role, use the policy QuickSight-Author-Account1.
    2. For QuickSight-Reader-Role, use the policy QuickSight-Reader-Account1.
22. Repeat these steps in other accounts where you want to enable SSO.

Create roles in Microsoft Graph Explorer

Optionally, you can create roles within Azure AD with paid subscriptions. Open Microsoft Graph Explorer, and then do the following:

1. Sign in to the Microsoft Graph Explorer site with the domain account for your tenant.

You need sufficient permissions to create the roles.

2. To grant permissions, choose the ellipsis (three dots) next to your name and choose Select permissions.

3. On the Permission list, expand Directory.
4. Select the three directory-level permissions as shown in the following screenshot and choose Consent.

5. Sign in to Graph Explorer again, and accept the site usage conditions.
6. Choose GET for the method, and 0 for the version.
7. In the query box, enter https://graph.microsoft.com/v1.0/servicePrincipals/<objectId> (use the object ID you saved earlier).
8. In the Response preview pane, copy the response to an editor of your choice to modify.

9. Extract the appRoles property from the service principal object.

Now you generate new roles for your application. These roles must match the IAM roles in AWS that you created earlier.

11. From the notepad, use the format <Role ARN>, <IdP ARN> to create your roles:
    1. arn:aws:iam::5xxxxxxxxxx9:role/QuickSight-Admin-Role,arn:aws:iam::5xxxxxxxxxx9:saml-provider/AzureActiveDirectory
    2. arn:aws:iam::5xxxxxxxxxx9:role/QuickSight-Author-Role,arn:aws:iam::5xxxxxxxxxx9:saml-provider/AzureActiveDirectory
    3. arn:aws:iam::5xxxxxxxxxx9:role/QuickSight-Reader-Role,arn:aws:iam::5xxxxxxxxxx9:saml-provider/AzureActiveDirectory
    4. arn:aws:iam::0xxxxxxxxxx2:role/QS-Admin-AZAd-Role,arn:aws:iam::0xxxxxxxxxx2:saml-provider/AzureAd-Acct2
    5. arn:aws:iam::0xxxxxxxxxx2:role/QS-Author-AZAd-Role,arn:aws:iam::0xxxxxxxxxx2:saml-provider/AzureAd-Acct2
    6. arn:aws:iam::0xxxxxxxxxx2:role/QS-Reader-AZAd-Role,arn:aws:iam::0xxxxxxxxxx2:saml-provider/AzureAd-Acct2
12. The following JSON code is an example of the appRoles Create a similar object to add the roles for your application:

            "appRoles": [
                {
                    "allowedMemberTypes": [
                        "User"
                    ],
                    "description": "User",
                    "displayName": "User",
                    "id": "8774f594-1d59-4279-b9d9-59ef09a23530",
                    "isEnabled": true,
                    "origin": "Application",
                    "value": null
                },
                {
                    "allowedMemberTypes": [
                        "User"
                    ],
                    "description": "msiam_access",
                    "displayName": "msiam_access",
                    "id": "e7f1a7f3-9eda-48e0-9963-bd67bf531afd",
                    "isEnabled": true,
                    "origin": "Application",
                    "value": null
                },
                {
                    "allowedMemberTypes": [
                        "User"
                    ],
                    "description": "Raji Quicksight Admin",
                    "displayName": "RajiQSAdmin",
                    "id": "9a07d03d-667f-405d-b5d7-68bec5b64584",
                    "isEnabled": true,
                    "origin": "ServicePrincipal",
                    "value": "arn:aws:iam::0xxxxxxxxxx2:role/QS-Admin-AZAd-Role,arn:aws:iam::0xxxxxxxxxx2:saml-provider/AzureAd-Acct2"
                },
                {
                    "allowedMemberTypes": [
                        "User"
                    ],
                    "description": "Sri Quicksight Admin",
                    "displayName": "SriQSAdmin",
                    "id": "77dd76d1-f897-4093-bf9a-8f3aaf25f30e",
                    "isEnabled": true,
                    "origin": "ServicePrincipal",
                    "value": "arn:aws:iam::5xxxxxxxxxx9:role/QuickSight-Admin-Role,arn:aws:iam::5xxxxxxxxxx9:saml-provider/AzureActiveDirectory"
                }
            ]

New roles must be followed by msiam_access for the patch operation. You can also add multiple roles, depending on your organization’s needs. Azure AD sends the value of these roles as the claim value in the SAML response.

When adding new roles, you must provide a new GUID for each ID attribute in the JSON payload. You can use online GUID generation tool for generating a new unique GUID per role.

12. In Microsoft Graph Explorer, change the method from GET to PATCH.
13. Patch the service principal object with the roles you want by updating the appRoles property, like the one shown in the preceding example.
14. Choose Run Query to run the patch operation. A success message confirms the creation of the role for your AWS application.

After the service principal is patched with new roles, you can assign users and groups to their respective roles.

15. In the Azure portal, go to the QuickSight application you created and choose Users and Groups.
16. Create your groups.

We recommend creating a new group for every AWS role in order to assign a particular role to the group. This one-to-one mapping means that one group is assigned to one role. You can then add members to the group.

17. After you create the groups, choose the group and assign it to the application.

Nested groups are not allowed.

18. To assign the role to the group, choose the role, then choose Assign.

Test the application

In this section, you test your Azure AD SSO configuration by using Microsoft Applications.

1. Navigate to Microsoft Applications.
2. On the My Apps page, choose AWS Single Sign-On.

3. Choose a specific role for the QuickSight account you want to use.

You’re redirected to the QuickSight console.

Summary

This post provided step-by-step instructions to configure federated SSO between a single Azure AD instance and multiple QuickSight accounts. We also discussed how to create new roles and map users and groups in Azure AD to IAM for secure access into multiple QuickSight accounts.

For information about federating from Azure AD to a single QuickSight account, see Enabling Amazon QuickSight federation with Azure AD.

About the Authors

Srikanth Baheti is a Specialized World Wide Sr. Solution Architect for Amazon QuickSight. He started his career as a consultant and worked for multiple private and government organizations. Later he worked for PerkinElmer Health and Sciences & eResearch Technology Inc, where he was responsible for designing and developing high traffic web applications, highly scalable and maintainable data pipelines for reporting platforms using AWS services and Serverless computing.

Raji Sivasubramaniam is a Specialist Solutions Architect at AWS, focusing on Analytics. Raji has 20 years of experience in architecting end-to-end Enterprise Data Management, Business Intelligence and Analytics solutions for Fortune 500 and Fortune 100 companies across the globe. She has in-depth experience in integrated healthcare data and analytics with wide variety of healthcare datasets including managed market, physician targeting and patient analytics. In her spare time, Raji enjoys hiking, yoga and gardening.

Padmaja Suren is a Senior Solutions Architect specialized in QuickSight. She has 20+ years of experience in building scalable data platforms for Reporting, Analytics and AI/ML using a variety of technologies. Prior to AWS, in her role as BI Architect at ERT, she designed, engineered and cloud-enabled the BI and Analytics platform for the management of large scale clinical trial data conducted across the world. She dedicates her free time on her passion project SanghWE which helps sexual trauma survivors in developing nations heal and recover.

Post Syndicated from Abhinav Sarin original https://aws.amazon.com/blogs/big-data/migrate-amazon-quicksight-across-aws-accounts/

This blog post is co-written by Glen Douglas and Alex Savchenko from Integrationworx.

Enterprises that follow an Agile software development lifecycle (SDLC) process for their dashboard development and deployment typically have distinct environments for development, staging, QA and test, and production. One recommended approach when developing using AWS is to create multiple AWS accounts corresponding to the various environments. Amazon QuickSight is a fully managed, serverless business intelligence service offered by AWS for building interactive dashboards. With QuickSight, you can share dashboards with your internal users, or embed dashboards into your applications for external users or customers, scaling to 100s of 1000s of users with no servers or infrastructure to maintain. When an account is created on QuickSight, it corresponds to the underlying AWS account. So, when dashboards are created in the development environment, you need to migrate these assets to a higher environment to be in alignment with current DevOps practices. This requires cross-account dashboard migration. This post outlines the steps involved in migrating dashboards from one account to another.

Solution overview

The following diagram shows the architecture of how QuickSight accounts are mapped to AWS accounts. In this post, we outline the steps involved in migrating QuickSight assets in the dev account to the prod account.

Migrating QuickSight dashboards from a dev account to a prod account involves converting the underlying dev dashboard assets into JSON and then recreating them in prod. QuickSight provides a robust set of APIs to create QuickSight assets, such as dashboards, analysis, datasets, and themes. You can run these APIs via the AWS Command Line Interface (AWS CLI) or through SDKs available for various programming languages. For this post, we use the AWS CLI for illustration; for regular usage, we recommend you implement this with the AWS SDK.

To set up the AWS CLI, see Developing Applications with the QuickSight API.

The following diagram illustrates the high-level steps to migrate QuickSight assets from one account to another. First, we need prepare the dataset and data sources, and create the analysis, template, and dashboards in the dev account.

 Next, we use the dev template with the test dataset and data sources to promote the dashboards.

You can create any QuickSight entity programmatically using APIs (for example, create-dataset and create-datasources). Another API allows generating a JSON representation of the entity that was created (such as describe-dataset and describe-datasource). Analysis entity is an exception—as of this writing, there are no APIs for creating or generating a JSON representation. All analysis entities must be created using the AWS Management Console for the first time. From that point on, you can programmatically manage analysis using templates.

Definitions

The following table defines the parameters used in this post.

The following figure summarizes the QuickSight objects and AWS Identity and Access Management (IAM) resources that are referenced in the migration between accounts. The QuickSight objects from the source account are referenced and recreated in the target account.

The following table summarizes the QuickSight objects used in this post for migration from the source account (dev) to the target account (test). As part of the migration, the name of the data source is changed in order to denote the change in underlying data used in the target environment. As we demonstrate in this post, a QuickSight template is created in the source account only. This template contains the information about the source dashboard that is to be created in the target account.

For this post, we use an Amazon RDS for PostgreSQL database as the dataset and create a QuickSight visualization using the database table sporting_event_ticket_info. You can use any of the data sources that QuickSight supports or easily test this process using a spreadsheet. The dashboard to be migrated from the development environment shows data from the corresponding dev database (QS_Dev).

Prerequisites

Consider the situation in which you need to migrate a QuickSight dashboard from a source (or dev) environment to a target (or test) environment. The migration requires the following prerequisites:

• IAM admin access in the source and target accounts that creates IAM policies, groups, and users. For instructions, see Creating your first IAM admin user and group.
• A QuickSight Enterprise account in the source and target accounts. For instructions, see Setting Up Amazon QuickSight.
• A relational database, such as from Amazon Relational Database Service (Amazon RDS), that contains source data.

Step 1: Enable permissions to migrate QuickSight objects

To facilitate using AWS CLI commands and sharing QuickSight templates from the source account to the target account, we perform the following actions in the source environment:

1a) Create an IAM policy.

1b) Create a new IAM group and attach the policy.

1c) Create a new IAM user and assign it to the group.

1d) Invite the IAM user created to the QuickSight (dev) account.

1e) Create another reader policy in the source (dev) account to grant access to the target (test) account.

1f) Create a deployer role in the source account.

Step 1a: Create an IAM policy

You start by creating IAM resources. For this post, we create an IAM policy called Dev-QuickSightAdmin, which is used by IAM users and roles in the source account. The purpose of this policy is to allow a user in the source account to perform various actions on QuickSight objects.

1. To get started, as the admin user, sign in to the IAM console.
2. In the navigation pane, choose Policies.
3. Choose Create policy.
4. In the Service section, select Choose a service and choose QuickSight.
5. Under Actions, select the following permissions:
   1. List – ListAnalysis, ListDashboards, ListDataSets, ListDataSources, ListTemplates
   2. Read – DescribeAnalysis, DescribeDashboard, DescribeDataSet, DescribeDataSource, DescribeTemplate
   3. Write – CreateTemplate, UpdateTemplate
   4. Permissions management – DescribeTemplatePermissions, UpdateTemplatePermissions
6. Select the appropriate resources.

You can restrict the resources and Region based on your requirement. We allow all resources for this post.

7. Review and create the policy.

Step 1b: Create a new IAM group

Create a new IAM group Dev-grp-QuickSightAdmin and assign the Dev-QuickSightAdmin policy (from Step 1a) to the group in the source account.

Step 1c: Create a new IAM user

Create a new IAM user called Dev-qs-admin-user and assign it to the Dev-grp-QuickSightAdmin group. You use this user later to run the AWS CLI commands in the source account. Alternately, you can use an existing IAM user for this purpose.

Step 1d: Invite the IAM user to the QuickSight (dev) account

Sign in to QuickSight in the source (dev) account and invite the user from Step 1c to QuickSight. Assign the role of ADMIN and for IAM user, choose Yes to indicate that this is an IAM user.

Step 1e: Create another reader policy in the source (dev) account

In the source (dev) account, create another IAM policy, called Dev-QuickSightReader, to grant access to the target (test) account. The purpose of this policy is to allow the target account to perform list and read actions on QuickSight objects in the source account.

1. On the IAM console, choose Policies in the navigation pane.
2. Choose Create policy.
3. In the Service section, select Choose a service and choose QuickSight.
4. Under Actions, make sure that All QuickSight actions is not selected.
5. Under Access level, select List and Read.
6. Select the following permissions:
   1. List – ListAnalysis, ListDashboards, ListDataSets, ListDataSources, ListTemplates
   2. Read – DescribeAnalysis, DescribeDashboard, DescribeDataSet, DescribeDataSource, DescribeTemplate
7. Review and create the policy.

Verify the reader IAM policy Dev-QuickSightReader shows only the list and read access level for QuickSight services when complete.

Step 1f: Create a deployer role in the source account (dev)

You now create an IAM role called Dev-QuickSight-Deployer in the source account (dev). This role is specifically assigned to the target account ID and assigned the QuickSightReader policy, as noted in the previous step. This allows the external AWS target (test) account to read the QuickSight template contained in the source (dev) account.

1. On the IAM console, choose Roles in the navigation pane.
2. Choose Create role.
3. Select Another AWS account and provide the account ID of the target account.
4. In the Attach permissions policies section, attach the Dev-QuickSightReader
5. In the Add tags section, add any tags (optional).
6. Choose Next.
7. In the Review section, assign a role name (we use Dev-QuickSight-Deployer).
8. Enter a role description.
9. Choose Create role.

You have completed the creation of the policy, group, and user in the source account. The next step is to configure the permissions in the target account.

10. Switch from the source account to the target account.
11. In the target account, sign in to the IAM console and repeat the steps in this section to create the Test-QuickSightAdmin policy and Test-grp-QuickSightAdmin group, and assign the policy to the group. Test-QuicksightAdmin should have following permissions:
    1. List – ListAnalysis, ListDashboards, ListDataSets, ListDataSources, ListTemplates
    2. Read – DescribeAnalysis, DescribeDashboard, DescribeDataSet, DescribeDataSource, DescribeTemplate
    3. Write – CreateTemplate, UpdateTemplate, Createdatasource, CreateDashboard, UpdateDataSet
    4. Permissions management – DescribeTemplatePermissions, UpdateTemplatePermissions
    5. Tag: TagResource, UntagResource

12. Create the IAM user Test-qs-admin-user and add it to the Test-grp-QuickSightAdmin group
13. Sign in to QuickSight and invite the test user.

One final step is to add Test-qs-admin-user as a trusted entity in the Dev-QuickSight-Deployer role. The reason is the target account needs cross-account access. We use the IAM user Test-qs-admin-user to create the dashboard in the test account.

14. Switch back to the source (dev) account.
15. Select role and search for the Dev-QuickSight-Deployer role.
16. Choose Trust relationships.
17. Edit the trust relationship and add the following ARN in the Principal section of the policy: arn:aws:iam::86XXXXX55:user/Test-qs-admin-user.

Step 2: Prepare QuickSight objects in the source account

To migrate QuickSight objects to a target environment, we perform the following actions in the source environment:

2a) Prepare the data source file.

2b) Prepare the dataset file.

2c) Create a dashboard template.

Step 2a: Prepare the data source file

Data sources represent connections to specific sources of data and are made available within a QuickSight account. Multiple data source types are supported within QuickSight, including a variety of relational databases, flat files, JSON semi-structured data files, and software as a service (SaaS) data providers.

Any QuickSight dashboards and datasets to be migrated to the target environment must have their corresponding data sources also migrated within the target environment.

In this step, you create a JSON file with the data source information from the source environment. Then you use the create-data-source AWS CLI command in the target environment with the JSON file as input. 

1. First, identify the data source for your implementation by running the list-data-sources command in the source (dev) account: 

aws quicksight list-data-sources --aws-account-id 31********64

The following code shows the top portion of the command output:

{
    "Status": 200,
    "DataSources": [
        {
            "Arn": "arn:aws:quicksight:us-east-1:31********64:datasource/4b98fee7-4df1-4dc2-8ca3-115c1c1839ab"",
            "DataSourceId": "4b98fee7-4df1-4dc2-8ca3-115c1c1839ab",
            "Name": "QS_Dev",
            "Type": "POSTGRESQL",
            "Status": "CREATION_SUCCESSFUL",
            "CreatedTime": "2020-12-16T20:42:37.280000-08:00",
            "LastUpdatedTime": "2020-12-16T20:42:37.280000-08:00",
            "DataSourceParameters": {
                "RdsParameters": {
                    "InstanceId": "dmslabinstance",
                    "Database": "sportstickets"
                }
            },
            "SslProperties": {
                "DisableSsl": false
            }
        },

2. Run the describe-data-source command, using the DataSourceId from the previous list-data-source command output.

This command provides details about the data source, which we use to create the data source in the target account.

aws quicksight describe-data-source --aws-account-id 31********64 --data-source-id "4b98fee7-4df1-4dc2-8ca3-115c1c1839ab "

The following is the resulting output:

{
    "Status": 200,
    "DataSource": {
        "Arn": "arn:aws:quicksight:us-east-1:31*******64:datasource/4b98fee7-4df1-4dc2-8ca3-115c1c1839ab",
        "DataSourceId": "4b98fee7-4df1-4dc2-8ca3-115c1c1839ab",
        "Name": "QS_Dev",
        "Type": "POSTGRESQL",
        "Status": "CREATION_SUCCESSFUL",
        "CreatedTime": "2020-12-16T20:42:37.280000-08:00",
        "LastUpdatedTime": "2020-12-16T20:42:37.280000-08:00",
        "DataSourceParameters": {
            "RdsParameters": {
                "InstanceId": "dmslabinstance",
                "Database": "sportstickets"
            }
        },
        "SslProperties": {
            "DisableSsl": false
        }
    },
    "RequestId": "c2455720-118c-442d-9ba3-4f446cb543f1"
}

If you’re migrating more than one data source, you need to repeat the step for each data source.

3. Use the output from the describe-data-source command to create a JSON file called create-data-source-cli-input.json, which represents the data source that is being migrated.

The contents of the following JSON file reference the data source information (name, host, credentials) for the target environment:

{
    "AwsAccountId": "86********55",
    "DataSourceId": "QS_Test",
    "Name": "QS_Test",
    "Type": "POSTGRESQL",
    "DataSourceParameters": {
        "PostgreSqlParameters": {
            "Host": "dmslabinstance.***********.us-east-1.rds.amazonaws.com",
            "Port": 5432,
            "Database": "sportstickets"
        }
    },
    "Credentials": {
        "CredentialPair": {
            "Username": "xxxxxxx",
            "Password": "yyyyyyy"
        }
    },
    "Permissions": [
        {
            "Principal": "arn:aws:quicksight:us-east-1:86********55:user/default/Test-qs-admin-user",
            "Actions": [
                "quicksight:UpdateDataSourcePermissions",
                "quicksight:DescribeDataSource",
                "quicksight:DescribeDataSourcePermissions",
                "quicksight:PassDataSource",
                "quicksight:UpdateDataSource",
                "quicksight:DeleteDataSource"
            ]
        }
    ],
    "Tags": [
        {
            "Key": "Name",
            "Value": "QS_Test"
        }
    ]
}

For this post, because the target environment is connecting to the same data source as the source environment, the values in the JSON can simply be provided from the previous describe-data-source command output.

Step 2b: Prepare the dataset file

Datasets provide an abstraction layer in QuickSight, which represents prepared data from a data source in the QuickSight account. The intent of prepared datasets is to enable reuse in multiple analyses and sharing amongst QuickSight users. A dataset can include calculated fields, filters, and changed file names or data types. When based on a relational database, datasets can join tables within QuickSight, or as part of the underlying SQL query used to define the dataset.

The sample dataset sporting_event_ticket_info represents a single table; however, in a relational database, datasets can join tables within QuickSight, or as part of the underlying SQL query used to define the dataset.

Similar to the process used for data sources, you create a JSON file representing the datasets from the source account.

1. Run the list-data-sets command to get all datasets from the source account:

aws quicksight list-data-sets --aws-account-id 31********64

The following code is the output:

{
            "Arn": "arn:aws:quicksight:us-east-1:31********64:dataset/24b1b03a-86ce-41c7-9df7-5be5343ff9d9",
            "DataSetId": "24b1b03a-86ce-41c7-9df7-5be5343ff9d9",
            "Name": "sporting_event_ticket_info",
            "CreatedTime": "2020-12-16T20:45:36.672000-08:00",
            "LastUpdatedTime": "2020-12-16T20:45:36.835000-08:00",
            "ImportMode": "SPICE"
}

2. Run the describe-data-set command, specifying the DataSetId from the previous command’s response:

aws quicksight describe-data-set --aws-account-id 31********64 --data-set-id "24b1b03a-86ce-41c7-9df7-5be5343ff9d9"

The following code shows the output:

{
    "Status": 200,
    "DataSet": {
        "Arn": "arn:aws:quicksight:us-east-1:31********64:dataset/24b1b03a-86ce-41c7-9df7-5be5343ff9d9",
        "DataSetId": "24b1b03a-86ce-41c7-9df7-5be5343ff9d9",
        "Name": "sporting_event_ticket_info",
        "CreatedTime": "2020-12-16T20:45:36.672000-08:00",
        "LastUpdatedTime": "2020-12-16T20:45:36.835000-08:00",
        "PhysicalTableMap": {
            "d7ec8dff-c136-4c9a-a338-7017a95a4473": {
                "RelationalTable": {
                    "DataSourceArn": "arn:aws:quicksight:us-east-1:31********64:datasource/f72f8c2a-f9e2-4c3c-8221-0b1853e920b2",
                    "Schema": "dms_sample",
                    "Name": "sporting_event_ticket_info",
                    "InputColumns": [
                        {
                            "Name": "ticket_id",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "event_id",
                            "Type": "INTEGER"
                        },
                        {
                            "Name": "sport",
                            "Type": "STRING"
                        },
                        {
                            "Name": "event_date_time",
                            "Type": "DATETIME"
                        },
                        {
                            "Name": "home_team",
                            "Type": "STRING"
                        },
                        {
                            "Name": "away_team",
                            "Type": "STRING"
                        },
                        {
                            "Name": "location",
                            "Type": "STRING"
                        },
                        {
                            "Name": "city",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat_level",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "seat_section",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat_row",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat",
                            "Type": "STRING"
                        },
                        {
                            "Name": "ticket_price",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "ticketholder",
                            "Type": "STRING"
                        }
                    ]
                }
            }
        },
        "LogicalTableMap": {
            "d7ec8dff-c136-4c9a-a338-7017a95a4473": {
                "Alias": "sporting_event_ticket_info",
                "DataTransforms": [
                    {
                        "TagColumnOperation": {
                            "ColumnName": "city",
                            "Tags": [
                                {
                                    "ColumnGeographicRole": "CITY"
                                }
                            ]
                        }
                    }
                ],
                "Source": {
                    "PhysicalTableId": "d7ec8dff-c136-4c9a-a338-7017a95a4473"
                }
            }
        },
        "OutputColumns": [
            {
                "Name": "ticket_id",
                "Type": "DECIMAL"
            },
            {
                "Name": "event_id",
                "Type": "INTEGER"
            },
            {
                "Name": "sport",
                "Type": "STRING"
            },
            {
                "Name": "event_date_time",
                "Type": "DATETIME"
            },
            {
                "Name": "home_team",
                "Type": "STRING"
            },
            {
                "Name": "away_team",
                "Type": "STRING"
            },
            {
                "Name": "location",
                "Type": "STRING"
            },
            {
                "Name": "city",
                "Type": "STRING"
            },
            {
                "Name": "seat_level",
                "Type": "DECIMAL"
            },
            {
                "Name": "seat_section",
                "Type": "STRING"
            },
            {
                "Name": "seat_row",
                "Type": "STRING"
            },
            {
                "Name": "seat",
                "Type": "STRING"
            },
            {
                "Name": "ticket_price",
                "Type": "DECIMAL"
            },
            {
                "Name": "ticketholder",
                "Type": "STRING"
            }
        ],
        "ImportMode": "SPICE",
        "ConsumedSpiceCapacityInBytes": 318386511
    },
    "RequestId": "8c6cabb4-5b9d-4607-922c-1916acc9da1a"
}

3. Based on the dataset description, create a JSON file based on the template file (create-data-set-cli-input-sql.json) with the details listed in the describe-data-set command output:

{

    "AwsAccountId": "86********55",
    "DataSetId": "24b1b03a-86ce-41c7-9df7-5be5343ff9d9",
    "Name": "person",
    "PhysicalTableMap": {
        "23fb761f-df37-4242-9f23-ba61be20a0df": {
            "RelationalTable": {
                "DataSourceArn": "arn:aws:quicksight:us-east-1: 86********55:datasource/QS_Test",
                "Schema": "dms_sample",
                "Name": " sporting_event_ticket_info ",
                "InputColumns": [

                        {
                            "Name": "ticket_id",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "event_id",
                            "Type": "INTEGER"
                        },
                        {
                            "Name": "sport",
                            "Type": "STRING"
                        },
                        {
                            "Name": "event_date_time",
                            "Type": "DATETIME"
                        },
                        {
                            "Name": "home_team",
                            "Type": "STRING"
                        },
                        {
                            "Name": "away_team",
                            "Type": "STRING"
                        },
                        {
                            "Name": "location",
                            "Type": "STRING"
                        },
                        {
                            "Name": "city",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat_level",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "seat_section",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat_row",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat",
                            "Type": "STRING"
                        },
                        {
                            "Name": "ticket_price",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "ticketholder",
                            "Type": "STRING"
                        }
                    ]
                }
            }
        },
        "LogicalTableMap": {
            "23fb761f-df37-4242-9f23-ba61be20a0df": {
                "Alias": "person",
                "Source": {
                    "PhysicalTableId": "23fb761f-df37-4242-9f23-ba61be20a0df"
                }
            }
        },
    "ImportMode": "SPICE",
    "Permissions": [
        {
            "Principal": "arn:aws:quicksight:us-east-1: 86********55:user/default/Test-qs-admin-user",
            "Actions": [
                "quicksight:UpdateDataSetPermissions",
                "quicksight:DescribeDataSet",
                "quicksight:DescribeDataSetPermissions",
                "quicksight:PassDataSet",
                "quicksight:DescribeIngestion",
                "quicksight:ListIngestions",
                "quicksight:UpdateDataSet",
                "quicksight:DeleteDataSet",
                "quicksight:CreateIngestion",
                "quicksight:CancelIngestion"
            ]
        }
    ],
    "Tags": [
        {
            "Key": "Name",
            "Value": "QS_Test"
        }
    ]
}

The DataSource Arn should reference the ARN of the existing data source (in this case, the source created in the previous step).

Step 2c: Create a QuickSight template

A template in QuickSight is an entity that encapsulates the metadata that describes an analysis. A template provides a layer of abstraction from a specific analysis by using placeholders for the underlying datasets used to create the analysis. When we replace dataset placeholders in a template, we can recreate an analysis for a different dataset that follows the same schema as the original analysis.

You can also share templates across accounts. This feature, combined with the dataset placeholders in a template, provides the means to migrate a dashboard from one account to another.

1. To create a template, begin by using the list-dashboards command to get list of available dashboards in the source environment:

aws quicksight list-dashboards --aws-account-id 31********64

The command output can be lengthy, depending on the number of dashboards in the source environment.

2. Search for the “Name” of the desired dashboard in the output file and copy the corresponding DashboardId to use in the next step:

{
            "Arn": "arn:aws:quicksight:us-east-1:31********64:dashboard/c5345f81-e79d-4a46-8203-2763738489d1",
            "DashboardId": "c5345f81-e79d-4a46-8203-2763738489d1",
            "Name": "Sportinng_event_ticket_info_dashboard",
            "CreatedTime": "2020-12-18T01:30:41.209000-08:00",
            "LastUpdatedTime": "2020-12-18T01:30:41.203000-08:00",
            "PublishedVersionNumber": 1,
            "LastPublishedTime": "2020-12-18T01:30:41.209000-08:00"
        }

3. Run the describe-dashboard command for the DashboardId copied in the preceding step:

aws quicksight describe-dashboard --aws-account-id 31********64 --dashboard-id "c5345f81-e79d-4a46-8203-2763738489d1"

The response should look like the following code:

{
    "Status": 200,
    "Dashboard": {
        "DashboardId": "c5345f81-e79d-4a46-8203-2763738489d1",
        "Arn": "arn:aws:quicksight:us-east-1:31********64:dashboard/c5345f81-e79d-4a46-8203-2763738489d1",
        "Name": "Sportinng_event_ticket_info_dashboard",
        "Version": {
            "CreatedTime": "2020-12-18T01:30:41.203000-08:00",
            "Errors": [],
            "VersionNumber": 1,
            "SourceEntityArn": "arn:aws:quicksight:us-east-1:31*******64:analysis/daddefc4-c97c-4460-86e0-5b488ec29cdb",
            "DataSetArns": [
                "arn:aws:quicksight:us-east-1:31********64:dataset/24b1b03a-86ce-41c7-9df7-5be5343ff9d9"
            ]
        },
        "CreatedTime": "2020-12-18T01:30:41.209000-08:00",
        "LastPublishedTime": "2020-12-18T01:30:41.209000-08:00",
        "LastUpdatedTime": "2020-12-18T01:30:41.203000-08:00"
    },
    "RequestId": "be9e60a6-d271-4aaf-ab6b-67f2ba5c1c20"
}

4. Use the details obtained from the describe-dashboard command to create a JSON file based on the file create-template-cli-input.json.

The following code represents the input for creating a QuickSight template:

 {
    "AwsAccountId": "31********64",
    "TemplateId": "Sporting_event_ticket_info_template",
    "Name": "Sporting event ticket info template",
    "SourceEntity": {
        "SourceAnalysis": {
            "Arn": "arn:aws:quicksight:us-east-1:31********64:analysis/daddefc4-c97c-4460-86e0-5b488ec29cdb",
            "DataSetReferences": [
                {
                    "DataSetPlaceholder": "TicketInfo",
                    "DataSetArn": "arn:aws:quicksight:us-east-1:31********64:dataset/24b1b03a-86ce-41c7-9df7-5be5343ff9d9"
                }
            ]
        }
    },
    "VersionDescription": "1"
}

5. Run the create-template command to create a template object based on the file you created.

For example, the JSON file named create-template-cli-input.json would be run as follows:

aws quicksight create-template --cli-input-json file://./create-template-cli-input.json

The following is the expected response for the create-template command:

{
    "Status": 202,
    "Arn": "arn:aws:quicksight:us-east-1:31********64:template/Sporting_event_ticket_info_template",
    "VersionArn": "arn:aws:quicksight:us-east-1:31********64:template/Sporting_event_ticket_info_template/version/1",
    "TemplateId": "Sporting_event_ticket_info_template",
    "CreationStatus": "CREATION_IN_PROGRESS",
    "RequestId": "c32e4eb1-ecb6-40fd-a2da-cce86e15660a" 
}

The template is created in the background, as noted by the CreationStatus. Templates aren’t visible within the QuickSight UI; they’re a developer-managed or admin-managed asset that is only accessible via the APIs.

6. To check status of the template, run the describe-template command:

aws quicksight describe-template --aws-account-id 31********64 --template-id "Sporting_event_ticket_info_template"

The expected response for the describe-template command should indicate a Status of CREATION_SUCCESSFUL:

{
    "Status": 200,
    "Template": {
        "Arn": "arn:aws:quicksight:us-east-1:31********64:template/Sporting_event_ticket_info_template",
        "Name": "Sporting event ticket info template",
        "Version": {
            "CreatedTime": "2020-12-18T01:56:49.135000-08:00",
            "VersionNumber": 1,
            "Status": "CREATION_SUCCESSFUL",
            "DataSetConfigurations": [
                {
                    "Placeholder": "TicketInfo",
                    "DataSetSchema": {
                        "ColumnSchemaList": [
                            [
                        {
                            "Name": "ticket_id",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "event_id",
                            "Type": "INTEGER"
                        },
                        {
                            "Name": "sport",
                            "Type": "STRING"
                        },
                        {
                            "Name": "event_date_time",
                            "Type": "DATETIME"
                        },
                        {
                            "Name": "home_team",
                            "Type": "STRING"
                        },
                        {
                            "Name": "away_team",
                            "Type": "STRING"
                        },
                        {
                            "Name": "location",
                            "Type": "STRING"
                        },
                        {
                            "Name": "city",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat_level",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "seat_section",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat_row",
                            "Type": "STRING"
                        },
                        {
                            "Name": "seat",
                            "Type": "STRING"
                        },
                        {
                            "Name": "ticket_price",
                            "Type": "DECIMAL"
                        },
                        {
                            "Name": "ticketholder",
                            "Type": "STRING"
                        }
                        ]
                    },
                    "ColumnGroupSchemaList": []
                }
            ],
            "Description": "1",
            "SourceEntityArn": "arn:aws:quicksight:us-east-1:31********64:analysis/daddefc4-c97c-4460-86e0-5b488ec29cdb"
        },
        "TemplateId": "Sporting_event_ticket_info_template",
        "LastUpdatedTime": "2020-12-18T01:56:49.125000-08:00",
        "CreatedTime": "2020-12-18T01:56:49.125000-08:00"
    },
    "RequestId": "06c61098-2a7e-4b0c-a27b-1d7fc1742e06"
}

7. Take note of the TemplateArn value in the output to use in subsequent steps.
8. After you verify the template has been created, create a second JSON file (TemplatePermissions.json) and replace the Principal value with the ARN for the target account:

[
{
"Principal": "arn:aws:iam::86*******55:root",
"Actions": ["quicksight:UpdateTemplatePermissions","quicksight:DescribeTemplate"]
}
]

9. Use this JSON file as the input for the update-template-permissions command, which allows cross-account read access from the source template (source account) to the target account:

aws quicksight update-template-permissions --aws-account-id 31********64 --template-id "Sporting_event_ticket_info_template" --grant-permissions file://./TemplatePermission.json --profile default

This command permits the target account to view the template in the source account. The expected response for the update-template-permissions command should look like the following code:

{
    "Status": 200,
    "TemplateId": "Sporting_event_ticket_info_template",
    "TemplateArn": "arn:aws:quicksight:us-east-1:31********64:template/Sporting_event_ticket_info_template",
    "Permissions": [
        {
            "Principal": "arn:aws:iam::86********55:root",
            "Actions": [
                "quicksight:UpdateTemplatePermissions",
                "quicksight:DescribeTemplate"
            ]
        }
    ],
    "RequestId": "fa153511-5674-4891-9018-a8409ad5b8b2"
}

At this point, all the required work in the source account is complete. The next steps use the AWS CLI configured for the target account.

Step 3: Create QuickSight resources in the target account

To create the data sources and data templates in the target account, you perform the following actions in the target environment using Test-qs-admin-user:

3a) Create a data source in the target account.

3b) Create datasets in the target account.

3c) Create dashboards in the target account.

Step 3a: Create a data source in the target account

To create a data source in your target account, complete the following steps:

1. Use the data source file created in Step 2a to run the create-data-source command in the target environment:

aws quicksight create-data-source --cli-input-json file://./create-data-source-cli-input.json

The response from the command should indicate the creation is in progress:

{
    "Status": 202,
    "Arn": "arn:aws:quicksight:us-east-1:86********55:datasource/QS_Test",
    "DataSourceId": "QS_Test",
    "CreationStatus": "CREATION_IN_PROGRESS",
    "RequestId": "3bf160e2-a5b5-4c74-8c67-f651bef9b729"
}

2. Use the describe-data-source command to validate that the data source was created successfully:

aws quicksight describe-data-source --aws-account-id 86********55 --data-source-id "QS_Test"

The following code shows the response:

{
    "Status": 200,
    "DataSource": {
        "Arn": "arn:aws:quicksight:us-east-1:86********55:datasource/QS_Test",
        "DataSourceId": "QS_Test",
        "Name": "QS_Test",
        "Type": "POSTGRESQL",
        "Status": "CREATION_SUCCESSFUL",
        "CreatedTime": "2020-12-21T12:11:30.734000-08:00",
        "LastUpdatedTime": "2020-12-21T12:11:31.236000-08:00",
        "DataSourceParameters": {
            "PostgreSqlParameters": {
                "Host": "dmslabinstance.*************.us-east-1.rds.amazonaws.com",
                "Port": 5432,
                "Database": "sportstickets"
            }
        },
        "SslProperties": {
            "DisableSsl": false
        }
    },
    "RequestId": "fbb72f11-1f84-4c57-90d2-60e3cbd20454"}

3. Take note of the DataSourceArn value to reference later when creating the dataset.

The data source should now be available within QuickSight. Keep in mind that the data source is visible to the Test-qs-admin-user user, so you must sign in as Test-qs-admin-user and open QuickSight. For this post, the input JSON file renamed the data source to reflect the test environment. Alternatively, sign in to the target QuickSight account and choose Create new dataset to view the available data source.

Step 3b: Create datasets in the target account

Now that the data source is in the target environment, you’re ready to create your datasets.

1. Use the create-data-set command to create the dataset using the create-data-set-cli-input-sql.json created in Step 2b.

Make sure to replace the DataSourceARN in create-data-set-cli-input-sql.json with the Data SourceArn value shown in the describe-data-source command in Step 3a.

aws quicksight create-data-set --cli-input-json file://./create-data-set-cli-input-sql.json

The following code shows our results:

{
    "Status": 201,
    "Arn": "arn:aws:quicksight:us-east-1:86********55:dataset/24b1b03a-86ce-41c7-9df7-5be5343ff9d9",
    "DataSetId": "24b1b03a-86ce-41c7-9df7-5be5343ff9d9",
    "IngestionArn": "arn:aws:quicksight:us-east-1:86********55:dataset/24b1b03a-86ce-41c7-9df7-5be5343ff9d9/ingestion/d819678e-89da-4392-b550-04221a0e4c11",
    "IngestionId": "d819678e-89da-4392-b550-04221a0e4c11",
    "RequestId": "d82caa28-ca43-4c18-86ab-5a92b6b5aa0c"
}

2. Make note of the ARN of the dataset to use in a later step.
3. Validate that the dataset was created using the describe-data-set command:

aws quicksight describe-data-set --aws-account-id 86********55 --data-set-id "24b1b03a-86ce-41c7-9df7-5be5343ff9d9"

Alternately, sign in to QuickSight to see new the new datasets on the list.

Step 3c: Create dashboards in the target account

Now that you shared the template with the target account in Step 2c, the final step is to create a JSON file that contains details about the dashboard to migrate to the target account.

1. Create a JSON file (create-dashboard-cli-input.json) based on the following sample code, and provide the target account and the source account that contains the template:

{
    "AwsAccountId": "86********55",
    "DashboardId": "TicketanalysisTest",
    "Name": "Sportinng_event_ticket_info_dashboard",
    "Permissions": [
        {
            "Principal": "arn:aws:quicksight:us-east-1:86********55:user/default/Test-qs-admin-user",
            "Actions": [
                "quicksight:DescribeDashboard",
                "quicksight:ListDashboardVersions",
                "quicksight:UpdateDashboardPermissions",
                "quicksight:QueryDashboard",
                "quicksight:UpdateDashboard",
                "quicksight:DeleteDashboard",
                "quicksight:DescribeDashboardPermissions",
                "quicksight:UpdateDashboardPublishedVersion"
            ]
        }
    ],
    "SourceEntity": {
        "SourceTemplate": {
            "DataSetReferences": [
                {
                   "DataSetPlaceholder": "TicketInfo",
                    "DataSetArn": "arn:aws:quicksight:us-east-1:86********55:dataset/24b1b03a-86ce-41c7-9df7-5be5343ff9d9"
                }
            ],
            "Arn": "arn:aws:quicksight:us-east-1:31********64:template/Sporting_event_ticket_info_template"
        }
    },
    "VersionDescription": "1",
    "DashboardPublishOptions": {
        "AdHocFilteringOption": {
            "AvailabilityStatus": "DISABLED"
        },
        "ExportToCSVOption": {
            "AvailabilityStatus": "ENABLED"
        },
        "SheetControlsOption": {
            "VisibilityState": "EXPANDED"
        }
    }
}

The preceding JSON file has a few important values:

• The Principal value in the Permissions section references a QuickSight user (Test-qs-admin-user) in the target QuickSight account, which is assigned various actions on the new dashboard to be created.
• The DataSetPlaceholder in the SourceTemplate must use the same name as specified in the template created in Step 2c. This applies to all DataSetPlaceholder values if more than one is referenced in the dashboard.
• The DataSetArn value is the ARN of the dataset created in Step 3b.
• The ARN value in the SourceTemplate section references the ARN of the template created in the source account in Step 2c.

2. After you create the file, run the create-dashboard command to create the dashboard in the target QuickSight account:

aws quicksight create-dashboard --cli-input-json file://./create-dashboard-cli-input.json

The following code shows the response:

{
    "Status": 202,
    "Arn": "arn:aws:quicksight:us-east-1:86********55:dashboard/TicketanalysisTest",
    "VersionArn": "arn:aws:quicksight:us-east-1:86********55:dashboard/TicketanalysisTest/version/1",
    "DashboardId": "TicketanalysisTest",
    "CreationStatus": "CREATION_IN_PROGRESS",
    "RequestId": "d57fa0fb-4736-441d-9e74-c5d64b3e4024"
}

3. Open QuickSight for the target account to see the newly created dashboard.

Use the same Test-qs-admin-user to sign in to QuickSight. The following screenshot shows that the provided DashboardId is part of the URL.

Like any QuickSight dashboard, you can share the dashboard with users and groups within the target QuickSight account.

The QuickSight CLI includes additional commands for performing operations to update existing datasets and dashboards in a QuickSight account. You can use these commands to promote new versions of existing dashboards.

Clean up your resources

As a matter of good practice, when migration is complete, you should revoke the cross-account role created in Step 1 that allows trusted account access. Similarly, you should disable or remove any other user accounts and permissions created as part of this process.

 Conclusion

This post demonstrated an approach to migrate QuickSight objects from one QuickSight account to another. You can use this solution as a general-purpose method to move QuickSight objects between any two accounts or as a way to support SDLC practices for managing and releasing versions of QuickSight solutions in operational environments.

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 video Admin Level-Up Virtual Workshop, V2 on YouTube.

About the authors

Abhinav Sarin is a senior partner solutions architect at Amazon Web Services, his core interests include databases, data analytics and machine learning. He works with AWS customers/partners to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

Michael Heyd is a Solutions Architect with Amazon Web Services and is based in Vancouver, Canada. Michael works with enterprise AWS customers to transform their business through innovative use of cloud technologies. Outside work he enjoys board games and biking.

Glen Douglas is an Enterprise Architect with over 25 years IT experience and is a Managing Partner at Integrationworx. He works with clients to solve challenges with data integration, master data management, analytics and data engineering, in a variety of industries and computing platforms. Glen is involved in all aspects of client solution definition through project delivery and has been TOGAF 8 & 9 certified since 2008.

Alex Savchenko is a Senior Data Specialist with Integrationworx. He is TOGAF 9 certified and has over 22 years experience applying deep knowledge in data movement, processing, and analytics in a variety of industries and platforms.

Post Syndicated from Luis Gerardo Baeza original https://aws.amazon.com/blogs/big-data/power-operational-insights-with-amazon-quicksight/

Organizations need a consolidated view of their applications, but typically application health status is siloed: end-users complain on social media platforms, operational data coming from application logs is stored on complex monitoring tools, formal ticketing systems track reported issues, and synthetic monitoring data is only available for the tool administrators.

In this post, we show how to use Amazon QuickSight, AWS’ fully managed, cloud-native Business Intelligence service to quickly build a dashboard that consolidates:

• Operational data from application logs coming from Amazon CloudWatch.
• Issues reported on Jira software as a service (SaaS) edition.
• Public posts on Twitter.
• Synthetic monitoring performed using a CloudWatch Synthetics canary.

This dashboard can provide a holistic view of the health status of a workload, for example, you can:

• Trace end-users complaining on Twitter or creating issues on Jira back to the application logs where the error occurred.
• Identify when customers are complaining about system performance or availability on social media.
• Corelate reports with monitoring metrics (such as availability and latency).
• Track down errors in application code using log information.
• Prioritize issues already being addressed based on Jira information.

Solution overview

The following architecture for the solution consists of a subscription filter for CloudWatch Logs to continuously send the application logs to an Amazon Simple Storage Service (Amazon S3) bucket, an AWS Glue crawler to update Amazon S3 log table metadata, a view on Amazon Athena that formats the data on the bucket, and three QuickSight datasets: Athena, Jira, and Twitter.

To implement this solution, complete the following steps:

1. Set up CloudWatch and AWS Glue resources.
2. Set up a QuickSight dataset for Athena.
3. Set up a QuickSight dataset for CloudWatch Synthetics.
4. Set up a QuickSight dataset for Twitter.
5. Set up a QuickSight dataset for Jira.
6. Create a QuickSight overview analysis.
7. Create a QuickSight detailed analysis.
8. Publish your QuickSight dashboard.

Prerequisites

To get started, make sure you make the following prerequisites:

• An AWS account.
• Previous experience working with the AWS Management Console.
• A Twitter account.
• A Jira SaaS account. Make sure that the DNS name of your Jira Cloud is accessible to QuickSight.
• Access to the Athena engine v2.

Set up CloudWatch and AWS Glue resources

Start by deploying a Lambda transformation function to use with your Amazon Kinesis Data Firehose delivery stream:

1. On the Lambda console, launch a new function using the kinesis-firehose-cloudwatch-logs-processor blueprint.
2. Enter a function name and choose Create function.
3. Modify the transformLogEvent function in the Lambda code:

function transformLogEvent(logEvent) {
return Promise.resolve(`${logEvent.timestamp},${logEvent.message}\n`);
}

4. Choose Deploy to update the function.

As part of these steps, you create a new AWS Identity and Access Management (IAM) role with basic permissions and will attach an IAM policy created by AWS CloudFormation later.

5. Choose Copy ARN and save the ARN temporarily for later use.

To create the sample resources, complete the following steps:

6. Choose Launch Stack:

7. Choose Next.
8. Enter a stack name.
9. For TransformationLambdaArn, enter the function ARN you copied earlier.
10. Choose Next twice, then acknowledge the message about IAM capabilities.
11. Choose Create stack.

By default, when you launch the template, you’re taken to the AWS CloudFormation Events page. After 5 minutes, the stack launch is complete.

Test the Lambda function

We can test the function to write sample logs into the log group.

1. On the Resources page of the AWS CloudFormation console search for LogGenerator.
2. Choose the physical ID of the Lambda function.
3. On the Lambda console, choose the function you created.
4. Choose Test, enter sample for the Event name and leave the other configurations at their default.
5. Choose Create.
6. Choose Test again and you should receive the message “Logs generated.”
7. On the Functions page of the Lambda console, choose the transformation function you created.

Change the AWS Lambda function configuration

8. On the Configuration tab, choose General configuration.
9. Choose Edit.
10. For Memory, set to 256 MB.
11. For Timeout, increase to 5 minutes.
12. Choose Save.
13. Choose Permissions, then choose the role name ID to open the IAM console.
14. Choose Attach policies.
15. Search for and select InsightsTransformationFunctionPolicy.
16. Choose Attach policy.

The policy you attached allows your Lambda transformation function to put records into your Kinesis Data Firehose delivery stream.

Partitioning has emerged as an important technique for organizing datasets so that they can be queried efficiently by a variety of big data systems. Data is organized in a hierarchical directory structure based on the distinct values of one or more columns. The Firehose delivery stream automatically partitions data by date.

17. On the Amazon S3 console, locate and choose the bucket insightlogsbucket.
18. Choose the cwlogs prefix and navigate through the partitions created by Kinesis Data Firehose (year/month/day/hour).

Make sure the bucket contains at least one file. (It may take up to 5 minutes to show because Kinesis Data Firehose buffers the data by default.)

To optimize the log storage, you can later enable record transformation to Parquet, a columnar data format. For more information, see Converting Input Record Format (Console).

Run the AWS Glue crawler

To complete this section, run the AWS Glue crawler to discover bucket metadata:

1. On the AWS Glue console, choose Crawlers.
2. Select the crawler InsightsLogCrawler and choose Run crawler.

3. Wait for the crawler to complete, then choose Tables.
4. Choose the filter bar and choose the resource attribute Database.
5. Enter insightsdb and choose Enter.
6. You should see a table with a CSV classification.
7. On the Athena console, enter the following into the query editor:

select * from cwlogs limit 5;

8. Choose Run query.

You should see a table like the following screenshot.

Set up a QuickSight dataset for Athena

If you haven’t signed up for a QuickSight subscription, do so before creating your dataset.

To use the table created in the AWS Glue Data Catalog, you have to authorize connections to Athena.

1. On the QuickSight console, choose your QuickSight username and choose Manage QuickSight.
2. Choose Security & permissions in the navigation pane.
3. Choose Add or remove to set up access to Amazon S3.

5. Choose your S3 bucket insightslosbucket created by the CloudFormation template to allow QuickSight to access it.
6. Choose the QuickSight logo to exit the management screen.

Add the Athena dataset

Now we can set up the Athena dataset.

1. On the QuickSight console, choose Datasets in the navigation pane, then choose New dataset.
2. Choose Athena from the available options.
3. For Data source name, enter cwlogs.
4. Leave the default workgroup selected (primary).
5. Choose Create data source.
6. Open the list of databases and choose insightsdb.
7. Choose the cwlogs table and choose Use custom SQL.
8. Replace New custom SQL with cwlogs and enter the following SQL code:

SELECT col1 as logmessage, col0 as datetime FROM "insightsdb"."cwlogs"

9. Choose Confirm query.

You receive a confirmation of the dataset creation.

10. Choose Edit/Preview Data.
11. For Dataset name, enter cwlogs.

Separate severity levels

The logs contain a severity level (INFO, WARN, ERRR) embedded into the message, to enable analysis of the logs based on this value, you separate the severity level from the message using QuickSight calculated fields.

1. Choose Dataset below the query editor and choose the datetime
2. Change the type to Date

3. Open the Fields panel on the left and choose Add calculated field.
4. For Add name, enter level.
5. Enter the following code:

substring(logmessage,1,4)

6. Choose Save.
7. Choose Add calculated field.
8. For Add name, enter message.
9. Enter the following code:

replace(logmessage,concat(level," - "),"")

10. Choose Save.
11. Choose the options icon (three dots) next to the logmessage field and choose Exclude field.

The final dataset should be similar to the following screenshot.

12. Choose Save.

Set up a QuickSight dataset for CloudWatch Synthetics

You add Synthetics monitoring metrics to our QuickSight dashboard to have visibility into the availability of your website. For this, you use the Athena CloudWatch connector.

Create a CloudWatch Synthetics canary

To create a CloudWatch Synthetics canary that monitors your website using heartbeats (sample requests) to test availability, complete the following steps:

1. On the CloudWatch Synthetics console, choose Create canary.
2. Make sure the blueprint Heartbeat monitoring is selected.
3. For Name, enter webstatus.
4. For Application or endpoint URL, enter your website’s URL.
5. Under Schedule, enter a frequency that works best for you (1–60 minutes).

The default setup is every 5 minutes.

6. Enter an S3 location to store artifacts.
7. Choose Create canary.

Set up the Athena CloudWatch connector

CloudWatch Synthetics sends availability data from the canary to CloudWatch Metrics. To query the metrics, you can use the Athena CloudWatch Metrics connector.

1. On the Athena console, choose Data sources.
2. Choose Connect data source and choose Query a data source.
3. Choose Amazon CloudWatch Metrics and choose Next.
4. Choose Configure new AWS Lambda function.

 A new tab opens in the browser, which you return to after deploying the Lambda function.

5. For SpillBucket, enter the name of your S3 bucket insightslobsbucket created by the CloudFormation template you deployed.
6. For AthenaCatalogName, enter cwmetrics.
7. Select I acknowledge that this app creates custom IAM roles.
8. Choose Deploy.
9. Close the browser tab and go to the Athena tab you were on before.
10. Refresh the list of Lambda functions by choosing the refresh icon.
11. Under Lambda function, choose the Lambda function you just created.
12. For Catalog name, enter cwmetrics.
13. Choose Connect.

Set up permissions for QuickSight to use the connector

The CloudWatch connector runs on Lambda, which uses a spill bucket to handle large queries, so QuickSight needs permission to invoke the Lambda function and write to the spill bucket. For more information, see the GitHub repo. Let’s set up permission to allow QuickSight use the CloudWatch connector.

1. On the QuickSight console admin page, choose Security & permissions.
2. Choose Add or remove.
3. Choose Athena.
4. On the S3 tab, specify write permissions for your insightslogsbucket S3 bucket
5. On the Lambda tab, choose your Lambda function cwmetrics.

Create the new QuickSight dataset

Now we set up the QuickSight dataset for CloudWatch Synthetics.

1. On the QuickSight console, choose Datasets.
2. Choose New dataset and choose Athena.
3. For Data source name, enter cwmetrics.
4. Choose Create data source.
5. Open the list of catalogs and choose cwmetrics.
6. Choose the metric_samples table and choose Use custom SQL.
7. For New custom SQL field, enter cwmetrics.
8. Enter the following SQL code:

SELECT timestamp, value, dimension.dim_name, dimension.dim_value
    FROM cwmetrics.default.metric_samples CROSS JOIN UNNEST(dimensions) as  t(dimension)
    WHERE namespace='CloudWatchSynthetics' AND metric_name='SuccessPercent'
        AND statistic='Average' and dim_name='StepName' 
        AND dimension.dim_name='CanaryName' AND dimension.dim_value='webstatus'
        AND timestamp BETWEEN To_unixtime(Now() - INTERVAL '7' DAY) 
        AND To_unixtime(Now())

9. Choose Confirm query.

You receive a confirmation of the dataset creation.

10. Choose Edit/Preview data.
11. Choose Dataset below the query editor and choose the timestamp field.
12. Change the type to Date.
13. Choose Save.

Set up a QuickSight dataset for Twitter

To set up the Twitter dataset, complete the following steps:

1. On the QuickSight console, create a new dataset with Twitter as the source.
2. For Data source name, enter twitterds.
3. For Query, enter a hashtag or keyword to analyze from Twitter posts.
4. Choose Create data source.

A new window opens requesting you to give QuickSight OAuth authorization for Twitter.

5. Sign in to Twitter and choose Authorize application.

6. Choose the table Twitt, then choose Edit/Preview data.

It might take a couple of minutes for the records to be imported into SPICE, the QuickSight Super-fast, Parallel, In-memory Calculation Engine. It’s engineered to rapidly perform advanced calculations and serve data. You can continue with the tutorial while SPICE finishes in the background.

Let’s create a calculated field that classifies tweets as a Good experience or Bad experience by searching for the words “error,” “problem,” or “expensive.” You can choose other words that fit your use case.

7. For Dataset name, enter twitterds.
8. Choose Add calculated field.
9. For Add name, enter Experience.
10. Enter the following code:

ifelse(locate(toLower({Text}),"error")<>0,"BAD",locate(toLower({Text}),"problem")<>0,"BAD",locate(toLower({Text}),"expensive")<>0,"BAD","GOOD")

11. Choose Save and then choose Save & visualize.

The Twitter Standard Search API returns data for 7 days only. For more information, see Supported Data Sources.

Set up a QuickSight dataset for Jira Cloud

QuickSight can connect to SaaS data sources, including Jira Cloud. To set up the Jira dataset, complete the following steps:

1. To create an API token, open the Jira website within an authenticated browser.
2. Choose Create API token.
3. For Label, enter QuickSight.
4. Choose Create.

5. Create a new dataset in QuickSight and choose Jira as the data source.
6. For Data source name, enter jirads.
7. For Site base URL, enter the URL you use to access Jira.
8. For Username, enter your Jira username.
9. For API token or password, enter the token you created.
10. Choose Create and choose the Issues  table.
11. Choose Select and then choose Visualize.

You’re redirected to a newly created QuickSight analysis.

Create a QuickSight overview analysis

We use the previously created analysis as a starting point for our overview analysis.

1. Choose the Edit data icon (pencil).

2. Choose Add dataset.
3. Choose the Issues dataset and choose Select.
4. Repeat the steps for the twitterds, cwlogs and cwmetrics

You should see the four datasets added to the QuickSight visual.

In the navigation pane, you can find the available fields for the selected dataset and on the right, the visuals. Each visual is tied to a particular dataset. When the tutorial instructs you to create a new visual from a dataset, you must choose the dataset from the list first, then choose + Add.

5. Choose the visual, then the options icon (three dots).
6. Choose Delete.

7. Update the QuickSight analysis name to Operational Dashboard.

Add a visual for the Twitter dataset

To add your first visual, complete the following steps:

1. Add a new visual from the Twitter dataset.
2. Change the visual type to KPI.
3. Choose the field RetweetCount.
4. Name the visual Bad experience retweet count.
5. Use the resize button to set the size to a fourth of the screen width.

14. In the navigation pane, choose Filter and then choose Create one.
15. Choose the Created field and then choose the filter.
16. Change the filter type to relative dates and choose
17. Choose Last N hours and enter 24 for Number of hours.
18. Choose

This filter allows us to see only the most up-to-date information from the last 24 hours.

19. Add a second filter and choose the Experience
20. Leave BAD selected marked and choose Apply.

Now you only see information about Twitter customers with a bad experience.

Add a visual for CloudWatch metrics

Next, we add a new visual for our CloudWatch metrics.

1. Choose Visualize and then choose the cwmetrics
2. Add a new gauge chart.
3. Choose Field wells to open the visual field configuration.
4. Choose Value and change Aggregate to Average.

5. Drag and drop the value field from the field list into Target value and change the aggregate to
6. Name the visual System health status.

7. Similar to what you did on the previous visual, add a filter to include only the last 24 hours based on the timestamp field.

Add a visual for CloudWatch error logs

Next, we create a visual for our CloudWatch logs dataset.

1. Choose the cwlogs dataset and add a new KPI visual.
2. Drag and drop the message field into the Value
3. Name the visual Error log count.
4. Create a filter using the level field and from the list of values.
5. Deselect all but ERRR.
6. Choose Apply.

This filters only logs where there was an error found.

7. Create a filter for the last 24 hours.

Add a visual for Jira issues

Now we add a visual for open Jira issues.

1. Choose the Issues dataset.
2. Create a KPI visual using the Id field for Value.
3. Add a filter for issues of type bug.
   1. Use the IssueType_Name field and select only the records with value Error.
4. Add a filter for issues open.
   1. Use the Status_Name field and select only the records with the value On-going or To-do.
5. Add a filter for the last 24 hours using the Date_Created
6. Name the filter Bug Issues open.
7. Resize the visuals and organize them as needed.

Complete the oversight dashboard

We’re almost done with our oversight dashboard.

1. Create four new visuals as specified in the following table.

2. Modify every filter you created to apply them to all the visuals.

13. Choose the tab Sheet 1 twice to edit it.
14. Enter Overview.
15. Choose Enter.

The overview analysis of our application health dashboard is complete.

QuickSight provides a drill-up and drill-down feature to view data at different levels of a hierarchy; the feature is added automatically for date fields. For more information, see Adding Drill-Downs to Visual Data in Amazon QuickSight.

In the previous step, you applied a drill-down when you changed the aggregation to hour.

Create a QuickSight detailed analysis

To create a detailed analysis, we create new tabs for CloudWatch logs, Tweets, and Jira issues.

Create a tab for CloudWatch logs

To create a tab to analyze our CloudWatch logs, complete the following steps:

1. Choose the add icon next to the Overview tab and name it Logs.
2. Create three visuals from the cwlogs dataset :
   1. Donut chart with the level field for Group/Color and message (count) for Value.
   2. Stacked combo bar with the datetime (aggregate: hour) field for X axis box, level (count) for Bars, and level for Group/Color for bars.
   3. Table with the fields level, message, and datetime for Value.

To improve the analysis of log data, format the level field based on its content using QuickSight conditional formatting: red for ERRR and Green for DEBG.

3. Choose the table visual and choose on the visual options icon (three dots), then choose Conditional formatting.
4. Choose the add icon and select the level field, then choose Add Text color.
5. For Value, enter ERRR.
6. For Color, choose red.
7. Choose Add condition.
8. For Value, enter DEBG.
9. For Color, choose green.

10. Choose Apply.
11. Resize the visuals and update their titles as needed.

To enable data exploration, let’s set up an action on the Logs tab.

12. Choose the Top Logs visual and choose Actions.
13. Choose Filter same-sheet visuals.
14. Choose ERRR on the donut chart.

The Latest Logs table filters only the DEBG level rows.

15. Create a filter for the last 24 hours applicable to all visuals from the dataset.

Create a tab for Tweets

To create a tab for Twitter analysis and add visuals, complete the following steps:

1. Choose the add icon next to the Logs tab and name the new tab Tweets.
2. Delete the visual added and create a new donut chart from the twitterds dataset.
3. Choose the field Source and name the visual Twit Source.
4. Create a new word cloud visual and choose the Username

With QuickSight, you can exclude visual elements to focus the analysis on certain data. If you see a big “Other” username on the word cloud, choose it and then choose Hide “other” categories.

5. To narrow down the elements on the word cloud to the top 50, choose the mesh icon.
6. Under the Group by panel, enter 50 for Number of words.
7. Choose the mesh icon again and choose Allow vertical words.
8. Name the visual Top 50 Users.

Let’s create a table with the Twitter details.

9. Add a new table visual.
10. Drag and drop the field Text into the Group by box and RetweetCount into Value.
11. Name the visual Top Retweet.
12. Resize the columns on the table using the headers border as needed.
13. To sort the table from the top retweeted posts, choose the header of the field RetweetCount and choose the sort descending icon.

Let’s add a color and an icon based on number of retweets.

14. Choose the configuration icon (three dots) and choose conditional formatting.
15. Choose the RetweetCount field, then choose the add icon and choose the three bars icon set.

16. Choose the Custom conditions option and enter the Value field as follows:
    1. Condition #1 – Value: 10000; color: red
    2. Condition #2 – Start Value: 2000; End Value: 10000; color: orange
    3. Condition #3 – Value: 2000; color: keep the default

Now you can see the field RetweetCount formatted with an icon and color based on the value.

Now we add the user location to the analysis.

17. Add a new horizontal bar chart visual.
18. Use the UserLocation field for Y axis and the RetweetCount as Value.
19. Sort descending by RetweetCount.
20. Choose the mesh icon to expand the Y-axis panel and enter 10 for Number of data points to show.
21. If you see an empty country, choose it and choose Exclude empty.
22. Name the visual Top 10 Locations.

To complete this tab of your analysis, resize the visuals and organize them as follows.

23. Similarly, as you did before, choose every visual and add the action Filter same-sheet visuals, which allows you to explore your data.

For example, you can choose one location or source and the Top users and Retweet tables are filtered.

24. Create a filter for the last 24 hours applicable to all visuals from the dataset.

Create a tab for Jira issues

Finally, we create a tab for Jira issue analysis.

1. Choose the add icon next to the Tweets tab and name the new tab Issues.
2. Delete the visual created and create a new horizontal stacked 100% bar chart visual from the Issues dataset.
3. Drag and drop the fields as follows (because this dataset has many fields, you can find them using the Field list search bar):
   1. Y-axis – Status_Name
   2. Value – Id (count)
   3. Group/Color – Assigne_DisplayName

This visual shows you how issues have progressed among assignee name.

4. Add a new area line chart visual with the field Date_Updated for X axis and TimeEstimate for Value.
5. Add another word cloud visual to find out who the top issue reporters are; use Reporter_DisplayName for Group by and Id (count) for Size.
6. The last visual you add for this tab is a table, include all the necessary fields on the Value box to be able to investigate. I suggest you include Id, Key, Summary, Votes, WatchCount, Priority, and Reporter_DisplayName.
7. Resize and rearrange the visuals as needed.

8. As you did before, choose every visual and add the action Filter same-sheet visuals, which allows you to explore your data.

For example, you can choose one reporter display name or a status and the other visuals are filtered.

9. Create the filter for the last 24 hours applicable to all visuals from the dataset.

Publish your QuickSight dashboard

The analysis that you’ve been working on is automatically saved. To enable other people to view your findings with read-only capabilities, publish your analysis as a dashboard.

1. Choose Share and choose Publish dashboard.
2. Enter a name for your dashboard, such as holistic health status, and choose Publish dashboard.
3. Optionally, select a person or group to share the dashboard with by entering a name in the search bar.
4. Choose Share.

Your dashboard is now published and ready to use. You can easily correlate errors in application logs with posts on Twitter and availability data from your website, and quickly identify which errors are being already addressed based on Jira bug issues open.

By default, this dashboard can only be accessed by you, but you can share your dashboard with other people in your QuickSight account.

When you created the QuickSight datasets for Twitter and Jira, the data was automatically imported into SPICE, accelerating the time to query. You can also set up SPICE for other dataset types. Remember that data is imported into SPICE and must be refreshed.

Conclusion

In this post, you created a dashboard with a holistic view of your workload health status, including application logs, issue tracking on Jira, social media comments on Twitter, and monitoring data from CloudWatch Synthetics. To expand on this solution, you can  include data from Amazon CloudFront logs or Application Load Balancer access logs so you can have a complete view of your application. Also, you could easily embed your dashboard into a custom application.

You can also use machine learning to discover hidden data trends, saving hours of manual analysis with QuickSight ML Insights, or use QuickSight Q to power data discovery using natural language questions on your dashboards. Both features are ready to use in QuickSight without machine learning experience required.

About the author

Luis Gerardo Baeza is an Amazon Web Services solutions architect with 10 years of experience in business process transformation, enterprise architecture, agile methodologies adoption, and cloud technologies integration. Luis has worked with education, healthcare, and financial companies in México and Chile.

Post Syndicated from Daiki Itoh original https://aws.amazon.com/blogs/big-data/docomo-empowers-business-units-with-self-service-knowledge-access-thanks-to-agile-aws-quicksight-business-intelligence/

NTT DOCOMO is the largest telecom company in Japan. It provides innovative, convenient, and secure mobile services that enable customers to realize smarter lives. More than 73 million customers in Japan connect through its advanced wireless networks, including a nationwide LTE network and one of the world’s most progressive LTE Advanced networks. In addition to a wide range of communications-related services, DOCOMO offers value-added Smart Life offerings, including those under its +d initiative. These include the d POINT CLUB customer loyalty point program and d Payment, which enables customers to shop online and make electronic payments using their mobile devices.

All of these services create tremendous amounts of data, providing an opportunity of the company to extract insights that drive business value. To accomplish this goal, the company uses Amazon QuickSight and AWS data technologies to help better understand customers and support sales teams.

Many products, one data team

The company’s data team manages the marketing platform, which includes capturing, analyzing, and reporting on data for DOCOMO Smart Life businesses. For data collection and aggregation, it uses Amazon Redshift as a data warehouse. Amazon Redshift is ideal for storing and querying large amounts of structured data with high performance and reliability.

“With millions of connected customers, we need a highly capable data platform,” says Issei Nishimura, Manager, Marketing Platform Planning Department at DOCOMO. “AWS delivers the right levels of performance.”

In addition to regular reporting from the data warehouse, the company’s business leadership is interested in real-time key performance indicators (KPIs). For d Payment, these include metrics such as active users on a monthly and daily basis. Based on these analyses, leadership can decide how to improve the usage or the sales of each service.

Helping business users access analytics

However, when non-technical decision-makers requested self-service access, the data team had no easy way to provide it—until it decided to adopt QuickSight. QuickSight is a fast, cloud-powered business intelligence service that was easy to deploy and required no on-premises infrastructure.

“Because of native integration with existing AWS services, especially Amazon Redshift, we were able to roll out Amazon QuickSight quickly and easily,” Nishimura says. “In fact, it only took one day to build our first QuickSight dashboards.”

The automated data pipeline starts with Amazon Elastic Compute Cloud (Amazon EC2) to perform extract, transform, and load (ETL) on data, which is then pushed to Amazon Redshift. Amazon SageMaker aggregates and exports it to Amazon Simple Storage Service (Amazon S3), from which QuickSight accesses data for dashboards.

The following is a sample dashboard from NTT DOCOMO. For every marketing campaign, NTT DOCOMO analyzes the number of new unique users of d Payment relative to the number of registrations to the campaign. This allows them to understand how much effect the campaign had on each user category.

With pay-per-session pricing, the company can provide ad hoc data access for line of business decision-makers without capital investment and at low total cost. At the same time, QuickSight can scale to support as many users as needed.

The dashboards can be accessed from any device, providing convenience to the product management teams. It’s easy and intuitive enough for non-technical users—there’s no need for them to write SQL or scripts.

Faster insights to compete in an accelerated marketplace

Previously, it would take a few days to meet requests for ad hoc reports. Now, when people want to check a KPI, they can do it instantly.

“Our team can focus on managing the data warehouse and the regular reporting cadence because the volume of out-of-band requests has been reduced,” Nishimura says.

The solution has had immediate benefits for d Payment sales representatives, who work closely with retailers that use the payment service. These sales representatives want to be able to present relevant KPIs and demographics to the retailers to show trends and improve the services. With QuickSight, the sales team can generate appealing, up-to-date visualizations of the relevant information. They no longer have to spend time building graphics because the QuickSight visualizations are ready to use right away.

Summary

DOCOMO is a data-driven company, using insights to continuously improve its services. AWS enables them to run an enterprise data warehouse that ingests millions of data points with unlimited scale—and provides services such as QuickSight that give non-technical users rapid access to the real-time information they need.

“With these solutions, DOCOMO is breaking down barriers to enable greater use of analytics across the organization,” Nishimura says.

About the Authors

Daiki Itoh is a Business Development Manager for Amazon QuickSight in Japan.

Chirag Dhull is a Principal Product Marketing Manager for Amazon QuickSight.

Post Syndicated from Lillie Atkins original https://aws.amazon.com/blogs/big-data/create-threshold-based-alerts-in-amazon-quicksight/

Every business has a set of key metrics that stakeholders focus on to make the most accurate, data-driven decisions, such as sales per week, inventory turnover rate, daily website visitors, and so on. With threshold-based alerts in Amazon QuickSight, we’re making it simpler than ever for consumers of QuickSight dashboards to stay informed about their key metrics. This blog post walks you through the process of setting up threshold-based alerts to track important metrics on QuickSight dashboards.

Set up an alert

Threshold alerts are set up on dashboards and can be created from KPIs or gauge charts. To set a threshold alert, choose the visual and then choose the alert icon. The alert takes into account all of the filters currently applied to the visual and creates a new alert rule.

The following two screenshots show first a KPI visual and then a gauge visual as well as where to locate the alert icon:

You can set up multiple alerts from a visual, which lets you monitor the data for different sets of changes or conditions.

After you choose the alert icon, you must provide a few configuration details. The alert name auto-fills to the name of the visual. The Alert value is the value in the data that the threshold is checked against based on your rule; this defaults to the primary value of the visual. For the gauge, this means the percent of the goal already achieved (currently 79.81%). In the following screenshot, we see for the KPI it means the week over week difference in the forecasted revenue (currently -$367,456).

Let’s say you want to be alerted whenever the forecasted new monthly revenue dips below $300,000—even if that is multiple times a day. To configure this alert, complete the following steps:

1. For Alert value¸ choose the actual value rather than the difference value (which was the default).
   
2. For Condition, choose Is below.
   
3. Enter the value 300,000.
   
4. For Notification preference, choose As frequently as possible.
   
5. Choose Save.
   

Let’s now say you change your mind and only want to get notified once a week at most when this alert is going off. This is controlled through the notification preference. To make changes to the alert, you go to the management portal, which can be found by choosing Alerts on the navigation bar.

Here is where you can edit, delete, enable, or disable the alert. When the alert has triggered, you will be able to see a list along with other historical (90-day) alerts. This alert doesn’t have any history, because it hasn’t been triggered yet.

To update your notification preference, choose Edit, under Notification Preference pick Weekly at most, then hit Save.

When an alert is triggered, you receive an email notification customized to what Alert Value you have the alert configured for. You can quickly get to the dashboard by choosing View Dashboard.

Alerts are created based on the visual at that point in time and don’t update with changes to the visual in the future. This means the visual can change or be deleted and the alert continues to work as long as the data in the dataset remains valid.

The evaluation schedule for threshold alerts is based on the dataset. For SPICE datasets alert rules are checked against the data after a successful data refresh. With datasets querying your data sources directly, alerts are evaluated daily at a random time between 6PM to 8AM based on the region of the dataset. We’re working on a control for direct query dataset owners to be able to set up their own schedules for checking alerts and increase the frequency up to hourly.

The admin for the QuickSight account can restrict who has access to set threshold alerts through custom permissions. For more information, see Customizing user permissions in Embed multi-tenant analytics in applications with Amazon QuickSight.

Pricing and availability

Threshold alerts are billed for each evaluation, and follow the familiar pricing used for anomaly detection, starting at $0.50 per 1,000 evaluations. For example, if you set up an alert on a SPICE dataset that refreshes daily, you have 30 evaluations of the alert rule in a month, which costs 30 * $0.5/1000 = $0.015 in a month. For more information, see Amazon QuickSight Pricing.

Threshold alerts are a QuickSight Enterprise Edition feature and available for dashboards consumed in the QuickSight website. Threshold alerts aren’t yet available in embedded QuickSight dashboards or on the mobile app.

Conclusion

In this post, we demonstrated how to set up threshold-based alerts to track important metrics on QuickSight dashboards. This makes it even easier for consumers of QuickSight dashboards to stay up to date on their key metrics. For more information see, Amazon QuickSight Documentation.

About the Author

Lillie Atkins is a Product Manager for Amazon QuickSight, Amazon Web Service’s cloud-native, fully managed BI service.