Post Syndicated from Sahitya Pandiri original https://aws.amazon.com/blogs/big-data/coming-january-2022-an-updated-amazon-quicksight-sign-in-experience/

Starting January 2022, the Amazon QuickSight will undergo minor updates, such as changes in the sign-in domain and a new look and feel while signing in. This won’t impact your access to QuickSight. In this post, we walk through changes to expect in the sign-in experience and domains to allow on your organization’s network to sign in to QuickSight.

If your QuickSight account is integrated with an identity provider such as Okta, this change is not applicable to you. If you sign in to QuickSight either using AWS root, AWS Identity and Access Management (IAM), corporate Active Directory, or your native QuickSight credentials, please keep reading.

What’s changing?

The QuickSight sign-in process will be a three-step experience.

Step 1: The first page requires your QuickSight account name.

Step 2: The second step asks for your user name.

Step 3: The third step varies depending on the user type you sign in as: native QuickSight or Active Directory user, AWS root user, or IAM user.

Native QuickSight or Active Directory

If you’re signing in as a native QuickSight user or use your corporate Active Directory credentials, you’re redirected to signin.aws, which prompts you to enter your password with the user name prefilled.

If your account is Multi-Factor Authentication enabled, you will be prompted to enter the MFA code as below,

AWS root user

If you’re signing in as an AWS root user, you’re redirected to signin.aws.amazon.com (or amazon.com) to complete the sign-in process. This page has your username prefilled. On selecting next, it prompts you to enter password.

IAM user

If signing in as an IAM user, you’re redirected to a sign-in page that prompts password with IAM user name pre-filled.

Summary

These changes to the QuickSight sign-in experience will be effective starting January, 2022. Please note the four new domains below you may encounter depending on who you sign in as. As a network administrator, please allow-list these domains within your organization network.

If you have any questions, please reach out to AWS Support via the Support Center on the AWS Management Console.

Post Syndicated from Jose Kunnackal original https://aws.amazon.com/blogs/big-data/create-stunning-pixel-perfect-dashboards-with-the-new-free-form-layout-mode-in-amazon-quicksight/

The latest update of Amazon QuickSight introduces a new free-form dashboard layout option, along with granular per-visual interaction controls and conditional rendering options that open up a range of creative possibilities for dashboard authors. In this post, we look at the new capabilities available and how you can use them to create and share stunning dashboards, whether for QuickSight readers in your organization or to embed in your application for end-users.

What’s new in QuickSight dashboard layouts?

Layout modes in QuickSight determine how visuals in a dashboard are sized and presented to end-users, and the degree of flexibility authors have in modifying the dashboard to meet their visual styling needs. QuickSight now supports three layout options for dashboards:

• Tiled – Visuals snap to a grid layout, minimizing effort needed to resize and arrange visuals. Authors can choose the base screen size they’re building for (for example, laptops or HD monitors), and the dashboard scales automatically to any screen width or display, zooming in or out as needed to scale and display the content on the user’s screen as designed by the author. In mobile view, the visuals display as designed when in landscape mode and automatically arrange to a single column when in portrait mode. As the name suggests, visuals are tiled in this mode, and can’t overlap each other.
• Free-form (new!) – This mode allows visuals to be placed anywhere, including overlapping other visuals, along with pixel-level control over elements on the dashboard. This offers very precise control and detail-oriented design approaches. In this mode, authors still pick a screen size they’re designing for, and dashboards display as designed on all screens, zooming in or out as needed. Because dashboards are designed intricately with overlaps and images, QuickSight doesn’t automatically adjust this view for mobile access, and so mobile users see the author-created layout as is, scaled to fit on their landscape or portrait screen orientation on smaller screens. This mode also offers a new capability of conditionally rendering a visual based on QuickSight parameter-based rules, which opens up creative possibilities for interactive dashboards.
• Classic – This is the legacy layout mode, where visuals snap to a grid layout. In this mode, QuickSight might show or hide content based on screen sizes, which can cause dashboards to display content differently on different devices.

Existing dashboards will remain in classic mode until authors explicitly modify and republish them in tiled or free-form mode as necessary.

Get started with free-form mode

You can get started with free-form mode in five easy steps:

1. Open a new QuickSight analysis and change the layout setting to Free-form.
   1. Optionally, set the View to Fit to window if you prefer seeing the full canvas (scaled up or down).
2. Try resizing and moving the initial visual around. See how the visual stays where you drop it.

When using mouse to move the visuals, they still snap to the closest grid for ease of alignment. Try using the arrow keys for finer adjustments.

3. Add another visual and move it on top of the first visual. See how the visual overlaps the first visual.
4. Make the second visual fully overlap the first visual.
5. To access the first visual without moving the second visual, choose the visual menu of the second bigger visual and send it backward.

Wondering if you can switch your existing analysis to free-form mode? Sure thing! Choose Settings on the left panel of the analysis and choose Free-form from the Layout options.

Get proficient in free-form layout

Now that you know the basics, let’s dive deeper. Four groups of settings are key to building in free-form layout: placement, style, interactions, and rules.

You can access these settings from each visual’s Format visual panel.

• Placement – This lets you control the exact position and size of each visual on your dashboard. You do this by specifying the X,Y coordinates (for the top left corner of your visual) and the desired height and width. Keep in mind that the X,Y coordinates start from the top left of your dashboard (the top left point of your dashboard is 0,0 (X,Y)). You can use the mouse to resize and position a visual, but the placement control gives you an easier way to get perfect alignment.
  
• Style – With style controls, you can set the background, border and selection colors, their transparencies, or turn these off. Disabling the background makes the visual fully transparent. This is extremely useful when you’re layering visuals on top of other visuals for awesome effects.
  
• Interactions – With these controls, you can remove all the visual-level interactions (like drill-downs, sorting, maximizing visuals, and exporting data) when desired. These settings apply to the dashboard view only, and allow you to overlay charts on other charts or images to create the impression of a single composite visual to the end-user.
  
• Rules – You can set rules to show or hide visuals based on parameter values. All visuals are visible by default. You can add rules on when they should be hidden. Alternatively, you can flip the default state of the visual to hidden and configure rules on when to show the visual. Pair this with overlaid placement of visuals to build dynamic dashboards that respond to user selections or inputs from the parent application (when used in embedded context).
  

You can explore a sample dashboard for inspiration, and examine the analysis view to see how it is built.

Also check out the QuickSight dashboard gallery with samples from our partners.

Conclusion

With the new free-form layout, QuickSight enables you to build dashboards that can be tailored to meet your exact dashboard specifications, which can then be distributed to hundreds of thousands of users via the QuickSight portal, mobile app, email, or embedded within your own applications.

About the Authors

Jose Kunnackal, is a principal product manager for Amazon QuickSight, AWS’ cloud-native, fully managed BI service. Jose started his career with Motorola, writing software for telecom and first responder systems. Later he was Director of Engineering at Trilibis Mobile, where he built a SaaS mobile web platform using AWS services. Jose is excited by the potential of cloud technologies and looks forward to helping customers with their transition to the cloud.

Arun Santhosh is a Senior Solution Architect for Amazon QuickSight. Arun started his career at IBM as a developer and progressed on to be an Application Architect. Later, he worked as a Technical Architect at Cognizant. Business Intelligence has been his core focus in these prior roles as well.

Post Syndicated from Raji Sivasubramaniam original https://aws.amazon.com/blogs/big-data/embed-multi-tenant-dashboards-in-saas-apps-using-amazon-quicksight-without-provisioning-or-managing-users/

Amazon QuickSight is a fully-managed, cloud-native business intelligence (BI) service that makes it easy to connect to your data, create interactive dashboards, and share these with tens of thousands of users, either within QuickSight itself, or embedded in software as a service (SaaS) apps.

QuickSight Enterprise Edition recently added row-level security (RLS) using tags, a new feature that allows developers to share a single dashboard with tens of thousands of users, while ensuring that each user can only see and have access to particular data. This means that when an independent software vendor (ISV) adds a QuickSight-embedded dashboard in their app, they don’t have to provision their end-users in QuickSight, and can simply set up tags to filter data based on who the dashboard is being served to. For example, if an ISV wanted to set up a dashboard that was to be shared with 20,000 users across 100 customers of an app, with all users within a customer having access to identical data, this new feature allows you to share a single dashboard for all users, without having to set up or manage the 20,000 users in QuickSight.

RLS enforced using tags makes sure that each end-user only sees data that is relevant to them, while QuickSight automatically scales to meet user concurrency to ensure every end-user sees consistently fast performance. In this post, we look at how this can be implemented.

