Introduction

AWS Quicksight is a complete data visualization and dashboard creation tool, however when integrating with existing applications it sometimes is necessary to filter data based on the User logging in to AWS Quicksight – ensuring that the dashboard does not inadvertently display data that is to be restricted.

This note describes an approach we used to filter data in AWS Quicksight for our internal application. The internal application uses SQL Server as the database and we used that to drive the User access information – however the approach described here can be used for other data repositories as well.

Data extraction, processing and storage

The data required for visualization and dashboard purposes is extracted from respective tables, processed based on the dashboard requirement and stored in de-normalised form in a database catalog separate from the transaction database of the application. For the rest of the article, we will call this database [ANALYTICS].

Each user group will have access to slightly different data and the list of usergroups will be stored in the database table [ANALYTICS].dbo.AnalyticsPermissions which should have a groupname and groupid column.

From the [ANALYTICS] database to Quicksight

Quicksight allows data to be drawn from SQL Server into SPICE, a temporary storage provided by Quicksight for visualization, so that hits to the DB can be limited. The data can be refreshed anytime from the [ANALYTICS] database and SPICE will be updated accordingly.

We scheduled database Jobs in the transaction DB to update the [ANALYTICS] database. And finally the dataset in Quicksight must also be refreshed so that the user always gets the updated data for visualization and analysis.

Creating new groups

In order to create a new user group in AWS quicksight we need to use the AWS console tools (AWS CLI – see: https://aws.amazon.com/cli/). Install this on a client machine with access to the AWS infrastructure and run the following command:

create a new group

aws quicksight create-group --aws-account-id=XXXXXXXX --namespace=default --group-name="MyGroup" --description="MyGroup users" --region="us-east-1"

Replace the account-id with the AWS account id that is to be used to deploy Quicksight and use the AWS region that is appropriate for your usecase.

Creating Users on Quicksight

To create a user login to Quicksight with your administrator account

Click on the Manage Quicksight

Click on Invite Users

Add the user name eg. whoami@changeme.com and click on the + button to add the user.

Using the AWS CLI on a console run the following command:

add a member to a group

aws quicksight create-group-membership --aws-account-id=XXXXXXXXX --namespace=default --group-name=MyGroup --member-name="<whoami@changeme.com>" --region="us-east-1"

To confirm that the user was added run the following command:

describe the group

aws quicksight describe-group --aws-account-id=855244721923 --namespace=default --group-name=GEO --region="us-east-1"

Joining the Quicksight Dataset

To ensure that the dataset used to drive the Quicksight dashboard is filtered by the user group, every dataset must be joined with the database table [ANALYTICS].dbo.AnalyticsPermissions so that the groupname field gets added.

Enable the row level security of the dataset (containing data). Select AnalyticsPermission from the drop down and this establishes a row level security for that dataset with the groupname.

Login with the newly created user and verify that the data being displayed is filtered for the group.