Post Syndicated from Niyati Upadhyay original https://aws.amazon.com/blogs/big-data/schedule-email-reports-and-configure-threshold-based-email-alerts-using-amazon-quicksight/

Amazon QuickSight is a cloud-scale business intelligence (BI) service that you can use to deliver easy-to-understand insights to the people you work with, wherever they are.

You can build dashboards using combinations of data in the cloud, on premises, in other software as a service (SaaS) apps, and in flat files. Although users can always view and interact with dashboards on-demand in their browser or our native mobile apps, sometimes users prefer to receive notifications and report snapshots on a scheduled basis or when a certain value surpasses a user-defined threshold.

In this post, we walk you through the features and process of scheduling email reports and configuring threshold-based alerts.

Overview of solution

In QuickSight Enterprise edition, you can email a report from each dashboard on a scheduled basis or based on a threshold set for KPI and gauge visuals. Scheduled reports include settings for when to send them, the contents to include, and who receives the email.

Scheduled email reports work with row-level security so that each user receives reports containing only data that is relevant to them. Alert reports include threshold value, alert condition, and the receiver’s email. To set up or change the report sent from a dashboard, make sure that you’re an owner or co-owner of the dashboard.

To receive email reports, the users or group members must be part of your QuickSight account. They must have completed the sign-up process to activate their subscription as QuickSight readers, authors, or admins.

In this post, we configure the email settings for a QuickSight dashboard for users and construct a custom email for each user or group based on their data permissions.

The solution includes the following high-level steps:

1. Set up scheduled email alerts for your existing reports.
2. Set up threshold-based email alerts for the existing reports.
3. View alert history.
4. Set up email alerts if the dataset refresh fails.

Prerequisites

For this walkthrough, you should have the following prerequisites:

• An AWS account
• QuickSight Enterprise edition
• Existing QuickSight reports with appropriate data permissions and dynamic defaults that are set on dashboards that will be honored in email reports. Each reader will receive emails and PDFs of dashboards personalized to them. To learn more about dynamic defaults, refer to Creating parameter defaults in Amazon QuickSight. You can follow the instructions in Applying row-level and column-level security on Amazon QuickSight dashboards to develop the reports and define permissions.

Set up scheduled email alerts

To configure scheduled emails for your reports, complete the following steps:

1. On the QuickSight console, choose Dashboard in the navigation pane.
2. Open a dashboard.
3. On the Share menu, choose Email report.

4. For Schedule, choose the frequency for the report. For this post, we choose Repeat once a week.

5. For Send first report on, choose a date and time.
6. For Time zone, choose the time zone.
7. For Report title, enter a custom title for the report.
8. For (Optional) E-mail subject line, leave it blank to use the report title or enter a custom subject line.
9. For (Optional) E-mail body text, leave it blank or enter a custom message to display at the beginning of the email.

10. Select Include PDF attachment to attach a PDF snapshot of the items visible on the first sheet of the dashboard.
11. For Optimize report for, choose a default layout option for new users.
12. Under Recipients, select specific recipients from the list (recommended), or select Send email report to all users with access to dashboard.
13. To send a sample of the report before you save changes, choose Send test report.

This option is displayed next to the user name of the dashboard owner.

14. To view a list of the datasets used by this report, choose View dataset list.

15. Choose Save report or Update report.

A “Report scheduled” message briefly appears to confirm your entries.

16. To immediately send a report, choose Update & send a report now.

The report is sent immediately, even if your schedule’s start date is in the future.

The following screenshot shows the PDF report visible to the user AlejandroRosalez. They have access to data where the state is California or Texas, and the city is Los Angeles or Fort Worth.

The following screenshot shows the report visible to the user SaanviSarkar. They can see data for any city, but only if the state is Texas.

The following screenshot shows the report visible to the user MarthaRivera. Martha can see the data for any city or state.

The following screenshot shows that no data is visible to the workshop user, which isn’t present in the permissions.csv file.

Set up threshold-based email alerts

To create an alert based on a threshold, complete the following steps:

1. On the QuickSight dashboard, choose Dashboards, and navigate to the dashboard that you want.

For more information about viewing dashboards as a dashboard subscriber in QuickSight, see Exploring Dashboards.