Solution overview

To embed dashboards without user provisioning, we use the API GenerateEmbedURLForAnonymousUser, which works with QuickSight’s session capacity pricing. With this API, the embedding server (logic in the SaaS app) determines and manages the identity of the user to whom the dashboard is being displayed (as opposed to this identity being provisioned and managed within QuickSight).

The following diagram shows an example workflow of embedded dashboards that secures data based on who is accessing the application using RLS with tags.

In this case, an ISV has a SaaS application that is accessed by two end-users. One is a manager and other is a site supervisor. Both users access the same application and the same QuickSight dashboard embedded in the application and they’re not provisioned in QuickSight. When the site supervisor accesses the dashboard, they only see data pertaining to their site, and when the manager accesses the dashboard, they see data pertaining to all the sites they manage.

To achieve this behavior, we use a new feature that enables configuring the row-level security using tags. This method of securing data on embedded dashboards works only when dashboards are embedded without user provisioning (also called anonymous embedding). The process includes two steps:

1. Set up tag keys on the columns of the datasets used to build the dashboard.
2. Set values for the tag keys at runtime when embedding the dashboard anonymously.

Set up tag keys on columns in the datasets used to build the dashboard

ISVs or developers can set columns on the datasets using the CreateDataset or UpdateDataset APIs as follows:

create-data-set
--aws-account-id 
--data-set-id 
--name 
--physical-table-map 
[--logical-table-map ]
--import-mode 
[--column-groups ]
[--field-folders ]
[--permissions ]
[--row-level-permission-data-set ]
[--column-level-permission-rules ]
[--tags ]
[--cli-input-json ]
[--generate-cli-skeleton ]
[--row-level-permission-tag-configuration //upto 50 tagkeys can be added at this time
    '{
       "Status": "ENABLED",
       "TagRules": 
        [
            {
               "TagKey": "tag_name_1", //upto 128 characters
               "ColumnName": "column_name_1",
               "TagMultiValueDelimiter": ",",
               "MatchAllValue": "*"
            },
            {
               "TagKey": "tag_name_2", //upto 128 characters
               "ColumnName": "column_name_2"
            }
        ]
    }'
]

update-data-set
--aws-account-id <value>
--data-set-id <value>
--name <value>
--physical-table-map <value>
[--logical-table-map <value>]
--import-mode <value>
[--column-groups <value>]
[--field-folders <value>]
[--row-level-permission-data-set <value>]
[--column-level-permission-rules <value>]
[--cli-input-json <value>]
[--generate-cli-skeleton <value>]
[--row-level-permission-tag-configuration //upto 50 tagkeys can be added at this time
    '{
       "Status": "ENABLED",
       "TagRules": 
        [
            {
               "TagKey": "tag_name_1", //upto 128 characters
               "ColumnName": "column_name_1",
               "TagMultiValueDelimiter": ",",
               "MatchAllValue": "*"
            },
            {
               "TagKey": "tag_name_2", //upto 128 characters
               "ColumnName": "column_name_2",
               "MatchAllValue": "*"
            },
           {
               "TagKey": "tag_name_3", //upto 128 characters
               "ColumnName": "column_name_3"
           } 
        ]
    }'
]

In the preceding example code, row-level-permission-tag-configuration is the element that you can use to define tag keys on the columns of a dataset. For each tag, you can define the following optional items:

1. TagMultiValueDelimiter – This option when set on a column enables you to pass more than one value to the tag at runtime, and the values are delimited by the string set for this option. In this sample, a comma is set as a delimiter string.
2. MatchAllValue – This option when set on a column enables you to pass all values of a column at runtime, and the values are represented by the string set for this option. In this sample, an asterisk is set as a match all string.

After we define our tags, we can enable or disable these rules using the Status element of the API. In this case the value is set to ENABLED. To disable the rules, the value is DISABLED. After the tags are enabled, we can pass values to the tags at runtime to secure the data displayed based on who is accessing the dashboard.

Each dataset can have up to 50 tag keys.

We receive the following response for the CreateDataset or UpdateDataset API:

{
"Status": 201,
“Arn”: “string”, //ARN of the dataset
“DataSetId”: “string”, //ID of the dataset
“RequestId”: “string”
}

Enable authors to access data protected by tag keys when authoring analysis

After tags keys are set and enabled on the dataset, it is secured. Authors when using this dataset to author a dashboard don’t see any data. They must be given permissions to see any of the data in the dataset when authoring a dashboard. To give QuickSight authors permission to see data in the dataset, create a permissions file or a rules dataset. For more information, see Creating Dataset Rules for Row-Level Security. The following is an example rules dataset.

In this sample dataset, we have the author’s username listed in the UserName column. The other three columns are the columns from the dataset on which we set tag keys. The values are left empty for these columns for the author added to this table. This enables the author to see all the data in these columns without any restriction when they’re authoring analyses.

Set values to the tag keys at runtime when embedding the dashboard

After the tag keys are set for columns of the datasets, developers set values to the keys at runtime when embedding the dashboard. Developers call the API GenerateDashboardEmbedURLForAnonymousUser to embed the dashboard and pass values to the tag keys in the element SessionTags, as shown in the following example code:

POST /accounts//embed-url/anonymous-user HTTP/1.1
Content-type: application/json
{
    "AwsAccountId": "string",
    "SessionLifetimeInMinutes": integer,
    "Namespace": "string", 
    "SessionTags": 
        [ 
            {
                "Key": "tag_name_1", // Length: [1-128]
                "Value": "value1, value2" // Length: [0-256]
            }
            {
               "Key": "tag_name_2", // Length: [1-128]
               "Value": "*" // Length: [0-256]
            }
            {
               "Key": "tag_name_3", // Length: [1-128]
               "Value": "value3" // Length: [0-256]
            }
        ],
    "AuthorizedResourceArns": 
        [ 
            // Length: [1-25]
            // Dashboard ARNs in the same AWS Account
            "string" 
        ],
        
        "ExperienceConfiguration": 
        {
            "Dashboard": 
            {
                "InitialDashboardId": "string" 
            }
        }
    }
} 

Because this feature secures data for users not provisioned in QuickSight, the API call is for AnonymousUser only and therefore this feature works only with the API GenerateDashboardEmbedURLForAnonymousUser.

The preceding example code has the following components:

• For tag_name_1, you set two values (value1 and value2) using the TagMultiValueDelimiter defined when setting the tag keys (in this case, a comma).
• For tag_name_2, you set one value as an asterisk. This enables this tag key to have all values for that column assigned because we defined asterisk as the MatchAllValue when setting a tag key on the column earlier.
• For tag_name_3, you set one value (value3).

API response definition

The response of the API has the EmbedURL, Status, and RequestID. You can embed this URL in your HTML page. Data in this dashboard is secured based on the values passed to the tag keys when calling the embedding API GenerateDashboardEmbedURLForAnonymousUser:

• EmbedUrl (string) – A single-use URL that you can put into your server-side webpage to embed your dashboard. This URL is valid for 5 minutes. The API operation provides the URL with an auth_code value that enables one (and only one) sign-on to a user session that is valid for up to 10 hours. This URL renders the dashboard with RLS rules applied based on the values set for the RLS tag keys.
• Status (integer) – The HTTP status of the request.
• RequestId (string) – The AWS request ID for this operation.

Fine-grained access control

You can achieve fine-grained access control by using dynamic AWS Identity and Access Management (IAM) policy generation. For more information, see Isolating SaaS Tenants with Dynamically Generated IAM Policies. When using the GenerateEmbedUrlForAnonymousUser API for embedding, you need to mention two resource types in the IAM policy: the namespace ARNs your anonymous users virtually belong to, and the dashboard ARNs that can be used in the AuthorizedResourceArns input parameter value. The sessions generated using this API can access the authorized resources and the ones (dashboards) shared with the namespace.

Because anonymous users are part of a namespace, any dashboards shared with the namespace are accessible to them, regardless of whether they are passed explicitly via the AuthorizedResourceArns parameter.

To allow the caller identity to generate a URL for any user and any dashboard, the Resource block of the policy can be set to *. To allow the caller identity to generate a URL for any anonymous user in a specific namespace (such as Tenant1), the Resource part of the policy can be set to arn:aws:quicksight:us-east-1:<YOUR_AWS_ACCOUNT_ID>:namespace/Tenant1. This is the same for the dashboard ID. For dynamic policy generation, you can also use placeholders for the namespace and users.

