Tag Archives: Amazon QuickSight

Enrich datasets for descriptive analytics with AWS Glue DataBrew

Post Syndicated from Daniel Rozo original https://aws.amazon.com/blogs/big-data/enrich-datasets-for-descriptive-analytics-with-aws-glue-databrew/

Data analytics remains a constantly hot topic. More and more businesses are beginning to understand the potential their data has to allow them to serve customers more effectively and give them a competitive advantage. However, for many small to medium businesses, gaining insight from their data can be challenging because they often lack in-house data engineering skills and knowledge.

Data enrichment is another challenge. Businesses that focus on analytics using only their internal datasets miss the opportunity to gain better insights by using reliable and credible public datasets. Small to medium businesses are no exception to this shortcoming, where obstacles such as not having sufficient data diminish their ability to make well-informed decisions based on accurate analytical insights.

In this post, we demonstrate how AWS Glue DataBrew enables businesses of all sizes to get started with data analytics with no prior coding knowledge. DataBrew is a visual data preparation tool that makes it easy for data analysts and scientists to clean and normalize data in preparation for analytics or machine learning. It includes more than 350 pre-built transformations for common data preparation use cases, enabling you to get started with cleaning, preparing, and combining your datasets without writing code.

For this post, we assume the role of a fictitious small Dutch solar panel distribution and installation company named OurCompany. We demonstrate how this company can prepare, combine, and enrich an internal dataset with publicly available data from the Dutch public entity, the Centraal Bureau voor de Statistiek (CBS), or in English, Statistics Netherlands. Ultimately, OurCompany desires to know how well they’re performing compared to the official reported values by the CBS across two important key performance indicators (KPIs): the amount of solar panel installations, and total energy capacity in kilowatt (kW) per region.

Solution overview

The architecture uses DataBrew for data preparation and transformation, Amazon Simple Storage Service (Amazon S3) as the storage layer of the entire data pipeline, and the AWS Glue Data Catalog for storing the dataset’s business and technical metadata. Following the modern data architecture best practices, this solution adheres to foundational logical layers of the Lake House Architecture.

The solution includes the following steps:

  1. We set up the storage layer using Amazon S3 by creating the following folders: raw-data, transformed-data, and curated-data. We use these folders to track the different stages of our data pipeline consumption readiness.
  2. Three CSV raw data files containing unprocessed data of solar panels as well as the external datasets from the CBS are ingested into the raw-data S3 folder.
  3. This part of the architecture incorporates both processing and cataloging capabilities:
    1. We use AWS Glue crawlers to populate the initial schema definition tables for the raw dataset automatically. For the remaining two stages of the data pipeline (transformed-data and curated-data), we utilize the functionality in DataBrew to directly create schema definition tables into the Data Catalog. Each table provides an up-to-date schema definition of the datasets we store on Amazon S3.
    2. We work with DataBrew projects as the centerpiece of our data analysis and transformation efforts. In here, we set up no-code data preparation and transformation steps, and visualize them through a highly interactive, intuitive user interface. Finally, we define DataBrew jobs to apply these steps and store transformation outputs on Amazon S3.
  4. To gain the benefits of granular access control and easily visualize data from Amazon S3, we take advantage of the seamless integration between Amazon Athena and Amazon QuickSight. This provides a SQL interface to query all the information we need from the curated dataset stored on Amazon S3 without the need to create and maintain manifest files.
  5. Finally, we construct an interactive dashboard with QuickSight to depict the final curated dataset alongside our two critical KPIs.

Prerequisites

Before beginning this tutorial, make sure you have the required Identity and Access Management (IAM) permissions to create the resources required as part of the solution. Your AWS account should also have an active subscription to QuickSight to create the visualization on processed data. If you don’t have a QuickSight account, you can sign up for an account.

The following sections provide a step-by-step guide to create and deploy the entire data pipeline for OurCompany without the use of code.

Data preparation steps

We work with the following files:

  • CBS Dutch municipalities and provinces (Gemeentelijke indeling op 1 januari 2021) – Holds all the municipalities and provinces names and codes of the Netherlands. Download the file gemeenten alfabetisch 2021. Open the file and save it as cbs_regions_nl.csv. Remember to change the format to CSV (comma-delimited).
  • CBS Solar power dataset (Zonnestroom; vermogen bedrijven en woningen, regio, 2012-2018) – This file contains the installed capacity in kilowatts and total number of installations for businesses and private homes across the Netherlands from 2012–2018. To download the file, go to the dataset page, choose the Onbewerkte dataset, and download the CSV file. Rename the file to cbs_sp_cap_nl.csv.
  • OurCompany’s solar panel historical data – Contains the reported energy capacity from all solar panel installations of OurCompany across the Netherlands from 2012 until 2018. Download the file.

As a result, the following are the expected input files we use to work with the data analytics pipeline:

  • cbs_regions_nl.csv
  • cbs_sp_cap_nl.csv
  • sp_data.csv

Set up the storage Layer

We first need to create the storage layer for our solution to store all raw, transformed, and curated datasets. We use Amazon S3 as the storage layer of our entire data pipeline.

  1. Create an S3 bucket in the AWS Region where you want to build this solution. In our case, the bucket is named cbs-solar-panel-data. You can use the same name followed by a unique identifier.
  2. Create the following three prefixes (folders) in your S3 bucket by choosing Create folder:
    1. curated-data/
    2. raw-data/
    3. transformed-data/

  3. Upload the three raw files to the raw-data/ prefix.
  4. Create two prefixes within the transformed-data/ prefix named cbs_data/ and sp_data/.

Create a Data Catalog database

After we set up the storage layer of our data pipeline, we need to create the Data Catalog to store all the metadata of the datasets hosted in Amazon S3. To do so, follow these steps:

  1. Open the AWS Glue console in the same Region of your newly created S3 bucket.
  2. In the navigation pane, choose Databases.
  3. Choose Add database.
  4. Enter the name for the Data Catalog to store all the dataset’s metadata.
  5. Name the database sp_catalog_db.

Create AWS Glue data crawlers

Now that we created the catalog database, it’s time to crawl the raw data prefix to automatically retrieve the metadata associated to each input file.

  1. On the AWS Glue console, choose Crawlers in the navigation pane.
  2. Add a crawler with the name crawler_raw and choose Next.
  3. For S3 path, select the raw-data folder of the cbs-solar-panel-data prefix.
  4. Create an IAM role and name it AWSGlueServiceRole-cbsdata.
  5. Leave the frequency as Run on demand.
  6. Choose the sp_catalog_db database created in the previous section, and enter the prefix raw_ to identify the tables that belong to the raw data folder.
  7. Review the parameters of the crawler and then choose Finish.
  8. After the crawler is created, select it and choose Run crawler.

After successful deployment of the crawler, your three tables are created in the sp_catalog_db database: raw_sp_data_csv, raw_cbs_regions_nl_csv, and raw_cbs_sp_cap_nl_csv.

Create DataBrew raw datasets

To utilize the power of DataBrew, we need to connect datasets that point to the Data Catalog S3 tables we just created. Follow these steps to connect the datasets:

  1. On the DataBrew console, choose Datasets in the navigation pane.
  2. Choose Connect new dataset.
  3. Name the dataset cbs-sp-cap-nl-dataset.
  4. For Connect to new dataset, choose Data Catalog S3 tables.
  5. Select the sp_catalog_db database and the raw_cbs_sp_cap_nl_csv table.
  6. Choose Create dataset.

We need to create to two more datasets following the same process. The following table summarizes the names and tables of the catalog required for the new datasets.

Dataset name Data catalog table
sp-dataset raw_sp_data_csv
cbs-regions-nl-dataset raw_cbs_regions_nl_csv

Import DataBrew recipes

A recipe is a set of data transformation steps. These transformations are applied to one or multiple datasets of your DataBrew project. For more information about recipes, see Creating and using AWS Glue DataBrew recipes.

We have prepared three DataBrew recipes, which contain the set of data transformation steps we need for this data pipeline. Some of these transformation steps include: renaming columns (from Dutch to English), removing null or missing values, aggregating rows based on specific attributes, and combining datasets in the transformation stage.

To import the recipes, follow these instructions:

  1. On the DataBrew console, choose Recipes in the navigation pane.
  2. Choose Upload recipe.
  3. Enter the name of the recipe: recipe-1-transform-cbs-data.
  4. Upload the following JSON recipe.
  5. Choose Create recipe.

Now we need to upload two more recipes that we use for transformation and aggregation projects in DataBrew.

  1. Follow the same procedure to import the following recipes:
Recipe name Recipe source file
recipe-2-transform-sp-data Download
recipe-3-curate-sp-cbs-data Download
  1. Make sure the recipes are listed in the Recipes section filtered by All recipes.

Set up DataBrew projects and jobs

After we successfully create the Data Catalog database, crawlers, DataBrew datasets, and import the DataBrew recipes, we need to create the first transformation project.

CBS external data transformation project

The first project takes care of transforming, cleaning, and preparing cbs-sp-cap-nl-dataset. To create the project, follow these steps:

  1. On the DataBrew console, choose Projects in the navigation pane.
  2. Create a new project with the name 1-transform-cbs-data.
  3. In the Recipe details section, choose Edit existing recipe and choose the recipe recipe-1-transform-cbs-data.
  4. Select the newly created cbs-sp-cap-nl-dataset under Select a dataset.
  5. In the Permissions section, choose Create a new IAM role.
  6. As suffix, enter sp-project.
  7. Choose Create project.

After you create the project, a preview dataset is displayed as a result of applying the selected recipe. When you choose 10 more recipe steps, the service shows the entire set of transformation steps.

