Tag Archives: Amazon QuickSight

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.

Designing a High-volume Streaming Data Ingestion Platform Natively on AWS

Post Syndicated from Soonam Jose original https://aws.amazon.com/blogs/architecture/designing-a-high-volume-streaming-data-ingestion-platform-natively-on-aws/

The total global data storage is projected to exceed 200 zettabytes by 2025. This exponential growth of data demands increased vigilance against cybercrimes. Emerging cybersecurity trends include increasing service attacks, ransomware, and critical infrastructure threats. Businesses are changing how they approach cybersecurity and are looking for new ways to tackle these threats. In the past, they have relied on internal IT or engaged a managed security services provider (MSSP) to monitor and prevent unauthorized access and attacks.

An end-to-end analytics solution should ingest and process log data streaming from various computing and IoT devices. It can then make processed data available to analytics systems users in near-real-time. However, the sheer volume of data in the future makes this difficult to address in a reliable and cost-effective manner.

In this blog post, we present three approaches for a high-volume log data ingestion and processing platform natively on Amazon Web Services (AWS). We also compare the pros and cons of each. We’ll discuss factors to consider when evaluating the different options and their associated flexibility, to take full advantage of AWS. We will showcase a fictional use case for a top MSSP who ingests high volumes of logs from security devices to cloud. This MSSP also performs downstream analytics and threat detection modeling.

The options we present here have a log collection platform (LCP) on-premises. It collects logs from security devices and sensors, performs necessary translations and tokenization, and pushes compressed log files to the processing tier on cloud. The collection platform can also be modernized to have the IoT-enabled devices send logs to AWS IoT services. This will push the data to Amazon Kinesis, a managed service for collecting and analyzing streaming data.

Approach 1: Amazon Kinesis for log ingestion and format conversion

Figure 1 illustrates a comprehensive solution that uses managed and serverless services on AWS.

Figure 1. Amazon Kinesis for log ingestion and format conversion

Figure 1. Amazon Kinesis for log ingestion and format conversion

1. LCP will invoke a scalable producer application for Amazon Kinesis Data Streams running on AWS Fargate behind an Application Load Balancer. The producer application will use the Amazon Kinesis Producer Library (KPL). KPL aggregates and batches data records to make ingestion into the data stream more efficient. The application may provide compressed records to the KPL to have it manage object compression.

The application can be set up as an HTTP endpoint that receives log files and processes them using KPL. Customer ID sent as part of an HTTP request header can be used to maintain affinity. The application can run in a Docker container, which is orchestrated by Amazon ECS on AWS Fargate. A target tracking scaling policy can manage the number of parallel running data ingestion containers to manage scalability of the ingestion process.

2. Amazon Kinesis Scaling Utility can be used to scale data streams up or down by a count, or as a percentage of the total fleet. The scaling utility archive file can be imported as a library to AWS Lambda. It will automatically manage the number of shards in the stream based on the observed PUT or GET rate of the stream. The combination of customer ID and security device ID may be used to define the partition key.

3. Records uploaded to the stream by the producer application will be consumed by Lambda. It will perform gateway transformations (required by all downstream consumers) and the normalization of record format. Any additional consumer level transformations may be handled separately, associated with respective consumers.

A combination of batch window and batch size configurations can improve efficiency of function invocations. Batch windows are the maximum amount of time in seconds to gather records before invoking the function. Batch size is the number of records to send to the function in each batch. The Lambda function will throttle sending records to Amazon Kinesis Data Firehose. Error handling will be accomplished via retries with a smaller batch size, with number of retries limited as appropriate. It will discard records that are too old.

An Amazon Simple Queue Service (SQS) queue can be configured as a failed-event destination for further offline analysis. A Lambda function can read from the error SQS queue to do basic checks and determine appropriate follow-up actions. This can be an initiated email for additional investigation or a command to discard the message.

4. Output of transformations by Lambda will be saved to the short term (hot) storage Amazon S3 bucket via Kinesis Data Firehose. This can efficiently handle Parquet format conversion required by downstream analytics applications. Kinesis Data Firehose delivery streams will be created per customer and configured with associated AWS Glue Data Catalog table, to perform parquet format conversion.

5. AWS Glue jobs will be used to consolidate and write larger files to the long term (cold) storage bucket.

6. The data in the cold storage bucket will be accessed by internal SOC analysts for threat detection and mitigation.

7. The data in cold storage buckets will also be accessed by end customers via dashboards in Amazon QuickSight.