The following code is an example IAM policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "QuickSightEmbeddingRole",
      "Effect": "Allow",
      "Action": [
        "quicksight:GenerateEmbedUrlForAnonymousUser"
      ],
      "Resource": [
        "arn:aws:quicksight:us-east-1::namespace/tenant1",
        "arn:aws:quicksight:us-east-1::dashboard/dashboard-id-123"
 
        // You can add specific Namespace IDs (tenant IDs), or namespace prefixes here
        // e.g. "arn:aws:quicksight:us-east-1::namespace/{{tenant-id}}" will allow the role to
        // generate embedding URL for namespace dynamically substituted
        // into the placeholder {{tenant-id}}
 
        // or "arn:aws:quicksight:us-east-1::namespace/MyTenantIdPrefix*" will allow the role to
        // generate embedding URL for namespaces having prefix MyTenantIdPrefix.
        
        
        // You can add specific Dashboard IDs, or ID prefixes here
        // e.g. "arn:aws:quicksight:us-east-1::dashboard/{{dashboard-id}}" will allow the role to
        // generate embedding URL for dashboard dynamically substituted
        // into the placeholder {{dashboard-id}}
 
        // or "arn:aws:quicksight:us-east-1::dashboard/MyDashboardIdPrefix*" will allow the role to
        // generate embedding URL for namespaces having prefix MyDashboardIdPrefix.
      ]
    }
  ]
}

Use case

OkTank is an ISV in the healthcare space. They have a SaaS application that is used by different hospitals across different regions of the country to manage their revenue. OkTank has thousands of healthcare employees accessing their application and has embedded operations related to their business in a QuickSight dashboard in their application. OkTank doesn’t want to manage their users in QuickSight separately, and wants to secure data based on which user from which hospital is accessing their application. OkTank is securing the data on the dashboards at runtime using row-level security using tags.

OkTank has hospitals (North Hospital, South Hospital, and Downtown Hospital) in regions Central, East, South, and West.

In this example, the following users access OkTank’s application and the embedded dashboard. Each user has a certain level of restriction rules that define what data they can access in the dashboards. PowerUser is a super user that can see the data for all hospitals and regions.

None of these users have been provisioned in QuickSight. OkTank manages these users in its own application and therefore knows which region and hospital each user belongs to. When any of these users access the embedded QuickSight dashboard in the application, OkTank must secure the data on the dashboard so that users can only see the data for their region and hospital.

First, OkTank created tag keys on the dataset they’re using to power the dashboard. In their UpdateDataset API call, the RowLevelPermissionTagConfiguration element on the dataset is as follows:

"RowLevelPermissionTagConfiguration": 
        {
            "Status": "ENABLED",
            "TagRules": [
                {
                    "TagKey": "customer_region",
                    "ColumnName": "region",
                    "TagMultiValueDelimiter": ",",
                    "MatchAllValue": "*"
                },
                {
                    "TagKey": "customer_hospital",
                    "ColumnName": "hospital",
                    "TagMultiValueDelimiter": ",",
                    "MatchAllValue": "*"
                }
            ]
        }

Second, at runtime when embedding the dashboard via the GenerateDashboardEmbedURLForAnonymousUser API, they set SessionTags for each user.

SessionTags for NorthUser in the GenerateDashboardEmbedURLForAnonymousUser API call are as follows:

"SessionTags": 
        [ 
            {
                "Key": "customer_hospital",
                "Value": "North Hospital"
            },
            {
               "Key": " customer_region",
               "Value": "Central, East"
            }
        ]

SessionTags for NorthAdmin are as follows:

"SessionTags": 
        [ 
            {
                "Key": " customer_hospital",
                "Value": "North Hospital"
            },
            {
               "Key": " customer_region",
               "Value": "*"
            }
        ]

SessionTags for SouthUser are as follows:

"SessionTags": 
        [ 
            {
                "Key": " customer_hospital",
                "Value": "South Hospital"
            },
            {
               "Key": " customer_region",
               "Value": "South"
            }
        ]

SessionTags for SouthAdmin are as follows:

"SessionTags": 
        [ 
            {
                "Key": " customer_hospital",
                "Value": "South Hospital"
            },
            {
               "Key": " customer_region",
               "Value": "*"
            }
        ]

SessionTags for PowerUser are as follows:

"SessionTags": 
        [ 
            {
                "Key": " customer_hospital",
                "Value": "*"
            },
            {
               "Key": " customer_region",
               "Value": "*"
            }
        ]

The following screenshot shows what SouthUser sees pertaining to South Hospital in the South region.

The following screenshot shows what SouthAdmin sees pertaining to South Hospital in all regions.

The following screenshot shows what PowerUser sees pertaining to all hospitals in all regions.

Based on session tags, OkTank has secured data on the embedded dashboards such that each user only sees specific data based on their access. You can access the dashboard as one of the users (by changing the user in the drop-down menu on the top right) and see how the data changes based on the user selected.

Overall, with row-level security using tags, OkTank is able to provide a compelling analytics experience within their SaaS application, while making sure that each user only sees the appropriate data without having to provision and manage users in QuickSight. QuickSight provides a highly scalable, secure analytics option that you can set up and roll out to production in days, instead of weeks or months previously.

Conclusion

The combination of embedding dashboard for users not provisioned in QuickSight and row-level security using tags enables developers and ISVs to quickly and easily set up sophisticated, customized analytics for their application users—all without any infrastructure setup or management while scaling to millions of users. For more updates from QuickSight embedded analytics, see What’s New in the Amazon QuickSight User Guide.

About the Authors

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.

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.

Kareem Syed-Mohammed is a Product Manager at Amazon QuickSight. He focuses on embedded analytics, APIs, and developer experience. Prior to QuickSight he has been with AWS Marketplace and Amazon retail as a PM. Kareem started his career as a developer and then PM for call center technologies, Local Expert and Ads for Expedia. He worked as a consultant with McKinsey and Company for a short while.

Post Syndicated from Ying Wang original https://aws.amazon.com/blogs/big-data/biops-amazon-quicksight-object-migration-and-version-control/

DevOps is a set of practices that combines software development and IT operations. It aims to shorten the systems development lifecycle and provide continuous delivery with high software quality. Similarly, BIOps (business intelligence and IT operations) can help your Amazon QuickSight admin team automate assets migration and version control. Your team can design the migration and version control strategy in your organization by following the suggested pattern in this post. You can utilize the suggested framework and sample scripts to reduce your daily workload.

In this post, we discuss the following:

• The suggested automation process of QuickSight assets migration across accounts or Regions
• The suggested workflow of dashboard version control in one QuickSight account
• The advanced usage of Amazon QuickSight APIs

For migration across accounts, we provide two options and sample code packages:

• Amazon SageMaker notebooks with migration Python scripts for data scientists or engineers. These Python scripts can do batch migration (migrate all assets of the source account to the target account) and on-demand incremental migration (migrate specific assets across accounts).
• An application to perform migrations with a QuickSight embedded website as an UI. The backend of this application contains an Amazon API Gateway endpoint, several AWS Lambda functions, an Amazon Simple Queue Service (Amazon SQS) queue, and an Amazon Simple Storage Service (Amazon S3) bucket. This application is packed in AWS Cloud Development Kit (AWS CDK) stacks and can be easily deployed into your environment.

Migrate across accounts and Regions automatically

Let’s assume that we have two QuickSight accounts: development and production. Both accounts are configured to connect to valid data sources. The following diagram illustrates our architecture.

The architecture contains the following workflow:

1. The Python scripts (SageMaker notebooks or Lambda functions) call QuickSight APIs (list_datasources) to get the data source list in the development account.
2. The scripts call the QuickSight describe_data_source API to describe the data source. The response of the describe_data_source API is a JSON object. The scripts update the JSON object with production account information, for instance, Amazon Redshift credentials or cluster ID.
3. The scripts create the data source in the production account and share the data source with the BI admin team.
4. The scripts perform the same procedure to the datasets.
5. The scripts create a template of the dashboard or analysis that the BI admin wants to migrate. (A template only can be created from an analysis or an existing template. When we create a template of a dashboard, we have to create the template from the underlying analysis of this dashboard. The version of the published dashboard might be behind the underlying analysis.)
6. The scripts call the create_analysis or create_dashboard API in the production account to create the analysis or the dashboard from the remote template in the development account, and apply the theme.
7. The scripts share the analysis or dashboard to some specific groups or users.
8. The scripts log the success messages and errors messages to Amazon CloudWatch Logs.