2. In the dashboard, select the KPI or gauge visual that you want to create an alert for.
3. On the options menu at upper-right on the visual, choose the Create alert icon.

4. For Alert name, enter a name for the alert.
5. For Alert value, choose a value that you want to set the threshold for.

The values that are available for this option are based on the values the dashboard author sets in the visual. For example, let’s say you have a KPI visual that shows a percent difference between two dates. Given that, you see two alert value options: percent difference and actual.

If the visual has only one value, you can’t change this option. It’s the current value and is displayed here so that you can use it as a reference while you choose a threshold. For example, if you’re setting an alert on actual, this value shows you what the current actual cost is (for example, $5). With this reference value, you can make more informed decisions while setting your threshold.

6. For Condition, choose a condition for the threshold.
   • Is above – The alert triggers if the alert value goes above the threshold.
   • Is below – The alert triggers if the alert value goes below the threshold.
   • Is equal to – The alert triggers if the alert value is equal to the threshold.

7. For Threshold, enter a value to prompt the alert.
8. Choose Save.

A message appears indicating that the alert has been saved. If your data crosses the threshold you set, you get a notification by email at the address that’s associated with your QuickSight account.

View alert history

To view the history of when an alert was triggered, complete the following steps:

1. On the QuickSight console, choose Dashboards, and navigate to the dashboard that you want to view alert history for.
2. Choose Alerts.
3. In the Manage dashboard alerts section, find the alert that you want to view the history for, and expand History under the alert name.

Set up email alerts if the dataset refresh fails.

To configure emails alerts, if the your dataset refresh fails, complete the following steps:

1. On the QuickSight console, choose Dataset, and choose the dataset that you want to set an alert for.
2. Select Email owners when a refresh fails.
3. Close the window.

Clean up

To avoid incurring future charges, delete the QuickSight users and Enterprise account.

Conclusion

This post showed how to set up email scheduling of QuickSight dashboards for users and groups, as well as how end-users (readers) can configure alerts to be sent to them when a value surpasses or drops below a given threshold.

You can send dashboard snapshots as emails to groups of readers, and each reader receives custom reports as per the security configurations set on the dataset. For more information, see sending reports by email and threshold alerts.

You can try this solution for your own use cases. If you have comments or feedback, please leave them in the comments.

---

About the Author

Niyati Upadhyay is a Solutions Architect at AWS. She joined AWS in 2019 and specializes in building and supporting big data solutions that help customers analyze and get value out of their data.

Post Syndicated from Niyati Upadhyay original https://aws.amazon.com/blogs/big-data/applying-row-level-and-column-level-security-on-amazon-quicksight-dashboards/

Amazon QuickSight is a cloud-scale business intelligence (BI) service that you can use to deliver easy-to-understand insights to the people you work with, wherever they are. QuickSight connects to your data in the cloud and combines data from many different sources. On a single data dashboard, QuickSight can include AWS data, third-party data, big data, spreadsheet data, SaaS data, B2B data, and more.

QuickSight users in your organization should have access to only certain data for compliance and security reasons. Without proper integrated features to enforce row-level and column-level security, you have to develop additional solutions such as views, data masking, or encryption, or try to integrate third-party solutions around your data to enforce security.

In this post, we walk through how to implement row-level and column-level security in QuickSight.

Overview of solution

You can use row-level and column-level security to share a dashboard with several users who can only view the data that they have permissions to. For example, if you have an internal team of sales agents who contact a list of potential customers, you need a single dashboard that is accessible to all the agents but displays only the list of prospects assigned to them.

Another example could be regarding financial data. You might want to restrict access to sensitive fields to a small set of users. You could further restrict access based on some other field, such as geography. Let’s assume we have three users: A, B and C. Users A and B are admin users and have access to financial data, but user C does not. Furthermore, users A and B are admins for different countries and have access to financial data for their respective countries only.

In this post, we set up row-level and column-level security permissions in our dataset for different sets of users and experiment with grant and deny access rules. We walk you through the following steps:

1. Upload the dataset and apply the permissions to it.
2. Create appropriate filters and reports for users.
3. Log in as different users and see how the dashboard’s data has been tailored just to what they are allowed to see.