After you create the project, you need to grant put and delete S3 object permissions to the created role AWSGlueDataBrewServiceRole-sp-project on IAM. Add an inline policy using the following JSON and replace the resource with your S3 bucket name:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::<your-S3-bucket-name>/*"
        }
    ]
}

This role also needs permissions to access the Data Catalog. To grant these permissions, add the managed policy AWSGlueServiceRole to the role.

CBS external data transformation job

After we define the project, we need to configure and run a job to apply the transformation across the entire raw dataset stored in the Raw-data folder of your S3 bucket. To do so, you need to do the following:

  1. On the DataBrew project page, choose Create job.
  2. For Job name, enter 1-transform-cbs-data-job.
  3. For Output to, choose Data Catalog S3 tables.
  4. For File type¸ choose Parquet.
  5. For Database name, choose sp_catalog_db.
  6. For Table name, choose Create new table.
  7. For Catalog table name, enter transformed_cbs_data.
  8. For S3 location, enter s3://<your-S3-bucket-name>/transformed-data/cbs_data/.
  9. In the job output settings section, choose Settings.
  10. Select Replace output files for each job run and then choose Save.
  11. In the permissions section, choose the automatically created role with the sp-project suffix; for example, AWSGlueDataBrewServiceRole-sp-project.
  12. Review the job details once more and then choose Create and run job.
  13. Back in the main project view, choose Job details.

After a few minutes, the job status changes from Running to Successful. Choose the output to go to the S3 location where all the generated Parquet files are stored.

Solar panels data transformation stage

We now create the second phase of the data pipeline. We create a project and a job using the same procedure described in the previous section.

  1. Create a DataBrew project with the following parameters:
    1. Project name – 2-transform-sp-data
    2. Imported recipe – recipe-2-transform-sp-data
    3. Dataset – sp_dataset
    4. Permissions role – AWSGlueDataBrewServiceRole-sp-project
  2. Create and run another DataBrew job with the following parameters:
    1. Job name – 2-transform-sp-data-job
    2. Output to – Data Catalog S3 tables
    3. File type – Parquet
    4. Database name – sp_catalog_db
    5. Create new table with table name – transformed_sp_data
    6. S3 location – s3://<your-S3-bucket-name>/transformed-data/sp_data/
    7. Settings – Replace output files for each job run.
    8. Permissions role – AWSGlueDataBrewServiceRole-sp-project
  3. After the job is complete, create the DataBrew datasets with the following parameters:
Dataset name Data catalog table
transformed-cbs-dataset awsgluedatabrew_transformed_cbs_data
transformed-sp-dataset awsgluedatabrew_transformed_sp_data

You should now see five items as part of your DataBrew dataset.

Data curation and aggregation stage

We now create the final DataBrew project and job.

  1. Create a DataBrew project with the following parameters:
    1. Project name – 3-curate-sp-cbs-data
    2. Imported recipe – recipe-3-curate-sp-cbs-data
    3. Dataset – transformed_sp_dataset
    4. Permissions role – AWSGlueDataBrewServiceRole-sp-project
  2. Create a DataBrew job with the following parameters:
    1. Job name – 3-curate-sp-cbs-data-job
    2. Output to – Data Catalog S3 tables
    3. File type – Parquet
    4. Database name – sp_catalog_db
    5. Create new table with table name – curated_data
    6. S3 location – s3://<your-S3-bucket-name>/curated-data/
    7. Settings – Replace output files for each job run
    8. Permissions role – AWSGlueDataBrewServiceRole-sp-project

The last project defines a single transformation step; the join between the transformed-cbs-dataset and the transformed-sp-dataset based on the municipality code and the year.

The DataBrew job should take a few minutes to complete.

Next, check your sp_catalog_db database. You should now have raw, transformed, and curated tables in your database. DataBrew automatically adds the prefix awsgluedatabrew_ to both the transformed and curated tables in the catalog.

Consume curated datasets for descriptive analytics

We’re now ready to build the consumption layer for descriptive analytics with QuickSight. In this section, we build a business intelligence dashboard that reflects OurCompany’s solar panel energy capacity and installations participation in contrast to the reported values by the CBS from 2012–2018.

To complete this section, you need to have the default primary workgroup already set up on Athena in the same Region where you implemented the data pipeline. If it’s your first time setting up workgroups on Athena, follow the instructions in Setting up Workgroups.

Also make sure that QuickSight has the right permissions to access Athena and your S3 bucket. Then complete the following steps:

  1. On the QuickSight console, choose Datasets in the navigation pane.
  2. Choose Create a new dataset.
  3. Select Athena as the data source.
  4. For Data source name, enter sp_data_source.
  5. Choose Create data source.
  6. Choose AWSDataCatalog as the catalog and sp_catalog_db as the database.
  7. Select the table curated_data.
  8. Choose Select.
  9. In the Finish dataset creation section, choose Directly query your data and choose Visualize.
  10. Choose the clustered bar combo chart from the Visual types list.
  11. Expand the field wells section and then drag and drop the following fields into each section as shown in the following screenshot.
  12. Rename the visualization as you like, and optionally filter the report by sp_year using the Filter option.

From this graph, we can already benchmark OurCompany against the regional values reported by the CBS across two dimensions: the total amount of installations and the total kW capacity generated by solar panels.

We went one step further and created two KPI visualizations to empower our descriptive analytics capabilities. The following is our final dashboard that we can use to enhance our decision-making process.

Clean up resources

To clean all the resources we created for the data pipeline, complete the following steps:

  1. Remove the QuickSight analyses you created.
  2. Delete the dataset curated_data.
  3. Delete all the DataBrew projects with their associated recipes.
  4. Delete all the DataBrew datasets.
  5. Delete all the AWS Glue crawlers you created.
  6. Delete the sp_catalog_db catalog database; this removes all the tables.
  7. Empty the contents of your S3 bucket and delete it.

Conclusion

In this post, we demonstrated how you can begin your data analytics journey. With DataBrew, you can prepare and combine the data you already have with publicly available datasets such as those from the Dutch CBS (Centraal Bureau voor de Statistiek) without needing to write a single line of code. Start using DataBrew today and enrich key datasets in AWS for enhanced descriptive analytics capabilities.

About the Authors

Daniel Rozo is a Solutions Architect with Amazon Web Services based out of Amsterdam, The Netherlands. He is devoted to working with customers and engineering simple data and analytics solutions on AWS. In his free time, he enjoys playing tennis and taking tours around the beautiful Dutch canals.

Maurits de Groot is an intern Solutions Architect at Amazon Web Services. He does research on startups with a focus on FinTech. Besides working, Maurits enjoys skiing and playing squash.

Terms of use: Gemeentelijke indeling op 1 januari 2021, Zonnestroom; vermogen bedrijven en woningen, regio (indeling 2018), 2012-2018, and copies of these datasets redistributed by AWS, are licensed under the Creative Commons 4.0 license (CC BY 4.0), sourced from Centraal Bureau voor de Statistiek (CBS). The datasets used in this solution are modified to rename columns from Dutch to English, remove null or missing values, aggregate rows based on specific attributes, and combine the datasets in the final transformation. Refer to the CC BY 4.0 use, adaptation, and attribution requirements for additional information.

ConexED uses Amazon QuickSight to empower its institutional partners by unifying and curating powerful insights using engagement data

Post Syndicated from Michael Gorham original https://aws.amazon.com/blogs/big-data/conexed-uses-amazon-quicksight-to-empower-its-institutional-partners-by-unifying-and-curating-powerful-insights-using-engagement-data/

This post was co-written with Michael Gorham, Co-Founder and CTO of ConexED.

ConexED is one of the country’s fastest-growing EdTech companies designed specifically for education to enhance the student experience and elevate student success. Founded as a startup in 2008 to remove obstacles that hinder student persistence and access to student services, ConexED provides advisors, counselors, faculty, and staff in all departments across campus the tools necessary to meet students where they are.

ConexED offers a student success and case management platform, HUB Kiosk – Queuing System, and now a business intelligence (BI) dashboard powered by Amazon QuickSight to empower its institutional partners.

ConexED strives to make education more accessible by providing tools that make it easy and convenient for all students to connect with the academic support services that are vital to their success in today’s challenging and ever-evolving educational environment. ConexED’s student- and user-friendly interface makes online academic communications intuitive and as personalized as face-to-face encounters, while also making on-campus meetings as streamlined, and well reported as online meetings.

One of the biggest obstacles facing school administrators is getting meaningful data quickly so that informed, data-driven decisions can be made. Reporting can be time-consuming, so they are often generated infrequently, which leads to outdated data. In addition, reporting often lacks customization and data is typically captured in spreadsheets, which doesn’t provide a visual representation of the information that is easy to interpret. ConnexED has always offered robust reporting features, but the problem was that in providing this kind of data for our partners, our development team was spending more than half its time creating custom reporting for the constantly increasing breadth of data the ConexED system generates.

Every new feature we built requires at least two or three new reports – and therefore more of our development team’s time. After we implemented QuickSight, not only can ConexED’s development team focus all its energies on creating competitive features to accelerate the rollout of new product features, but also the reporting and data visualization are now features our customers can control and customize. QuickSight features such as drill-down filtering, predictive forecasting, and aggregation insights have given us the competitive edge that our customers expect from a modern, cloud-based solution.

New technology enables strategic planning

With QuickSight, we’re able to focus on building customer-facing solutions that capture data rather than spending a large portion of our development time solving data visualization and custom report problems. Our development team no longer has to spend its time creating reports for all the data generated, and our customers don’t need to wait. Partnering with QuickSight has enabled ConexED to develop its business intelligence dashboard, which is designed to create operational efficiencies, identify opportunities, and empower institutions by uniting critical data insights to cross-campus student support services. The QuickSight data used in ConexED’s BI dashboard analyzes collected information in real time, allowing our partners to properly project trends in the coming school year using predictive analytics to improve staff efficiency, enhance the student experience, and increase rates of retention and graduation.

The following image demonstrates heat mapping, which displays the recurring days and times when student requests for support services are most frequent, with the busiest hour segments appearing more saturated in color. This enables leadership to utilize staff efficiently so that students have the support services they need when they need it on their pathway to graduation. ConexED’s BI dashboard powered by QuickSight makes this kind of information possible so that our partners can plan strategically.

QuickSight dashboards allow our customers to drill down on the data to glean even more insights of what is happening on their campus. In the following example, the pie chart depicts a whole-campus view of meetings by department, but leadership can choose one of the colored segments to drill down further for more information about a specific department. Whatever the starting point, leadership now has the ability to access more specific, real-time data to understand what’s happening on their campus or any part of it.

Dashboards provide data visualization

Our customers have been extremely impressed with our QuickSight dashboards because they provide data visualizations that make the information easier to comprehend and parse. The dynamic, interactive nature of the dashboards allows ConexED’s partners to go deeper into the data with just a click of the mouse, which immediately generates new data based on what was clicked and therefore new visuals.

With QuickSight, not only can we programmatically display boiler-plate dashboards based on role type, but we can also allow our clients to branch off these dashboards and customize the reporting to their liking. The development team is now able to move quickly to build interesting features that ingest data and provide insightful visualizations and reports on the gathered data easily. ConexED’s BI dashboard powered by QuickSight enables leadership at our partner institutions to understand how users engage with support services on their campus – when they meet, why they meet, how they meet – so that they can make informed decisions to improve student engagement and services.

The right people with the right information

In education, giving the right level of data access to the right people is essential. With intuitive row- and column-level security and anonymous tagging in QuickSight, the ConexED development team was able to quickly build visualizations that correctly display partitioned data to thousands of different users with varying levels of access across our client base.

At ConexED, student success is paramount, and with QuickSight powering our BI dashboard, the right people get the right data, and our institutional customers can now easily analyze vast amounts of data to identify trends in student acquisition, retention, and completion rates. They can also solve student support staffing allocation problems and improve the student experience at their institutions.

QuickSight does the heavy lifting

The ability to securely pull and aggregate data from disparate sources with very little setup work has given ConexED a head start on the predictive analytics space in the EdTech market. Now building visualizations is intuitive, insightful, and fun. In fact, the development team even built in only 1 day an internal QuickSight dashboard to view our own customers’ QuickSight usage. The data visualization combinations are seemingly endless and infinitely valuable to our customers.

ConexED’s partnership with AWS has enabled us to use QuickSight to drive our BI dashboard and provide our customers with the power and information needed for today’s dynamic modern student support services teams.

About the Author

Michael Gorham is Co-Founder and CTO of ConexED. Michael is a multidisciplinary software architect with over 20 years’ experience

Visualize live analytics from Amazon QuickSight connected to Amazon OpenSearch Service

Post Syndicated from Lokesh Yellanur original https://aws.amazon.com/blogs/big-data/visualize-live-analytics-from-amazon-quicksight-connected-to-amazon-opensearch-service/

Live analytics refers to the process of preparing and measuring data as soon as it enters the database or persistent store. In other words, you get insights or arrive at conclusions immediately. Live analytics enables businesses to respond to events without delay. You can seize opportunities or prevent problems before they happen. Speed is the main benefit of live analytics. The faster a business can use data for insights, the faster they can act on critical decisions.

Some live analytics use cases include:

  • Analyzing access logs and application logs from servers to identify any server performance issues that could lead to application downtime or help detect unusual activity. For instance, analyzing monitoring data from a manufacturing line can help early intervention before machinery malfunctions.
  • Targeting individual customers in retail outlets with promotions and incentives while the customers are in the store and close to the merchandise.

We see customers using real-time analytics using our ELK stack. The ELK stack is an acronym used to describe a stack that comprises three popular open-source projects: Elasticsearch, Logstash, and Kibana. Often referred to as Elasticsearch, the ELK stack gives you the ability to aggregate logs from all your systems and applications, analyze these logs, and create visualizations for application and infrastructure monitoring, faster troubleshooting, security analytics, and more. In this post, we extend the live analytics visualizations using Amazon QuickSight.

Solution overview

Amazon OpenSearch Service (successor to Amazon Elasticsearch Service) is a fully managed service that makes it easy for you to deploy, secure, and run OpenSearch cost-effectively at scale. You can build, monitor, and troubleshoot your applications using the tools you love at the scale you need. The service provides support for open-source OpenSearch APIs, managed Kibana, integration with Logstash and other AWS services, and built-in alerting and SQL querying. In addition, Amazon OpenSearch Service lets you pay only for what you use—there are no upfront costs or usage requirements. With Amazon OpenSearch Service, you get the ELK stack you need without the operational overhead.

QuickSight is a scalable, serverless, embeddable, machine learning (ML)-powered business intelligence (BI) service built for the cloud. QuickSight lets you easily create and publish interactive BI dashboards that include ML-powered insights. QuickSight dashboards can be accessed from any device and seamlessly embedded into your applications, portals, and websites.

This post helps you visualize the Centralized Logging solution using QuickSight. Centralized logging helps organizations collect, analyze, and display Amazon CloudWatch logs in a single dashboard in QuickSight.

This solution consolidates, manages, and analyzes log files from various sources. You can collect CloudWatch logs from multiple accounts and AWS Regions. Access log information can be beneficial in security and access audits. It can also help you learn about your customer base and understand your Amazon Simple Storage Service (Amazon S3) bill.

The following diagram illustrates the solution architecture.

For more information about the solution, see Centralized Logging.

Prerequisites

Before you implement the solution, complete the prerequisite steps in this section.

Provision your resources

Launch the following AWS CloudFormation template to launch the Centralized Logging solution:

After you create the stack, you receive an email (to the administrator email address) with your login information, as shown in the following screenshot.

Launch QuickSight in a VPC

Sign up for a QuickSight subscription with the Enterprise license.

QuickSight Enterprise Edition is fully integrated with Amazon Virtual Private Cloud (Amazon VPC). A VPC based on this service closely resembles a traditional network that you operate in your own data center. It enables you to secure and isolate traffic between resources.

Allow QuickSight to access Amazon OpenSearch Service

Make sure QuickSight has access to both the VPC and Amazon OpenSearch Service.

  1. On the QuickSight dashboard, choose the user icon and choose Manage QuickSight.
  2. Choose Security & permissions in the navigation pane.
  3. Choose Add or Remove to update QuickSight access to AWS services.
  1. For Allow access and autodiscovery for these recourses, select Amazon OpenSearch Service.

Manage the VPC and security group connections

You need to give permissions on the QuickSight console to connect to Amazon OpenSearch Service. After you enable Amazon OpenSearch Service on the Security & permissions page, you add a VPC connection with the same VPC and subnet as your Amazon OpenSearch Service domain and create a new security group.

You first create a security group for QuickSight.

  1. Add an inbound rule to allow all communication from the Amazon OpenSearch Service domain.
  2. For Type, choose All TCP.
  3. For Source, select Custom, then enter the ID of the security group used by your Amazon OpenSearch Service domain.
  4. Add an outbound rule to allow all traffic to the Amazon OpenSearch Service domain.
  5. For Type, choose Custom TCP Rule.
  6. For Port Range, enter 443.
  7. For Destination, select Custom, then enter the ID of the security group used by your Amazon OpenSearch Service domain.

Next, you create a security group for the Amazon OpenSearch Service domain.

  1. Add an inbound rule that allows all incoming traffic from the QuickSight security group.
  2. For Type, choose Custom TCP.
  3. For Port Range, enter 443.
  4. For Source, select Custom, then enter the QuickSight security group ID.
  5. Add an outbound rule that allows all traffic to the QuickSight security group.
  6. For Type, choose All TCP.
  7. For Destination, select Custom, then enter the QuickSight security group ID.

Choose your datasets

To validate the connection and create the data source, complete the following steps:

  1. On the QuickSight console, choose Datasets.
  2. Choose Create dataset.
  3. Choose Amazon OpenSearch Service.
  4. For Data source name, enter a name.
  5. Depending on your Amazon OpenSearch Service connections of either public or VPC, choose your connection type and Amazon OpenSearch Service domain.
  6. Choose Validate connection.
  7. Choose Create data source.

  1. Choose Tables.
  2. Select the table in the data source you created.
  3. Review your settings and choose Visualize.

Visualize the data loaded

QuickSight, with its wide array of visuals available, allows you to create meaningful visuals from Amazon OpenSearch Service data.

When you choose Visualize from the previous steps, you start creating an analysis. QuickSight provides a range of visual types to display data, such as graphs, tables, heat maps, scatter plots, line charts, pie charts, and more. The following steps allow you to add a visual type to display the data from the datasets.

  1. On the Add menu, choose Add visual.
  2. Choose your visual type.
  3. Add fields to the field wells to bring data into the visuals to be displayed.

The following screenshot shows a sample group of visuals.

Automatically refresh your data

You can access and visualize your data through direct queries. Your data is queried live each time a visual is rendered. This gives you live access to your data. Additionally, you can automatically refresh the visuals every 1–60 minutes, so that you don’t have to reload the page to see the most up-to-date information. The following screenshot shows the auto-refresh settings while preparing to publish your dashboard.

For more information about the auto-refresh option, see Using Amazon OpenSearch with Amazon QuickSight.

The following screenshot shows an example visualization.

Clean up

When you’re done using this solution, to avoid incurring future charges, delete the resources you created in this walkthrough, including your S3 buckets,  Amazon OpenSearch Service cluster, and other associated resources.

Summary

This post demonstrated how to extend your ELK stack with QuickSight in a secure way for analyzing access logs. The application logs help you identify any server performance issues that could lead to application downtime. They can also help detect unusual activity.

As always, AWS welcomes feedback. Please submit comments or questions in the comments section.

About the Authors

Lokesh Yellanur is a Solutions Architect at AWS. He helps customers with data and analytics solutions in AWS.

Joshua Morrison is a Senior Solutions Architect at AWS based in Richmond, Virginia. He spends time working with customers to help with their adoption of modern cloud technology and security best practices. He enjoys being a father and picking up heavy objects.

Suresh Patnam is a Sr Solutions Architect at AWS; He works with customers to build IT strategy, making digital transformation through the cloud more accessible, focusing on big data, data lakes, and AI/ML. In his spare time, Suresh enjoys playing tennis and spending time with his family. Connect him on LinkedIn.

How to set up Amazon Quicksight dashboard for Amazon Pinpoint and Amazon SES engagement events

Post Syndicated from satyaso original https://aws.amazon.com/blogs/messaging-and-targeting/how-to-set-up-amazon-quicksight-dashboard-for-amazon-pinpoint-and-amazon-ses-events/

In this post, we will walk through using Amazon Pinpoint and Amazon Quicksight to create customizable messaging campaign reports. Amazon Pinpoint is a flexible and scalable outbound and inbound marketing communications service that allows customers to connect with users over channels like email, SMS, push, or voice. Amazon QuickSight is a scalable, serverless, embeddable, machine learning-powered business intelligence (BI) service built for the cloud. This solution allows event and user data from Amazon Pinpoint to flow into Amazon Quicksight. Once in Quicksight, customers can build their own reports that shows campaign performance on a more granular level.

Engagement Event Dashboard

Customers want to view the results of their messaging campaigns in ever increasing levels of granularity and ensure their users see value from the email, SMS or push notifications they receive. Customers also want to analyze how different user segments respond to different messages, and how to optimize subsequent user communication. Previously, customers could only view this data in Amazon Pinpoint analytics, which offers robust reporting on: events, funnels, and campaigns. However, does not allow analysis across these different parameters and the building of custom reports. For example, show campaign revenue across different user segments, or show what events were generated after a user viewed a campaign in a funnel analysis. Customers would need to extract this data themselves and do the analysis in excel.

Prerequisites

  • Digital user engagement event database solution must be setup at 1st.
  • Customers should be prepared to purchase Amazon Quicksight because it has its own set of costs which is not covered within Amazon Pinpoint cost.

Solution Overview

This Solution uses the Athena tables created by Digital user engagement events database solution. The AWS CloudFormation template given in this post automatically sets up the different architecture components, to capture detailed notifications about Amazon Pinpoint engagement events and log those in Amazon Athena in the form of Athena views. You still need to manually configure Amazon Quicksight dashboards to link to these newly generated Athena views. Please follow the steps below in order for further information.

Use case(s)

Event dashboard solutions have following use cases: –

  • Deep dive into engagement insights. (eg: SMS events, Email events, Campaign events, Journey events)
  • The ability to view engagement events at the individual user level.
  • Data/process mining turn raw event data into useful marking insights.
  • User engagement benchmarking and end user event funneling.
  • Compute campaign conversions (post campaign user analysis to show campaign effectiveness)
  • Build funnels that shows user progression.

Getting started with solution deployment

Prerequisite tasks to be completed before deploying the logging solution

Step 1 – Create AWS account, Pinpoint Project, Implement Event-Database-Solution.
As part of this step customers need to implement DUE Event database solution as the current solution (DUE event dashboard) is an extension of DUE event database solution. The basic assumption here is that the customer has already configured Amazon Pinpoint project or Amazon SES within the required AWS region before implementing this step.

The steps required to implement an event dashboard solution are as follows.

a/Follow the steps mentioned in Event database solution to implement the complete stack. Prior installing the complete stack copy and save the name Athena events database name as shown in the diagram. For my case it is due_eventdb. Database name is required as an input parameter for the current Event Dashboard solution.

b/Once the solution is deployed, navigate to the output page of the cloud formation stack, and copy, and save the following information, which will be required as input parameters in step 2 of the current Event Dashboard solution.

Step 2 – Deploy Cloud formation template for Event dashboard solution
This step generates a number of new Amazon Athena views that will serve as a data source for Amazon Quicksight. Continue with the following actions.

  • Download the cloud formation template(“Event-dashboard.yaml”) from AWS samples.
  • Navigate to Cloud formation page in AWS console, click up right on “Create stack” and select the option “With new resources (standard)”
  • Leave the “Prerequisite – Prepare template” to “Template is ready” and for the “Specify template” option, select “Upload a template file”. On the same page, click on “Choose file”, browse to find the file “Event-dashboard.yaml” file and select it. Once the file is uploaded, click “Next” and deploy the stack.

  • Enter following information under the section “Specify stack details”:
    • EventAthenaDatabaseName – As mentioned in Step 1-a.
    • S3DataLogBucket- As mentioned in Step 1-b
    • This solution will create additional 5 Athena views which are
      • All_email_events
      • All_SMS_events
      • All_custom_events (Custom events can be Mobile app/WebApp/Push Events)
      • All_campaign_events
      • All_journey_events

Step 3 – Create Amazon Quicksight engagement Dashboard
This step walks you through the process of creating an Amazon Quicksight dashboard for Amazon Pinpoint engagement events using the Athena views you created in step-2

  1. To Setup Amazon Quicksight for the 1st time please follow this link (this process is not needed if you have already setup Amazon Quicksight). Please make sure you are an Amazon Quicksight Administrator.
  2. Go/search Amazon Quicksight on AWS console.
  3. Create New Analysis and then select “New dataset”
  4. Select Athena as data source
  5. As a next step, you need to select what all analysis you need for respective events. This solution provides option to create 5 different set of analysis as mentioned in Step 2. They are a/All email events, b/All SMS Events, c/All Custom Events (Mobile/Web App, web push etc), d/ All Campaign events, e/All Journey events. Dashboard can be created from Quicksight analysis and same can be shared among the organization stake holders. Following are the steps to create analysis and dashboards for different type of events.
  6. Email Events –
    • For all email events, name the analysis “All-emails-events” (this can be any kind of customer preferred nomenclature), select Athena workgroup as primary, and then create a data source.
    • Once you create the data source Quicksight lists all the views and tables available under the specified database (in our case it is:-  due_eventdb). Select the email_all_events view as data source.
    • Select the event data location for analysis. There are mainly two options available which are a/ Import to Spice quicker analysis b/ Directly query your data. Please select the preferred options and then click on “visualize the data”.
    • Import to Spice quicker analysis – SPICE is the Amazon QuickSight Super-fast, Parallel, In-memory Calculation Engine. It’s engineered to rapidly perform advanced calculations and serve data. In Enterprise edition, data stored in SPICE is encrypted at rest. (1 GB of storage is available for free for extra storage customer need to pay extra, please refer cost section in this document )
    • Directly query your data – This process enables Quicksight to query directly to the Athena or source database (In the current case it is Athena) and Quicksight will not store any data.
    • Now that you have selected a data source, you will be taken to a blank quick sight canvas (Blank analysis page) as shown in the following Image, please drag and drop what visualization type you need to visualize onto the auto-graph pane. Please note that Amazon QuickSight is a Busines intelligence platform, so customers are free to choose the desired visualization types to observe the individual engagement events.
    • As part of this blog, we have displayed how to create some simple analysis graphs to visualize the engagement events.
    • As an initial step please Select tabular Visualization as shown in the Image.
    • Select all the event dimensions that you want to put it as part of the Table in X axis. Amazon Quicksight table can be extended to show as many as tables columns, this completely depends upon the business requirement how much data marketers want to visualize.
    • Further filtering on the table can be done using Quicksight filters, you can apply the filter on specific granular values to enable further filtering. For Eg – If you want to apply filtering on the destination email Id then 1/Select the filter from left hand menu 2/Add destination field as the filtering criterion 3/ Tick on the destination field you are trying to filter or search for the Destination email ID that 4/ All the result in the table gets further filtered as per the filter criterion
    • As a next step please add another visual from top left corner “Add -> Add Visual”, then select the Donut Chart from Visual types pane. Donut charts are always used for displaying aggregation.
    • Then select the “event_type” as the Group to visualize the aggregated events, this helps marketers/business users to figure out how many email events occurred and what are the aggregated success ratio, click ratio, complain ratio or bounce ratio etc for the emails/Campaign that’s sent to end users.
    • To create a Quicksight dashboards from the Quicksight analysis click Share menu option at the top right corner then select publish dashboard”. Provide required dashboard name while publishing the dashboard”. Same dashboard can be shared with multiple audiences in the Organization.
    • Following is the final version of the dashboard. As mentioned above Quicksight dashboards can be shared with other stakeholders and also complete dashboard can be exported as excel sheet.
  7. SMS Events-
    • As shown above SMS events can be analyzed using Quicksight and dash boards can be created out of the analysis. Please repeat all of the sub-steps listed in step 6. Following is a sample SMS dashboard.
  8. Custom Events-
    • After you integrate your application (app) with Amazon Pinpoint, Amazon Pinpoint can stream event data about user activity, different type custom events, and message deliveries for the app. Eg :- Session.start, Product_page_view, _session.stop etc. Do repeat all of the sub-steps listed in step 6 create a custom event dashboards.
  9. Campaign events
    • As shown before campaign also can be included in the same dashboard or you can create new dashboard only for campaign events.

Cost for Event dashboard solution
You are responsible for the cost of the AWS services used while running this solution. As of the date of publication, the cost for running this solution with default settings in the US West (Oregon) Region is approximately $65 a month. The cost estimate includes the cost of AWS Lambda, Amazon Athena, Amazon Quicksight. The estimate assumes querying 1TB of data in a month, and two authors managing Amazon Quicksight every month, four Amazon Quicksight readers witnessing the events dashboard unlimited times in a month, and a Quicksight spice capacity is 50 GB per month. Prices are subject to change. For full details, see the pricing webpage for each AWS service you will be using in this solution.

Clean up

When you’re done with this exercise, complete the following steps to delete your resources and stop incurring costs:

  1. On the CloudFormation console, select your stack and choose Delete. This cleans up all the resources created by the stack,
  2. Delete the Amazon Quicksight Dashboards and data sets that you have created.

Conclusion

In this blog post, I have demonstrated how marketers, business users, and business analysts can utilize Amazon Quicksight dashboards to evaluate and exploit user engagement data from Amazon SES and Pinpoint event streams. Customers can also utilize this solution to understand how Amazon Pinpoint campaigns lead to business conversions, in addition to analyzing multi-channel communication metrics at the individual user level.

Next steps

The personas for this blog are both the tech team and the marketing analyst team, as it involves a code deployment to create very simple Athena views, as well as the steps to create an Amazon Quicksight dashboard to analyse Amazon SES and Amazon Pinpoint engagement events at the individual user level. Customers may then create their own Amazon Quicksight dashboards to illustrate the conversion ratio and propensity trends in real time by integrating campaign events with app-level events such as purchase conversions, order placement, and so on.

Extending the solution

You can download the AWS Cloudformation templates, code for this solution from our public GitHub repository and modify it to fit your needs.

About the Author


Satyasovan Tripathy works at Amazon Web Services as a Senior Specialist Solution Architect. He is based in Bengaluru, India, and specialises on the AWS Digital User Engagement product portfolio. He likes reading and travelling outside of work.

Lucerna Health uses Amazon QuickSight embedded analytics to help healthcare customers uncover new insights

Post Syndicated from David Atkins original https://aws.amazon.com/blogs/big-data/lucerna-health-uses-amazon-quicksight-embedded-analytics-to-help-healthcare-customers-uncover-new-insights/

This is a guest post by Lucerna Health. Founded in 2018, Lucerna Health is a data technology company that connects people and data to deliver value-based care (VBC) results and operational transformation.

At Lucerna Health, data is at the heart of our business. Every day, we use clinical, sales, and operational data to help healthcare providers and payers grow and succeed in the value-based care (VBC) environment. Through our HITRUST CSF® certified Healthcare Data Platform, we support payer-provider integration, health engagement, database marketing, and VBC operations.

As our business grew, we found that faster real-time analysis and reporting capabilities through our platform were critical to success. However, that was a challenge for our data analytics team, which was busier than ever developing our proprietary data engine and data model. No matter how many dashboards we built, we knew we could never keep up with user demand with our previous BI solutions. We needed a more scalable technology that could grow as our customer base continued to expand.

In this post, we will outline how Amazon QuickSight helped us overcome these challenges.

Embedding analytics with QuickSight

We had a rising demand for business intelligence (BI) from our customers, and we needed a better tool to help us keep pace that met our security requirements and was part of a comprehensive business associate contract (BAA) and met HIPAA and other privacy standards. We were using several other BI solutions internally for impromptu analysis and reporting, but we realized we needed a fully embedded solution to provide more automation and an integrated experience within our Healthcare Data Platform. After trying out a different solution, we discovered it wasn’t cost-effective for us. That’s when we turned our attention to AWS.

Three years ago, we decided to go all-in on AWS, implementing a range of AWS services for compute, storage, and networking. Today, each of the building blocks we have in our IT infrastructure run on AWS. For example, we use Amazon Redshift, AWS Glue, and Amazon EMR for our Spark data pipelines, data lake, and data analytics. Because of our all-in approach, we were pleased to find that AWS had a BI platform called QuickSight. QuickSight is a powerful and cost-effective BI service that offers a strong feature set including self-service BI capabilities and interactive dashboards, and we liked the idea of continuing to be all-in on AWS by implementing this service.

One of the QuickSight’s features we were most excited about was its ability to embed analytics deep within our Healthcare Data Platform. With this solution’s embedded analytics software, we were able to integrate QuickSight dashboards directly into our own platform. For example, we offer our customers a portal where they can register a new analytical dashboard through our user interface. That interface connects to the QuickSight application programming interface (API) to enable embedding in a highly configurable and secure way.

With this functionality, our customers can ingest and visualize complex healthcare data, such as clinical data from electronic medical record (EMR) systems, eligibility and claims, CRM and digital interactions data. Our Insights data model is projected into Quicksight’s high performance in memory calculation engine enabling high performance analysis on massive datasets.

Creating a developer experience for customers

We have also embedded the QuickSight console into our platform. Through this approach, our healthcare data customers can build their own datasets and quickly share that data with a wider group of users through our platform. This gives our customers a developer experience that enables them to customize and share analytical reports with their colleagues. With only a few clicks, users can aggregate and compare data from their sales and EMR solutions.

QuickSight has also improved collaboration for our own teams when it comes to custom reports. In the past, teams could only do monthly or specialized reports, spending a lot of time building them, downloading them as PDFs, and sending them out to clients as slides. It was a time-consuming and inefficient way to share data. Now, our users can get easy access to data from previously siloed sources, and then simply publish reports and share access to that data immediately.

Helping healthcare providers uncover new insights

Because healthcare providers now have centralized data at their fingertips, they can make faster and more strategic decisions. For instance, management teams can look at dashboards on our platform to see updated demand data to plan more accurate staffing models. We’ve also created patient and provider data models that provide a 360-degree view of patient and payer data, increasing visibility. Additionally, care coordinators can reprioritize tasks and take action if necessary because they can view gaps in care through the dashboards. Armed with this data, care coordinators can work to improve the patient experience at the point of care.

Building and publishing reports twice as fast

QuickSight is a faster BI solution than anything we’ve used before. We can now craft a new dataset, apply permissions to it, build out an analysis, and publish and share it in a report twice as fast as we could before. The solution also gives our developers a better overall experience. For rapid development and deployment at scale, QuickSight performs extremely well at a very competitive price.

Because QuickSight is a serverless solution, we no longer need to worry about our BI overhead. With our previous solution, we had a lot of infrastructure, maintenance, and licensing costs. We have eliminated those challenges by implementing QuickSight. This is a key benefit because we’re an early stage company and our lean product development team can now focus on innovation instead of spinning up servers.

As our platform has become more sophisticated over the past few years, QuickSight has introduced vast number of great features for data catalog management, security, ML integrations, and look/feel that has really improved on our original solution’s BI capabilities. We look forward to continuing to use this powerful tool to help our customers get more out of their data.

About the Authors

David Atkins is the Co-Founder & Chief Operating Officer at Lucerna Health. Before co-founding Lucnera Health in 2018, David held multiple leadership roles in healthcare organizations, including spending six years at Centen Corporation as the Corporate Vice President of Enterprise Data and Analytic Solutions. Additionally, he served as the Provider Network Management Director at Anthem. When he isn’t spending time with his family, he can be found on the ski slopes or admiring his motorcycle, which he never rides.

Adriana Murillo is the Co-Founder & Chief Marketing Officer at Lucerna Health. Adriana has been involved in the healthcare industry for nearly 20 years. Before co-founding Lucerna Health, she founded Andes Unite, a marketing firm primarily serving healthcare provider organizations and health insurance plans. In addition, Adriana held leadership roles across market segment leadership, product development, and multicultural marketing at not-for-profit health solutions company Florida Blue. Adriana is a passionate cook who loves creating recipes and cooking for her family.

Amazon QuickSight: 2021 in review

Post Syndicated from Jose Kunnackal original https://aws.amazon.com/blogs/big-data/amazon-quicksight-2021-in-review/

With AWS re:Invent just around the corner, we at the Amazon QuickSight team have put together this post to provide you with a handy list of all the key updates this year. We’ve broken this post into three key sections: insights for every user, embedded analytics with QuickSight, scaling and governance.

Insights for every user

Amazon QuickSight allows every user in the organization to get a better understanding of data – through simple natural language questions and interactive dashboards for end-users, or ML-powered data exploration for business analysts. Developers can add embedded visualizations, dashboards, and Q to their apps to differentiate and enhance user experiences. Let’s take a look at the new experiences and features that you can deploy to your users from our 2021 updates.

Amazon QuickSight Q for true self-service for end-users

Earlier this year, Amazon QuickSight Q became generally available, making machine learning (ML) powered Q&A available for end-users to simply ask questions of their data—no training or preparation needed. End-users can go beyond what is presented in the dashboard with Q, avoiding the typical back-and-forth exchanges between the end-user and business intelligence (BI) teams, and the often weeks-long wait associated with adding a new dashboard or visual. It also allows end-users to more intuitively understand data, without having to interpret different visualizations, or understand filters or other elements in a traditional BI dashboard.

For example, a sales manager can simply ask the question “What were monthly sales in California this year?” to get a response from Q. To go deeper, they could follow up with “Who were the top five customers by sales in California?” Q presents a visual response to the user, no manual changes or analysis needed. Q can also be embedded into applications, allowing developers to augment and differentiate their application’s experiences. For more information on Q and how to get started, see Amazon QuickSight Q – Business Intelligence Using Natural Language Questions.

Free-form layouts, new chart types, and much more for pixel-perfect, interactive dashboards

Authors of QuickSight dashboards can now use the new free-form layout, which allows precise placement and sizing of dashboard components, overlay of charts and images, and conditional rendering of elements based on parameters. The combination of these features, along with granular customization options now available across charts (such as hiding grid lines, axis labels, and more) allow dashboards in QuickSight to be highly tailored to specific use cases or designs. The following screenshots show examples of customized dashboards using the free-form layout.

Authors can also use new visual types in QuickSight, such as the dual axis line chart and Sankey, to quickly add new ways of presenting data in dashboards. Sankey charts in particular have been very popular among QuickSight users, allowing visualization of cash flows, process steps, or visitor flows on a website—without having to extensively customize charts or license external plug-ins. We also added the option to add custom web components in dashboards, which allows you to embed images, videos, web pages, or external apps. When combined with the ability to pass parameter values into the custom components, this provides dashboard authors with a very broad set of creative possibilities.

The screenshot below shows an example of dual axis line chart (on the left) where high and volume metrics are mapped on two different scales within the same chart.

The screenshot below shows a Sankey chart showing consumption modes and channels for different energy sources.

The following screenshot shows an example of embedded web content (physical store navigation by different transit modes) within a QuickSight dashboard.

Tables and pivot tables have also received a broad set of updates, allowing authors to customize these extensively to meet organizational design standards, with new features allowing you to do the following:

  • Increase row height
  • Wrap text
  • Vertically align content
  • Customize background color, font color, borders, grid lines, and banding
  • Style and highlight your totals and subtotals
  • Style and hyperlink content to external resources
  • Add images within table cells

The following screenshot shows a customized table visual with links, images, font color, borders, grid lines, banding, text wrap, and custom row height.

The following screenshot shows a pivot table with custom styling for totals and sub-totals.

For deeper analytical exploration of data, we’ve enabled custom sorting of content in visualizations and pivot tables to allow well-defined presentation of content. Custom tooltips allow dashboard authors to add additional context beyond what’s readily available from the visual data on screen. You can now use parameters to dynamically populate titles and subtitles of visuals in dashboards. Time data can be aggregated to seconds, which is helpful for Internet of Things (IoT) and industrial use cases, and filters now allow exclusion of time fields completely to support business-facing use cases where day/month/year are the primary factors.

In filters, we’ve added wildcard search for faster filters for authors and end-users, multi-line filters to allow multiple values to be easily pasted for filtering, and an update to the relative date control to allow readers to select a custom date range over a relative period that has been selected besides selecting time period relative to today.

Consume and collaborate on dashboards

For easier collaboration within an organization, QuickSight now supports 1-click embedding of dashboards in wikis, SharePoint, Google sites, and more, requiring zero development efforts. This makes embedding dashboards as easy as embedding your favorite music video. We’ve also introduced link-based sharing of dashboards, which means that if desired, you can share a dashboard with all users in your organization without having to enable specific users or groups individually.

Threshold-based alerts in QuickSight allow dashboard readers to be notified when specific thresholds are breached by KPIs in a dashboard. Together with available ML-powered automated anomaly alerts, this allows readers to set up notification mechanisms when there are important expected or unexpected changes in data.

This year, we also launched the ability to share a view of a QuickSight dashboard, which allows readers to generate and provide a unique URL to others that captures the state of their filters. This allows for easy discussions around the shared view of data.

For offline access, readers can now receive PDF snapshots of their data, personalized to their specific roles and use cases. Authors set this up using the new personalized email reports feature, allowing unique emails to be sent to thousands of users at a predefined interval, each showing the end-user’s specific view of the data.

Create a reusable data architecture

Whether in a large organization or in a developer setting, creating and reusing datasets plays a significant role in ensuring that shared interpretations of data across the organization are accurate. To support this, QuickSight introduced dataset as a source, a new feature that allows a QuickSight dataset to be a source for creating another dataset. This creates a data lineage across the datasets. Updates related to calculated fields, data refreshes, row-level security, and column-level security can be configured to automatically propagate to datasets, providing a powerful data management tool. For more information, see Creating a Dataset Using an Existing Dataset in Amazon QuickSight.

As part of the logical information contained in the dataset, you can now create field folders to group fields, add metadata to fields, or include aggregate calculations in your dataset, which allows standardized calculations to be predefined and shared for easy inclusion in dashboards by authors.

Datasets are now also versioned, allowing authors and data owners to quickly switch from one version to another, with no API calls or changes needed.

The screenshot below shows an example of version/publishing history of dataset from the preparation screen.

Lastly, QuickSight continues to add to existing live analytics options across Amazon Redshift, Snowflake, SQL Server, Oracle, and other data warehouses with the addition of Exasol. This allows authors a range of options in exploring PB-scale datasets directly from the cloud.

Embed insights into apps

Customers such as 3M, Bolt, Blackboard, NFL, Comcast, and Panasonic Avionics use QuickSight for embedded analytics that serve their customers and partners, saving months and years of development and ongoing maintenance time that would otherwise be needed to create a rich analytics layer in their products. QuickSight also lets customers introduce the latest advancements in BI such as ML-powered Insights and Natural Language Querying in end-user facing applications.

Getting started with embedded dashboards for proofs of concept in software as a service (SaaS) app integrations now only takes minutes, with our new 1-click embedding option. For deeper app integration with transparent authentication, we support server-side calls to QuickSight for embedding, including a new tag-based row-level security option so you can easily add non-modifiable filters to your dashboard. This means that you can embed a multi-tenant embedded dashboard for hundreds of thousands of users without all the heavy lifting needed to duplicate and manage these users in QuickSight or another BI product.

Developers now also have the powerful differentiator of Q as part of QuickSight’s embedded feature set. Q can be embedded into applications, allowing end-users to simply ask questions of data, along with shared context of insights provided through embedded QuickSight dashboards in the app. Some sample embedded dashboards are available on DemoCentral.

For developers and independent software vendors looking to consolidate their interactive dashboards and email reports in QuickSight, we also introduced the ability to customize email reports. This allows customization of the from address, logo, background color, and footer in the email, as shown in the following screenshot.

When combined with the existing functionality of embedding the QuickSight authoring experience, QuickSight now provides developers with a strong suite of embedded analytics capabilities ranging from embedded interactive dashboards, embedded ML-powered Q&A with Q, embedded authoring, and customized email reports.

Scaling and governance

The fully managed, cloud-native architecture of QuickSight has been a delighter for our broad customer base—no servers or nodes to set up, no software updates or patches to manage, and absolutely no infrastructure to think about.

SPICE, the in-memory calculation engine in QuickSight, has been a key pillar of this serverless architecture, allowing data to scale from tens of users to hundreds of thousands without any customer intervention. We have doubled our SPICE data limits to 500 million rows of data per dataset, and now support incremental data refreshes for SQL-based data sources, such as Amazon Redshift, Amazon Athena, PostgreSQL, or Snowflake every 15 minutes, which cuts down time between data updates by 75%. Incremental refreshes also update SPICE datasets in a fraction of the time a full refresh would take, enabling access to the most recent insights much sooner.

This year, we introduced multiple simplifications and security mechanisms as you create your QuickSight account. Administrators signing up to QuickSight can pick from an existing role in their AWS account instead of QuickSight creating a custom service role for the account. This allows you to set up your own role for a group of codependent AWS services and QuickSight that you want to work together.

Admins can now use service control policies (SCPs) to control QuickSight sign-up options within your organization. For example, admins can set up service control policies that deny sign-ups for QuickSight Standard Edition and turn off the ability to invite any users other than those possible via federated single sign-on (SSO).

Admins can also set up QuickSight with SSO such that email addresses for end-users are automatically synced at first-time login, avoiding any manual errors during entry, and preventing use of personal email addresses. See Secure and simplify account setup and access management with new Amazon QuickSight administrative controls to learn more.

QuickSight admins can now also enforce source IP restrictions on access to the QuickSight UI, mobile app, as well as embedded pages. This allows you to secure your data within QuickSight and only keep it for trusted sources to access. See Use IP restrictions to control access to Amazon QuickSight to learn more.

Lastly, adding to our existing certifications (SOC, PCI, HIPAA, and more), we’re now FedRamp High compliant in US GovCloud (West), providing government workloads with the same serverless benefits that our customers have enjoyed.

Conclusion

QuickSight serves millions of dashboard views weekly, enabling data-driven decision-making in organizations of all sizes. Best Western Hotels and Resorts use QuickSight to improve operations worldwide, and provides hotel operators with a real-time look at key metrics that are critical to the business, with over 23,000 users of QuickSight. True Blue, a company focused on specialized workforce solutions, including staffing, talent management, and recruitment process outsourcing, uses QuickSight to deliver more accurate pricing and grow their business across over 500 locations. Vyaire Medical, a global company focused on breathing in every stage of life, used QuickSight to scale up production of ventilators by 20 times during the COVID-19 pandemic. Accelo, a leading cloud-based platform for managing client work from prospect to payment for professional services companies, chose QuickSight to provide embedded analytics to their end-users within their web application.

The features we discussed in this post provide a key summary of the changes over this year that have helped accelerate these and other customers adopt QuickSight.

At re:Invent 2021, you will hear from the NFL—the world’s biggest sports league—about how QuickSight powers their Next Gen Stats portal and provides the NFL clubs, broadcasters, and researchers with real-time and historical stats. You’ll also learn and how Q will revolutionize how data is consumed.

On the embedded analytics front, we will have 3M, a pioneer in global healthcare, and Bolt, which is redefining the online checkout space for millions of users, speak about how QuickSight powers analytics for their end-users and lets them scale to all of their users without any infrastructure overheads.

We also have Accenture and Amazon’s own finance team speaking about how QuickSight allows them to move away from legacy BI to a cloud-native future, while providing the governance and compliance needs typical in the finance world.

This year, you can simply register for re:Invent online and view these sessions from the comfort of your chair. We look forward to connecting with you at re:Invent, whether in-person at our booth and sessions or virtually, and as always look forward to your feedback.

About the Author

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.

Sahitya Pandiri is a technical program manager with Amazon Web Services.

Iterate confidently on Amazon QuickSight datasets with new Dataset Versions capability

Post Syndicated from Shailesh Chauhan original https://aws.amazon.com/blogs/big-data/iterate-confidently-on-amazon-quicksight-datasets-with-new-dataset-versions-capability/

Amazon QuickSight allows data owners and authors to create and model their data in QuickSight using datasets, which contain logical and semantic information about the data. Datasets can be created from a single or multiple data sources, and can be shared across the organization with strong controls around data access (object/row/column level security) and metadata included, and can be programmatically created or modified. QuickSight now supports dataset versioning, which allows dataset owners to see how a dataset has progressed, preview a version, or revert back to a stable working version in case something goes wrong. Dataset Versions gives you the confidence to experiment with your content, knowing that your older versions are available and you can easily revert back to it, if needed. For more details, see Dataset Versions.

In this post, we look at a use case of an author editing a dataset and how QuickSight makes it easy to iterate on your dataset definitions.

What is Dataset Versions?

Previously, changes made to a dataset weren’t tracked. Dataset authors would often make a change that would break the underlying dashboards, and they were often worried about the changes made to the dataset definitions. Dataset authors spent time figuring out how to fix the dataset, which could take significant time.

With Dataset Versions, each publish event associated with the dataset is tracked. Dataset authors can review previous versions of the dataset and how dataset has progressed. Each time someone publishes a dataset, QuickSight creates a new version, which becomes the active version. It makes the previous version the most recent version in the version list. With Dataset Versions, authors can restore back to a previous version if they encounter any issue with the current version.

To help you understand versions better, let’s take the following scenario. Imagine you have a dataset and have iterated on it by making changes over time. You have multiple dashboards based on this dataset. You just added a new table called regions to this dataset. QuickSight saves a new version, and dashboards dependent on it the dataset break due to the addition of this table. You realize that you added the wrong table—you were supposed to add the stateandcity table instead. Let’s see how the Dataset Versions feature comes to your rescue.

Access versions

To access your dataset versions, choose the Manage menu and Publishing History on the data prep page of the dataset.

A panel opens on the right for you to see all the versions. In the following screenshot, the current active version of the dataset is version 38—published on November 10, 2021. This is the version that is breaking your dependent dashboards.

See publishing history

As you make changes to the dataset and publish the changes, QuickSight creates a timeline of all the publishes. You see the publishing history with all the events tracked as a tile. You can choose the tile to preview a particular version and see the respective dataset definition at that time. You know that the dataset was working fine on October 18, 2021 (the previous version), and you choose Preview to verify the dataset definition.

Revert back

After you confirm the dataset definition, choose Revert to go back the previous stable version (published on October 18, 2021). QuickSight asks you to confirm, and you choose Publish. The dataset reverts back to the old working definition and the dependent dashboards are fixed.

Start a new version

Alternatively, as you’re previewing the previously published good version (version 37, published October 18, 2021), you can start fresh from that version. The previous version just had the retail_sales_new table, and you can add the correct table stateandcity to the dataset definition. When you choose Publish, a new version (version 39) is created, and all the dashboards have this new working version, thereby fixing them.

Conclusion

This post showed how the new Dataset Versions feature in QuickSight helps you easily iterate on your datasets, showing you how a dataset has progressed over time and allowing you to revert back to a specific version. Dataset Versions gives you the freedom to experiment with your content, knowing that your older versions are available and you can revert back to them, if required. Dataset Versions is now generally available in QuickSight Standard and Enterprise Editions in all QuickSight Regions. For further details, visit see Dataset Versions.

About the Authors

Shailesh Chauhan is a product manager for Amazon QuickSight, AWS’s cloud-native, fully managed SaaS BI service. Before QuickSight, Shailesh was global product lead at Uber for all data applications built from the ground up. Earlier, he was a founding team member at ThoughtSpot, where he created world’s first analytics search engine. Shailesh is passionate about building meaningful and impactful products from scratch. He looks forward to helping customers while working with people with a great mind and big heart.

Mayank Jain is a Software Development Manager at Amazon QuickSight. He leads the data preparation team that delivers an enterprise-ready platform to transform, define and organize data. Before QuickSight, he was Senior Software Engineer at Microsoft Bing where he developed core search experiences. Mayank is passionate about solving complex problems with simplistic user experience that can empower customer to be more productive.

Embed interactive dashboards in your apps and portals in minutes with Amazon QuickSight’s new 1-click embedding feature

Post Syndicated from Kareem Syed-Mohammed original https://aws.amazon.com/blogs/big-data/embed-interactive-dashboards-in-your-apps-and-portals-in-minutes-with-amazon-quicksights-new-1-click-embedding-feature/

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 directly within a QuickSight application, or embedded in web apps and portals.

QuickSight Enterprise Edition now supports 1-click embedding, a feature that allows you to embed rich, interactive dashboards in apps, wikis, and portals without needing to call embedding APIs. Authorized end-users can start accessing these dashboards instantly, without any server deployments or infrastructure licensing needed! 1-click embedding allows you to enable your users with insights in minutes.

In this post, we discuss the steps to implement this solution, the end-user experience, and a sample use case.

Solution overview

To implement the solution, we will walk through the following steps:

  1. Enable permissions on the dashboard (through the UI or API).
  2. Allow list the domain where you want to embed the dashboard in QuickSight.
  3. Embed the dashboard.

Step 1: Enable permissions on the dashboard

After you create a QuickSight dashboard, to enable access, open the dashboard and on the top right choose the share icon and choose Share dashboard.

This opens the share screen. By default, a dashboard in QuickSight isn’t shared with anyone and is only accessible to the owner. In the following screenshot, the dashboard is shared with (and therefore accessible by) admin-user1 (the owner of the dashboard).

You can search for individual users or groups in your account that you want to share this dashboard with. For example, see the following screenshot.

In this example, the owner of the dashboard searches for a user whose email starts with son, which returns three options. The listed users can be authors or readers in the account. When you choose ADD next to every search result, based on the role (author or reader), you can select the permission level (viewer or co-owner) and add the user to access the dashboard. Authors can be added to this dashboard as viewers or co-owners. Viewers can view, export, and print a dashboard. Co-owners can do all actions that viewers can do and can also edit, delete, or share the dashboard, or build new dashboards from this dashboard. Readers can be added only as viewers. Dashboard owners can similarly search for and add groups to access the dashboard.

The following screenshot shows all the added users with whom we want to share this dashboard.

You can also enable all users on your QuickSight account to access the dashboard by enabling access to Everyone in this account, as shown in the following screenshot.

When this option is enabled, users who haven’t been added explicitly to access the dashboard can now access the dashboard via the link available via the Copy option (available on the top of the Share dashboard page) or when embedded. To revoke this account-wide dashboard access to everyone on your account, you disable the same toggle.

Also, when this option is enabled, owners have the option to enable the dashboard to show this dashboard in all users’ QuickSight accounts, as shown in the following screenshot. Enabling this option ensures that the dashboard is visible in the Dashboards list for every user in the account. If this is disabled, they can still access the dashboard if they have the link, or if it’s embedded, but they can’t see it in their list of dashboards on the home screen.

Step 2: Allow list the embedding domain

The domain where the dashboard is to be embedded must be allow listed in QuickSight. For instructions, see Adding Domains for Embedded Users.

Step 3: Embed the dashboard

After you set your desired access to the dashboard, you can choose Copy embed code, which copies the embed code for that dashboard. This code embeds the dashboard when added to the internal application.

The copied embed code is similar to the following code (the QuickSight domain is the URL you use to access your QuickSight account):

    <iframe
        width="960"
        height="720"
        src="https://quicksightdomain/sn/embed/share/accounts/
        <accountid>/dashboards/<dashboardid>">
    </iframe>

Embed a dashboard in an HTML page

To embed the dashboard in an HTML page, open the HTML of the page where you want to embed the dashboard and enter the copied embed code into the HTML code.

Now, let’s look at some common embedding scenarios in an organization.

Embed a dashboard in a Google site

If you have your internal applications built on Google sites, to embed your dashboard, open the page on Google site, and choose Insert and Embed. A pop-up window appears with a prompt to enter a URL or embed code. Choose Embed code and enter the copied embed code in the text box.

Make sure to allow list the following domains in QuickSight when embedding in Google sites https://googleusercontent.com (enable subdomains), https://www.gstatic.com and https://sites.google.com.

Embed a dashboard in a SharePoint site

You can embed a dashboard when you’re creating a SharePoint site (template type Publishing). Choose New to create a new page.

On the Insert ribbon, choose Embed Code.

In the pop-up that opens, insert the embed code that you copied.

If you’re embedding dashboards in software as a service (SaaS) apps or portals that have their own authentication schemes, you can continue using these and seamlessly serve dashboard to the users. For more information, see Embed multi-tenant dashboards in SaaS apps using Amazon QuickSight without provisioning or managing users.

End-user experience

After you embed the dashboard in your application, users that you enabled earlier can access it. In this section, we walk through the user experience with and without single sign-on (SSO) integration with QuickSight.

Experience when QuickSight has SSO integration enabled

When SSO is enabled, when users access the application, they are single signed-on to QuickSight and aren’t presented with the authentication pop-up. If users have access to the dashboard, they see the data on the dashboard and can interact with it. If they don’t have access to the dashboard, they see a message that they’re not authorized to view the dashboard.

In the following screenshot, the user is authenticated with SSO and sees the dashboard.

You can set up SP-initiated SSO if you’re using an existing identity provider (IdP) such as Ping, Okta, or Azure AD. To learn more about enabling SSO on QuickSight, see Using Identity Federation and Single Sign-on (SSO) with Amazon QuickSight and Federate Amazon QuickSight access with Okta.

Experience when QuickSight doesn’t have SSO integration enabled

Without SSO integration, when end-users access the application, they see the embedded dashboard (requiring authentication) along with a pop-up to authenticate into QuickSight. After users enter their QuickSight credentials, the pop-up closes and the dashboard is loaded on the application. If the user has access to the dashboard, they see the data on the dashboard and can interact with it.

The following screenshot shows a dashboard that is embedded in an internal SharePoint site that tracks weekly shipped orders, and users are asked to authenticate.

The following is the pop-up to authenticate.

Use case

In this section, we explore an example use case of a small enterprise, a shoe retailer named WonderShoes. They have recently ventured into telecommerce (selling via phone channel) and have set up call centers in their company to take customer calls from those who are interested in buying shoes.

They’re embedding a dashboard in their internal site that tracks the call volume, other call-related metrics, and sales metrics that they measure on daily business. They have built out a dashboard and enabled all their internal users to be able to access this dashboard. With the 1-click embed feature, they have embedded the following embed code in their internal application’s page:

<iframe
        width="800" 
        height="600" 
        src="https://us-east-1.quicksight.aws.amazon.com/sn/embed/share/
        accounts/ACCOUNTID/dashboards/DASHBOARDID> 
</iframe>

The following screenshot shows their internal application with the embedded dashboard and authentication prompt.

Users are authenticated and then can see the dashboard. If they come back to the site with valid authentication cookies, they can see the dashboard without needing to authenticate again.

This feature enabled WonderShoes to embed their dashboard quickly and have all their internal employees access the embedded dashboard. They can now gather rich insights and make quick data-driven business decisions to grow their new telecommerce business division.

Conclusion

With 1-click enterprise embedding, enterprises can now embed rich and interactive QuickSight dashboards quickly and easily. This enables you to share important metrics and data insights with all users in your account with a click of a button – all without any infrastructure setup or management while scaling to millions of users. QuickSight also supports embedding in SaaS apps without any user management needed. To learn more about this, read this blogpost.

For more updates about QuickSight embedded analytics, see What’s New in the Amazon QuickSight User Guide.

About the Authors

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.

Kenz Shane is a UI Designer for Amazon QuickSight. As part of the product’s Business Intelligence User Experience (BIUX) team, she specializes in creating customer-focused visual interfaces. Previously, she worked with the Experience Innovation Group at Dell, serving as a subject matter expert in enterprise-grade user interface (UI) design, accessible data visualization, and design systems. Kenz has provided art direction and design for clients across multiple industries, including Nordstrom, Columbia Hospitality, AIGA, and Warner Bros.

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

Send custom branded email reports from Amazon QuickSight

Post Syndicated from Kareem Syed-Mohammed original https://aws.amazon.com/blogs/big-data/send-custom-branded-email-reports-from-amazon-quicksight/

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 directly within QuickSight application, or embedded in web apps and portals.

QuickSight Enterprise Edition now supports the ability to send custom branded email reports. You can customize the email sender domain for email reports sent from QuickSight, along with the logo and header color of the email, as well as footer text of the email. If you have your dashboard embedded in your own application, you can also customize the URL to open the dashboard from the email to the URL of your application. This lets you customize emails to reflect your corporate branding, whether you want to send these reports to 1000s of your internal users or external customers.

In this post, we will go through the following:

  1. Steps to implement the solution
    1. Create a customized email template
    2. Create an email schedule and subscribe email recipients
  2. End user experience
  3. Sample use case

Solution overview

Step 1: Create a customized email template

This new feature lets you customize your email with the following customization options:

  1. Custom sender email address
  2. Custom logo in the email header and custom header color
  3. Custom link to open the dashboard (if your dashboard is embedded in your own application)
  4. Custom footer

You can customize all or any of these options. To customize, create an email template in your QuickSight account, which will be used when sending email reports for any dashboard to any user. This email template is specific to the AWS region and account it is created in.

Log in to QuickSight as an admin, and select your name in the top right, then in the menu select “Manage QuickSight” as shown in the following screenshot:

In the next screen, select “Account Customization”, and you will see the available account customization options. Under the “Email report template” section, select “Update” as shown in the following screenshot. You must have the right IAM Identity-Based Policies assigned to you to create or edit the template.

In the next screen, you can set customizations that we will see one by one.

Customize sender email address

This option lets you set a custom email address or use QuickSight’s email address <[email protected]> to send email reports. To select sending via QuickSight email address, select the radio button for QuickSight.

To send a custom email, select the radio button for custom email setting. At this time, only verified email addresses can be used for a custom email address. SES and QuickSight must be in the same AWS account and region. If you do not have an SES account, then you can get started <HERE> with SES’ free tier of XX. Steps to add a custom email address.

  1. Add a verified SES email address and click “Verify email”. If you get an error, then refer here for creating a verified SES email address.
  2. Once the email address is verified, you must authorize QuickSight to send emails on your behalf. To do this, copy the given “Authorization Policy”, and add it as a “Sending authorization policy” for your verified email address in SES. Refer here to learn about SES sending authorization policy.

    As we can see in the screenshot above, once the authorization policy is verified, QuickSight is authorized to send email using the SES email address.
  3. You can set a friendly name for the email address as shown in the following screenshot.

Customize logo

Email reports from QuickSight have a QuickSight logo in the header of the email body. You can choose to select a custom logo, use QuickSight logo, or have no logo by selecting the corresponding radio button.

When you select the “Custom logo” option, you can select your own logo (for format jpg, jpeg, or png) and a maximum file size of 1MB. Your logo will be scaled to a height of 32px, maintaining the aspect ratio. When you upload the logo image, you get an option to set the background color (as a HEX code) of the header in the email report.

Select where the dashboard opens

Email reports have an image of the first sheet of the QuickSight dashboards. In order for the recipient to interact with the dashboard, email reports also provide a link to open the dashboard. By default, this link opens the dashboard in the QuickSight application. Now you can select where the dashboard opens. If you have embedded the dashboard in your application, then you can provide the URL of your application. Moreover, you can choose to hide the option to disable opening the dashboard from the email entirely. Please see the following screenshot for reference.

If you want to add your custom link, then you will have to add the following query parameters – account-id, dashboard-id, and region – to your link. QuickSight will populate these parameters at runtime, and when your customers select the open dashboard link from the email, they will be taken to the link you have provided. With the account-id, dashboard-id, and region now available with the link, you can provide logic to take your customers to where you have embedded the dashboard in your application.

Custom footer

Email reports default QuickSight footers have content and a link related to QuickSight and QuickSight application. You have an option to customize the footer or hide the entire footer. Please refer to the following screenshot for reference.

If you select the option to set a custom footer, then you can provide custom text and hyperlink content in the textbox. At this time, we only allow plain text.

Step 2: Create an email schedule and subscribe recipients

Once your QuickSight account has an email template saved, any email report sent in the same AWS region will use this template. To send an email report, the author of the dashboard should create an email schedule for the dashboard and assign recipients to that schedule.

To set a schedule, the  dashboard author should open the dashboard in QuickSight application, select “Share” in the top right, and select “Email report” in the menu. Please refer to the following screenshot for reference.

You will be taken to the “Edit email report” screen, where you can create a schedule for the email to be sent and add email recipients. Please refer to this documentation on sending reports by email and this post for sending personalized email reports.

If you are embedding dashboards in your application, then your readers cannot subscribe to the schedule from the embedded dashboard. Authors must add those readers to the recipient list through the steps stated above. Therefore, your readers must be provisioned in QuickSight.

End user experience

The end user gets the email as per the schedule set. If the email template has been set, then recipients get the look and feel of the email based on the customization done on the template. The following screenshot shows the email with a custom look and feel.

As you can see, this email has the following:

  1. From address customized to [email protected] with a friendly name, “data-insights-team”
  2. Logo customized to a brand logo, and header customized to the brand green shade
  3. Dashboard open link customized to take customers to your app if the dashboard is embedded in that app
  4. Footer customized with a custom message

Use case

ShipPronto is a logistics service provider for heavy machinery. It has many customers that store their heavy machinery at ShipPronto’s warehouse. When customers get purchase orders on these machineries, they have ShipPronto fulfill those orders on their behalf from its warehouse. ShipPronto has an application where each customer can login and see rich data on their order shipment and machinery quantity at the warehouse. ShipPronto uses QuickSight dashboard embedded in its application to provide the insights. Furthermore, it sends daily emails to its customers on this dashboard. It’s using the email customization feature of QuickSight to customize the look and feel of the email so that customers receiving the email get a seamless experience.

Below is the customized email that their customers receive daily with the sender email address, logo, header color, and footer customized.

When customers click on the “Open Dashboard” link in the email, they are taken to ShipPronto’s app, on which they must log in, as shown in the following screenshot.

Once the customers log in, based on the query string parameters that were passed along with the custom URL (which was set as part of the URL, to open the dashboard in the email template), ShipPronto can take its customers to the page where they have this dashboard embedded.

This experience means that ShipPronto’s end users see the ShipPronto branded email and get a seamless experience where they access the embedded dashboard, in the application, from the email.

Conclusion

Email customizations let you send branded email reports to your customers, thereby enabling a seamless experience when customers are accessing the email or the application where the dashboard is embedded. And all of this is done 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 Author

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.

Kenz Shane is a UI Designer for Amazon QuickSight. As part of the product’s Business Intelligence User Experience (BIUX) team, she specializes in creating customer-focused visual interfaces. Previously, she worked with the Experience Innovation Group at Dell, serving as a subject matter expert in enterprise-grade user interface (UI) design, accessible data visualization, and design systems. Kenz has provided art direction and design for clients across multiple industries, including Nordstrom, Columbia Hospitality, AIGA, and Warner Bros.

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

Send personalized email reports with Amazon QuickSight

Post Syndicated from Sahitya Pandiri original https://aws.amazon.com/blogs/big-data/send-personalized-email-reports-with-amazon-quicksight/

Amazon QuickSight now supports personalization of email reports by user, which allows you to send customized snapshots of data in either PDF or image formats. This allows you to create a single dashboard that you can configure to load with different defaults for each user, providing a customized view of the dashboard in both email and interactive formats. In this post, we walk you through how to roll out customized daily, weekly, or monthly reports for thousands of users – without any servers to set up or manage.

Solution overview

QuickSight supports personalized emails via row-level or column-level security, or dynamic defaults for parameters. You can use row-level or column-level security when you want to restrict data available on dashboards by user, and only present data that they are authorized to see. Dynamic defaults, on the other hand, allow users to access all the data but make sure that each user gets a personalized view without data restrictions if they wish to browse other views of the data.

When used with emails, both models allow you to provide personalized email reports for each user. Dynamic defaults, however, also allow you to handle conditional rendering of visuals using parameter settings that allow you to personalize dashboards and email reports by the user by showing and hiding visuals as needed.

Let’s start with the following example dashboard, which shows sales insights and trends across different segments, categories, and states for any given date.

This dashboard is built with the new free-form layout that allows you to build pixel-perfect dashboards. You can define visual placement with X and Y coordinates, define height and width of visuals at the pixel level, and overlay visuals if needed. In addition to flexible visual placements, you can also set background, borders on visuals and filter controls. To learn more about building dashboards with free-form layouts, see Create stunning, pixel perfect dashboards with the new free-form layout mode in Amazon QuickSight.

Personalizing your dashboard

You can further customize this view for your readers so it always shows insights relevant to them on the dashboard, email reports, and the PDF attached to the email.

To personalize the dashboard, create a data table with dynamic default rules similar to the following table. In this table, you need to have the following columns: UserID for QuickSight usernames of dashboard readers, followed by one column each for parameters to set defaults to. For example, after we apply the following dynamic defaults dataset to our sample dashboard, when Ben Brown with username [email protected] accesses the dashboard, it shows business metrics for the Strategic segment within Aluminium category and Washington state.

To apply this dynamic default table to the dashboard, complete the following steps:

  1. Create a dataset with your dynamic default table on QuickSight.

This can be a SPICE or direct query dataset depending on where the rules are and how frequently the rules are updated. If rules are maintained in your backend source tables and updated often, create a direct query dataset. If the rules are uploaded from a flat file or are maintained in your backend source tables but not updated often, you can keep them in SPICE and schedule a refresh if needed.

  1. Add the dynamic default dataset to the analysis.
  2. Navigate to the analysis you want to set default rules on.
  3. In the navigation pane, choose Parameters.
  4. Choose the parameter you want to set defaults on and choose Set a dynamic default.
  5. Configure dynamic defaults by choosing the rules dataset, and mapping the user name, group name, and default columns to those from the dataset.

You can set dynamic defaults for individual users and also user groups.

  1. Repeat these steps for all parameters you want to set dynamic defaults on.

You can also add these parameters within titles and subtitles for a personalized view so readers know what fields the dashboard is filtered by.

Show and hide visuals

Additionally, you can conditionally show and hide visuals based on parameter values. You can use this in many creative ways, such as changing the visual type based on the parameter selected. For example, selecting Strategic as the segment could show a box plot of order quantity range grouped by Category. If you set the segment to SMB, you can replace the box plot with a different chart type. To conditionally show and hide visuals, complete the following steps:

  1. Create the visual you want to conditionally show and hide on the analysis.
  2. Click the pencil icon to edit the visual’s settings.
  3. Expand Rules and turn Hide this visual by default on.

In the following dashboard, the box plot is hidden by default, and is configured to show only when the segment parameter is set to Strategic.

  1. Similarly, create a scatter plot and configure the dashboard to hide this visual by default and only show when the segment parameter is set to SMB.
  2. Overlap this visual with the box plot visual so that either visual shows within this placement depending on the segment selected.

Publish and schedule email reports

Finally, publish the dashboard and share with all your readers, and schedule an email report and also configure to attach dashboard PDF to the report.

Readers now receive different views of the same dashboard, personalized to them, and showing metrics on the business sectors they care about.

For our example dashboard, Ben Brown receives an email report with business metrics for the Strategic segment and Aluminum category within Washington.

Anna Scott receives an email report of the same dashboard with for the SMB segment, Copper & Diamond category, and California state.

Conclusion

With the support for dynamic defaults on email reports, free form layout, and condition rendering of visuals, QuickSight allows you to build and deliver custom dashboards with personalized insights with end-users, directly to their email inboxes.

Learn more about other core capabilities such as Natural Language Querying with QuickSight Q and Embedded Analytics here.

About the Author

Sahitya Pandiri is a technical program manager with Amazon Web Services.

Accelo uses Amazon QuickSight to accelerate time to value in delivering embedded analytics to professional services businesses

Post Syndicated from Mahlon Duke original https://aws.amazon.com/blogs/big-data/accelo-uses-amazon-quicksight-to-accelerate-time-to-value-in-delivering-embedded-analytics-to-professional-services-businesses/

This is a guest post by Accelo. In their own words, “Accelo is the leading cloud-based platform for managing client work, from prospect to payment, for professional services companies. Each month, tens of thousands of Accelo users across 43 countries create more than 3 million activities, log 1.2 million hours of work, and generate over $140 million in invoices.”

Imagine driving a car with a blacked-out windshield. It sounds terrifying, but it’s the way things are for most small businesses. While they look into the rear-view mirror to see where they’ve been, they lack visibility into what’s ahead of them. The lack of real-time data and reliable forecasts leaves critical decisions like investment, hiring, and resourcing to “gut feel.” An industry survey conducted by Accelo shows 67% of senior leaders don’t have visibility into team utilization, and 54% of them can’t track client project budgets, much less profitability.

Professional services businesses generate most of their revenue directly from billable work they do for clients every day. Because no two clients, projects, or team members are the same, real-time and actionable insight is paramount to ensure happy clients and a successful, profitable business. A big part of the problem is that many businesses are trying to manage their client work with a cocktail of different, disconnected systems. No wonder KPMG found that 56% of CEOs have little confidence in the integrity of the data they’re using for decision-making.

Accelo’s mission is to solve this problem by giving businesses an integrated system to manage all their client work, from prospect to payment. By combining what have historically been disparate parts of the business—CRM, sales, project management, time tracking, client support, and billing—Accelo becomes the single source of truth for your business’s most important data.

Even with a trustworthy, automated and integrated system, decision makers still need to harness the data so they see what’s in front of them and can anticipate for the future. Accelo devoted all our resources and expertise to building a complete client work management platform, made up of essential products to achieve the greatest profitability. We recognized that in order to make the platform most effective, users needed to be empowered with the strongest analytics and actionable insights for strategic decision making. This drove us to seek out a leading BI solution that could seamlessly integrate with our platform and create the greatest user experience. Our objective was to ensure that Accelo users had access to the best BI tool without requiring them to spend more of their valuable time learning yet another tool – not to mention another login. We needed a powerful embedded analytics solution.

We evaluated dozens of leading BI and embedded reporting solutions, and Amazon QuickSight was the clear winner. In this post, we discuss why, and how QuickSight accelerated our time to value in delivering embedded analytics to our users.

Data drives insights

Even today, many organizations track their work manually. They extract data from different systems that don’t talk to each other, and manually manipulate it in spreadsheets, which wastes time and introduces the kinds of data integrity problems that cause CEOs to lose their confidence. As companies grow, these manual and error-prone approaches don’t scale with them, and the sheer level of effort required to keep data up to date can easily result in leaders just giving up.

With this in mind, Accelo’s embedded analytics solution was built from the ground up to grow with us and with our users. As a part of the AWS family, QuickSight eliminated one of the biggest hurdles for embedded analytics through its SPICE storage system. SPICE enables us to create unlimited, purpose-built datasets that are hosted in Amazon’s dynamic storage infrastructure. These smaller datasets load more quickly than your typical monolithic database, and can be updated as often as we need, all at an affordable per-gigabyte rate. This allows us to provide real-time analytics to our users swiftly, accurately, and economically.

“Being able to rely on Accelo to tell us everything about our projects saves us a lot of time, instead of having to go in and download a lot of information to create a spreadsheet to do any kind of analysis,” says Katherine Jonelis, Director of Operations, MHA Consulting. “My boss loves the dashboards. He loves just being able to look at that and instantly know, ‘Here’s where we are.’”

In addition to powering analytics for our users, QuickSight also helps our internal teams identify and track vital KPIs, which historically has been done via third-party apps. These metrics can cover anything, from calculating the effective billable rate across hundreds of projects and thousands of time entries, to determining how much time is left for the team to finish their tasks profitably and on budget. Because the reports are embedded directly in Accelo, which already houses all the data, it was easy for our team to adapt to the new reports and require minimal training.

Integrated vs. embedded

One of the most important factors in our evaluation of BI platforms was the time to value. We asked ourselves two questions: How long would it take to have the solution up and running, and how long would it take for our users to see value from it?

While there are plenty of powerful third-party, integrated BI products out there, they often require a complete integration, adding authentication and configuration on top of basic data extraction and transformations. This makes them an unattractive option, especially in an increasingly security-focused landscape. Meanwhile, most of the embedded products we evaluated required a time to launch that numbered in the months—spending time on infrastructure, data sources, and more. And that’s without considering the infrastructure and engineering costs of ongoing maintenance. One key benefit that propels QuickSight above other products is that it allowed us to reduce that setup time from months to weeks, and completely eliminated any configuration work for the end-user. This is possible thanks to built-in tools like native connections for AWS data sources, row-level security for datasets, and a simple user provisioning process.

Developer hours can be expensive, and are always in high demand. Even in a responsive and agile development environment like Accelo’s, development work still requires lead time before it can be scheduled and completed. Engineering resources are also finite—if they’re working on one thing today, something else is probably going into the backlog. QuickSight enables us to eliminate this bottleneck by shifting the task of managing these analytics from developers to data analysts. We used QuickSight to easily create datasets and reports, and placed a simple API call to embed them for our clients so they can start using them instantly. Now we’re able to quickly respond to our users’ ever-changing needs without requiring developers. That further improves the speed and quality of our data by using both the analysts’ general expertise with data visualization and their unique knowledge of Accelo’s schema. Today, all of Accelo’s reports are created and deployed through QuickSight. We’re able to accommodate dozens of custom requests each month for improvements—major and minor—without ever needing to involve a developer.

Implementation and training were also key considerations during our evaluation. Our customers are busy running their businesses. The last thing they want is to get trained on a new tool, not to mention the typically high cost associated with implementation. As a turnkey solution that requires no configuration and minimal education, QuickSight was the clear winner.

Delivering value in an agile environment

It’s no secret that employees dislike timesheets and would rather spend time working with their clients. For many services companies, logged time is how they bill their clients and get paid. Therefore, it’s vital that employees log all their hours. To make that process as painless as possible, Accelo offers several tools that minimize the amount of work it takes an employee to log their time. For example, the Auto Scheduling tool automatically builds out employees’ schedules based on the work they’re assigned, and logs their time with a single click. Inevitably, however, someone always forgets to log their time, leading to lost revenue.

To address this issue, Accelo built the Missing Time report, which pulls hundreds of thousands of time entries, complex work schedules, and even holiday and PTO time together to offer answers to these questions: Who hasn’t logged their time? How much time is missing? And from what time period?

Every business needs to know whether they’re profitable. Professional services businesses are unique in that profitability is tied directly to their individual clients and the relationships with them. Some clients may generate high revenues but require so much extra maintenance that they become unprofitable. On the other hand, low-profile clients that don’t require a lot of attention can significantly contribute to the business’s bottom line. By having all the client data under one roof, these centralized and embedded reports can provide visibility into your budgets, time entries, work status, and team utilization. This makes it possible to make real-time, data-driven actions without having to spend all day to get the data.

Summary

Clean and holistic data fosters deep insights that can lead to higher margins and profits. We’re excited to partner with AWS and QuickSight to provide professional services businesses with real-time insights into their operations so they can become truly data driven, effortlessly. Learn more about Accelo, and Amazon QuickSight Embedded Analytics!

About the Authors

Mahlon Duke, Accelo Product Manager of BI and Data.

Geoff McQueen, Accelo Founder and CEO.

Create larger SPICE datasets and refresh data faster in Amazon QuickSight with new SPICE features

Post Syndicated from Shailesh Chauhan original https://aws.amazon.com/blogs/big-data/create-larger-spice-datasets-and-refresh-data-faster-in-amazon-quicksight-with-new-spice-features/

Amazon QuickSight is a scalable business intelligence (BI) service built for the cloud, which allows insights to be shared with all users in the organization. QuickSight offers SPICE, an in-memory, cloud-native data store that allows end-users to interactively explore data. SPICE provides consistently fast query performance and automatically scales for high concurrency. With SPICE, you save time and cost because you don’t need to retrieve data from the data source (whether a database or data warehouse) every time you change an analysis or update a visual, and you remove the load of concurrent access or analytical complexity off the underlying data source with the data.

Today, we’re introducing incremental refresh in SPICE, with a refresh rate of 15 minutes (four times faster than before), which improves freshness of data in SPICE. In addition, we’re doubling SPICE limits on a per dataset basis to 500 million rows (twice that of our previous 250 million row limit). In this post, we walk through these new capabilities and how you can use them to create SPICE datasets that can help you scale your data to all your users.

What’s new with QuickSight SPICE?

We’ve added the following capabilities to QuickSight:

  • Incremental refresh – QuickSight now supports incrementally loading new data to SPICE datasets without needing to refresh the full set of data. With incremental refresh, you can update SPICE datasets in a fraction of the time a full refresh would take, enabling access to the most recent insights much sooner. You can schedule incremental refresh to run up to every 15 minutes on a dataset on SQL-based data sources, such as Amazon Redshift, Amazon Athena, PostgreSQL, Microsoft SQL Server, or Snowflake.
  • 500 million row SPICE capacity – The QuickSight SPICE engine now supports datasets up to 500 million rows or 500 GB in size. This change lets you use SPICE for datasets twice as large than before.

In the next sections, we show you how to get started with incremental refresh and 500 million row SPICE capacity.

Create large datasets

Let’s say you’re part of the central data team that has access to data tables in data sources. You want to create a central dataset for analysts. SPICE can now scale to double the capacity, so you can create a large scaled dataset rather than create and maintain several unconnected datasets. You can bring in up to 32 tables (from different data sources) in a single dataset to a total of 500 million rows. You can enjoy the double capacity of SPICE with no extra step—it’s automatically available. To create a dataset, simply choose New Dataset on the Data page. On the Data Prep page for the new dataset, choose Add data to add tables to a single dataset.

Set up incremental refresh

With incremental refresh, QuickSight now allows you to ingest data incrementally for your SQL-based sources (such as Amazon Redshift, Athena, PostgreSQL, or Snowflake) in a specified time period. On the Datasets page, choose the dataset, and choose Refresh now or Schedule a refresh.

For Refresh type, select Incremental refresh.

Configure look-back window

While setting up incremental refresh, you have to specify a look-back window (for example, 1 day, 1 week, 6 hours) in which new rows are found, and modified and deleted rows sync. This means that less data needs to be queried and transferred for each refresh, thereby increasing the speed at which ingestions can complete.

Let’s walk through an example to illustrate the concept. We have a dataset that contains 6 months’ worth of sales records: 180,000 records (1,000 records per day). Right now, the dataset contains data from January 1 to June 30, and today is July 1. I run an incremental refresh with a look-back window of 7 days. QuickSight queries the database asking for all data since June 24 (7 days ago): 7,000 records. All the changes since June 24, including deleted, updated, and added data, are propagated into SPICE. The next day, July 2, QuickSight does the same, but querying from June 25 (7,000 records). The end result is that rather than having to ingest 180,000 records every day, you only have to process 7,000 records.

You can set up a look-back window as part of setting up your incremental refresh. After you select Incremental refresh from the steps in the preceding section, choose Configure.

You can choose all eligible date columns to use for look-back and the window size, which QuickSight uses to query for that range. Then choose Submit.

Schedule an incremental refresh

A scheduled SQL incremental refresh allows you to regularly ingest data from a data source to SPICE, incrementally. To set up a scheduled SQL incremental refresh, similar to manual incremental refresh, if this is a first-time setup, you’re prompted to set up a look-back window. After configuration, choose the time zone, repetition interval, and starting time and choose Create.

The scheduled refresh begins at the time you specified.

Set up full ingestion

Previously, for SPICE datasets, the only update mechanism in QuickSight was a full refresh. All the data defined by the dataset was queried and transferred into the dataset from its source, fully replacing what previously existed. With incremental refresh, you can update your data every 15 minutes. However, we still recommend a full refresh to make sure your dataset is in sync with the source. You can set up a full ingestion every week on a weekend to not disrupt any business workflows.

Conclusion

With incremental refresh and double SPICE capacity, QuickSight enables you to create datasets to cater to your scaling business needs in the following ways:

  • Faster and reliable refreshes – Incremental refreshes are faster because only the most recent data needs to be refreshed and not the entire dataset. Additionally, the refreshes are also more reliable because you don’t need to spend time on long-running queries or any potential network disruptions.
  • Large datasets – SPICE can now scale up to 500 million rows, but you don’t have to spend time updating, because you can update incrementally and don’t need to refresh the entire dataset.
  • Easy setup with fewer resources – With incremental refresh, you have less data to refresh. This reduces overall consumption of resources needed. The setup process is also much simpler with scheduled incremental refresh.

QuickSight’s SPICE incremental refresh and 500 million row SPICE capacity can help you create scalable and reliable datasets without putting a strain on underlying data sources. These features are now generally available in QuickSight Enterprise Editions in all Regions. Go ahead and try it out! To learn more about refreshing data in QuickSight, see Refreshing Data.

About the Authors

Shailesh Chauhan is a Product Manager at Amazon QuickSight, AWS’ cloud-native, fully managed BI service. Before QuickSight, Shailesh was global product lead at Uber for all data applications built from ground-up. Earlier, he was a founding team member at ThoughtSpot, where he created world’s first analytics search engine. Shailesh is passionate about building meaningful and impactful products from scratch. He looks forward to helping customers while working with people with great mind and big heart.

Anilkumar Senesetti is a Software Development Manager at AWS QuickSight. He leads the Data Ingestion (DI) team that delivers solutions to accelerate ingestion of customers data into SPICE ensuring correctness, durability, consistency and security of the data. With 15 years of industry experience in business intelligence domain, he provides valuable insights across layers to deliver solutions that improve customer experience. He is passionate about predictive analytics and outside of the work, he enjoys building features on astrological website that he owns.

Use IP restrictions to control access to Amazon QuickSight

Post Syndicated from Mayank Agarwal original https://aws.amazon.com/blogs/big-data/use-ip-restrictions-to-control-access-to-amazon-quicksight/

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 the QuickSight interface, or embedded in software as a service (SaaS) applications or web portals. Unlike many of the other solutions in the market today, QuickSight requires no server deployments or management for scaling to tens of thousands of users, and authors build dashboards using a web-based interface, with out any client downloads needed. QuickSight also supports private VPC connectivity to AWS databases and analytics services such as Amazon Relational Database Service (Amazon RDS) and Amazon Redshift, and AWS Identity and Access Management (IAM) permissions-based access to Amazon Simple Storage Service (Amazon S3) and Amazon Athena, making it secure and easy to access data in AWS via QuickSight.

In this post, we explore a new feature in QuickSight that allows administrators to further secure access to QuickSight with IP-based access restrictions. With this feature, you can enforce source IP restrictions on access to the QuickSight UI, mobile app, as well as embedded pages. For more information, see Turning On Internet Protocol (IP) Restrictions in Amazon QuickSight.

Solution overview

Our use case features OkTank, a fictional enterprise in the fintech space. They have hundreds of users across internal teams such as finance and HR that use QuickSight for their BI gathering needs. Employees in these teams use their respective QuickSight credentials to log in to QuickSight and do their work. In addition to the team-specific BI dashboards, some common dashboards are accessible to all the employees in the organization. These dashboards reflect overall business metrics such as number of active customers and the company’s growth over time.

Employees with access to the common dashboard and their QuickSight account are sometimes working with sensitive data, and in certain cases end-user data as well. Even though they need to have login credentials to use QuickSight, QuickSight is accessible outside of OkTank’s VPN network.

OkTank’s information security team would like to ensure employees only access QuickSight or view common dashboards while they’re within the company’s private network via VPN.

Enable IP-based restrictions

To enable IP-based restrictions, OkTank’s IT administrator with IAM credentials who has access to QuickSight admin console takes the following steps:

  1. On the QuickSight console, on the user name menu, choose Manage QuickSight.
  2. In the navigation pane, choose Security & permissions.
  3. Under IP restrictions, choose Manage.
  4. For IP address, enter the IP address which is to be allowed access in CIDR format.
  5. Choose Add.
  6. To edit an existing rule, choose the pencil icon next to the rule.
  7. To delete an existing rule, choose the trash icon next to the rule.
  8. Make sure to add your own IP address to the list to prevent being locked out yourself.
  9. After you add, edit or delete IP address rules, choose Save changes.
  10. Turn on the rules to start your IP-based restriction.

When the IP restriction is turned on and the list of allowed IP addresses in CIDR format is in place, any OkTank employee trying to access QuickSight when not logged in to OkTank’s VPN (regardless of their role of admin, author, or reader) is presented with an error page.

IP restriction can be turned on or off and rules can be viewed and edited by using following public APIs

Conclusion

With IP restrictions in place, administrators can now strengthen controls around QuickSight access by ensuring that only employees logged in the organization’s VPN network can access QuickSight. Stay tuned for more new admin capabilities, and follow What’s New with Analytics for the latest on QuickSight.

About the Author

Mayank Agarwal is a product manager for Amazon QuickSight, AWS’ cloud-native, fully managed BI service. He focuses on account administration, governance and developer experience. He started his career as an embedded software engineer developing handheld devices. Prior to QuickSight he was leading engineering teams at Credence ID, developing custom mobile embedded device and web solutions using AWS services that make biometric enrollment and identification fast, intuitive, and cost-effective for Government sector, healthcare and transaction security applications.

Design captivating Amazon QuickSight dashboards with new Table and Pivot Table features

Post Syndicated from Rushabh Vora original https://aws.amazon.com/blogs/big-data/design-captivating-amazon-quicksight-dashboards-with-new-table-and-pivot-table-features/

Amazon QuickSight is a fast and cloud-powered business intelligence (BI) service that makes it easy to create and deliver insights to everyone in your organization without any servers or infrastructure. QuickSight dashboards can also be embedded into applications and portals to deliver insights to external stakeholders. And QuickSight Q lets end-users simply ask questions in natural language to get machine learning (ML)-powered visual responses to their questions.

Within QuickSight, tables and pivot tables are among the most-used visuals and almost always present in dashboards. These visuals let data be presented in a clear and easy-to-read format with filtering, conditional formatting, and cross visual actions, while scaling to millions of rows of data. This can be either directly queried from a data warehouse, such as Amazon Redshift, Snowflake, or QuickSight’s SPICE data store. When used with QuickSight’s new Free form layout, this lets you present pixel-perfect views of your data to your stakeholders. We recently introduced eight new features that let tables and pivot tables in QuickSight be extensively customized to meet your presentation needs:

  1. Row height customization
  2. Vertical alignment in cells
  3. Text wrapping in cells
  4. Background and font colors
  5. Borders and gridline customization
  6. Images in table cells
  7. Hyperlinks in table cells
  8. Custom styling for totals and subtotals

In this post, we explore how, as authors of QuickSight dashboards, you can now supercharge your table and pivot tables using these new features.

Use styling options to present your data better

These new changes let you create tables that follow a distinct design pattern or apply a corporate identity to your tabular visuals. For example, you might want to create a table visual about a set of videos, with views and votes information for each, along with links to the video. It is important to follow the color pallete of the organization, choose an appropriate line height, use a specific grid style, and display thumbnail images of a specific size. Let’s see how you can achieve all of this using the newly launched table styling options. You can access these settings from the format visual panel of the visual.

To customize the background color, row-height, and text color on table headers:

  • In the Format visual pane, choose Headers.
  • For Background, choose the background color icon, and then choose a color. You can choose one of the provided colors, reset the header text color to the default color, or create a custom color.
  • For Row height, enter a number in pixels. You can enter a whole number between 8 and 500.
  • To change the font color, choose the Abc color icon, and then choose a color. You can choose one of the provided colors, reset the header text color to the default color, or create a custom color.

To customize the background color, row-height and text color, and wrap-text on table cells:

  • In the Format visual pane, choose Cells. The Cells section expands to show options for customizing cells.
  • For Row height, enter a number in pixels. You can enter a whole number between 8 and 500.
  • For Background, select alternate background colors between rows, and then choose a color for Odd rows and a color for Even rows by choosing the background color icon for each and selecting a color. You can choose one of the provided colors, reset the background color to the default color, or create a custom color.
  • For Text, to change the font color, choose the Abc color icon, and then choose a color.
  • To wrap text in cells, select Wrap text and increase the Row-height.

To show URLs as images:

  • In the Format visual menu, choose Field styling.
  • In the Field styling pane, choose the field you want to style from the dropdown menu.
  • In the Url options section of the Field styling menu, choose Show URLs as images.
  • After rendering images in a table, you can choose how to size the images in the Image sizing section of the Field style pane. You can fit images to their cell’s height or width, or you can choose not to scale the image. Images will fit to a cell’s height by default.

A table visual on QuickSight analyzing the views and votes information about a set of videos, with thumbnail images and link to the video

Steps to create a table visual with Hyperlinks and Images

Custom styling options for Totals and Sub-totals

In some use cases (most commonly with financial data), it might be important to style your totals and sub-totals independently. You can access these settings from the format visual panel of the visual.

Customizing the background color, font color, and borders for totals and subtotals:

  • In the Format visual pane, choose Total or Subtotal.
  • For Background, choose the background color icon, and then choose a color. You can choose one of the provided colors, reset the background color to the default color, or create a custom color. In pivot tables, you can also add background colors for column totals and subtotals. To do so, choose the background color icon for Background in the Columns section.
  • To change the font color, choose the Abc color icon, and then choose a color. You can choose one of the provided colors, reset the cell text color to the default color, or create a custom color.
  • For Borders, do one or more of the following:
    • To customize the type of border that you want, choose a border type icon. You can choose no borders, horizontal borders only, vertical borders only, or all borders.
    • To customize the border thickness, choose a border thickness.
    • To customize the border color, choose the border color icon, and then choose a color. You can choose one of the provided colors, reset the border color to the default color, or create a custom color.
  • To apply totals and subtotals styling to cells, choose Apply styling to cells.

Here is an example of a pivot table that has been formatted with some of the above techniques:

A pivot table visual in a form of financial report

Steps to create pivot table visual in a form of financial report by using custom styling for Totals and Subtotals

Sorting and custom actions

Combining the new formatting features with some of QuickSight’s existing capabilities lets you unlock a range of possibilities in your dashboards.

Custom sorting

Sometimes you must organize the fields beyond the conventional methods of ascending/descending by the dimension or a measure. For example, you cannot organize the days of the week or month names by sorting them alphabetically or by any measure. With the sort options on Table and Pivot table (launched in early 2021), you can now sort a field by any other field in your dataset or a calculated field. This is done regardless of whether it is being used in the visual or is off the visual. The following steps sort a field using an off-visual field:

  • Add the required fields.
  • Add a new calculated field using the rank function to rank days of the week in the required order.
  • Choose the field that has days of the week, and then choose Sort options:
    • For Sort by, choose the new calculated field from the drop-down list.
    • Since this field is based on rank function, it uses custom aggregation.
    • Select Ascending.
    • Click Apply.
  • You will see that now the days of the week are arranged as we wanted.
  • Note: It is not necessary to use a calculated field using rank function. You can also use one of the fields in your data-set that may be used for maintaining the rank. For example, I want to order the list of players on the team based on a specific display order that should not be visible on the visual.

 

Custom Actions

What if you don’t want your readers to go away from your dashboard to watch the training video? You can do this with help of Table, custom actions, and custom visual content. Let’s take a closer look.

Step 1: Add video link to the visual.

  • Add the field that has the Video URL to the Table (hide the field if you do not want to show that on the dashboard).

Step 2: Add the parameter for video URL.

  • Add a new parameter by choosing the add icon (+) near the top of the pane.
  • For Name, enter an alphanumeric value for the parameter.
  • For Data type, choose String.
  • For Values, choose Single value, as this parameter will contain only one value.

Step 3: Add custom action.

  • Set the action name.
  • For Activation, choose Select so that the action is activated when a specific row of the table is selected.
  • For Action type, choose Navigation Action.
  • For Target sheet, choose the same sheet where the Table visual is created.
  • For Parameters, select the Parameter created in Step 2.
  • Save your selections, and then create the custom action.

Steps 4 and 5: Add Custom visual content and Link the Video URL parameter.

  • In the Visual types pane, choose the custom visual content and then choose Customize visual.
  • Add the parameter to the custom visual content by by choosing the add icon (+) near the URL section of the format visual pane.

Below is the example of Video Player on the dashboard using the Table visual linked to custom visual content using the custom action:

Video Player on the dashboard using the Table visual linked to custom visual content using the custom action

Get started today!

We hope this blog has sparked new and creative ideas for your next Amazon QuickSight dashboard! Also, don’t forget to learn more about recent features in our what’s new.

About the Authors

Rushabh Vora is a Senior Technical Product Manager for Amazon QuickSight, Amazon Web Service’s cloud-native, fully managed BI service. He is passionate about Data Visualization. Prior to QuickSight, he was working with Amazon Business as a Product Manager.

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.

Secure and simplify account setup and access management with new Amazon QuickSight administrative controls

Post Syndicated from Raji Sivasubramaniam original https://aws.amazon.com/blogs/big-data/secure-and-simplify-account-setup-and-access-management-with-new-amazon-quicksight-administrative-controls/

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 the QuickSight interface, or embedded in software as a service (SaaS) applications or web portals. Unlike many BI solutions in the market today, QuickSight requires no server deployments or management for scaling to tens of thousands of users, and authors build dashboards using a web-based interface, without any client downloads needed. QuickSight also supports private VPC connectivity to AWS databases and analytics services such as Amazon Relational Database Service (Amazon RDS) and Amazon Redshift, and AWS Identity and Access Management (IAM) permissions-based access to Amazon Simple Storage Service (Amazon S3) and Amazon Athena, making it secure and easy to access data in AWS via QuickSight.

In this post, we explore three new features in QuickSight that enable administrators to further simplify QuickSight setup and access controls, which makes it easier than ever to scale QuickSight to all your AWS accounts.

Overview of new QuickSight features

Administrators can take advantage of the following new features in QuickSight:

  • Service control policy based sign-up controls – Admins can now use service control policies (SCPs) to restrict QuickSight sign-up options within your organization. You can restrict the QuickSight edition (Standard or Enterprise), and also the type of identity mechanisms that can be used. For example, admins can set up service control policies that deny sign-ups for a QuickSight Standard Edition and turn off the ability to invite any users other than those possible via federated single sign-on (SSO). For more information, see Using Service Control Policies to Restrict Amazon QuickSight Sign-up Options.
  • Automated email sync for federated SSO users – Admins can set up QuickSight and SSO such that email addresses for end-users are automatically synced at first-time login. This avoids any manual errors during entry, and prevents use of personal email addresses (such as Gmail or Hotmail). For example, administrators can make it so that only corporate-assigned email addresses are used when users are provisioned to their QuickSight account through their identity provider (IdP). For more information, see Configuring Email Syncing for Federated Users in Amazon QuickSight.
  • Bring your own role during QuickSight account setup – QuickSight allows you to bring in data stored in several AWS services to create datasets, analyses, and dashboards. QuickSight uses an IAM role to specify permissions to the AWS resources (such as Amazon S3 or Athena) at the QuickSight account level (which you can further control within QuickSight). This service role was previously created during QuickSight sign-up, and required the user signing up to have permissions to create this role. Now, administrators signing up to QuickSight can pick from an existing role in their AWS account instead of QuickSight creating a custom service role for the account. This allows you to set up your own role for a group of codependent AWS services and QuickSight that you want to work together. For more information, see Passing IAM Roles to Amazon QuickSight.

Use case overview

Let’s walk through a use case for these features.

OkTank is an enterprise in the healthcare space, where it owns and manages multiple hospitals. OkTank’s IT infrastructure is managed centrally by a team that is responsible for ensuring security and governance of the entire IT infrastructure. Each individual facility has its own AWS account, which is a member of OkTank’s central AWS Organizations account.

Each hospital needs its own QuickSight account for gathering business intelligence and improving the healthcare service they provide to their customers. The central IT team requires that each hospital when setting up their QuickSight account only signs up for Enterprise edition. In addition, they want to authenticate each hospital’s QuickSight users (admins, authors, and readers) using Okta, which is their corporate IdP. This helps them make sure that QuickSight administrators can’t invite non-federated users intentionally or by mistake.

Administrators also want to make sure that when users get an invitation to sign up for their hospital’s QuickSight account, they only use pre-approved email address as configured in Okta and don’t enter their personal email address. This provides a seamless sign-up experience for new users because they don’t have to enter an email address anymore, and it provides more security because users can’t use their personal email for sign-up and future logins.

Finally, because AWS administrators manages other services such as Amazon S3 and Athena, which are being used by QuickSight, they have configured roles for each of these services. Administrators want to make sure they can use preconfigured roles when external services are being used by QuickSight. This makes sure that users and QuickSight admins can’t create their own roles for these services, and the roles can be enforced by administrators of those external services.

To enable all these setup and access controls, OkTank’s Organizations administrator and the hospital’s QuickSight administrator use the new features in the following order:

  • Bring your own role during QuickSight account setup
  • SCP-based sign-up controls
  • Automated email sync for federated SSO users

Bring your own role during QuickSight account setup

OkTank uses Amazon S3 for storage, and wants to use it as a data source in all the hospitals’ QuickSight accounts. An IT administrator creates an IAM role for Amazon S3 that only allows read-only access to a QuickSight account and its users. During QuickSight account creation, the administrator can select the read-only Amazon S3 role. OkTank’s Organizations administrator for each hospital’s AWS account completes the following steps to create an Amazon S3 role and configure it to be used by QuickSight:

  1. On the IAM console, choose Roles in the navigation pane.
  2. Choose Create role.
  3. Choose AWS Service and choose S3.
  4. Choose Next: Permissions.
  5. Search for S3 and select AmazonS3ReadOnlyAccess.
  6. Choose Next: Tags.
  7. Choose Next: Review.
  8. For Role name, enter QuickSightS3Role.
  9. Choose Create role.
  10. Choose the newly created role.
  11. On the Trust relationships tab,
  12. Choose Edit trust relationship.
  13. Enter the following JSON: 
    {
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "quicksight.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}

  14. Choose Update Trust Policy.

This newly created role is now available for the administrator to choose while creating a QuickSight account in the next section.

SCP-based sign-up controls

To ensure that hospitals when creating their QuickSight account only choose Enterprise edition and their users are only invited via Okta, OkTank’s Organizations administrator completes the following steps:

  1. On the Organizations console, choose Policies in the navigation pane.
  2. Choose Service control policies.
  3. Choose Create policy.
  4. For Policy name, enter QuickSightSCP.
  5. Enter the following JSON in the policy section: 
    {
"Version": "2012-10-17",
"Statement": [
{
"Sid": "Statement1",
"Effect": "Deny",
"Action": [
"quicksight:Subscribe"
],
"Resource": [
"*"
],
"Condition": {
"ForAnyValue:StringEquals": {
"quicksight:DirectoryType": [
"microsoft_ad",
"quicksight",
"ad_connector"
]
}
}
},
{
"Sid": "Statement2",
"Effect": "Deny",
"Action": [
"quicksight:Subscribe"
],
"Resource": [
"*"
],
"Condition": {
"StringEquals": {
"quicksight:Edition": "standard"
}
}
}
]
}

  6. Choose Create policy.
  7. Choose AWS accounts in the navigation pane.
  8. Choose the Root account.
  9. On the Policies tab, under Service control policies, choose Attach.
  10. Select the policy QuickSightSCP that you created earlier and choose Attach policy.

Now the newly created policy is attached to all the hospital’s AWS accounts.

Test the sign-up controls

As the hospital’s AWS admin, you can test the sign-up controls to make sure they prevent you from using Standard Edition.

  1. When signing up for a QuickSight account, choose Standard.
  2. Select Use IAM federated identities & QuickSight-managed users.

You’re presented with the following error message.

As per the new SCP policy attached to the hospital’s AWS account, the admin has to choose Enterprise Edition and use IAM federated identities in order to successfully set up a QuickSight account.

  1. Choose Enterprise.
  2. Select Use IAM federated identities only.

The Amazon S3 read-only role that you created earlier is available in QuickSight.

  1. Select Use an existing role and choose QuickSightS3Role.
  2. Choose Finish.

After you make your selections per the SCP policy and the custom role for Amazon S3 read-only access, this QuickSight account is created successfully for the hospital.

Automated email sync for federated SSO users

The hospital’s QuickSight account is now set up to only accept users invited through federated SSO. In this case, they use Okta, which is their corporate IdP. After authentication via Okta is complete, the QuickSight users are asked to enter their email address when they log in for the very first time.

This email request may create confusion for some users as to which email address they should use.

The hospital’s QuickSight admin team wants to streamline the user login process and prevent users from entering any emails other than their corporate email. To ensure that, the hospital’s QuickSight admin decides to use the new automated email sync feature for federated SSO users. With this new feature, admins can set up QuickSight and SSO such that email addresses for end-users are automatically synced at first-time login. This prevents any manual errors during entry, or users signing up with personal email addresses. OkTank’s administrators can set up controls so that only corporate-assigned email addresses are used when users are provisioned to their QuickSight account through their IdP.

The hospital’s admin completes the following steps to use this feature:

  1. On the IAM console, choose Roles in the navigation pane.
  2. Search for the role you use with AssumeRoleWithSAML (for this post, it’s called QuickSightOktaFederatedRole).
  3. On the Trust relationships tab, choose Edit trust relationship.
  4. For the policy details, enter the following JSON: 
    {
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Federated": "arn:aws:iam::xxxxxxxxxx:saml-provider/Okta"
},
"Action": "sts:AssumeRoleWithSAML",
"Condition": {
"StringEquals": {
"SAML:aud": "https://signin.aws.amazon.com/saml"
}
}
},
{
"Effect": "Allow",
"Principal": {
"Federated": "arn:aws:iam::xxxxxxxxxx:saml-provider/Okta"
},
"Action": "sts:TagSession",
"Condition": {
"StringLike": {
"aws:RequestTag/Email": "*"
}
}
}
]
}

  5. Choose Update Trust Policy.

OkTank’s central IT administrator (responsible for managing Okta’s configuration) makes the following changes in the Okta configuration via Okta’s admin console:

  1. Log in to the Okta admin console.
  2. Choose Applications in the navigation pane.
  3. Choose the Okta application for QuickSight federation (in this case, it’s called AWS Account Federation – QuickSight).
  4. Choose the Sign On tab.
  5. In the Settings section, choose Edit.
  6. Select SAML 2.0 and expand the Attributes section.
  7. Add an attribute statement as follows:
    1. For Name, enter https://aws.amazon.com/SAML/Attributes/PrincipalTag:Email.
    2. For Name format, select URI reference.
    3. For Value, select user.email.
  8. Choose Save.

Finally, after you update the trust relationship for the IAM role with AssumeRoleWithSAML and add a SAML attribute for the IAM Principal tag in Okta, the next step is to turn on email syncing for federated users in QuickSight.

OkTank’s central IT administrator (responsible for managing Okta’s configuration) makes the following changes in the Okta configuration via Okta’s admin console.

  1. On the QuickSight console, on the user name menu, choose Manage QuickSight.
  2. Choose Single sign-on (SSO) in the navigation pane.
  3. In the Email Syncing for Federated Users section, select ON.

Once turned on, users when launching the QuickSight application via the Okta console for the first time bypass the email request and are redirected to the QuickSight console.

Conclusion

With these features, administrators can now strengthen controls around QuickSight accounts and open up QuickSight access to more AWS accounts within your organization. Try out these features to strengthen the security of your QuickSight account and simplify end-user access, and share your feedback and questions in the comments.

Stay tuned for more new admin capabilities, and check out what’s new for the latest updates.

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.

Mayank Agarwal is a product manager for Amazon QuickSight, AWS’ cloud-native, fully managed BI service. He focuses on account administration, governance and developer experience. He started his career as an embedded software engineer developing handheld devices. Prior to QuickSight he was leading engineering teams at Credence ID, developing custom mobile embedded device and web solutions using AWS services that make biometric enrollment and identification fast, intuitive, and cost-effective for Government sector, healthcare and transaction security applications.

Your guide to all things Amazon QuickSight at AWS re:Invent 2021

Post Syndicated from Mia Heard original https://aws.amazon.com/blogs/big-data/your-guide-to-all-things-amazon-quicksight-at-aws-reinvent-2021/

AWS re:Invent is a learning conference hosted by AWS for the global cloud computing community. This year’s re:Invent will be held in Las Vegas, NV from November 29th to December 3rd. Amazon QuickSight, a scalable, embeddable, ML-powered business intelligence (BI) service for the cloud will be represented at re:Invent within the Business Intelligence track through keynotes, breakout sessions, chalk talks, workshops, and other planned activities.

This post walks you through the details of all QuickSight related sessions and activities to help you plan your conference week accordingly. These sessions should appeal to anyone wanting to learn more about modernizing their BI capabilities and curating a more data driven culture including business decision makers, BI analysts, data scientists, app developers, and others! To access the session catalog and reserve your seat for one of our Business Intelligence sessions, you must be registered for re:Invent. Register now!

Keynotes

Swami Sivasubramanian – Vice President, Amazon Machine Learning – Keynote

Join Swami Sivasubramanian, Vice President, Amazon Machine Learning, on an exploration of what it takes to put data in action with an end to end data strategy including the latest news on databases, analytics, and machine learning.

Rahul Pathak – Vice President, Analytics – Leadership Session: Reinvent your business for the future with AWS Analytics 

The next wave of digital transformation will be data-driven, and organizations will have to reinvent themselves using data to make decisions quickly and gain faster and deeper insights to serve their customers. In this session, Rahul Pathak, VP of AWS Analytics, addresses the current state of analytics on AWS, focusing on the latest service innovations. Learn how you can put your data to work with the best of both data lakes and purpose-built data stores. Also, discover how AWS can help you build new experiences and reimagine old processes with a modern data architecture on AWS.

Breakout sessions 

BSI201 (LVL 200) – Self-service analytics for everyone with ML-powered Amazon QuickSight Q

In our increasingly fast-paced world, it is critical for companies to make data-driven business decisions quickly. In order to move fast, teams need the ability to answer business questions without relying on the time-consuming efforts of business intelligence (BI) teams. Amazon QuickSight Q is a machine learning-powered capability that uses natural language processing to answer business questions instantly in the form of a visual, without requiring authors to create visuals, dashboards, or analyses. In this session, the Amazon QuickSight team provides an overview of Q and guidance on how to get started with this new capability.

BSI202 (LVL 200) – Modernize your BI and reporting with Amazon QuickSight 

Organizations increasingly strive to improve data literacy for all their employees and reduce dependence on IT and data scientists while gaining efficiency by moving to the cloud. Existing business intelligence tools can limit business users’ insight into their data. They can also limit IT departments’ ability to provide self-service analytics in a secure, scalable, and cost-efficient way. Join this session to learn how customers have modernized their advanced analytics, self-service, and reporting needs by migrating to Amazon QuickSight. With QuickSight, you only pay for what you use and are not required to manage any infrastructure.

BSI203 (LVL 200) – Enhance your apps with Amazon QuickSight embedded analytics 

Empower your users by embedding business analytics capabilities directly into your application. Amazon QuickSight embedded analytics seamlessly integrate into your application and enable your users to perform advanced analytics with capabilities such as one-time, or ad hoc, analyses and machine learning-based insights, providing you with new ways of monetizing and differentiating your applications. Join this session to learn about QuickSight’s new capabilities for embedding rich data visualizations within SaaS applications that provide governance and data security. The session also shows how QuickSight can be easily connected to your choice of data services including Amazon RDS, Amazon Athena, Amazon S3, or Amazon Redshift.

Chalk talks 

BSI204 (LVL 200) – Authoring configuration for Amazon QuickSight Q

Amazon QuickSight Q is a machine learning-powered capability that uses natural language processing to instantly answer business questions about data. Q interprets questions to understand their intent and generates an answer instantly in the form of a visual, without requiring authors to create visuals, dashboards, or analyses. In this chalk talk, the Amazon QuickSight Q team provides an overview of Q and the different question types that are currently supported. Following the product demo, learn how to set up datasets to answer natural language questions from users.

BSI301 (LVL 300) – Advanced deployment options at scale with Amazon QuickSight

Amazon QuickSight is a scalable, serverless, embeddable, ML-powered BI service built for the cloud that you can use to deliver easy-to-understand insights to the people that you work with – wherever they are. In this chalk talk, learn about deployment methodologies and best practices around multi-tenancy, cross-account data connectivity, single sign-on, content migration, and automation, to give you confidence deploying into even the most sophisticated environments.

Workshops 

BSI302 (LVL 300) – Build stunning dashboards with Amazon QuickSight 

Want to grow your dashboard-building skills from beginner to advanced? In this workshop, the Amazon QuickSight team guides you through the latest authoring functionality, such as high-fidelity layouts, custom content, and advanced formatting. These features are designed to empower you to build beautiful layouts and robust interactive experiences with other applications, right from within your dashboard.

Additional activities

Demo: “Get answers on your data in seconds with Amazon QuickSight”

Join us in the demo theater on Tuesday November 30 at 3:00PM PST for an Amazon QuickSight Q deep dive demo.

Business Intelligence kiosk in the AWS Village

Visit the Business Intelligence kiosk within the AWS Village to meet with experts to dive deeper into QuickSight capabilities such as ML-powered Q and Embedded Analytics. You will be able to ask our experts questions and experience live demos for our newly launched capabilities.

Grab your QuickSight swag

Make sure to stop by the swag distribution table to grab free QuickSight swag. You must attend one of the breakout sessions, chalk talks, workshops, or visit our kiosk to obtain a swag voucher.

Other QuickSight related sessions 

LFS304 – Accelerate science by unifying data silos across the enterprise

When shifting to data-driven decision-making, customers—especially those in the life sciences—often struggle with the need to access data and free it from organizational silos or find it in other enterprises. In this workshop, learn how to build an effective data mesh platform on AWS to make data discoverable, secure, and interoperable. Free your data from silos and minimize unnecessary data movement. Utilizing AWS Glue, Amazon Athena, AWS Lake Formation, Amazon S3, Amazon QuickSight, and Amazon Neptune, bring together disparate data sources from across the enterprise. Use what you learn to build a scalable data mesh to serve as the foundation for any analytics and data science effort.

DEM066 – S –  Assess, plan & automate the transition from legacy BI to Amazon QuickSight (sponsored by Ironside)

Ironside’s Ascent suite, powered by AWS, enables a rapid, efficient, and successful way to build new analytics, explore AI, and migrate from legacy platforms to the cloud. Join this session to learn how to use Ironside’s AscentIQ offering for Amazon QuickSight to inventory, rationalize, and plan the migration of your existing reporting implementation; build the business case for your migration; refactor and automate generation of legacy report specifications; and accelerate functional validation of legacy content. This presentation is brought to you by Ironside, an AWS Partner.

COP311 –  Build your own customizable cost reporting dashboards

Get actionable insights to track your AWS cost and usage performance, drive cost-efficient consumption behavior in your organization, and make cost-aware architectural decisions. Join this workshop to learn how you can integrate AWS Cost and Usage Reports with Amazon QuickSight and build your own dashboards to visualize specific KPIs and savings opportunities that are meaningful to you and your business.

GPS306 – Data warehouse and business intelligence modernization

Amazon Redshift is a fast, fully managed, petabyte-scale, cloud-native analytics data warehouse. A large number of organizations are migrating their current data warehousing systems into Amazon Redshift to take advantage of better performance and scale, lower costs, the ease of use of a managed service, and data lake integration. Amazon QuickSight is a scalable, serverless, cloud-powered business intelligence (BI) service that makes it easy for you to publish insights through interactive BI dashboards. Join this workshop to learn and practice how to modernize and migrate legacy data warehouse systems to Amazon Redshift and how to migrate your BI dashboards to Amazon QuickSight.

ENT306 –  Outcome-oriented analytics for SAP on AWS

In this workshop, learn how to solve common business problems using native AWS services in conjunction with SAP applications. First in the workshop, build a data extraction layer from SAP. Then, prepare the data with AWS Glue DataBrew and use Amazon QuickSight to visualize, forecast, and use NLP for business process reporting.

AIM208 – S – Port of Vancouver: Improving supply chain visibility (sponsored by Deloitte)

As Canada’s largest port and the third-largest port in North America, the Port of Vancouver needed to overhaul its container examination process because it was a major source of challenges across the network. Join this session to learn how the Port of Vancouver used a blockchain application integrated with AWS Panorama to help identify and track containers, feed real-time data into the blockchain system, enable proper assignment of cost, and identify efficiency improvement opportunities. Discover how other AWS services, such as Amazon SageMaker, Amazon QuickSight, AWS IoT Core, and more, were also used to deliver the data and insights, while meeting privacy, security, and data residency requirements. This presentation is brought to you by Deloitte, an AWS Partner.

FSI203 –  State Farm: Enabling rapid deployment with risk automation

In this session, learn how State Farm streamlined deployment approval from months to minutes by implementing an automated, data-driven risk management process for all use cases leveraging AWS services. Approving deployments for production in financial services can often be a manual, opinion-based, and time-consuming process that frustrates developers, security teams, and executive leadership alike. State Farm’s solution not only notifies account administrators of potential compliance issues but also supports auto-remediation of these issues. It provides continuous, real-time testing and monitoring of controls, which accelerates service adoption for developers, delivers assurance to InfoSec professionals, and enables agile executive decision-making.

AIM317 –  Uncover insights from customer conversations with no ML expertise required

Understanding what your customers are saying is critical to your business, but navigating the technology needed to make sense of these conversations can be daunting. In this workshop, discover how to uncover valuable insights from your data using custom models that are tailored to your business needs—with no ML expertise required. Using a set of customer calls, learn how to boost transcription accuracy with Amazon Transcribe custom language models, extract insights with Amazon Comprehend custom entities, localize content with Amazon Translate Active Custom Translation, and create powerful visualizations with Amazon QuickSight.

Useful resources 

Whether you plan on attending re:Invent in person or virtually this year you can always learn more about QuickSight through these helpful resources:

QuickSight YouTube Channel
Subscribe to stay up to date on the latest QuickSight workshops, getting started tutorials, and demo videos.

QuickSight DemoCentral

Experience QuickSight first hand through interactive dashboards and demos.

QuickSight workshops

 Enhance your BI skills with self-paces QuickSight workshops.

About the Author

Mia Heard is a product marketing manager for Amazon QuickSight, AWS’ cloud-native, fully managed BI service.

Create a serverless event-driven workflow to ingest and process Microsoft data with AWS Glue and Amazon EventBridge

Post Syndicated from Venkata Sistla original https://aws.amazon.com/blogs/big-data/create-a-serverless-event-driven-workflow-to-ingest-and-process-microsoft-data-with-aws-glue-and-amazon-eventbridge/

Microsoft SharePoint is a document management system for storing files, organizing documents, and sharing and editing documents in collaboration with others. Your organization may want to ingest SharePoint data into your data lake, combine the SharePoint data with other data that’s available in the data lake, and use it for reporting and analytics purposes. AWS Glue is a serverless data integration service that makes it easy to discover, prepare, and combine data for analytics, machine learning, and application development. AWS Glue provides all the capabilities needed for data integration so that you can start analyzing your data and putting it to use in minutes instead of months.

Organizations often manage their data on SharePoint in the form of files and lists, and you can use this data for easier discovery, better auditing, and compliance. SharePoint as a data source is not a typical relational database and the data is mostly semi structured, which is why it’s often difficult to join the SharePoint data with other relational data sources. This post shows how to ingest and process SharePoint lists and files with AWS Glue and Amazon EventBridge, which enables you to join other data that is available in your data lake. We use SharePoint REST APIs with a standard open data protocol (OData) syntax. OData advocates a standard way of implementing REST APIs that allows for SQL-like querying capabilities. OData helps you focus on your business logic while building RESTful APIs without having to worry about the various approaches to define request and response headers, query options, and so on.

AWS Glue event-driven workflows

Unlike a traditional relational database, SharePoint data may or may not change frequently, and it’s difficult to predict the frequency at which your SharePoint server generates new data, which makes it difficult to plan and schedule data processing pipelines efficiently. Running data processing frequently can be expensive, whereas scheduling pipelines to run infrequently can deliver cold data. Similarly, triggering pipelines from an external process can increase complexity, cost, and job startup time.

AWS Glue supports event-driven workflows, a capability that lets developers start AWS Glue workflows based on events delivered by EventBridge. The main reason to choose EventBridge in this architecture is because it allows you to process events, update the target tables, and make information available to consume in near-real time. Because frequency of data change in SharePoint is unpredictable, using EventBridge to capture events as they arrive enables you to run the data processing pipeline only when new data is available.

To get started, you simply create a new AWS Glue trigger of type EVENT and place it as the first trigger in your workflow. You can optionally specify a batching condition. Without event batching, the AWS Glue workflow is triggered every time an EventBridge rule matches, which may result in multiple concurrent workflows running. AWS Glue protects you by setting default limits that restrict the number of concurrent runs of a workflow. You can increase the required limits by opening a support case. Event batching allows you to configure the number of events to buffer or the maximum elapsed time before firing the particular trigger. When the batching condition is met, a workflow run is started. For example, you can trigger your workflow when 100 files are uploaded in Amazon Simple Storage Service (Amazon S3) or 5 minutes after the first upload. We recommend configuring event batching to avoid too many concurrent workflows, and optimize resource usage and cost.

To illustrate this solution better, consider the following use case for a wine manufacturing and distribution company that operates across multiple countries. They currently host all their transactional system’s data on a data lake in Amazon S3. They also use SharePoint lists to capture feedback and comments on wine quality and composition from their suppliers and other stakeholders. The supply chain team wants to join their transactional data with the wine quality comments in SharePoint data to improve their wine quality and manage their production issues better. They want to capture those comments from the SharePoint server within an hour and publish that data to a wine quality dashboard in Amazon QuickSight. With an event-driven approach to ingest and process their SharePoint data, the supply chain team can consume the data in less than an hour.

Overview of solution

In this post, we walk through a solution to set up an AWS Glue job to ingest SharePoint lists and files into an S3 bucket and an AWS Glue workflow that listens to S3 PutObject data events captured by AWS CloudTrail. This workflow is configured with an event-based trigger to run when an AWS Glue ingest job adds new files into the S3 bucket. The following diagram illustrates the architecture.

To make it simple to deploy, we captured the entire solution in an AWS CloudFormation template that enables you to automatically ingest SharePoint data into Amazon S3. SharePoint uses ClientID and TenantID credentials for authentication and uses Oauth2 for authorization.

The template helps you perform the following steps:

  1. Create an AWS Glue Python shell job to make the REST API call to the SharePoint server and ingest files or lists into Amazon S3.
  2. Create an AWS Glue workflow with a starting trigger of EVENT type.
  3. Configure CloudTrail to log data events, such as PutObject API calls to CloudTrail.
  4. Create a rule in EventBridge to forward the PutObject API events to AWS Glue when they’re emitted by CloudTrail.
  5. Add an AWS Glue event-driven workflow as a target to the EventBridge rule. The workflow gets triggered when the SharePoint ingest AWS Glue job adds new files to the S3 bucket.

Prerequisites

For this walkthrough, you should have the following prerequisites:

Configure SharePoint server authentication details

Before launching the CloudFormation stack, you need to set up your SharePoint server authentication details, namely, TenantID, Tenant, ClientID, ClientSecret, and the SharePoint URL in AWS Systems Manager Parameter Store of the account you’re deploying in. This makes sure that no authentication details are stored in the code and they’re fetched in real time from Parameter Store when the solution is running.

To create your AWS Systems Manager parameters, complete the following steps:

  1. On the Systems Manager console, under Application Management in the navigation pane, choose Parameter Store.
    systems manager
  2. Choose Create Parameter.
  3. For Name, enter the parameter name /DATALAKE/GlueIngest/SharePoint/tenant.
  4. Leave the type as string.
  5. Enter your SharePoint tenant detail into the value field.
  6. Choose Create parameter.
  7. Repeat these steps to create the following parameters:
    1. /DataLake/GlueIngest/SharePoint/tenant
    2. /DataLake/GlueIngest/SharePoint/tenant_id
    3. /DataLake/GlueIngest/SharePoint/client_id/list
    4. /DataLake/GlueIngest/SharePoint/client_secret/list
    5. /DataLake/GlueIngest/SharePoint/client_id/file
    6. /DataLake/GlueIngest/SharePoint/client_secret/file
    7. /DataLake/GlueIngest/SharePoint/url/list
    8. /DataLake/GlueIngest/SharePoint/url/file

Deploy the solution with AWS CloudFormation

For a quick start of this solution, you can deploy the provided CloudFormation stack. This creates all the required resources in your account.

The CloudFormation template generates the following resources:

  • S3 bucket – Stores data, CloudTrail logs, job scripts, and any temporary files generated during the AWS Glue extract, transform, and load (ETL) job run.
  • CloudTrail trail with S3 data events enabled – Enables EventBridge to receive PutObject API call data in a specific bucket.
  • AWS Glue Job – A Python shell job that fetches the data from the SharePoint server.
  • AWS Glue workflow – A data processing pipeline that is comprised of a crawler, jobs, and triggers. This workflow converts uploaded data files into Apache Parquet format.
  • AWS Glue database – The AWS Glue Data Catalog database that holds the tables created in this walkthrough.
  • AWS Glue table – The Data Catalog table representing the Parquet files being converted by the workflow.
  • AWS Lambda function – The AWS Lambda function is used as an AWS CloudFormation custom resource to copy job scripts from an AWS Glue-managed GitHub repository and an AWS Big Data blog S3 bucket to your S3 bucket.
  • IAM roles and policies – We use the following AWS Identity and Access Management (IAM) roles:
    • LambdaExecutionRole – Runs the Lambda function that has permission to upload the job scripts to the S3 bucket.
    • GlueServiceRole – Runs the AWS Glue job that has permission to download the script, read data from the source, and write data to the destination after conversion.
    • EventBridgeGlueExecutionRole – Has permissions to invoke the NotifyEvent API for an AWS Glue workflow.
    • IngestGlueRole – Runs the AWS Glue job that has permission to ingest data into the S3 bucket.

To launch the CloudFormation stack, complete the following steps:

  1. Sign in to the AWS CloudFormation console.
  2. Choose Launch Stack:
  3. Choose Next.
  4. For pS3BucketName, enter the unique name of your new S3 bucket.
  5. Leave pWorkflowName and pDatabaseName as the default.

cloud formation 1

  1. For pDatasetName, enter the SharePoint list name or file name you want to ingest.
  2. Choose Next.

cloud formation 2

  1. On the next page, choose Next.
  2. Review the details on the final page and select I acknowledge that AWS CloudFormation might create IAM resources.
  3. Choose Create.

It takes a few minutes for the stack creation to complete; you can follow the progress on the Events tab.

You can run the ingest AWS Glue job either on a schedule or on demand. As the job successfully finishes and ingests data into the raw prefix of the S3 bucket, the AWS Glue workflow runs and transforms the ingested raw CSV files into Parquet files and loads them into the transformed prefix.

Review the EventBridge rule

The CloudFormation template created an EventBridge rule to forward S3 PutObject API events to AWS Glue. Let’s review the configuration of the EventBridge rule:

  1. On the EventBridge console, under Events, choose Rules.
  2. Choose the rule s3_file_upload_trigger_rule-<CloudFormation-stack-name>.
  3. Review the information in the Event pattern section.

event bridge

The event pattern shows that this rule is triggered when an S3 object is uploaded to s3://<bucket_name>/data/SharePoint/tablename_raw/. CloudTrail captures the PutObject API calls made and relays them as events to EventBridge.

  1. In the Targets section, you can verify that this EventBridge rule is configured with an AWS Glue workflow as a target.

event bridge target section

Run the ingest AWS Glue job and verify the AWS Glue workflow is triggered successfully

To test the workflow, we run the ingest-glue-job-SharePoint-file job using the following steps:

  1. On the AWS Glue console, select the ingest-glue-job-SharePoint-file job.

glue job

  1. On the Action menu, choose Run job.

glue job action menu

  1. Choose the History tab and wait until the job succeeds.

glue job history tab

You can now see the CSV files in the raw prefix of your S3 bucket.

csv file s3 location

Now the workflow should be triggered.

  1. On the AWS Glue console, validate that your workflow is in the RUNNING state.

glue workflow running status

  1. Choose the workflow to view the run details.
  2. On the History tab of the workflow, choose the current or most recent workflow run.
  3. Choose View run details.

glue workflow visual

When the workflow run status changes to Completed, let’s check the converted files in your S3 bucket.

  1. Switch to the Amazon S3 console, and navigate to your bucket.

You can see the Parquet files under s3://<bucket_name>/data/SharePoint/tablename_transformed/.

parquet file s3 location

Congratulations! Your workflow ran successfully based on S3 events triggered by uploading files to your bucket. You can verify everything works as expected by running a query against the generated table using Amazon Athena.

Sample wine dataset

Let’s analyze a sample red wine dataset. The following screenshot shows a SharePoint list that contains various readings that relate to the characteristics of the wine and an associated wine category. This is populated by various wine tasters from multiple countries.

redwine dataset

The following screenshot shows a supplier dataset from the data lake with wine categories ordered per supplier.

supplier dataset

We process the red wine dataset using this solution and use Athena to query the red wine data and supplier data where wine quality is greater than or equal to 7.

athena query and results

We can visualize the processed dataset using QuickSight.

Clean up

To avoid incurring unnecessary charges, you can use the AWS CloudFormation console to delete the stack that you deployed. This removes all the resources you created when deploying the solution.

Conclusion

Event-driven architectures provide access to near-real-time information and help you make business decisions on fresh data. In this post, we demonstrated how to ingest and process SharePoint data using AWS serverless services like AWS Glue and EventBridge. We saw how to configure a rule in EventBridge to forward events to AWS Glue. You can use this pattern for your analytical use cases, such as joining SharePoint data with other data in your lake to generate insights, or auditing SharePoint data and compliance requirements.

About the Author

Venkata Sistla is a Big Data & Analytics Consultant on the AWS Professional Services team. He specializes in building data processing capabilities and helping customers remove constraints that prevent them from leveraging their data to develop business insights.

TrueBlue uses Amazon QuickSight to deliver more accurate pricing and grow business

Post Syndicated from Robert Ward original https://aws.amazon.com/blogs/big-data/trueblue-uses-amazon-quicksight-to-deliver-more-accurate-pricing-and-grow-business/

This is a guest post by TrueBlue. In their own words, “Founded in 1989, TrueBlue provides specialized workforce solutions, including staffing, talent management, and recruitment process outsourcing (RPO). In 2020, the company connected approximately 490,000 people with work.”

At TrueBlue, we offer solutions that help employers connect with workers worldwide. Every day, sales teams at our 500-plus locations offer our customers job quotes. These quotes show our staff the hourly rates they should charge and what the gross margin might be on a bill rate.

As part of our work, our sales professionals use a concept called lockout, which is the process for approving sales orders below standard margins. As our company has grown, these approval requests have skyrocketed. We have more than 850 people bidding for potential customers at any time, but only a few dozen managers can approve lockout requests. The number of requests that managers had in their inboxes was increasingly overwhelming and took time away from more important daily tasks. They wanted a way to avoid the process altogether by standardizing job rate information.

In this post, I discuss the steps we took to solve our problem using data analysis and Amazon QuickSight.

Identifying regional pricing differences

To begin, I looked at hourly worker rates across all our locations and added state tax data and other information. That gave me our customer billing rate, plus the overhead to calculate the gross margin. Through my research, I discovered that regionality is important in determining different rates and margins, and that pricing isn’t consistent overall.

Our sales leaders wanted to take this to the next level and figure out the gross margin they would need to maintain a specific hourly billing rate. I could only see 7 months of information, but it amounted to nearly 1 million rows of data. We needed a fast, easy way to use spreadsheet software to find what we were looking for.

Using QuickSight to give sales teams better pricing data

In 2020, we decided to go all in on AWS to create a new data lake and invest in other business intelligence (BI) solutions. After speaking with the AWS team, we learned that QuickSight, a powerful BI service that runs on AWS, could give us the detailed filtering and analytical capabilities we needed.

We used QuickSight to create a new customer job quoting engine for our sales teams in 40 of our branch offices. This solution provides our team with the price quotes that optimize profit margins and the data to calculate the precise charge in each market, all of which can be quickly accessed on their laptops. Now, the lockout requests are disappearing because the sales teams have the information at their fingertips and don’t need to ask for approvals. And because our sales leaders don’t have to read through countless emails every day, they can focus on more value-added tasks.

The following diagram illustrates our solution workflow, which sends data from AWS Database Migration Service (AWS DMS) through a data pipeline to Amazon Athena for analysis, and ultimately to QuickSight.

Boosting customer retention and acquisition by 3%

With the data we’re getting from QuickSight, we can present our customers with more accurate pricing and billing information. As a result, we’ve increased new customer acquisition and retention. Our sales teams are closing phone deals at rates 3% higher than an internal sales control group. We’ve also seen an 11% increase in gross margin for the market in which we’ve used the job quoting engine the longest. Applying the data we have now is really making a difference in our business.

And with the live data powering QuickSight, we’re able to increase our margins. Every time we pay someone, our pricing is updated based on real-time regional data. The solution is always adapting to market conditions, so we can give customers nationwide a price with detailed market segmentation. For example, they can see why we’re charging more in the Midwest than in the South.

Being more transparent with customers

Our frontline sales teams can be more transparent about pricing with potential customers because they have better, more accurate pricing data. When a salesperson is on the phone with a customer, they can view the data in QuickSight and accurately explain what’s going on in a specific market. The pricing information is no longer an estimate; it’s completely accurate and up to date, and we can talk more confidently about what’s driving the cost, such as local conditions or risk ratings.

Another advantage of QuickSight and AWS is the agility and speed they give us. With AWS services, we can control how quickly to roll out the solution and who gets access. And we have more flexibility with AWS, so we can change things as we go and create better, faster tools for our internal teams without relying on a time-consuming, cumbersome development process. We can try things tomorrow that would have previously taken us 6 weeks to get into production, giving salespeople the new features they ask for quickly. And as a rapid prototyping vehicle, QuickSight is perfect for defining the next generation of job quoting packages that we’ll create for our customers.

Our job quoting tool isn’t just helping our frontline sales employees, it’s also benefiting staffing specialists, branch managers, market managers, and even regional and senior vice presidents. They can all see pricing averages and trends (as in the following screenshot), and select the data for specific markets or TrueBlue branches.

Conclusion

The downstream implications of our new job quoting tool powered by QuickSight are huge. Now conversations are happening at the right level, with the right kinds of customers driving more value for our business.

About the Authors

Robert Ward is the Senior Director of Technology at PeopleReady. His teams are responsible for delivering data science and machine learning solutions, strategy and data insights, democratized data, and business analytics solutions. PeopleReady is modernizing how the North American staffing industry connects people with work. Robert Ward is driven to craft innovations for desired outcomes.

Ryan Coyle is the AWS Account Manager for TrueBlue. He has partnered with TrueBlue on their digital transformation efforts since the beginning of 2020. In this function he has collaborated with them to close on-premises datacenter facilities, develop and deliver new products to market, and deliver data driven results to TrueBlue business units.

Shivani Sharma is one of the Account Managers supporting TrueBlue. She joined the team July 2020 where she partners with TrueBlue to drive and collaborate on their transformation initiatives.

Now Available: Updated guidance on the Data Analytics Lens for AWS Well-Architected Framework

Post Syndicated from Wallace Printz original https://aws.amazon.com/blogs/big-data/now-available-updated-guidance-on-the-data-analytics-lens-for-aws-well-architected-framework/

Nearly all businesses today require some form of data analytics processing, from auditing user access to generating sales reports. For all your analytics needs, the Data Analytics Lens for AWS Well-Architected Framework provides prescriptive guidance to help you assess your workloads and identify best practices aligned to the AWS Well-Architected Pillars: Operational Excellence, Security, Reliability, Performance Efficiency, and Cost Optimization. Today, we’re pleased to announce a completely revised and updated version of the Data Analytics Lens whitepaper.

Self-assess with Well-Architected design principles

The updated version of the Data Analytics Lens whitepaper has been revised to provide guidance to CxOs as well as all data personas. Within each of the five Well-Architected Pillars, we provide top-level design principles for CxOs to quickly identify areas for teams and fundamental rules that analytics workloads designers should follow. Each design principle is followed by a series of questions and best practices that architects and system designers can use to perform self-assessments. Additionally, the Data Analytics Lens includes suggestions that prescriptively explain steps to implement best practices useful for implementation teams.

For example, the Security Pillar design principle “Control data access” works with the best practice to build user identity solutions that uniquely identify people and systems. The associated suggestion for this best practice is to centralize workforce identities, which details how to use this principle and includes links to more documentation on the suggestion.

“Building Data Analytics platform or workloads is one of the complex architecture patterns. It involves multi-layered approach such as Data Ingestion, Data Landing, Transformation Layer, Analytical/Insight and Reporting. Choices of technology and service for each of these layers are wide. The AWS Well-Architected Analytics Lens helps us to design and validate with great confidence against each of the pillars. Now Cognizant Architects can perform assessments using the Data Analytics Lens to validate and help build secure, scalable and innovative data solutions for customers.”

– Supriyo Chakraborty, Principal Architect & Head of Data Engineering Guild, Cognizant Germany
– Somasundaram Janavikulam, Cloud Enterprise Architect & Well Architected Partner Program Lead, Cognizant

In addition to performing your own assessment, AWS can provide a guided experience through reviewing your workload with a Well-Architected Framework Review engagement. For customers building data analytics workloads with AWS Professional Services, our teams of Data Architects can perform assessments using the Data Analytics Lens during the project engagements. This provides you with an objective assessment of your workloads and guidance on future improvements. The integration is available now for customers of the AWS Data Lake launch offering, with additional Data Analytics offerings coming in 2022. Reach out to your AWS Account Team if you’d like to know more about these guided Reviews.

Updated architectural patterns and scenarios

In this version of the Data Analytics Lens, we have also revised the discussion of data analytics patterns and scenarios to keep up with the industry and modern data analytics practices. Each scenario includes sections on characteristics that help you plan when developing systems for that scenario, a reference architecture to visualize and explain how the components work together, and configuration notes to help you properly configure your solution.

This version covers the following topics:

  • Building a modern data architecture (formerly Lake House Architecture)
  • Organize around data domains by delivering data as a product using a data mesh
  • Efficiently and securely provide batch data processing
  • Use streaming ingest and stream processing for real-time workloads
  • Build operational analytics systems to improve business processes and performance
  • Provide data visualization securely and cost-effectively at scale

Changed from the first release, the machine learning and tenant analytics scenarios have been migrated to a separate Machine Learning Lens whitepaper and SaaS Lens whitepaper.

Conclusion

We expect this updated version will provide better guidance to validate your existing architectures, as well as provide recommendations for any gaps that identified.

For more information about building your own Well-Architected systems using the Data Analytics Lens, see the Data Analytics Lens whitepaper.

Special thanks to everyone across the AWS Solution Architecture and Data Analytics communities who contributed. These contributions encompassed diverse perspectives, expertise, and experiences in developing the new AWS Well-Architected Data Analytics Lens.

About the Authors

Wallace Printz is a Senior Solutions Architect based in Austin, Texas. He helps customers across Texas transform their businesses in the cloud. He has a background in semiconductors, R&D, and machine learning.

Indira Balakrishnan is a Senior Solutions Architect in the AWS Analytics Specialist SA Team. She is passionate about helping customers build cloud-based analytics solutions to solve their business problems using data-driven decisions. Outside of work, she volunteers at her kids’ activities and spends time with her family.

How Parametric Built Audit Surveillance using AWS Data Lake Architecture

Post Syndicated from Raghavarao Sodabathina original https://aws.amazon.com/blogs/architecture/how-parametric-built-audit-surveillance-using-aws-data-lake-architecture/

Parametric Portfolio Associates (Parametric), a wholly owned subsidiary of Morgan Stanley, is a registered investment adviser. Parametric provides investment advisory services to individual and institutional investors around the world. Parametric manages over 100,000 client portfolios with assets under management exceeding $400B (as of 9/30/21).

As a registered investment adviser, Parametric is subject to numerous regulatory requirements. The Parametric Compliance team conducts regular reviews on the firm’s portfolio management activities. To accomplish this, the organization needs both active and archived audit data to be readily available.

Parametric’s on-premises data lake solution was based on an MS-SQL server. They used an Apache Hadoop platform for their data storage, data management, and analytics. Significant gaps existed with the on-premises solution, which complicated audit processes. They were spending a large amount of effort on system maintenance, operational management, and software version upgrades. This required expensive consulting services and challenges with keeping the maintenance windows updated. This limited their agility, and also impacted their ability to derive more insights and value from their data. In an environment of rapid growth, adoption of more sophisticated analytics tools and processes has been slower to evolve.

In this blog post, we will show how Parametric implemented their Audit Surveillance Data Lake on AWS with purpose-built fully managed analytics services. With this solution, Parametric was able to respond to various audit requests within hours rather than days or weeks. This resulted in a system with a cost savings of 5x, with no data growth. Additionally, this new system can seamlessly support a 10x data growth.

Audit surveillance platform

The Parametric data management office (DMO) was previously running their data workloads using an on-premises data lake, which ran on the Hortonworks data platform of Apache Hadoop. This platform wasn’t up to date, and Parametric’s hardware was reaching end-of-life. Parametric was faced with a decision to either reinvest in their on-premises infrastructure or modernize their infrastructure using a modern data analytics platform on AWS. After doing a detailed cost/benefit analysis, the DMO calculated a 5x cost savings by using AWS. They decided to move forward and modernize with AWS due to these cost benefits, in addition to elasticity and security features.

The PPA compliance team asked the DMO to provide an enterprise data service to consume data from a data lake. This data was destined for downstream applications and ad-hoc data querying capabilities. It was accessed via standard JDBC tools and user-friendly business intelligence dashboards. The goal was to ensure that seven years of audit data would be readily available.

The DMO team worked with AWS to conceptualize an audit surveillance data platform architecture and help accelerate the implementation. They attended a series of AWS Immersion Days focusing on AWS fundamentals, Data Lakes, Devops, Amazon EMR, and serverless architectures. They later were involved in a four-day AWS Data Lab with AWS SMEs to create a data lake. The first use case in this Lab was creating the Audit Surveillance system on AWS.

Audit surveillance architecture on AWS

The following diagram shows the Audit Surveillance data lake architecture on AWS by using AWS purpose-built analytics services.

Figure 1. Audit Surveillance data lake architecture diagram

Figure 1. Audit Surveillance data lake architecture diagram

Architecture flow

  1. User personas: As first step, the DMO team identified three user personas for the Audit Surveillance system on AWS.
    • Data service compliance users who would like to consume audit surveillance data from the data lake into their respective applications through an enterprise data service.
    • Business users who would like to create business intelligence dashboards using a BI tool to audit data for compliance needs.
    • Complaince IT users who would like to perform ad-hoc queries on the data lake to perform analytics using an interactive query tool.
  2. Data ingestion: Data is ingested into Amazon Simple Storage Service (S3) from different on-premises data sources by using AWS Lake Formation blueprints. AWS Lake Formation provides workflows that define the data source and schedule to import data into the data lake. It is a container for AWS Glue crawlers, jobs, and triggers that are used to orchestrate the process to load and update the data lake.
  3. Data storage: Parametric used Amazon S3 as a data storage to build an Audit Surveillance data lake, as it has unmatched 11 nines of durability and 99.99% availability. The existing Hadoop storage was replaced with Amazon S3. The DMO team created a drop zone (raw), an analytics zone (transformed), and curated (enriched) storage layers for their data lake on AWS.
  4. Data cataloging: AWS Glue Data Catalog was the central catalog used to store and manage metadata for all datasets hosted in the Audit Surveillance data lake. The existing Hadoop metadata store was replaced with AWS Glue Data Catalog. AWS services such as AWS Glue, Amazon EMR, and Amazon Athena, natively integrate with AWS Glue Data Catalog.
  5. Data processing: Amazon EMR and AWS Glue process the raw data and places it into analytics zones (transformed) and curated zones (enriched) S3 buckets. Amazon EMR was used for big data processing and AWS Glue for standard ETL processes. AWS Lambda and AWS Step Functions were used to initiate monitoring and ETL processes.
  6. Data consumption: After Audit Surveillance data was transformed and enriched, the data was consumed by various personas within the firm as follows:
    • AWS Lambda and Amazon API Gateway were used to support consumption for data service compliance users.
    • Amazon QuickSight was used to create business intelligence dashboards for compliance business users.
    • Amazon Athena was used to query transformed and enriched data for compliance IT users.
  7. Security: AWS Key Management Service (KMS) customer managed keys were used for encryption at rest, and TLS for encryption at transition. Access to the encryption keys is controlled using AWS Identity and Access Management (IAM) and is monitored through detailed audit trails in AWS CloudTrail. Amazon CloudWatch was used for monitoring, and thresholds were created to determine when to send alerts.
  8. Governance: AWS IAM roles were attached to compliance users that permitted the administrator to grant access. This was only given to approved users or programs that went through authentication and authorization through AWS SSO. Access is logged and permissions can be granted or denied by the administrator. AWS Lake Formation is used for fine-grained access controls to grant/revoke permissions at the database, table, or column-level access.

Conclusion

The Parametric DMO team successfully replaced their on-premises Audit Surveillance Data Lake. They now have a modern, flexible, highly available, and scalable data platform on AWS, with purpose-built analytics services.

This change resulted in a 5x cost savings, and provides for a 10x data growth. There are now fast responses to internal and external audit requests (hours rather than days or weeks). This migration has given the company access to a wider breadth of AWS analytics services, which offers greater flexibility and options.

Maintaining the on-premises data lake would have required significant investment in both hardware upgrade costs and annual licensing and upgrade vendor consulting fees. Parametric’s decision to migrate their on-premises data lake has yielded proven cost benefits. And it has introduced new functions, service, and capabilities that were previously unavailable to Parametric DMO.

You may also achieve similar efficiencies and increase scalability by migrating on-premises data platforms into AWS. Read more and get started on building Data Lakes on AWS.