For migration across Regions, the BI admin can follow the same procedure to migrate assets from the source Region to the target Region. Instead of changing the account ID in the ARN of assets, change the Region name of the ARN.

We provide sample Python scripts later in this post.

Dashboard version control in one account

Under some conditions, the BI team might want to perform version control of the dashboard development in one account. The following diagram illustrates our architecture.

The workflow includes the following steps:

1. The BI developer creates an analysis and a template of this analysis. Let’s call the analysis and template version 1 assets.
2. The BI developer publishes the analysis as a dashboard, and the QA team runs tests on this dashboard.
3. After the QA test, the BI developer continues to develop the analysis to be version 2.
4. The BI team publishes version 2 of the dashboard.
5. The QA team tests version 2 of dashboard again, and takes the following action based on the result:
   1. If the test is successful, the BI admin can update the template to be version 2.
   2. If the tests detect errors, the BI developer has to edit the analysis to fix the issues. However, some issues in the analysis may be unfixable. The BI admin can roll back the analysis or dashboard to be version 1 with the backup template. QuickSight allows authors to roll back analysis to previous version using an undo button. In case the undo history was reset (with user’s confirmation) due to an event like dataset swap, or authors want to go back to a confirmed V1 starting point, you can use the V1 template in an update-analysis API call to reset the analysis to V1 state.
6. The BI developer works on the version 1 analysis to repeat the development cycle.

This workflow is the best practice we suggest to QuickSight users. You can modify the sample code packages we provide to automate this suggested process.

QuickSight API

For more information about the QuickSight API, see the QuickSight API reference and Boto3 QuickSight documentation.

Option 1: SageMaker notebooks of migration scripts

In this section, we present the first migration option for data scientists and engineers: using SageMaker notebooks with migration scripts.

Solution overview

We provide the sample Python scripts for migrating across accounts in three SageMaker notebooks:

• functions – Provides all the functions, including describe objects, create objects, and so on. The supportive functions are developed to perform the tasks to automate the whole process. For example, update the data source connection information, get the dashboard ID from dashboard name, and write logs.
• batch migration – Provides the sample automation procedure to migrate all the assets from the source account to the target account.
• incremental migration – Provides on-demand incremental migration to migrate specific assets across accounts.

The following diagram illustrates the functions of each notebook.

You can download the notebooks from the GitHub repo.

Prerequisites

For this solution, you should have the following prerequisites:

• Access to the following AWS services:
  • QuickSight
  • SageMaker
  • AWS Identity and Access Management (IAM)
• Two different QuickSight accounts, for instance, development and production
• Basic knowledge of Python
• Basic AWS SDK knowledge

Create resources

Create your resources in the source account by completing the following steps:

1. Download the notebooks from the GitHub repository.
2. Create a notebook instance.
3. Edit the IAM role of this instance to add an inline policy called qs-admin-source:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "sts:AssumeRole",
                "quicksight:*"
            ],
            "Resource": "*"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Deny",
            "Action": [
                "quicksight:DeleteA*",
                "quicksight:DeleteC*",
                "quicksight:DeleteD*",
                "quicksight:DeleteG*",
                "quicksight:DeleteI*",
                "quicksight:DeleteN*",
                "quicksight:DeleteTh*",
                "quicksight:DeleteU*",
                "quicksight:DeleteV*",
                "quicksight:Unsubscribe"
            ],
            "Resource": "*"
        }
    ]
}

4. On the notebook instance page, on the Actions menu, choose Open JupyterLab.
5. Upload the three notebooks into the notebook instance.

Implement the solution

In this section, we walk you through the steps to implement the solution.

AssumeRole

To use AssumeRole, complete the following steps:

1. Create an IAM role in the target (production) account that can be used by the source (development) account.
2. On the IAM console, choose Roles in the navigation pane.
3. Choose Create role.
4. Choose the Another AWS account role type.
5. For Account ID, enter the source (development) account ID.
6. Create an IAM policy called qs-admin-target:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": ["quicksight:*",
		      "sts:AssumeRole"],
            "Resource": "*"
        },
       {
            "Effect": "Deny",
            "Action": "quicksight:Unsubscribe",
            "Resource": "*"
        }
    ]
}

7. Grant the IAM role the qs-admin-target IAM policy.
   
8. Provide the qs-admin-source and qs-admin-target role name in the Assume Role cells of the notebooks.

Static profile

To use a static profile, complete the following steps:

1. Create the IAM user qs-admin-source with policy qs-admin-source in the source account.
2. Create the IAM user qs-admin-target with policy qs-admin-target in the target account.
3. Get the aws_access_key_id and secret_access_key of these two IAM users.
4. In the terminal of the SageMaker notebook, go to the directory /home/ec2-user/.aws.
5. Edit the config and credential file to add a profile named source with the aws_access_key_id and secret_access_key of qs-admin-source.
6. Edit the config and credential file to add a profile named target with the aws_access_key_id and secret_access_key of qs-admin-target.
7. Provide the source and target profile name in the Static Profile cell of the notebook.
   

The tutorials of these notebooks are provided as comments inside the notebooks. You can run it cell by cell. If you want to schedule the notebooks to run automatically, you can schedule the Jupyter notebooks on SageMaker ephemeral instances.

In this solution, we assume that the name of dashboard and dataset are unique in the target (production) account. If you have multiple dashboards or datasets with the same name, you will encounter an error during the migration. Every dashboard has its own business purpose, so we shouldn’t create multiple dashboards with the same name in the production environment to confuse the dashboard viewers.

Option 2: Dashboard as UI to enter the migration workflow

In this section, we present the second migration option with the use of a QuickSight embedded website as an UI.

Solution overview

The following diagram illustrates our solution architecture.

The following diagram illustrates the resources deployed in the central account to facilitate the migration process.

The resources include the following:

• Dashboard as UI – The QuickSight dashboard is based on a ticketing backend, QuickSight assets information, and migration status data. You can use the bottom banner of the dashboard to trigger a migration of resources. Choosing Submit sends the migration request and required parameters (asset name, source environment, and target environment) to API Gateway. The dashboard also displays the migration results, which are stored in an S3 bucket.
• S3 bucket – An S3 bucket hosts a static website to present you with a simple embedded dashboard that shows all active dashboards, analyses, datasets, data sources, and migration status.
• API Gateway – API Gateway provides endpoints for embedding a QuickSight dashboard and accepting POST requests to perform migrations:
  • quicksight-embed – Embeds the migration status QuickSight dashboard. The API endpoint invokes the Lambda backend to generate a short-lived QuickSight embed URL, and presents the dashboard in an iFrame.
  • quicksight-migration-sqs – Presents a footer form that allows the user to submit migration details with POST requests, which invoke the QuickSight migration Lambda function.
• SQS queue – We use an SQS queue between the QuickSight migration API endpoint and the backend Lambda function to perform the migration. Messages are deleted after a migration is complete.
• Lambda functions – We use three different functions:
  • QuickSight migration – This function is invoked by the SQS queue, and it performs the necessary migration tasks depending on the parameters it receives. This function can perform both batch and incremental migration of QuickSight resources by querying the QuickSight service API, AWS Systems Manager Parameter Store, and AWS Secrets Manager.
  • QuickSight embed URL – When invoked, this function fetches an embed URL of a given dashboard and returns an HTTP payload to the caller.
  • QuickSight status – This function periodically queries the QuickSight API for details about dashboards, datasets, data sources, analyses, and themes, and uploads the results to Amazon S3. This S3 bucket is then used as a data source for a QuickSight dashboard to display a centralized view of all relevant resources.

Prerequisites

For this walkthrough, you should have the following prerequisites:

• Access to the following AWS services:
  • API Gateway
  • Amazon Athena
  • Lambda
  • QuickSight
  • Amazon SQS
  • Amazon S3
  • Amazon CloudFront
  • Parameter Store
  • Secrets Manager
• Two different QuickSight accounts, such as development and production
• Basic knowledge of Python
• Basic AWS SDK knowledge
• Git and npm installed
• The AWS CDK installed (see AWS CDK Intro Workshop: Python Workshop)

Create resources

Create your resources by cloning the following AWS CDK stack from the GitHub repo:

git clone https://github.com/aws-samples/amazon-quicksight-sdk-proserve.git ~/amazon-quicksight-sdk-proserve

Implement the solution

The following diagram illustrates the services deployed to our central and target accounts.

Deploy to the central account

We use the following stacks to deploy resources to the central account:

• QuicksightStatusStack – Deploys the Lambda functions and related resources to populate the S3 bucket with active QuickSight dashboard details
• QuicksightMigrationStack – Deploys the Lambda function, SQS queue, S3 bucket, and the API Gateway endpoint for initiating migration of QuickSight resources
• QuicksightEmbedStack – Deploys the API Gateway endpoint, CloudFront distribution, and Lambda functions to process the embed URL requests

The migration scripts require a QuickSight user to be created with the name quicksight-migration-user. This user is given permissions to the migrated resources in the destination. However, another QuickSight user or group can be used in place of quicksight-migration-user by replacing the following:

• The parameter in ~/amazon-quicksight-sdk-proserve/Migration-scripts/cdk/lambda/quicksight_migration/quicksight_migration/lambda_function.py (line 66)
• The QUICKSIGHT_USER_ARN variable in ~/amazon-quicksight-sdk-proserve/Migration-scripts/cdk/cdk/quicksight_embed_stack.py (line 81)
  

Creating VPC connections in QuickSight allows QuickSight to access your private data resources and enhances your security. Create this connection in the central account with the VPC connection name set to the VPC ID.

Set up your environment

Set up your environment with the following code:

cd ~/amazon-quicksight-sdk-proserve/Migration-scripts/cdk/
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

Deploy QuickSight status and migration stacks

Deploy the QuickSight status and migration stacks with the following code:

export CDK_DEPLOY_ACCOUNT=CENTRAL_ACCOUNT_ID
export CDK_DEPLOY_REGION=CENTRAL_REGION
cdk bootstrap aws://CENTRAL_ACCOUNT_ID/CENTRAL_REGION
cdk deploy quicksight-status-stack quicksight-migration-stack

Note down the API Gateway endpoint from the output for a future step.

Create a dashboard

After the AWS CDK is deployed, run the Lambda function quicksight_status manually and then two files, group_membership.csv and object_access.csv, are created in the S3 bucket quicksight-dash-CENTRAL_ACCOUNT_ID. By default, this Lambda function is invoked hourly.

In the source account, you can run the following SQL query to create two Athena tables (group_membership and object_access):