8. This architecture also provides additional options to modernize streaming analytics using Amazon Kinesis Data Analytics or AWS Glue streaming jobs as appropriate.

While this architecture proposes a fully managed, end-to-end solution, the sheer volume of log messages may drive up the total cost of the solution. This is especially true for Kinesis Data Streams and Kinesis Data Firehose costs.

Approach 2: Containerized application on AWS Fargate for ingestion and Amazon Kinesis for format conversion

An alternative approach shown in Figure 2 replaces the gateway Kinesis Data Streams and transformations, with a containerized application on Fargate. Conversion to Parquet format and writing to the S3 bucket is still handled by Kinesis Data Firehose.

Figure 2. Containerized application for ingestion and Amazon Kinesis for format conversion

Figure 2. Containerized application for ingestion and Amazon Kinesis for format conversion

1. LCP will upload log files to a raw storage bucket in Amazon S3.

2. A Lambda function will process Event Notifications from the raw data storage bucket. It can insert Amazon S3 object pointers to a Kinesis Data Stream partitioned by Customer ID and Device ID.

3. The producer application will retrieve the Event Notifications from the Data Stream and retrieve corresponding log files from S3. It will perform initial aggregations and transformations, and output to Kinesis Data Firehose. The application can run in a Docker container that is orchestrated by Amazon ECS on Fargate. A target tracking scaling policy can manage the number of parallel running data ingestion containers, to manage scalability of the ingestion process. ECS cluster capacity can be scaled up or down based on Amazon CloudWatch alarms.

4. Kinesis Data Firehose converts to Parquet format, zips the data, and persists to a short-term storage bucket in S3. This is backed by Glue Data Catalog.

Steps 5, 6 and 7 perform consolidation and availability of the processed data to downstream consumers, as in the previous approach.

This option uses the built-in capabilities of Kinesis Data Firehose to transform to Parquet format and deliver to S3. Note that higher costs associated with the service may still be cost prohibitive for larger data volumes.

Approach 3: Containerized application on AWS Fargate for ingestion and format conversion

Figure 3 uses a containerized application running on Fargate for both gateway transformations. This app also provides conversion to Parquet format before writing the files to a short term (hot) storage bucket. All the other steps are the same as in option 2.

Figure 3. Containerized application for ingestion and format conversion

Figure 3. Containerized application for ingestion and format conversion

This option offers the least expensive way to transform, aggregate, and enrich the incoming log records, as well as convert them to Parquet format. But it comes with additional overhead for custom development of format conversion, checkpointing, error handling, and application management. Evaluate based on your business needs and workflow.

Conclusion

In this post, we discussed multiple approaches to design a platform on AWS to ingest and process high-volume security log records. We compared the pros and cons for each option. Amazon Kinesis is a fully managed and scalable service that helps easily collect, process, and analyze video and data streams in real time. A solution primarily based on Kinesis may become cost prohibitive due to large data volumes. Consider alternate approaches that use containerized applications on AWS Fargate. The trade-off would be the ability for custom development versus application management overhead.

To improve your security log analysis solution, explore one of the approaches we illustrate and customize as appropriate to fit your unique needs.

Create and reuse governed datasets in Amazon QuickSight with new Dataset-as-a-Source feature

Post Syndicated from Shailesh Chauhan original https://aws.amazon.com/blogs/big-data/create-and-reuse-governed-datasets-in-amazon-quicksight-with-new-dataset-as-a-source-feature/

Amazon QuickSight is a fast, cloud-powered, business intelligence (BI) service that makes it easy to deliver insights to everyone in your organization. QuickSight recently introduced Dataset-as-a-Source, a new feature that allows data owners to create authoritative datasets that can then be reused and further extended by thousands of users across the enterprise. This post walks through an example of how QuickSight makes it easy to create datasets that are reusable and easy to govern, with Dataset-as-a-source.

Introducing Dataset-as-a-Source

Dataset-as-a-Source allows QuickSight authors and data owners to create authoritative datasets, as a single source of truth, that either use the QuickSight SPICE in-memory engine, or directly query the underlying database. These datasets may contain data from a single table from a single database, or a combination of data across multiple data sources, including flat files, software as a service (SaaS) sources, databases, and data warehouses. Data owners with a deep understanding of the data can predefine metrics and calculations in the dataset with metadata that makes it easy for authors to understand and consume this data.

