Filtering data in AWS Quicksight based on User Groups
Tags: aws, analytics
Published: 2022-08-20 16:11:10

## 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 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1tng1qiv64yfnhvtbrow.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pmfq32zi1fj0rbvc5tmi.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g5vhqpvrythpqx54i4sa.png)

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