CREATE EXTERNAL TABLE `group_membership`(
`account_id` string,   
`namespace` string,   
`group` string, 
`user` string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://quicksight-dash-Source_ACCOUNT_ID/monitoring/quicksight/group_membership/'
TBLPROPERTIES (
'areColumnsQuoted'='false', 
'classification'='csv', 
'columnsOrdered'='true', 
'compressionType'='none', 
'delimiter'=',',
'typeOfData'='file')

CREATE EXTERNAL TABLE `object_access`(
`account_id` string,   
`aws_region` string,   
`object_type` string, 
`object_name` string,
`object_id` string,
`principal_type` string,
`principal_name` string,
`namespace` string,
`permissions` string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://quicksight-dash-Source_ACCOUNT_ID/monitoring/quicksight/object_access/'
TBLPROPERTIES (
'areColumnsQuoted'='false', 
'classification'='csv', 
'columnsOrdered'='true', 
'compressionType'='none', 
  'delimiter'=',',
  'typeOfData'='file')

You can create two SPICE datasets in QuickSight with the two new Athena tables, and then create a dashboard based on these two datasets. For more information, see Using administrative dashboards for a centralized view of Amazon QuickSight objects.

Deploy the QuickSight embed stack

Update DASHBOARD_ID in cdk/quicksight_embed_stack.py (line 80) with the dashboard ID that you just created with the two joined Athena tables.

Update basic authentication credentials in lambda/embed_auth/index.js (lines 9–10) with your preferred username and password.

Deploy the QuickSight embed stack with the following code:

cdk deploy quicksight-embed-stack

In the html/index.html file, update the following values to the output values from the QuicksightMigrationStack and QuicksightEmbedStack deployment steps, then upload it to the S3 bucket (quicksight-embed-CENTRAL_ACCOUNT_ID) created by this stack:

• quicksight-embed-stack.EmbedAPIGatewayURL output value (line 85) – apiGatewayUrl: '<quicksight-embed-stack.EmbedAPIGatewayURL>'
• quicksight-migration-stack.MigrationAPIGatewayURL output value (line 38) – const apiGatewayUrl = '<quicksight-migration-stack.MigrationAPIGatewayURL>';

The index.html file should be placed in the root of the S3 bucket with no prefix.

In the event index.html was updated after CloudFront was deployed, and is displaying the wrong content, the CloudFront cache may need to be invalidated. The URI /index.html should be invalidated in the central account CloudFront distribution.

The URL for the CloudFront distribution we created is outputted when the QuicksightEmbedStack stack is deployed. In QuickSight, add the output value for quicksight-embed-stack.EmbedCloudFrontURL to allow dashboard embedding, and select Include subdomains.

Deploy to the target account

We use the following stacks to deploy resources to the target account:

• InfraTargetAccountStack – Deploys an IAM role that can be assumed by the migration Lambda role. This stack should also be deployed to any target accounts that contain QuickSight resources.
• OptionalInfraTargetAccountStack – Deploys Amazon VPC, Amazon Redshift cluster, and Amazon Aurora cluster. This stack is optional and can be ignored if you have existing infrastructure for this proof of concept.

Deployment of target resources to the target account can either be done from the central account Amazon Elastic Compute Cloud (Amazon EC2) instance with the appropriate cross-account permissions or from an EC2 instance provisioned within the target account.

For this post, Amazon Redshift and Amazon Relational Database Service (Amazon RDS) clusters are required to perform migrations. Amazon Redshift and Amazon RDS aren’t necessary for migrating QuickSight resources that depend on Amazon S3 or Athena, for example. The stack optional-infra-stack deploys both Amazon Redshift and Amazon RDS clusters in the target account. Although deploying this stack isn’t necessary if you already have these resources provisioned in your target account, it does set up the environment correctly for the example migrations. To deploy, use the following command:

cdk deploy optional-infra-target-account-stack

Deploy the target account stack

Update line 16 in cdk/infra_target_account_stack.py:

self.central_account_id = "123456789123" with the central account ID.

If OptionalInfraTargetAccountStack was deployed, update the /infra/config AWS Systems Manager parameter found in cdk/infra_target_account_stack.py (lines 67–77) file with the values of the newly created Amazon Redshift or Amazon RDS clusters. All values are provided as AWS CloudFormation outputs.

However, if you already have clusters deployed in your environment, update the /infra/config Systems Manager parameter found in the cdk/infra_target_account_stack.py (lines 67–77) file with the values of your existing Amazon Redshift or Amazon RDS clusters. Set redshiftPassword and rdsPassword to the name of the secret found in Secrets Manager for these resources. These secrets for Amazon Redshift and Amazon RDS should include username and password values, as shown in the following screenshot.

The following are example values for the /infra/config parameter:

def to_dict(self):
    config={}
    config['vpcId'] = 'vpc-0b13eb0989c8de79f'
    config['redshiftUsername'] = 'admin'
    config['redshiftPassword'] = 'redshift-secret-name-here'
    config['redshiftClusterId'] = 'test-redshift-cluster'
    config['redshiftHost'] = 'test-redshift-cluster-1fsoudi1bunb6.c6mqlnbwke57.us-east-1.redshift.amazonaws.com'
    config['redshiftDB'] = 'test'
    config['rdsUsername'] = 'admin'
    config['rdsPassword'] = 'rds-secrent-name-here'
    config['rdsClusterId'] = 'test-rds-cluster-socet72ltstg'
    config['namespace'] = 'default'
    config['version'] = '1'

After the values have been updated in cdk/infra_target_account_stack.py file run the following shell commands:

export CDK_DEPLOY_ACCOUNT=TARGET_ACCOUNT_ID
export CDK_DEPLOY_REGION=TARGET_REGION
cdk bootstrap aws://TARGET_ACCOUNT_ID/TARGET_REGION
cdk deploy infra-target-account-stack

Creating an Amazon VPC connection in QuickSight allows QuickSight to access your private data resources, and enhances your security. Create this connection in the target account with the VPC connection name set to the VPC ID. This is required because the data sources created in the OptionalInfraTargetAccountStack stack are within a VPC.

Trigger a migration

The URL for the CloudFront distribution we created is outputted when the QuicksightEmbedStack stack is deployed. Navigate in your browser to the output value for quicksight-embed-stack.EmbedCloudFrontURL; the CloudFront distribution prompts you for basic authentication credentials, then redirects to the embedded QuickSight dashboard.

You can trigger a migration via the migration tool at the bottom of the dashboard.

As of this writing, this tool supports two types of migrations:

• Batch – Attempts to migrate all resources, including themes, dashboards, analyses, datasets, and data sources
• Incremental – Allows you to select a migration resource of the dashboard, analysis, or theme, and provide names of these items in the Migration Items field.

Choosing Submit sends a message to the SQS queue, which triggers the migration Lambda function on the backend. The embedded dashboard should reflect the status of the dashboard migration after it performs its periodic refresh.

Clean up

Finally, to clean up the resources created in this post, perform the following cleanup steps, depending on the solution option you used.

Option 1 cleanup

If you implemented the Option 1 solution, complete the following steps:

1. Delete the SageMaker notebooks running the migration scripts.
2. Delete the IAM role attached to the SageMaker notebooks.

Option 2 cleanup

If you implemented the Option 2 solution, complete the following steps:

1. Destroy the resources created by the AWS CDK:

cdk destroy quicksight-status-stack quicksight-migration-stack quicksight-embed-stack

2. Destroy the resources created by the AWS CDK in the target accounts:

cdk destroy infra-target-account-stack optional-infra-target-account-stack

3. Manually delete S3 buckets created in both central and target accounts.

Things to consider

This solution will help you with QuickSight object migration and version control. Here are few limitations to consider:

1. If there are deleted datasets used in the QuickSight analysis or dashboard, then consider deleting such datasets.
2. If there are duplicate object names then consider naming them different.
3. If there are file based data sources then consider converting them to S3 based data sources.

Pricing

For the pricing details of the services used in this post, see the following:

• Amazon S3 pricing
• AWS Lambda pricing
• Amazon SQS pricing
• Amazon API Gateway pricing
• AWS Secrets Manager pricing

Conclusion

Object migration and version control in a programmable method is always highly demanded by the BI community. This post provides the best practices and practical code package to address QuickSight object migration and version control. This solution can readily fit into a ticketing system or CI/CD pipelines.

If you have any feedback or questions, please leave them in the comments section. You can also start a new thread on the Amazon QuickSight forum.

About the Authors

Ying Wang is a Senior Data Visualization Architect with the Data & Analytics Global Specialty Practice in AWS Professional Services.

Vamsi Bhadriraju is a Data Architect at AWS. He works closely with enterprise customers to build data lakes and analytical applications on the AWS Cloud.

Samruth Reddy is a DevOps Consultant in the AWS ProServe Global Delivery team working on automation tooling, security and infrastructure implementations, and promoting DevOps methodologies and practices to his customers.

Post Syndicated from Anand Sakhare original https://aws.amazon.com/blogs/big-data/automate-amazon-quicksight-user-and-group-management-using-ldap-data-for-row-level-security/

In any business intelligence system, securing and restricting access to the data is important. For example, you might want a particular dashboard to only be viewed by the users with whom the dashboard has been shared, yet customize the data displayed on that dashboard per user by implementing row-level security. With row-level security, you can grant and restrict access to only specific rows, all rows, or no rows of any dataset.

Organizations typically want to display different data to users within different business units. To do this, we need to traverse the organizational hierarchy to find the ultimate business owner for each employee. This post goes into the details of how we can use Lightweight Directory Access Protocol (LDAP) data to find what business unit each employee belongs to, and how we can apply data restriction using row-level security at an Amazon QuickSight group level rather than at an individual user level.

Additionally, this post explains how to automate user and group management within QuickSight to add or remove a QuickSight group or a user to a group.

Architectural overview

The following diagram illustrates the solution architecture.

The solution has two parts:

• We first move data from an LDAP data store to Amazon Redshift or any other QuickSight supported data source, flatten it and integrate it with existing analytical data, and create a joined result set matching the organizational hierarchy
• Then we automate the user and group management, which we apply to the permission file in QuickSight to dictate access to a particular dataset

We demonstrate this with a fictitious IT support ticketing system. This system tracks who is creating a particular support ticket and who is working on that ticket. We use QuickSight to visualize trends like number of tickets worked on by a particular assignment group, number of open vs. closed tickets, and so on for a particular business unit. We use row-level security to demonstrate different levels of access and how the same dataset changes for a super user like a president of a company overseeing multiple business units vs. a manager of a particular business or a direct report working on the actual tickets.

Solution overview

We extract and dump the LDAP dataset into Amazon Simple Storage Service (Amazon S3). This dataset can be a direct database dump. The LDAP dataset is usually not flattened and can’t be directly queried to find the reporting hierarchy of a particular employee. To flatten the directory structure, we load the data in an Amazon Relational Database Service (Amazon RDS) instance that supports recursive SQLs. Optionally, we can also flatten the LDAP data using AWS Glue with Apache Spark. The flattened LDAP data is written back into Amazon S3 and then loaded into Amazon Redshift or a QuickSight supported data store. This portion of loading to Amazon S3 and then to Amazon Redshift is optional if the QuickSight data source you’re using supports running recursive SQLs.

The flattened LDAP structure table should now consist of a user column, their manager, and who they report to, up to a vice president in the organization hierarchy. This is then joined with the ticketing system dataset and assignment group or business unit ownership table, which tells which manager or vice president is responsible for a particular business unit. The final joined and aggregated table provides ticketing metadata, ownership, and the business unit hierarchy for each of the tickets. This can be directly loaded into SPICE either as a query or a dataset. You can apply a permission file to this new dataset that dictates which group has access to which datasets.

The second component of the solution goes into the details of how to automate user management, which is done by uploading separate CSV files to Amazon S3 for adding new groups, adding new users, and removing users. When a particular object is uploaded, an event triggers an AWS Lambda function, which makes API calls for the QuickSight client to add or remove the users or add a group.

When you combine these two components, you get an automated way of incorporating your LDAP structure in QuickSight and managing the users in a similar fashion to how you manage users in your corporate active directory.

Prerequisites

To follow along with this post, clone the GitHub repo, deploy the infrastructure, and download the scripts.

Prepare the data

To get started, create a new S3 bucket or use an existing bucket to upload the provided scripts. On the Amazon S3 console, the prefix structure of the bucket should look like the following screenshot.

Use the redshift-cluster.yml AWS CloudFormation template file under the folder infrastructure deploy to deploy the Amazon Redshift cluster and an AWS Identity and Access Management (IAM) role that is attached to Amazon Redshift to access Amazon S3.

The CloudFormation template requires several parameters, including the following:

• InboundTraffic – We recommend restricting access to IP addresses within your network or to the IP address of the client. Entering 0.0.0.0/0 allows incoming traffic from all IP addresses.
• S3BucketForRedshiftIAMRole – The name of the S3 bucket where the scripts are uploaded. This is used to create an IAM role that is assumed by the cluster.

Alternatively, you can create the cluster via the Amazon Redshift console and attach the IAM role to access the S3 bucket to the cluster.

To perform the next steps, you can use the Amazon Redshift query editor. The scripts that need to be run are under the scripts folder. In the provided queries, replace bucket-name with the bucket where the scripts and data are uploaded, replace the iam_role ARN (arn:aws:iam::111122223333:role/quicksight-row-level-demo-redshift-role) with your actual account number, and replace the Region us-east-1 with the Region where the S3 bucket is created. When the queries are complete, several tables are now in the cluster (see the following screenshot).

Ticketing data

For this post, we assume that the IT support ticketing data is already loaded into Amazon Redshift. The important columns of interest in this dataset are ticket_no, which can be a primary key for this table, and assigned_user_email_id, which we use as a foreign key to join with the flattened LDAP dataset. We refer to this table as ticket_data. You can query this table in your Amazon Redshift cluster, as shown in the following screenshot.

In this post, we take the organizational structure where different business units are managed by different vice presidents.

We want the ability to categorize our ticketing data based on user assignment into business units. Our ticket data has assigned users for each ticket. We use assigned_user_email_id to identify owners for each of these tickets. Based on the LDAP dataset, we identify the vice president in the hierarchy for each assigned user. We also have an assignment group and assignment_group_manager_email mapping in a separate table, which gives us relationships between business units (or assignment group) and the respective vice presidents.

LDAP data

Our primary key for this data in this example dataset is user_email_id, and reports_to is the foreign key that refers to user_email_id in the same dataset. The following screenshot shows the employees table in an RDS instance. This table can also exist in any database that supports recursive queries.

We’re interested in taking a particular user, finding their manager, and moving up the hierarchy until we find the vice president of a particular business unit. For example, in the preceding LDAP table, Todd V reports to Jimmy P, Jimmy P reports to Steve W, and Steve W reports to President Thomas J. This hierarchy is difficult to query in the current table structure because Amazon Redshift doesn’t support recursive queries. As an alternative, we decided to load the data in an RDS instance that supports recursive queries first and then load it into Amazon Redshift.

After we load the data into the RDS instance, we can use a SQL query like the following to find the hierarchy order we discussed:

SELECT USER_EMAIL_ID, NAME, DESIGNATION, REPORTS_TO, 
SYS_CONNECT_BY_PATH(USER_EMAIL_ID, '/') "Path", 
SUBSTR(SYS_CONNECT_BY_PATH(USER_EMAIL_ID, '/'), 
Instr(SYS_CONNECT_BY_PATH(USER_EMAIL_ID, '/'), '/', 2)-2) vp_roll_up
FROM employees 
WHERE designation != 'Vice President'
START WITH designation = 'Vice President' 
CONNECT BY PRIOR USER_EMAIL_ID = REPORTS_TO
ORDER BY REPORTS_TO;

The following screenshot shows the flattened sample LDAP dataset for the preceding example. Let’s call this LDAP_flattened_data. You can query this table in your Amazon Redshift cluster.

Note that for this post, the dataset is already flattened.

Assignment groups and manager mapping

The next step is to identify the business unit or assignment group each vice president belongs to.

For simplicity, we assume the following mapping between assignment groups (business units) and their respective vice presidents (or assignment group manager). This can be simply stored in a table. Let’s call the table assignment_group_manager_mapping. After we join LDAP_flattened_data with the assignment_group_manager_mapping table, we can identify which assignment group each user belongs to. Carrying forward the previous example, Todd V rolls up to vice president Steve W and therefore belongs to the IT Repairs assignment group. You can query this table in your Amazon Redshift cluster.

Now that we have the three base tables ready, all we need to do is join them to form a flattened table that is suitable for visualizations in QuickSight. We store this data into SPICE, which is an in-memory optimized calculation engine designed specifically to support low-latency, ad hoc data visualization. You can create this table by performing a join between ticket_data and LDAP_flattened_data on assigned_user_email_id and USER_EMAIL_ID, which gives us VP_ROLL_UP, which we can then use to join with the assignment_group_manager_mapping table on assignment_group_manager_email. The final dataset created from the sample data is shown in the following table.

For this post, we use the following sample SQL statement, but you can achieve this in multiple ways.

SELECT * 
FROM ticket_data td, LDAP_flattened_data ld, assignment_group_manager_mapping ag
WHERE td.assigned_user_email_id = ld.USER_EMAIL_ID and
ld.VP_ROLL_UP = ag.assignment_group_manager_email;

You can directly load this table and query into SPICE from Amazon S3, Amazon Athena, Amazon Redshift, or any other supported data sources. For more information, see Working with Datasets.

Add row-level security

To add row-level security, we follow the same steps as in the documentation Using Row-Level Security (RLS) to Restrict Access to a Dataset. We create a dataset in QuickSight by querying the table in Amazon Redshift. Alternatively, you can upload the permissions file from the GitHub repository. You can query this table in your Amazon Redshift cluster with select * from row_level_security. The following screenshot shows our results.

With the preceding examples, the users that are added to a particular assignment group get the same permissions as the group, and the users that belong to the superuser group have elevated access. In QuickSight, we perform the following steps.

1. Create a new dataset with the row_level_security table.
   

Both datasets are visible in QuickSight.

2. In the details for the ticketing-data-final dataset, and choose Row-level security.
   
3. Select the row_level_security dataset that we created.
   
4. Select Apply dataset and confirm.
   

When the row-level security has been successfully applied, the lock icon is visible next to the ticketing dataset.

Automate user management

To tie it all together, we automate user and group management. When a particular user is added to a group, they get access as defined by the preceding permissions file. For this setup, we use the following Amazon S3 prefix structure, essentially a separate path for adding groups, adding users, and removing users.

We upload CSV files under each of these Amazon S3 paths.

To add or remove users from a group, refer to the user.csv file under the quicksight-user-group-management folder and copy it to the add-user-to-group folder.

To add groups, refer to the groups.csv file under the quicksight-user-group-management folder and copy it to the add-group folder.

When a file is added in any of these S3 folder paths, a Lambda function is triggered, which makes a Boto3 call to add groups or add or remove users. You can use the quicksight-row-level-lambda.yml CloudFormation template under the Infrastructure deploy folder of the GitHub repo to create the Lambda function and execution role.

Now we create an Amazon S3 event trigger for this bucket to trigger the Lambda function. On the Properties tab of the bucket, choose Events.

Add a notification and provide the ARN of the Lambda function that you created.

Visualize the data

To demonstrate the solution, we show how Steve W. can view all the tickets under IT Repairs, and Todd V can view only the tickets assigned to him.

We create a sample analysis using the dataset created earlier. Then we can publish it as a dashboard and share it with the target user group.

For this post, we set up the following visualizations:

• Tickets assigned this month – The count of the tickets opened in the current month
  • Visual type – KPI
  • Value – ticket_no
  • Filters – created_date should be current month
• Ticket Details – The details of the tickets, such as status and assigned owner
  • Visual type – Table
  • Groupby columns – ticket_no, created_by, problem_issue, assigned_user
  • Filters – created_date should be current month
• Tickets by Status – The status of all the tickets by assignment group
  • Visual type – Pie chart
  • Group/Color – Status
  • Value – ticket_no
  • Filters – created_date should be current month
• Tickets Assignment by Groups – The status of all the tickets by assignment group
  • Visual type – Bar chart
  • X-axis – assignment_group
  • Y-axis – count of ticket_no
  • Filters – created_date should be current month
• Tickets Resolved by day – The number of tickets closed each day
  • Visual type – Line chart
  • X-axis – resolved_date
  • Y-axis – count of ticket_no
  • Filters – created_date should be current month and status is closed

When user Todd V (the QuickSight user name is the same as the users email ID) logs in, he sees a dashboard like the following screenshot.

When Steve W logs in, his dashboard shows more information.

When a superuser logs in, they see the following dashboard.

Conclusion

We demonstrated one of the many ways we can use LDAP data for organizational hierarchy-based visualizations in QuickSight. In this post, we talked about how we can find organizational ownership for ticket data. You can further generalize this solution to fit any data within an organization that needs business unit-based grouping. Another use case for this can be for visualizing security threats or sales data across different business units.

This post covers only one level of organizational hierarchy, but in many enterprises, the organizational structure can be much more complicated. You can use a similar approach to deal with these nested organizational hierarchies, where we can report on different levels of business units. If you are interested in implementing row-level security using organizational LDAP hierarchical structure refer to Implement row-level security using a complete LDAP hierarchical organization structure in Amazon QuickSight.

About the Author

Anand Sakhare is a Big Data Architect with AWS. He helps customers build big data, analytics, and machine learning capabilities using a mix of technologies. He is passionate about innovation and solving complex problems.

Rohan Jamadagni is a Sr. Data Architect, working with AWS for the past 5 years. He works closely with customers to implement data and analytics solutions on AWS. He enjoys understanding the meaning behind data and helping customers visualize their data to provide meaningful insights.

Umair Nawaz is a DevOps Engineer at Amazon Web Services in New York City. He works on building secure architectures and advises enterprises on agile software delivery. He is motivated to solve problems strategically by utilizing modern technologies.

Post Syndicated from Anand Sakhare original https://aws.amazon.com/blogs/big-data/implement-row-level-security-using-a-complete-ldap-hierarchical-organization-structure-in-amazon-quicksight/

In a world where data security is a crucial concern, it’s very important to secure data even within an organization. Amazon QuickSight provides a sophisticated way of implementing data security by applying row-level security so you can restrict data access for visualizations.

An entire organization may need access to the same dashboard, but may also want to restrict access to the data within the dashboard per the organization’s hierarchical structure. For instance, vice presidents need visibility into all data within their organization, team managers need to see the data related to all their direct reports, and an individual contributor just needs to see the their own data. Creating and maintaining these data security rules can be laborious if managed manually.

In this post, we go into the details of how to extract the organizational hierarchical structure from Lightweight Directory Access Protocol (LDAP) data, flatten it, and create a row-level security permissions file to mimic the same level of hierarchical access controls to a QuickSight dataset. We show this with mock datasets of the LDAP data and ticketing data, and use that data to implement user-level access on QuickSight visualizations and datasets.

Overview of solution

In this post, we demonstrate processing LDAP data and implementing row-level security to control user-level access according to organizational hierarchies. We demonstrate with a sample dataset how to dynamically change the data behind visualizations. We also talk about automatically creating the permissions file required for implementing security on QuickSight visualizations using the LDAP data. Additionally, we create a sample dashboard and apply the generated permissions file to manage data access for the users.

LDAP hierarchical data (employee dataset)

We use sample LDAP data, which is a mock organizational hierarchical structure. Sample code to generate the hierarchical data is available on the GitHub repo.

The basic idea is we have a hierarchical dataset where an employee-manager or employee-supervisor relationship exists. In our sample dataset, we have employee_id and manager_id columns, which represent the employee-manager relationship. The following screenshot is the hierarchical representation of the first few employees in the dataset and the first few rows in the table. The data shows “Mies, Crin” (employee_id 0) is the root user. In our mock data, employee IDs range from 0–249, with 10 levels of hierarchies.

The following screenshot shows the hierarchical structure of the sample data.

Ticketing dataset

Our randomly generated sample ticketing dataset has about 250 tickets (see the following screenshot). Each of these tickets is assigned to an employee. The column assigned_to_emp_id represents the employee that the ticket is assigned to. The code to generate a random sample of the data is available on the GitHub repo.

We can replace emp_id with any unique identifier such as UID, RACF ID, email ID, and so on.

Sample organizational structure

The row-level security that we talk about in the next sections ties back to these two datasets. For example, tickets assigned to any employee ID present in the column assigned_to_emp_id should only be visible to the employees that are higher in that employee’s hierarchy. Tickets assigned to employee ID 18 can only be viewed by employee IDs 18, 7, 5, 1, and 0, because employee ID 18 directly or indirectly reports to them. The following screenshot shows an example of the hierarchy.

Preprocess ticketing data by flattening hierarchical relationships

We first need to flatten the LDAP data to get the employee IDs in the hierarchy in a row. This flattened data needs to be refreshed to account for any organizational changes, such as new employees onboarding. In this example, we use the SYS_CONNECT_BY_PATH function on an Amazon Relational Database Service (Amazon RDS) database to achieve that. We can achieve the same result programmatically or by using common table expressions (CTEs) in Amazon Redshift. The goal is to create 10 columns containing the complete path from that employee to the highest-level manager. Not every employee has a value in every column, assuming the employee isn’t at the lowest level of the hierarchy. A given employee ID appears on their own row, as well as rows for employees they’re a direct or indirect manager of. We query the data with the following code:

SELECT EMPLOYEE_ID, MANAGER_ID, "name", DOB, DEPT, SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/') "Path", 
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 1) "CEO",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 2) "CEO_1",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 3) "CEO_2",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 4) "CEO_3",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 5) "CEO_4",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 6) "CEO_5",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 7) "CEO_6",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 8) "CEO_7",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 9) "CEO_8",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 10) "CEO_9",
regexp_substr(SYS_CONNECT_BY_PATH(EMPLOYEE_ID,'/'), '[^\/]+', 1, 11) "CEO_10"
FROM employees
START WITH MANAGER_ID is NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER BY EMPLOYEE_ID;