After you create these authoritative datasets, you can share them with authors who want to consume this data – either directly on a per-user or group basis, or using shared folders in QuickSight. Authors can now simply use these datasets for their dashboard creation activities, or choose to further augment these datasets by adding additional calculated fields or joining them with other data that is relevant to them. Any updates made by the data owner to the authoritative dataset automatically cascades to the downstream datasets created by individual authors using those datasets. This provides the organization with a secure, governed, and effortless data sharing process that can scale to thousands of authors as needed, without any server setup or data silos on client desktops.

Dataset-as-a-Source example use case

This example use case uses the Amazon Customer Reviews Data. It is public data stored in the us-east-1 Region. You have the following three tables:

  • product_reviews – Customer reviews for a specific product
  • customer – Customer profile data
  • customer_address – Customer address information

The following diagram shows the relationship of the three tables.

In the following sections, we explain the process of using Dataset-as-a-Source using the tables from the preceding schema.

Create central datasets

Let’s say you’re part of the central data engineering team that has access to the preceding data with three tables in a database. Your team serves over 100 business analysts across 10 different teams in eight countries, namely the Coupons team, Outbound Marketing team, Mobile Platform team, and Recommendations team. Analysts on each team want to analyze product and customer data along with data specific to their teams.

Rather than manually creating and maintaining several unconnected datasets for each team, your team created a central dataset. You created the central Product and Customer dataset by joining the three tables in a schema. Dashboard performance and query costs are optimized for large central dataset with Dynamic Querying. Dynamic Querying enables direct query datasets that contain joined tables to run more efficiently. Joins are applied dynamically so that only the subset of tables needed to render a visual are used in the join. Your team also created a key metric (calculated field): Average Rating. All of these teams use the Average Rating metric as a base to analyze their own business line. Its definition is: Average Rating = Sum(Star_rating) / Unique_Count(Start_rating).

Reuse and join with other datasets

Individual teams reused the central Product and Customer dataset and joined it with the data of their own, to create their own datasets. For example, the Marketing team wanted to understand how their team helped improving the product ratings. Therefore, they combined the central Product and Customer dataset with campaign data to create a new dataset: Marketing – Product & Customer Rating. Similarly, the Mobile Platform team combined mobile data with the Product and Customer dataset to understand rating impact on mobile.

With Dataset-as-a-Source, you can centralize data management while allowing each team to customize the data for their own needs, all while syncing updates to the data, such as updates to metric definitions, and maintaining dataset definitions like column names, descriptions, and field folders. Additionally, if these are SPICE datasets, the newly created datasets are created with SPICE as a source, so you don’t reach to the datasource every time a dataset is created or refreshed.

Govern central datasets

You and the central team defined business metrics like Average Rating in the central dataset. With Dataset-as-a-Source, individual teams can use the central dataset without having to redo the work of recreating the field themselves, while extending it with data representing their specific business needs. All teams are able to use a uniform Average Rating metric definition.

Suppose you now want to make a change to the Average Rating definition because of a business operation change. You want to exclude Digital_Video_Download from the product category, and need to provide an updated definition to each team. To make modifications, you have to modify just the central dataset, and the associated datasets get the updates automatically. This saves you time and prevents errors in business metric definitions.

Conclusion

Creating datasets from existing datasets using Dataset-as-a-Source helps you with the following:

  • Dataset governance – Data engineers can easily scale to the needs of multiple teams within their organization by developing and maintaining a few general-purpose datasets that describe the organization’s main data models—without compromising on query performance.
  • Data source management reduction – Analysts spend considerable amounts of time and effort requesting access to databases, managing database credentials, finding the right tables, and managing data refresh schedules. Building new datasets from existing datasets means that analysts don’t have to start from scratch with raw data from databases. They can start with pre-curated data, while also ensuring that on the data source side, optimizations such as workload management can be put in place to ensure optimal performance of backend stores.
  • Metrics accuracy – Creating datasets from existing datasets allows data engineers to centrally define and maintain critical data definitions, such as sales growth and net marginal return, across their company’s many organizations. It also allows them to distribute changes to those definitions, and gives their analysts the ability to get started with visualizing the right data more quickly and reliably.
  • Dataset customization and flexibility – Creating datasets from existing datasets gives analysts more flexibility to customize datasets for their own business needs without worrying about disrupting data for other teams.

This post showed how QuickSight’s Dataset-as-a-Source can help your data management workflows. This feature greatly improves governance and reusability of the datasets. Dataset-as-a-Source is now generally available in Amazon QuickSight Standard and Enterprise Editions in all QuickSight regions. For further details, visit here.


About the Author

Shailesh Chauhan is product managing 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.