Prerequisites

For this walkthrough, you should have the following prerequisites:

• An AWS account
• QuickSight Enterprise edition

In the Enterprise edition of QuickSight, you can restrict access to a dataset by configuring row-level and column-level security. Only the people you shared with can see any of the data.

You can also send dashboard snapshots as emails to groups of readers, with each reader receiving customer reports as per the security configurations set on the dashboard.

Granting row-level access

For our use case, a business analyst in the marketing team creates a dataset of monthly sales in QuickSight using the file month_b2bsalesdata.xlsx. The following screenshot shows the dataset details. We use the location field to restrict user access to the data.

To grant row-level access, complete the following steps:

1. Upload the permission file and define appropriate user access.

In the permission file, we have the location field same as our primary month_b2bsalesdata dataset. It doesn’t matter what order the fields are in. However, all the fields are case-sensitive. They must exactly match the field names and values. You must have at least one field that identifies either users or groups. The field you use for users or groups can have any name you choose.

If you’re specifying groups, use only QuickSight groups or Microsoft AD groups.

The following screenshot shows the dataset rules (permissions) for row-level security. Here we have defined access for users to different states and cities.

2. On the Datasets tab, choose the dataset you want to enable row-level security for.
3. Choose Row-level security this dataset.

4. For Selected data set rules, select the appropriate permission file (for this post, permissions.csv).
5. For Permission Policy, you can grant permissions. For this post, select Grant access to data set.

By default, access is denied for all users, and you use the permissions file to allow access to the data. For example, if I don’t put a row for users in the permissions file, they don’t see any data in the report. If I put blanks (empty strings “”) for users in the permissions file, they see everything.

6. Choose Apply data set.

Testing row-level restrictions

You can now test the restrictions by logging in as various users.

1. Log in as the user AlejandroRosalez.

This user has access to data where the state is California or Texas, and the city is Los Angeles or Fort Worth. The following screenshot shows the data visible to AlejandroRosalez.

2. Log in using SaanviSarkar.

The following screenshot shows the data visible to SaanviSarkar. They can see data only for any city, but only if the state is Texas.

3. Log in using MarthaRivera.

The following screenshot shows the data visible to MarthaRivera. Martha can see the data for any city or state.

4. Log in using the workshop user, which isn’t present in the permissions.csv file.

The following screenshot shows that no data is visible to the workshop user.

Granting column-level access

We can also grant access at the column level to specific users and groups.

1. On the QuickSight console, on the Datasets page, choose the dataset you want to enable column-level security for.
2. Choose Column-level security.

By default, all users and groups have access to the data. Column-level security allows you to manage access to specific columns in your dataset.

You can restrict column access to particular users and groups. In the following screenshot, I’m restricting access for the sales column only to PauloSantos and ZhangWei.

Testing column-level access

To test these additional restrictions, log in as the Zhangwei user.

The following screenshot shows the data visible to Zhangwei user. They can see all the columns.

If you log in as the workshop user, Sales is marked with the Restricted symbol in the Fields list pane. This user doesn’t have access to view or use the Sales field in any visual within the analysis.

Pivot tables and regular tables

If the column is in the Values field, you can see Not Authorised in pivot tables and regular tables.

In the following screenshots, you can see Sales is in the Values field.

If the column is in the Group by, Row, or Column field, you get an error message saying You can’t access this visual because this field is restricted : Sales.

In the following screenshots, you can see Sales is in the Column and Group by fields.

Cleaning up

To avoid incurring future charges, delete the QuickSight users and Enterprise account.

Conclusion

This post showed how QuickSight allows you to set up row-level and column-level security at the user and group level. QuickSight also supports emailing dashboards with row-level security enabled. You can send dashboard snapshots as emails to groups of readers, and each reader receives custom reports as per the security configurations set on the dataset. For more information, see Sending Reports by Email.

You can try this solution for your own use cases. If you have comments or feedback, please leave them in the comments.

---

About the Author

Niyati Upadhyay is a Solutions Architect at AWS. She joined AWS in 2019 and specializes in building and supporting Big Data solutions that help customers analyze and get value out of their data.