The following screenshot shows the query output.

The following screenshot shows an example of the hierarchy for employee ID 147.

For employee ID 147, we can see the employees in the hierarchy are organized in columns with their levels. Employee ID 147 reports to 142, 142 reports to 128, and so on. Similarly, for employee ID 142, we can see that the employees above 142 are present in their respective columns.

Join the ticketing data with the flattened LDAP data

To get to the final dataset that we need for visualizations, we need to join the ticketing data with the LDAP data with flattened hierarchies. For our demo, we created two tables, Tickets and Employees, and copied the data we showed earlier to these tables using an Amazon Redshift copy command from Amazon Simple Storage Service (Amazon S3). The following is a sample output of the join query between these tables. This dataset is what we import into SPICE in QuickSight. SPICE is QuickSight’s Super-fast, Parallel, In-memory Calculation Engine, and it’s engineered to rapidly perform advanced calculations and serve data.

select ticket_num,assigned_to_emp_id,name,category,manager_id,ceo,ceo_1,ceo_2,ceo_3,ceo_4,ceo_5,ceo_6,ceo_7,ceo_8,ceo_9,ceo_10  
from blogpostdb.Tickets a JOIN blogpostdb.Employees b
ON a.assigned_to_emp_id = b.EMPLOYEE_ID;

The following screenshot shows our flattened data.

Create the permissions file

You can use the following code snippet to create the permissions file needed to apply row-level security on your dataset in QuickSight:

import csv
def create_permissions_file(list_of_emp_ids, number_of_levels):
    output_header=["ceo_" + str(i) if i!=0 else 'ceo' for i in range(number_of_levels)]
    output_header.insert(0,'UserName')
    f=open("./sample_permissions_file.csv", 'w')
    writer = csv.writer(f)
    writer.writerow(output_header)
    for i in list_of_emp_ids:
        for j in range(1,len(output_header)):
            l = [None] * (len(output_header))
            l[j]=i
            l[0]=i
            print(l)
            writer.writerow(l)
    f.close()

The input to this function is a list of your employee IDs. These employee IDs appear as owners in the ticketing data as well. There are multiple ways to get the data. If the ticketing data ownership is related to any other user-specific information such as email ID or any unique identifier, then a list of that information is the input to this function. The second input is the number of levels your organization has (integer value). The goal is to create a CSV file to use as a permissions file for your dataset in QuickSight.

Assume there are 10 hierarchical levels in your organization. The output permissions file looks something like the following screenshot.

Create QuickSight analyses

We now apply the permission file to the QuickSight dataset. For instructions, see Using Row-Level Security (RLS) to Restrict Access to a Dataset.

Now we create a sample visualization to show the specific tickets owned by an employee or their reportees.

After we import the permissions file and apply it to the final dataset (created by joining the ticketing data with the flattened LDAP data) in SPICE, we’ve created the following visualization. The goal is to verify that when different users log in and see the visualization, they see the same visualization with different data, in this case the only tickets that concern them.

The following screenshot shows the visualization without any row-level security.

For our hierarchy, we’ve created QuickSight users with usernames that are the same as their employee IDs (the employee with ID 142 has the QuickSight username 142; this can easily be replaced by any unique identifiers your organization uses). We log in with employee IDs 232, 147, 61, 84, and 28, and verify that they only see the tickets that concern them. In the visualization “You are viewing tickets concerning these employees,” we can see whose tickets the logged-in user is authorized to see. Because the mocked data only consists of around 250 tickets randomly assigned to 250 employees, some visualizations may show no data.

The following screenshot shows the example hierarchy. Employee ID 232 is a leaf node (nobody reports to them).

Employee 232 is only authorized to view their own tickets, as shown in the following visualization.

Similarly, because employee ID 147 is also a leaf node, they can only view their assigned tickets.

In our example hierarchy, employee IDs 72, 75, 174, 229, and 134 report to employee 61. In our dataset, only four tickets are assigned to those employees. The following screenshot shows the tickets of concern to employee ID 61.

The following screenshot shows the visualizations visible to employee ID 61.

Similarly, when we log in with employee IDs 84 and 28, we can verify that they only see the tickets concerning them.

Publish the dashboard

You can use the share function to publish the analysis to a dashboard and share the data with stakeholders.

Clean up

To avoid incurring future charges, make sure to remove resources you created when you’re done using them.

Conclusion

Data security is an important concern for many organizations. This solution is an easy way to use organizational LDAP data to implement data security with row-level security in QuickSight. With organizational restructuring, hierarchies are bound to change with time. Therefore, the LDAP data can be dumped on a periodic basis and be updated in the respective Amazon Redshift table. This enables users to have better visibility in the data within their organizational hierarchy.

About the Author

Anand Sakhare is a Big Data Architect with AWS. He helps customers build big data, analytics, and machine learning capabilities using a mix of technologies. He is passionate about innovation and solving complex problems.

Rohan Jamadagni is a Sr. Data Architect, working with AWS for the past 5 years. He works closely with customers to implement data and analytics solutions on AWS. He enjoys understanding the meaning behind data and helping customers visualize their data to provide meaningful insights.

Umair Nawaz is a DevOps Engineer at Amazon Web Services in New York City. He works on building secure architectures and advises enterprises on agile software delivery. He is motivated to solve problems strategically by utilizing modern technologies.