Post Syndicated from Rashid Sajjad original https://aws.amazon.com/blogs/big-data/introduction-to-amazon-quicksight-ml-insights/

Amazon QuickSight was launched in November 2016 as a fast, cloud-powered business analytics service to build visualizations, perform ad hoc analysis, and quickly get business insights from a variety of data sources. In 2018, ML Insights for QuickSight (Enterprise Edition) was announced to add machine learning (ML)-powered forecasting and anomaly detection with a few clicks. These insights are automatically generated as suggested insights, and you can also add custom insights to your analysis. Because they’re written out in narrative format, they’re easily consumable by any non-technical user and are a great way to increase adoption of your dashboards. Let’s dive deeper on how these insights are built and how to correctly set up your data to maximize the Suggested Insights feature.

What are ML Insights?

QuickSight uses ML to help uncover hidden insights and trends in your data. It does that by using an ML model that over time and with an increasing volume of data being fed into QuickSight, continually learns and improves its abilities to provide three key features (as of this writing):

• ML-powered anomaly detection – Detect outliers that show significant variance from the dataset. This can help identify significant changes in your business metrics such has low-performing stores or products, or top selling items.
• ML-powered forecasting – Detect trends and seasonality to forecast based on historical data. This can help project sales, orders, website traffic, and more.
• Autonarratives – Embed narratives in your dashboard to tell the story of your data in plain language. This can help convey a shared understanding of the data within your organization. You can use either the suggested autonarrative or you can customize the computations and language to meet your organization’s unique requirements.

How does the ML model work?

QuickSight uses a built-in version of the Random Cut Forest (RCF) algorithm. This is a special type of Random Forest (RF) algorithm, a widely used and successful technique in ML. It takes a set of random data points, cuts them down to the same number of points, and then builds a collection of models. In contrast, a model corresponds to a decision tree—thereby the name “forest.” Because RFs can’t be easily updated in an incremental manner, RCFs were invented with variables in tree construction that were designed to allow incremental updates.

The key takeaway is that RCF is great for finding anomalies and building forecasts. This algorithm is good at finding data points that are outliers or finding trends and patterns to forecast future values.

One important thing to know about ML models is that each model is good at a certain set of predictive activities, but no one model is good for all activities.

Now that you understand what the RCF model is good at, namely anomaly detection and forecasting, you need to make sure the data meets certain requirements, so let’s walk through those steps.

Best practices for setting up data

To maximize the RCF model’s efficiency, the data that is being imported needs to contain certain properties:

• At least one metric – Whatever you’re measuring (sold units, orders, and so on).
• At least one dimension – The category or slice by which you look at the metric (product category, industry, customer type, and so on).
• Data volumes – Your dataset requirements depend on your objective:
  • Anomaly detection – Requires at least 15 data points. For example, if you have Bicycles as a product category and want to detect anomalies at a daily level, you need at least 15 days of transactions (you could have multiple rows for multiple transactions in a given day) for Bicycles in the dataset.
  • Forecasting – This works best with a large dataset simply because the more history you have, the better the model can extract patterns and trends and generate future probable values. If you have daily aggregates, you need at least 38 days of data.
• At least one date column – If we want to analyze anomalies or forecasts in the dataset.

QuickSight supports a wide variety of connections, like Amazon Simple Storage Service (Amazon S3), Amazon Athena, and Apache Spark. For more information about supported connections and some connection examples, refer to Amazon QuickSight Connection examples.

Get started with Suggested Insights

Let’s use a sample dataset and walk through an example of how to use the Suggested Insights feature.

To get started, let’s download a sample dataset from the public domain. For this post, we use House Sales in King County, USA. You need to have a Kaggle account to download the resource.

1. Download and unzip the file.

If you inspect the CVS file, you will notice it has the right grain (date), metrics (price, bedrooms) and categories (zipcode, waterfront).

Depending on what your analysis needs are, even bedrooms could be a category by which you analyze price. So your metrics and categories ultimately depend on your analysis goals.

2. Log in to your QuickSight account or sign up for a QuickSight Enterprise Edition account to use ML Insights.

We need to create a dataset first before we can create a QuickSight analysis.

3. Choose New dataset.
4. Choose Upload a file.
   
5. Choose the unzipped CSV file.
6. In the pop-up window, confirm the file upload settings, then choose Edit settings and prepare data.
   

You’re redirected to the data preparation editor. This is one of the most important yet overlooked functions in QuickSight.

This editor allows you to review your imported fields and their data types, specify if the field will be used as a dimension or measure, along with many other important data import functions. For production datasets, you should spend time reviewing how the dataset has been set up here.

For our sample CSV file, it’s imported into a QuickSight SPICE by default. SPICE is an in-memory engine for fast querying of imported data. For more details, see Importing data into SPICE.

7. Choose Save & publish to start importing the CSV file into the SPICE engine.

The default dataset name is the file name that was imported, so in our case it’s kc_house_data. You can choose the dataset on the Datasets page to see the import stats for the dataset.

8. Choose Create analysis to start creating your QuickSight analysis.
   

The analysis editor page starts by showing a blank Sheet 1 on your workspace. On the top right, your dataset’s import stats are shown again (this becomes important when importing or refreshing large datasets because the import job might still be in progress).

Let’s start by creating our first visual. The default visual type is AutoGraph, which will try to pick the best visual type based on the fields being selected.

9. Choose the date field.

The visual changes to Count of Records by Date, with the date aggregation set to Day.

10. To change the aggregation to monthly, choose the down arrow next to date on the X axis.
    
11. Choose the price field.

The AutoGraph detects that the date is a dimension (blue color) and the price is a measure (green color) because these were set up like that in the dataset editor screen (I mentioned earlier how important the data preparation editor was).

Because these fields are already set up as dimensions and measures, the AutoGraph automatically changes to Sum of Price by Date.

This visualization isn’t very helpful. What we’re really looking for is the average price per month.

12. For Field wells, choose price for Value and change the aggregate to Average.

We now have a nice visual that shows us the average sale price of homes in Kings County by month.

Now comes the fun part—ML Insights!

13. In the navigation pane, choose Insights.

Voila! QuickSight has already run the RCF model along with other statistical computations and has generated insights that are ready to be added.

These suggested insights change based on the type of visual and data that is currently in the visual. We look at how suggested insights change later in this post.

Two immediately useful insights are Highest Month and Lowest Month.

Hover over the Highest Month insight and choose the plus sign to add it to the current Sheet 1.

I can start rearranging insights and visuals and format the price field to give my current layout a more polished look.

14. For this post, change the format of the price field to 1,2345 to remove decimals.
15. You can also add titles for the insights and rename the X axis label date to Aggregate.
    
16. To add another sheet, choose the plus sign next to Sheet 1.

By default, we start again with an AutoGraph visual.

17. Under Visual types¸ choose the vertical bar chart.
18. Choose the price and zipcode fields.
19. Change the aggregation of price from Sum to Average.
20. Choose Insights in the navigation pane.
    

Suggested Insights now displays a completely different set of data highlights compared to Sheet 1.

Although the vertical bar chart may already tell you the top three and bottom three zip codes, Suggested Insights already recognized the type of analysis and selected the best insights to display.

Although you might eventually build a visual to portray the intended story, Suggested Insights speeds up the process of showcasing the highlights in your data and adding them to your worksheet to quickly give the reader the most important insights from your visuals.

Anomaly detection

An anomaly in QuickSight is described a data point that fall outside an overall pattern of distribution. ML-powered anomaly detection in QuickSight enables you to identify the causations and correlations to make data-driven decisions.

We already talked about data preparation for anomaly detection earlier. QuickSight already ran the RCF model during data import. As soon as a visual is added, QuickSight notifies you on the visual if it has detected an “Anomaly Insight.” This part of Suggested Insights. You can choose Setup anomaly detection to add this to your sheet.

You can also manually add an ML insight to detect anomalies.

1. Let’s go back to Sheet 1 with the line chart displayed.
2. When you choose the first suggested insight, it starts creating a widget for anomaly detection.
   

You can add up to five dimensions fields (not calculated fields, unless they were created in the data prep screen). QuickSight splits the metrics using the fields in the Categories section. We use the date field (our time dimension), price (our metric), and yr_built (our category) to create an anomaly detection insight. The question we are trying to answer is “Were there any monthly outliers in price based on the year built?”

3. Choose Get started to set up anomaly detection.
   
4. For Combinations to be analyzed, choose your field combinations.

Choosing Exact means that the date and price are analyzed against the yr_built dimension. You can also choose Hierarchical or All. These latter options become relevant when you choose multiple dimensions in the Categories list. For more information about these options, refer to Adding an ML insight to detect outliers and key drivers.

5. Choose Save to return to Sheet 1.

Our widget is configured at this point.

6. Choose Run now to start analyzing the data for anomalies.

Based on the volume of data and the number of data points in the analysis, it may take a while to run the anomaly detection.

Keep in mind that at least 15 data points are needed to run an anomaly, but then you can change the aggregation of a field to have a zoom-out view and therefore view anomalies at a higher level.

For example, if you choose the date field and change Aggregate to Monthly, you get the top anomalies at the monthly level.

In our test case, QuickSight identified a top anomaly. This is a great widget that immediately draws the reader to highlights in data that are outliers and might require further investigation.

Forecast

With ML-powered forecasting, you can forecast your key business metrics in QuickSight easily. The ML algorithm in QuickSight is designed to handle complex real-world scenarios. Not only does QuickSight provide the capability to create forecasts, it also provides Forecast as a Suggested Insight.

1. Going back to Sheet 1, choose the line chart and expand Insights.

At the bottom you will see a suggested forecast insight. Forecast insights, along with all other suggested insights, are dynamic in the sense that when your data updates or when a user applies filters, the values in the insight will update immediately. Once you add this to your sheet you can even customize how many periods in the future you want the insight to display for the forecast by editing the Narrative and then editing the forecast Calculation.

What if we wanted to customize the price forecasting on this line chart and add it in the visual?

2. Choose the options menu (three dots) at the top right of the visual and choose Add forecast.
   
3. For Periods forward, enter 6.

That is the time interval selected for the visual.

4. Set Prediction interval to 70.

This is the amount of interval between data points. It causes the forecast to either go wider or narrower. A wider interval means wider gaps between data points, which means the net change is higher, and vice versa.

5. Leave Seasonality set to Automatic.

Seasonality takes into account complex seasonal trends in your data. You can experiment with both settings to see how it affects the forecast. For our scenario, because house sales are seasonal, we chose Automatic.

6. Choose Apply.

With just a few clicks, we have added a forecast to our visual, as shown in the following screenshot. The orange shaded area represents the upper and lower bound of the forecasted price.

This is another great way to add intelligence to your data and quickly let analysts focus on key data points and trends.

Conclusion

The Suggested Insights feature in QuickSight allows you to speed up the discovery and highlighting of key data elements. You can find insights in your data faster, and because they’re written out in narrative format, they’re very easy for non-technical users to quickly gain insight into the most interesting trends in the data with no ML training needed.

For more details on QuickSight ML Insights, refer to the QuickSight documentation or interact with the QuickSight Community.

As always, AWS is customer obsessed and we are ready to help with any specific questions.

About the Author

Rashid Sajjad is a Partner Management Solutions Architect focused on Big Data & Analytics with Amazon Web Services. He works with APN Partners to help develop their Migration, Data & Analytics and AI/ML Practices with enterprise, mission critical solutions for their end customers.r

Post Syndicated from Sriharsh Adari original https://aws.amazon.com/blogs/big-data/enable-amazon-quicksight-federation-with-google-workspace/

Amazon QuickSight is a scalable, serverless, embeddable, machine learning (ML)-powered business intelligence (BI) service built for the cloud that supports identity federation in both Standard and Enterprise editions. Organizations are working towards centralizing their identity and access strategy across all of their applications, including on-premises, third-party, and applications on AWS. Many organizations use Google Workspace to control and manage user authentication and authorization centrally. You can enable federation to QuickSight accounts without needing to create and manage users. This authorizes users to access QuickSight assets—analyses, dashboards, folders, and datasets—through centrally managed Google Workspace Identities.

In this post, we go through the steps to configure federated single sign-on (SSO) between a Google Workspace instance and QuickSight account. We demonstrate registering an SSO application in Google Workspace, and map QuickSight roles (admin, author, and reader) to Google Workspace Identities. These QuickSight roles represent three different personas supported in QuickSight. Administrators can publish the QuickSight app in a Google Workspace Dashboard to enable users to SSO to QuickSight using their Google Workspace credentials.

Solution overview

In your organization, the portal is typically a function of your identity provider (IdP), which handles the exchange of trust between your organization and QuickSight.

On the Google Workspace Dashboard, you can review a list of apps. This post shows you how to configure the custom app for AWS.

The user flow consists of the following steps:

1. The user logs in to your organization’s portal and chooses the option to go to the QuickSight console.
2. The portal verifies the user’s identity in your organization.
3. The portal generates a SAML authentication response that includes assertions that identify the user and include attributes about the user. The portal sends this response to the client browser. Although not discussed here, you can also configure your IdP to include a SAML assertion attribute called SessionDuration that specifies how long the console session is valid.
4. The client browser is redirected to the AWS single sign-on endpoint and posts the SAML assertion.
5. The endpoint requests temporary security credentials on behalf of the user, and creates a QuickSight sign-in URL that uses those credentials.
6. AWS sends the sign-in URL back to the client as a redirect.
7. The client browser is redirected to the QuickSight console. If the SAML authentication response includes attributes that map to multiple AWS Identity and Access Management (IAM) roles, the user is first prompted to select the role for accessing the console.

The following diagram illustrates the solution architecture.

The following are the high-level steps to set up federated single sign-on access via Google Workspace:

1. Download the Google IdP information.
2. Create an IAM IdP with Google as SAML IdP.
3. Configure IAM policies for QuickSight roles.
4. Configure IAM QuickSight roles for federated users.
5. Create a custom user attribute in Google Workspace.
6. Add the AWS SAML attributes to your Google Workspace user profile.
7. Set up the AWS SAML app in Google Workspace.
8. Grant access to users in Google Workspace.
9. Verify federated access to your QuickSight instance.

Detailed procedures for each of these steps comprise the remainder of this post.

Prerequisites

For this walkthrough, you should have the following prerequisites:

• A Google Workspace subscription
• An AWS account with QuickSight subscription
• Basic understanding of QuickSight roles—admin, author, and reader
• Basic understanding of IAM and privileges required to create an IAM identity provider, roles, policies, and users

Download the Google IdP information

First, let’s get the SAML metadata that contains essential information to enable your AWS account to authenticate the IdP and locate the necessary communication endpoint locations. Complete the following steps:

1. Log in to the Google Workspace Admin console.
2. On the Admin console home page, under Security in the navigation pane, choose Authentication and SSO with SAML applications.
3. Under IdP metadata, choose Download Metadata.
   

Create an IAM IdP with Google as SAML IdP

You now configure Azure AD as your SAML IdP via the IAM console. Complete the following steps:

1. On the IAM console, choose Identity providers in the navigation pane.
2. Choose Add provider.
3. For Configure provider, select SAML.
4. For Provider name, enter a name for the IdP (such as Google).
5. For Metadata document, choose Choose file and specify the SAML metadata document that you downloaded.
6. Choose Add provider.
   
7. Document the Amazon Resource Name (ARN) by viewing the IdP you just created.

The ARN should looks similar to arn:aws:iam::<YOURACCOUNTNUMBER>:saml-provider/Google. We need this ARN to configure claim rules later in this post.

Configure IAM policies for QuickSight roles

In this step, we create three IAM policies for different role permissions in QuickSight:

• QuickSight-Federated-Admin
• QuickSight-Federated-Author
• QuickSight-Federated-Reader

Use the following steps to set up the QuickSight-Federated-Admin policy. This policy grants admin privileges in QuickSight to the federated user:

1. On the IAM console, choose Policies.
2. Choose Create policy.
3. Choose JSON and replace the existing text with the following code:

   {
       “Version”: “2012-10-17”,
       “Statement”: [
           {
               “Effect”: “Allow”,
               “Action”: “quicksight:CreateAdmin”,
               “Resource”: “*”
           }
       ]
   }

4. Choose Review policy.
5. For Name, enter QuickSight-Federated-Admin.
6. Choose Create policy.
7. Repeat these steps to create QuickSight-Federated-Author, and use the following policy to grant author privileges in QuickSight to the federated user:

   {
       “Version”: “2012-10-17”,
       “Statement”: [
           {
               “Effect”: “Allow”,
               “Action”: “quicksight:CreateUser”,
               “Resource”: “*”
           }
       ]
   }

8. Repeat the steps to create QuickSight-Federated-Reader, and use the following policy to grant reader privileges in QuickSight to the federated user:

   {
       “Version”: "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Action": "quicksight:CreateReader",
               "Resource": "*"
           }
       ]
   }

Configure IAM QuickSight roles for federated users

Next, create the roles that Google IdP users assume when federating into QuickSight. The following steps set up the admin role:

1. On the IAM console, choose Roles in the navigation pane.
2. Choose Create role.
3. For Trusted entity type, choose SAML 2.0 federation.
4. For SAML provider, choose the provider you created earlier (Google).
5. For Attribute, choose SAML:aud.
6. For Value, enter https://signin.aws.amazon.com/saml.
7. Choose Next.
   
8. On the Add permissions page, select the QuickSight-Federated-Admin IAM policy you created earlier.
9. Choose Next.
   
10. For Role name, enter QuickSight-Admin-Role.
11. For Role description, enter a description.
12. Choose Create role.
    
13. On the IAM console, in the navigation pane, choose Roles.
14. Choose the QuickSight-Admin-Role role you created to open the role’s properties.
15. On the Trust relationships tab, choose Edit trust relationship.
16. Under Trusted entities, verify that the IdP you created is listed.
17. Under Condition, verify that SAML:aud with a value of https://signin.aws.amazon.com/saml is present.
18. Repeat these steps to create author and reader roles and attach the appropriate policies:
    1. For QuickSight-Author-Role, use the policy QuickSight-Federated-Author.
    2. For QuickSight-Reader-Role, use the policy QuickSight-Federated-Reader.
       
19. Navigate to the newly created roles and note the ARNs for them.

We use these ARNs to configure claims rules later in this post. They are in the following format:

• arn:aws:iam:: <YOURACCOUNTNUMBER>:role/QuickSight-Admin-Role
• arn:aws:iam:: <YOURACCOUNTNUMBER>:role/QuickSight-Author-Role
• arn:aws:iam:: <YOURACCOUNTNUMBER>:role/QuickSight-Reader-Role

Create a custom user attribute in Google Workspace

Now let’s create a custom user attribute in your Google Workspace. This allows us to add the SAML attributes that the AWS Management Console expects in order to allow a SAML-based authentication.

1. Log in to Google Admin console with admin credentials.
2. Under Directory, choose Users.
3. On the More options menu, choose Manage custom attributes.
   
4. Choose Add Custom Attribute.
5. For Select type of trusted entity, choose SAML 2.0 federation.
6. Configure the custom attribute as follows:
   1. Category: Amazon
   2. Description: Amazon Custom Attributes
7. For Custom fields, enter the following:
   1. Name: Role
   2. Info type: Text
   3. Visibility: Visible to user and admin
   4. No. of values: Multi-value
8. Choose Add.

The new category appears on the Manage user attributes page.

Add the AWS SAML attributes to the Google Workspace user profile

Now that we have configured a custom user attribute, let’s add the SAML attributes that we noted earlier to the Google Workspace user profile.

1. While logged in to the Google Admin console with admin credentials, navigate to the Users page.
2. In the Users list, find the user. If you need help, see Find a user account.
3. Choose the user’s name to open their account page.
4. Choose User information.
5. Choose custom attribute you recently created, named Amazon.
6. Add a value to this custom attribute noted earlier in the following format: <AWS Role ARN>,<AWS provider/IdP ARN>.
7. Choose Save.
   

Set up the AWS SAML app in Google Workspace

Now that we have everything in place, we’re ready to create a SAML app within our Google Workspace account and provide the QuickSight instance starting URL. This provides the entry point for Google Workspace users to SSO into the QuickSight instance.

1. While logged in to Google Admin console with admin credentials, under Apps, choose Web and mobile apps.
2. Choose Add App, and Search for apps.
3. Enter Amazon Web Services in the search field.
4. In the search results, hover over the Amazon Web Services SAML app and choose Select.
   
5. On the Google Identity Provider details page, choose Continue.
6. On the Service provider details page, the ACS URL and Entity ID values for Amazon Web Services are configured by default.
7. For Start URL, enter https://quicksight.aws.amazon.com.
   
8. On the Attribute Mapping page, choose the Select field menu and map the following Google directory attributes to their corresponding Amazon Web Services attributes:
   
   

   Google Directory Attribute	Amazon Web Services Attribute
   Basic Information > Primary Email	https://aws.amazon.com/SAML/Attributes/RoleSessionName
   Amazon > Role	https://aws.amazon.com/SAML/Attributes/Role

   

9. Choose Finish.

Grant access to users in Google Workspace

When the SAML app is created in Google workspace, it’s turned off by default. This means for users logged in to their Google Workspace account, the SAML app isn’t visible to them. We now enable the AWS SAML app to your Google Workspace users.

1. While logged in to the Google Admin console with admin credentials, navigate to the Web and mobile apps page.
2. Choose Amazon Web Services.
   
   
3. Choose User access.
   
4. To turn on a service for everyone in your organization, choose ON for everyone.
5. Choose Save.

If you don’t want to activate this application for all users, you can alternatively grant access to a subset of users by using Google Workspace organizational units.

Verify federated access to the QuickSight instance

To test your SAML 2.0-based authentication with QuickSight for users in your existing IDP (Google Workspace), complete the following steps:

1. Open a new browser session, for example, using Chrome, in a new incognito window.
2. Log in to your Google Workspace account (for the purpose of this demo, we use the Google Workspace admin account).
3. Choose Amazon Web Services from the list of Google apps.
   

Conclusion

This post provided a step-by-step guide for configuring Google Workspace as your IdP, and using IAM roles to enable SSO to QuickSight. Now your users have a seamless sign-in experience to QuickSight and have the appropriate level of access related to their role.

Although this post demonstrated the integration of IAM and Google Workspace, you can replicate this solution using your choice of SAML 2.0 IdPs. For other supported federation options, see Using identity federation and single sign-on (SSO) with Amazon QuickSight.

To get answers to your questions related to QuickSight, refer to the QuickSight Community.

If you have any questions or feedback, please leave a comment.

About the Authors

Sriharsh Adari is a Senior Solutions Architect at Amazon Web Services (AWS), where he helps customers work backwards from business outcomes to develop innovative solutions on AWS. Over the years, he has helped multiple customers on data platform transformations across industry verticals. His core area of expertise include Technology Strategy, Data Analytics, and Data Science. In his spare time, he enjoys playing sports, binge-watching TV shows, and playing Tabla.

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.

Post Syndicated from Shekhar Kopuri original https://aws.amazon.com/blogs/big-data/tips-and-tricks-for-high-performant-dashboards-in-amazon-quicksight/

Amazon QuickSight is cloud-native business intelligence (BI) service. QuickSight automatically optimizes queries and execution to help dashboards load quickly, but you can make your dashboard loads even faster and make sure you’re getting the best possible performance by following the tips and tricks outlined in this post.

Data flow and execution of QuickSight dashboard loads

The data flow in QuickSight starts from the client browser to the web server and then flows to the QuickSight engine, which in some cases executes queries against SPICE—a Super-fast, Parallel, In-memory Calculation Engine—or in other cases directly against the database. SPICE uses a combination of columnar storage, in-memory technologies enabled through the latest hardware innovations, and machine code generation to run interactive queries on large datasets and get rapid responses.

The web server, QuickSight engine, and SPICE are auto scaled by QuickSight. This is a fully managed service—you don’t need to worry about provisioning or managing infrastructure when you want to scale up a particular dashboard from tens to thousands of users on SPICE. Dashboards built against direct query data sources may require provisioning or managing infrastructure on the customer side.

The following diagram illustrates the data flow:

Let’s look at the general execution process to understand the implications:

• A request is triggered in the browser, leading to several static assets such as JavaScript, fonts, and images being downloaded.
• All the metadata (such as visual configurations and layout) is fetched for the dashboard.
• Queries are performed, which may include setting up row-level and column-level security, or fetching dynamic control values, default parameters, and all values of drop-downs in filter controls.
• Up to your concurrency limit, the queries to render your visuals run in a specific sequence (described later in this post). If you’re using SPICE, the concurrency of queries is much higher. Pagination within visuals may lead to additional queries.

The actual execution is more complex and depends on how dashboards are configured and other factors such as the data source types, Direct Query vs. SPICE, cardinality of fields and how often data is getting refreshed etc.  Many operations run in parallel and all visual-related queries are run via WebSocket, as shown in the following screenshot. Many of the steps run in the end-user’s browser, therefore there are limitations such as the number of sequences and workloads that can be pushed onto the browser. Performance may also be slightly different based on the browser type because each browser handles contention differently.

Now let’s look at many great tips that can improve your dashboard’s performance!

SPICE

Utilizing the capabilities of SPICE when possible is a great way to boost overall performance because SPICE manages scaling as well as caching results for you. We recommend using SPICE whenever possible.

Metadata

As seen in the preceding execution sequence, QuickSight fetches metadata up front for a given dashboard during the initial load. We recommend the following actions regarding metadata.

Remove unused datasets from analysis

Datasets that may have been used in the past but have no visual associated with the dashboard anymore add to the metadata payload unnecessarily. It’s likely to impact to dashboard performance.

Make sure your row-level and column-level security is performant

Row-Level security, column-level security and dynamic default parameters each require lookups to take place before the visual queries are issued. When possible, try to limit the number and the complexity of your rules datasets to help these lookups execute faster. Use SPICE for your rules dataset when possible. If you must use a direct query, make sure that the queries are optimal and that the data source you’re querying is scaled appropriately up front.

For embedded dashboards, a great way to optimize row-level security lookups is by utilizing session tags for row-level security paired with an anonymous identity. Similarly, dynamic default parameters, if used, can be evaluated in the host application up front and passed using the embedding SDK.

Calculated functions

In this section, we offer tips regarding calculated functions.

Move calculations to the data prep stage

QuickSight allows you to add calculated fields in the data prep or analysis experiences. We strongly encourage you to move as many calculations as possible to the data prep stage which will allow QuickSight to materialize calculations which do not contain aggregation or parameters into the SPICE dataset. Materializing calculated fields in the dataset helps you reduce the runtime calculations, which improves query performance. Even if you are using aggregation or parameters in your calculation, it might still be possible to move parts of the calculations to data prep. For instance, if you have a formula like the following:

You can remove the sum() and just keep the ifelse(), which will allow QuickSight to materialize (precompute) it and save it as a real field in your SPICE dataset. Then you can either add another calculation which sums it up, or just use sum aggregation once you add it to your visuals.

Generally materializing calculations that use complex ifelse logic or do string manipulation/lookups will result in the greatest improvements in dashboard performance.

Implement the simplified ifelse syntax

The ifelse function supports simplified statements. For example, you might start with the following statement:

The following simplified statement is more performant:

Use the toString() function judiciously

The toString() function has a much lower performance and is much heavier on the database engine than a simple integer or number-based arithmatic calculations. Therefore, you should use it sparingly.

Know when nulls are returned by the system and use null value customization

Most authors make sure that null conditions on calculated fields are handled gracefully. QuickSight often handles nulls gracefully for you. You can use that to your advantage and make the calculations simpler. In the following example, the division by 0 is already handled by QuickSight:

You can write the preceding code as the following:

If you need to represent nulls on visuals with a static string, QuickSight allows you to set custom values when a null value is returned in a visual configuration. In the preceding example, you could just set a custom value of 0 in the formatting option. Removing such handling from the calculated fields can significantly help query performance.

On-sheet filters vs. parameters

Parameters are seemingly a very simple construct but they can quickly get complicated, especially when used in nested calculation functions or when used in controls. Parameters are all evaluated on the fly, forcing all the dependencies to be handled real time. Ask yourself if each parameter is really required. In some cases, you may be able to replace them with simple dropdown control, as shown in the following example for $market.

Instead of creating a control parameter to use in a calculated field, you might be able to use the field with a dropdown filter control.

Text field vs. Dropdown (or List) filter controls

When you are designing an analysis, you can add a filter control for the visuals you want to filter. if the data type of the field is string, you have several choices for the type of control filter. Text field which displays a text box where you can enter a single entry or multiple entries is suggested for the better performance, rather than Dropdown (or List) which requires to fetch the values to populate a list that you can select a single or multiple values.

On-sheet controls

The control panel at the top of the dashboard is collapsible by default, but this setting allows you to have an expanded state while publishing the dashboard. If this setting is enabled, QuickSight prioritizes the calls in order to fetch the controls’ values before the visual loads. If any of the controls have high cardinality, it could impact the performance of loading the dashboard. Evaluate this need against the fact that QuickSight persists last-used control values and the reader might not actually need to adjust controls as a first step.

Visual types: Charts

In this section, we provide advice when using Charts.

Use ‘Hide the “other” category’ when your dimension has less than the cutoff limit

You can choose to limit how many data points you want to display in your visual, before they are added to the other category. This category contains the aggregated data for all the data beyond the cutoff limit for the visual type you are using – either the one you impose or the one based on display limits. If you know your dimension has less than the cutoff limit, use this option. This will improve your dashboard performance.

The other category does not show on scatter plots, heat maps, maps, tables (tabular reports), or key performance indicators (KPIs). It also doesn’t show on line charts when the x-axis is a date.

Visual types: Tables and pivot tables

In this section, we provide advice when using tables and pivot tables.

Use the Values field well when displaying a raw table view

If you want to output all the raw data into table, you can use Group by fields, Values fields, or a mix of them. The most performant approach is set every field into Values. When using Group by, a query is first run under the hood followed by the Group by function, therefore all the data is pulled from the database, which is expensive.

Deploy a minimal set of rows, columns, metrics, and table calculations

If you include too many combinations of rows, columns, metrics, and table calculations in one pivot table, you risk overwhelming the viewer. You can also run into the computational limitations of the underlying database. To reduce the level of complexity and potential errors, you can take the following actions:

• Apply filters to reduce the data included in for the visual
• Use fewer fields in the Row and Column field wells
• Use as few fields as possible in the Values field well
• Create additional pivot tables so that each displays fewer metrics
• Reduce subtotals, totals and conditional formatting when possible

Uncollapsed columns are always the simplest case and will likely remain more performant outside of a few cases.

Visual queries sequence

The execution of the individual visual sequence is left to right, then top to bottom. Understanding the sequence of execution can be helpful: you can rearrange visuals on your dashboard without losing the context. Place heavier visuals further down in the dashboard, and place lightweight KPI and insight visuals near the top to display “above-the-fold” content sooner, which improves the dashboard performance’s perception for your readers.

Embedding

Our final set of recommendations are in regards to embedding.

Remove user management flows from the critical path

Most times, user management and authentication flows (such as DescribeUser and RegisterUser APIs) can run asynchronously on the host application.

Consider registering the user in advance before the actual embedding, so that the overhead is removed from every analytics page visit.

Authenticate the user on your website in advance, and acquire any Amazon Cognito or AWS Security Token Service (Amazon STS) session tokens (if required) in advance (for example, at user login time or home page visit). This reduces additional runtime latency overhead when a user visits an analytics page.

Move workloads from clients to the web server or backend services

If a QuickSight dashboard is embedded on a webpage on the host application, which performs other activities too, play close attention to the sequence of API calls on the host. The QuickSight dashboard load might be gated by other heavy API calls on the host application. Move the logic to the web server or backend services as much as possible to limit contention on the browser.

Don’t tear down the embedding iFrame when the user navigates away from analytics section

When the user moves temporarily to a non-analytics page of your web application (especially in single-page applications), instead of removing the embedding iframe from DOM, you can hide it from the user while keeping the iFrame in the page DOM elements. This allows you to resume the same session when the user navigates back to analytics section of your application, and they don’t need to wait for reload.

Use navigateToDashboard() and navigateToSheet() whenever possible

If you have multiple dashboards on your host application that don’t need to load concurrently, you can optimize the authentication flow by utilizing two APIs we expose, navigateToDashboard() or navigateToSheet(), in our JavaScript SDK. These APIs reuse the same iFrame for each load, while reusing the authentication token.

This technique has proven to be very effective for many of our embedding users.

For more information about these APIs, refer to Amazon QuickSight Embedding SDK.

Conclusion

In this post, we shared some tips and tricks for tuning the performance of your QuickSight dashboards. In 2021, we doubled our SPICE data limits to 500 million rows of data per dataset. In addition, incremental data refresh is available for SQL-based data sources such as Amazon Redshift, Amazon Athena, Amazon RDS, Amazon Aurora, PostgreSQL, MySQL, Oracle, SQL Server, MariaDB, Presto, Teradata or Snowflake up to every 15 minutes, which cuts down time between data updates by 75%. In 2022, we continue to innovate on your behalf to make QuickSight dashboard loads even more performant.

We look forward to your feedback on how these tips and tricks helped your dashboards load faster.

About the Authors

Shekhar Kopuri is a Senior Software Development Manager for Amazon QuickSight. He leads the front platform engineering team that focusses on various aspects of front end experience including website performance. Before joining AWS, Shekhar led development of multiple provisioning and activation network OSS applications for a large global telecommunications service provider.

Blake Carroll is a Senior Frontend Engineer for Amazon QuickSight. He works with the frontend platform engineering team with a focus on website performance and has previously been the frontend lead for initial reporting and theming functionality in QuickSight. Prior to joining Amazon, Blake was a co-founder in the digital interactive agency space working with national brands to produce creative web experiences.

Vijay Chaudhari is a Senior Software Development Engineer for Amazon QuickSight, AWS’ cloud-native, fully managed BI service. Vijay started his career with IBM, writing software for the Information Management group. At Amazon, he has built backend applications for retail systems, and near real-time data pre-computation, reporting and analytics systems at Amazon scale. He is passionate about learning and solving new customer problems, and helping them adopt cloud native technologies.

Wakana Vilquin-Sakashita is Specialist Solution Architect for Amazon QuickSight. She works closely with customers to help making sense of the data through visualization. Previously Wakana worked for S&P Global  assisting customers to access data, insights and researches relevant for their business.

Post Syndicated from Mia Heard original https://aws.amazon.com/blogs/big-data/top-amazon-quicksight-features-and-updates-launched-q1-2022/

Amazon QuickSight is a serverless, cloud-based business intelligence (BI) service that brings data insights to your teams and end users through machine learning (ML) powered dashboards and data visualizations, which can be access via QuickSight or embedded in apps and portals that your users access. This post shares the top QuickSight features and updates launched in Q1 2022.

Amazon QuickSight Community

In the new Amazon QuickSight Community, you can ask and answer questions, network with and learn from other BI users from across the globe, access learning content, and stay up to date with what’s new on QuickSight all in one place!

Join the global QuickSight Community today!

Groups Management UI

QuickSight now provides a user interface to manage user groups, allowing admins to efficiently and easily manage user groups via the QuickSight admin console. Groups Management UI is available to administrators with access to QuickSight admin console pages via AWS Identity and Access Management (IAM) credentials.

To learn more, see Creating and managing groups in Amazon QuickSight.

Comparative and cumulative date and time calculations

Amazon QuickSight authors can now quickly implement advanced date/time calculations without having to use complicated row offsets or pre-computed columns. You can add these calculations in regular business reporting, trend analysis, and time series analysis.

To learn more about the new period functions and their capabilities in various use cases, see Add comparative and cumulative date/time calculations in Amazon QuickSight.

Rich text formatting on visual titles and subtitles 

QuickSight authors can now add rich context to their visuals by choosing from various formatting options like font type, size, style, color and style. You can also better organize the text by choosing from various alignment and ordering options. Visual titles and subtitles now also support hyperlinks as well as parameter-based dynamic text.

To learn more, see Formatting a visual title and subtitle.

Custom subtotals at all levels on pivot table

QuickSight allows you to customize how subtotals are displayed in pivot tables, with options for display at multiple levels and for both rows and columns.

To learn more, see Displaying Totals and Subtotals.

Auto refresh direct query controls

QuickSight now supports automatic refreshes of values displayed in drop-down, multi-select and other controls in dashboards that are in direct query mode. Values within controls are updated every 24 hours to ensure the data is automatically updated without any end-user intervention.

For further details, see Refreshing data in Amazon QuickSight.

Conclusion

QuickSight serves millions of dashboard views weekly, enabling data-driven decision-making in organizations of all sizes, including customers like the NFL, 3M, Accenture, and more.

To stay up to date on all things new with QuickSight, visit What’s New with Analytics!

About the Author

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

Post Syndicated from Kareem Syed-Mohammed original https://aws.amazon.com/blogs/big-data/amazon-quicksight-1-click-public-embedding-available-in-preview/

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 public embedding, a feature that allows you to embed your dashboards into public applications, wikis, and portals without any coding or development needed. Anyone on the internet can start accessing these embedded dashboards with up-to-date information instantly, without any server deployments or infrastructure licensing needed! 1-click public embedding allows you to empower your end-users with access to insights.

In this post, we walk you through the steps to use this feature, demonstrate the end-user experience, and share sample use cases.

Solution overview

1-click public embedding requires an administrator of the QuickSight account to enable this feature and use session-based pricing. After you complete the prerequisites (see the following section), the 1-click embedding process involves three simple steps:

1. Enable public access on the dashboard
2. Allow list the domain where you want to embed the dashboard in QuickSight
3. Embed the dashboard

Prerequisites

1. The account needs to have session capacity pricing enabled
2. As a prerequisite, make sure your QuickSight account is enabled for dashboards to be shared by the public. Because this feature allows dashboards to be enabled for any user, the usability of this feature must be enabled by an administrator of your QuickSight account with the following AWS Identity and Administration (IAM) permissions:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Action": "quicksight:UpdatePublicSharingSettings",
               "Resource": "*",
               "Effect": "Allow"
           }
        ]
   }

After you enable this policy, you can activate the account-level settings.

1. Sign in to your QuickSight account.
2. On the user name drop-down menu, choose Manage QuickSight.
   
3. Choose Security & permissions in the navigation pane.
4. Under Public access to dashboards, choose Manage.
   
5. Enable the setting Everyone on the internet to allow public access.
   
6. Confirm this setting and choose Accept.
   

Enabling this setting doesn’t automatically enable all the dashboards to be accessed by anyone on the internet. It gives the ability for authors of the dashboards to individually enable the dashboard to be accessed by anyone on the internet via the share link or when embedded.

Enable public access permissions on the dashboard

After you create a QuickSight dashboard, to enable public access, complete the following steps:

1. On the QuickSight dashboard, on the Share menu, choose Share dashboard.

Only owners and co-owners of the dashboard can perform this action.

2. Under Enable access for, turn on Anyone on the internet (public).
   

This setting allows you to share this dashboard with anyone on the internet via the share link or when embedded.

3. Accept this setting and choose Confirm.
   

You now have the option to copy the link or the embed code to share the dashboard. Note that when this setting is enabled, the dashboard can only be accessed using the link or when embedded using the embed code.

After you enable the dashboard for public access, you can see badges on the dashboard as follows.

The dashboard toolbar has a PUBLIC badge.

The dashboards grid view has an eye icon for each dashboard.

The dashboards list view has an eye icon for each dashboard.

Disable the public access

You can disable the public access of your dashboards in two ways:

1. Disable the Anyone on the internet setting for the dashboard (to be done by the owners and co-owners of the dashboard).
2. Disable the Public access to dashboard setting at the account level (to be done by the IAM admin). You can disable the account-level settings even if the dashboard-level setting is enabled. This still prevents the dashboards from being accessed publicly.

Allow list the domain where you want to embed the dashboard in QuickSight

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

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 an internal application.

The copied embed code is similar to the following code:

    <iframe
        width="960"
        height="720"
        src="https://quicksight.aws.amazon.com/sn/
        embed/share/accounts/accountid/dashboards/dashboardid">
    </iframe>

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.

If your public-facing applications are built on Google Sites, to embed your dashboard, open the page on Google Sites, then 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.

After you embed the dashboard in your application, anyone who can access your application can now access the embedded dashboard.

Sample use cases

1-click public embedding enables you to embed your dashboards into public applications, wikis, and portals without any coding or development needed. In this section, we present two sample use cases.

For our first use case, a fictitious school district uses 1-click public embedding to report on the teachers’ enrollment in the district. They built a dashboard and used this feature to embed it on their public-facing site.

For our second use case, a fictitious fintech that provides investment solutions is using 1-click public embedding to show how their investment compares against other well-known indexes and commodities. They used this feature to add this comparison dashboard on their public-facing marketing pages.

Try out 1-click public embedding

To try out this feature, see Embed Amazon QuickSight dashboard in seconds. In this demo, you can change the dashboard between a logistics or sales dashboard by choosing Change Dashboard and entering the embed code for the dashboard you want to render on the site.

Conclusion

With 1-click public embedding, you can now embed rich and interactive QuickSight dashboards quickly and easily. Enable your end-users to dive deeper into their data through embedded dashboard with the click of a button—and with no infrastructure setup or management, scale to millions of users. 1-click public embedding is now in preview; to access this feature, please contact [email protected].

QuickSight also supports embedding in SaaS apps without any user management needed. For more information, refer to Embed multi-tenant dashboards in SaaS apps using Amazon QuickSight without provisioning or managing users.

To stay up to date on QuickSight embedded analytics, check out what’s new with the 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.

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

Post Syndicated from Lillie Atkins original https://aws.amazon.com/blogs/big-data/announcing-the-new-amazon-quicksight-community/

On February 22, 2022, we launched our new Amazon QuickSight Community. Here you can ask and answer questions, network with and learn from other Business Intelligence (BI) users from across the globe, access learning content, and stay up to date with what’s new on Amazon QuickSight—all in one place!

In this post, we discuss some of the features of the QuickSight Community and show you how to sign up, start posting, create a profile and set up notifications.

Content on the Community

The QuickSight Community has three main sections:

1. Question and Answer – A discussion forum where you can ask your QuickSight questions and get answers from community experts. You can also showcase your QuickSight expertise by sharing your knowledge with others.
   
2. Learning Center – A hub for on-demand QuickSight content, including how-to videos workshop videos, articles, and additional educational resources.
   
3. Announcements – Stay up-to-date on the latest QuickSight launches, blogs, feature demo videos, and monthly newsletters. We continuously update this section as new content related to QuickSight is added.
   

Join the QuickSight Community

This QuickSight Community does not require any login to search or browse existing content. You only need to create an account if you want to interact with the community (such as liking posts, replying to posts, and posting your own questions).

This is a public community, so be careful not to post any confidential or private information.

Sign up for the Community

To sign up, complete the following steps:

1. On the QuickSight Community home page, choose Sign Up.
   
2. You can either use your existing Amazon.com account, or create a new login.
   
3. Read the Community guidelines.

You can now contribute to the Community. Welcome!

Post a question

You need to be logged in with an account to start asking questions. Before you post a question, search the recommended questions to make sure your question hasn’t already been answered.

To post a question, complete the following steps:

1. On the Question and answer page, choose New Question.
   
2. Enter the information to create your post, including a title and tag.
3. Choose New Question.
   

Set up your profile

To view your profile and change the settings, complete the following steps:

1. Choose your user icon at the top of the page and choose the person icon.
   
2. Select the section you are interested in:
   
3. If you chose Preferences, you can control security and notification preferences, and update your profile details.
   

Set up notifications

You can set up notification preferences to be alerted on a specific post, channel, or tag.

To set up notifications on a post, choose the post and then choose the notification icon to set your notification preferences.

To get notified on a specific channel, go to the channel (in this case the Question and answer channel) and choose the notification bell to set your notification preferences.

To get notified on a specific tag, complete the following steps:

1. Choose the additional options icon.
   
2. Choose Tags.
   
3. Choose the tag you are interested in.
   
4. Choose the notification icon and set your notification preferences.
   

Conclusion

In this post, we discussed the new Amazon QuickSight Community, and how you can sign up for it, create a post, edit your profile, and set up notifications. The QuickSight Community is a one-stop shop for all of your QuickSight learning needs, and a place to network with other BI users from around the globe. Start exploring today!

About the Authors

Lillie Atkins is a Product Manager for Amazon QuickSight, Amazon Web Service’s cloud-native, fully managed BI service.

Mia Heard is a Product Marketing Manager for Amazon QuickSight, AWS’ cloud-native, fully managed BI service.

Post Syndicated from Deepak Murthy original https://aws.amazon.com/blogs/big-data/enable-users-to-ask-questions-about-data-using-natural-language-within-your-applications-by-embedding-amazon-quicksight-q/

Amazon QuickSight Q is a new machine learning-based capability in Amazon QuickSight that enables users to ask business questions in natural language and receive answers with relevant visualizations instantly to gain insights from data. QuickSight Q doesn’t depend on prebuilt dashboards or reports to answer questions, which removes the need for business intelligence (BI) teams to create or update dashboards every time a new business question arises. Users can ask questions and receive visual answers in seconds directly from within QuickSight or from web applications and portals. In this post, we look at how you can embed Q in your web applications or portals.

Solution overview

You can now embed Q in your application without any custom development. Q is a fully managed cloud-native BI offering that you can easily embed without requiring expertise within your team to develop and maintain this capability. You can easily democratize your data and scale your insights to a growing user base, while ensuring you only pay for usage with Q’s unique pay-per-question pricing model.

Applications can authenticate users with any identity provider of choice (such as Active Directory, Amazon Cognito, or any SAML-based federated SSO provider that your organization uses) and act on behalf of the user to get access to the Q question bar. This means that every user receives a secure, personalized question answering experience while requiring no user-facing QuickSight-specific authentication. This enables a novel experience to provide insights within your application with minimal upfront work and allows you to focus on your core application functionality! QuickSight Q embedding is available in QuickSight Enterprise Edition and Q-supported Regions.

To facilitate an easy embedding experience, AWS has also launched the Amazon QuickSight Embedding SDK (JavaScript) and a rich set of Q-specific functionalities. The QuickSight Embedding SDK lets you efficiently integrate Q in your application pages, set a default topic, enable topic selection, set themes, and control Q search bar behavior. This helps you roll out Q to your users faster.

To embed Q in your application, you must complete the following high-level steps:

1. Set up permissions to generate embedded Q URLs.
2. Generate a URL with the authentication code attached.
3. Embed the Q search bar URL.

Set up permissions to generate embedded Q URLs

In this step, you set up permissions for your backend application or web server to embed the Q search bar. This task requires administrative access to AWS Identity and Access Management (IAM). Each user who accesses the Q search bar assumes a role that gives them QuickSight permissions to retrieve a Q-embedded URL.

To make this possible, create an IAM role in your AWS account. Associate an IAM policy with the role to provide permissions to any user who assumes it. The IAM role needs to provide permissions to retrieve embedding URLs for a specific user pool. With the help of the wildcard character *, you can grant the permissions to generate a URL for all users in a specific namespace. Or you can grant permissions to generate a URL for a subset of users in specific namespaces. For this, you add quicksight:GenerateEmbedUrlForRegisteredUser.

The following sample policy provides these permissions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "quicksight:GenerateEmbedUrlForRegisteredUser"
            ],
            "Resource": "arn:partition:quicksight:region:accountId:user/namespace/userName"
        }
    ]
}

Also, if you’re creating first-time users who will be QuickSight readers, make sure to add the quicksight:RegisterUser permission in the policy.

The following sample policy provides permission to retrieve an embedding URL for first-time users who will be QuickSight readers:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": "quicksight:RegisterUser",
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Effect": "Allow",
            "Action": [
              "quicksight:GenerateEmbedUrlForRegisteredUser"
            ],
            "Resource": [
              "arn:partition:quicksight:region:accountId:user/namespace/userName"
            ]
        }
    ]
}

Finally, your application’s IAM identity must have a trust policy associated with it to allow access to the role that you just created. This means that when a user accesses your application, your application assumes the role on the user’s behalf and provisions the user in QuickSight.

The following example uses a role called embedding_quicksight_q_search_bar_role, which has the sample policy preceding as its resource:

{
    "Version": "2012-10-17",
    "Statement": {
        "Effect": "Allow",
        "Action": "sts:AssumeRole",
        "Resource": "arn:aws:iam::11112222333:role/embedding_quicksight_q_search_bar_role"
    }
}

Generate a URL with the authentication code attached

In this step, you authenticate your user and get the embeddable Q topic URL on your application server. If you plan to embed the Q bar for IAM or QuickSight identity types, share the Q topic with the users. When a user accesses your app, the app assumes the IAM role of the user. If that user is new, the app adds the user to QuickSight, then passes an identifier as the unique role session ID.

These steps make sure that each viewer of the Q topic is uniquely provisioned in QuickSight. It also enforces per-user settings, such as the row-level security and dynamic defaults for parameters.

The following example code performs the IAM authentication on the user’s behalf. This code runs on your app server:

import com.amazonaws.auth.AWSCredentials;
import com.amazonaws.auth.BasicAWSCredentials;
import com.amazonaws.auth.AWSCredentialsProvider;
import com.amazonaws.regions.Regions;
import com.amazonaws.services.quicksight.AmazonQuickSight;
import com.amazonaws.services.quicksight.AmazonQuickSightClientBuilder;
import com.amazonaws.services.quicksight.model.GenerateEmbedUrlForRegisteredUserRequest;
import com.amazonaws.services.quicksight.model.GenerateEmbedUrlForRegisteredUserResult;
import com.amazonaws.services.quicksight.model.RegisteredUserEmbeddingExperienceConfiguration;
import com.amazonaws.services.quicksight.model.RegisteredUserQSearchBarEmbeddingConfiguration;

/**
 * Class to call QuickSight AWS SDK to get url for embedding the Q search bar.
 */
public class RegisteredUserQSearchBarEmbeddingConfiguration {

    private final AmazonQuickSight quickSightClient;

    public RegisteredUserQSearchBarEmbeddingConfiguration() {
        this.quickSightClient = AmazonQuickSightClientBuilder
                .standard()
                .withRegion(Regions.US_EAST_1.getName())
                .withCredentials(new AWSCredentialsProvider() {
                                     @Override
                                     public AWSCredentials getCredentials() {
                                         // provide actual IAM access key and secret key here
                                         return new BasicAWSCredentials("access-key", "secret-key");
                                     }

                                     @Override
                                     public void refresh() {}
                                 }
                )
                .build();
    }

    public String getQuicksightEmbedUrl(
            final String accountId, // AWS Account ID
            final String topicId, // Topic ID to embed
            final String userArn // Registered user arn to use for embedding. Refer to Get Embed Url section in developer portal to find how to get user arn for a QuickSight user.
    ) throws Exception {
        final RegisteredUserEmbeddingExperienceConfiguration experienceConfiguration = new RegisteredUserEmbeddingExperienceConfiguration()
                .withQSearchBar(new RegisteredUserQSearchBarEmbeddingConfiguration().withInitialTopicId(topicId));
        final GenerateEmbedUrlForRegisteredUserRequest generateEmbedUrlForRegisteredUserRequest = new GenerateEmbedUrlForRegisteredUserRequest();
        generateEmbedUrlForRegisteredUserRequest.setAwsAccountId(accountId);
        generateEmbedUrlForRegisteredUserRequest.setUserArn(userArn);
        generateEmbedUrlForRegisteredUserRequest.setExperienceConfiguration(QSearchBar);

        final GenerateEmbedUrlForRegisteredUserResult generateEmbedUrlForRegisteredUserResult = quickSightClient.generateEmbedUrlForRegisteredUser(generateEmbedUrlForRegisteredUserRequest);

        return generateEmbedUrlForRegisteredUserResult.getEmbedUrl();
    }
}

To generate the URL that you can embed in your app, call the GenerateEmbedUrlForRegisteredUser API operation. This URL is valid for 5 minutes, and the resulting session is valid for up to 10 hours. You can configure the session validity by setting the sessionLifetimeinMinutes parameter for GenerateEmbedURL APIs. The API operation provides the URL with an auth_code value that enables a single-sign on session. The following code shows an example response from generate-embed-url-for-registered-user:

//The URL returned is over 900 characters. For this example, we've shortened the string for
//readability and added ellipsis to indicate that it's incomplete.
{
 "Status": "200",
 "EmbedUrl": "https: //dashboards.example.com/embed/620bef10822743fab329fb3751187d2d...",
 "RequestId": "7bee030e-f191-45c4-97fe-d9faf0e03713"
} 

Embed the Q search bar URL

In this step, you embed the Q search bar URL in your website or application page. You can do this with the QuickSight Embedding SDK, which allows you to do the following:

• Place the Q search bar on an HTML page
• Pass parameters into the Q search bar
• Handle error states with messages that are customized to your application

Embed the Q search bar in your webpage by using the QuickSight Embedding SDK or by adding this URL into an iFrame. If you set a fixed height and width number (in pixels), QuickSight uses those and doesn’t change your visual as your window resizes. If you set a relative percent height and width, QuickSight provides a responsive layout that is modified as your window size changes. When you use the QuickSight Embedding SDK, the Q search bar on your page is dynamically resized based on the state. By using the QuickSight Embedding SDK, you can also control parameters within the Q search bar and receive callbacks in terms of page load completion and errors.

The following example code shows how to use the generated URL. This code is generated on your app server:

<!DOCTYPE html>
         <html>
     
         <head>
             <title>QuickSight Q Search Bar Embedding</title>
             <script src="https://unpkg.com/[email protected]/dist/quicksight-embedding-js-sdk.min.js"></script>
             <script type="text/javascript">
                 var session
     
                 function onError(payload) {
                     console.log("Do something when the session fails loading");
                 }
     
                 function onOpen() {
                     console.log("Do something when the Q search bar opens");
                 }
     
                 function onClose() {
                     console.log("Do something when the Q search bar closes");
                 }
     
                 function embedQSearchBar() {
                     var containerDiv = document.getElementById("embeddingContainer");
                     var options = {
                         url: "https://us-east-1.quicksight.aws.amazon.com/sn/dashboards/dashboardId?isauthcode=true&identityprovider=quicksight&code=authcode", // replace this dummy url with the one generated via embedding API
                         container: containerDiv,
                         width: "1000px",
                         locale: "en-US",
                         qSearchBarOptions: {
                             expandCallback: onOpen,
                             collapseCallback: onClose,
                             iconDisabled: false,
                             topicNameDisabled: false, 
                             themeId: 'theme12345',
                             allowTopicSelection: true
                         }
                     };
                     session = QuickSightEmbedding.embedQSearchBar(options);
                     session.on("error", onError);
                 }
     
                 function onCountryChange(obj) {
                     session.setParameters({country: obj.value});
                 }
             </script>
         </head>
     
         <body onload="embedQSearchBar()">
             <div id="embeddingContainer"></div>
         </body>
     
         </html>

For this example to work, make sure to use the QuickSight Embedding SDK to load the embedded dashboard on your website using JavaScript. You can get the SDK in the following ways:

• Download the QuickSight Embedding SDK from GitHub. This repository is maintained by a group of QuickSight developers.
• Download the latest QuickSight Embedding SDK version from npmjs.com.
• If you use npm for JavaScript dependencies, download and install it by running the following command:

  npm install amazon-quicksight-embedding-sdk

The following screenshot is an example of the embedded Q question bar and a QuickSight dashboard to support natural language questions and analysis of pharmaceutical clinical trial data. You can try out this topic and other such topics in an embedded application demo.

Summary

Enterprises can empower users to ask questions about data in plain English within their applications by embedding the QuickSight Q question bar. Embedding Q into your application is straightforward and requires no custom development from your team.

Get started with a free trial of QuickSight Q.

About the Authors

Deepak Murthy is a Senior Product Manager for Amazon QuickSight, AWS’s cloud-native, fully managed BI service. Deepak started his career with Staples, developing enterprise data warehouse solutions. Later, he was the architect of data warehouse and analytics solutions at Wells Fargo, AMC, and Blackhawk Network. Deepak is excited about the potential of self-service analytics and improving data accessibility by enabling new natural language interactions with data, and looks forward to helping customers leverage these latest analytics innovations.

Rob Foley is a Software Development Engineer for Amazon QuickSight, AWS’s cloud-native, fully managed BI service. Rob began his career with AWS, and has been a member of the QuickSight team for over 1.5 years. He has development experience in a breadth of services and stacks, primarily having worked on data-centric applications like Q.

Post Syndicated from Sumitha AP original https://aws.amazon.com/blogs/big-data/use-analyticsiq-with-amazon-quicksight-to-gain-insights-for-your-business/

Decisions are made every day in your organization that impact your business. Making the right decision at the right moment can deeply impact your organization’s growth and your customers. Likewise, having the right data and tools that generate insights into the data can empower your organization’s leaders to make the right decisions.

In the healthcare industry where decisions directly impact an individual’s wellness, having the right data to generate the right insight into the individual experience through the lens of social determinants of health can greatly improve health outcomes and save lives. Understanding the unique social situations of the individuals they serve, from access to transportation, technology to economic, food security and more, allows healthcare providers to address disparities and give all their patients an equal opportunity to achieve their desired level of health.

For example, let’s say a healthcare organization or government agency wants to better understand the factors that affect public health in order to improve the quality of life for various ethnic groups, based on data.

In this post, we show you how to use AnalyticsIQ datasets and Amazon QuickSight to generate valuable insights that could improve your organization’s decision-making. we use the AnalyticsIQ Social Determinants of Health Sample Data dataset to gain insights into the relationship between ethnicity and health, as well as how the social determinants impact the health and wellness of individuals.

Solution overview

The following architecture diagram outlines the components of this solution:

The solution consists of the following components:

• The AnalyticsIQ Social Determinants of Health Sample Data dataset to demonstrate an example of the impact of social determinants on health and wellness. This dataset is listed as AnalyticsIQ in the Extended Provider Program.
• AWS Data Exchange to subscribe to the sample AnalyticsIQ dataset from AWS Marketplace.
• An Amazon Simple Storage Service bucket to store the AnalyticsIQ sample dataset.
• QuickSight enterprise edition to generate visualizations using the sample dataset.

To implement the solution, you complete the following high-level steps:

1. Export the dataset to an S3 bucket.
2. Sign up for a QuickSight subscription.
3. Create a QuickSight dataset.
4. Create visualizations in QuickSight.

Prerequisites

To run this solution, you must have an AWS account. If you don’t already have one, you can create one.

Export the dataset to an S3 bucket

To start working with your dataset, you must subscribe to the dataset and then export the data to an S3 bucket. Complete the following steps:

1. If you don’t already have a bucket, navigate to the Amazon S3 console, and choose Create bucket.
2. Give a unique name for your bucket.

Make sure that you create the bucket in the us-east-1 Region.

3. To subscribe to the sample dataset, follow this link. On the AWS Data Exchange console, choose Continue to subscribe.
4. On the Complete subscription page, choose Subscribe.
5. For Select Amazon S3 bucket folder destination, choose your S3 bucket.

The subscription process can take up to 2 minutes to complete.

6. On the AWS Data Exchange Console, under My subscriptions in the navigation pane, choose Entitled Data.
7. Under Products, expand Social Determinants of Health Sample Data – Offline, and choose the AnalyticsIQ sample dataset.
8. On the Revisions tab, select the revision and choose Export to Amazon S3.
   
9. Enter the name of the S3 bucket you created for this dataset.
10. Leave the other options as default.
11. Choose Export.

You can view the dataset in your S3 bucket under the prefix Sample-Data.

Sign up for a QuickSight subscription

To sign up for a QuickSight subscription, complete the following steps:

1. On the AWS Management Console, open QuickSight.
2. Choose Sign up for QuickSight and choose Enterprise.
3. For QuickSight account name, enter a unique name.
4. Enter a valid email.
5. Under Allow access and autodiscovery for these resources, select Amazon S3 and choose Select S3 buckets.
   
6. Choose the S3 bucket that you created earlier, and choose Finish.
7. After your QuickSight account is created, choose Go to QuickSight account.

Create a QuickSight dataset

To create your dataset, complete the following steps:

1. Using a local text editor, create a JSON file. Copy the following content and replace the placeholder with the name of the bucket that you created earlier:

   {
         "fileLocations": [
             {
                 "URIPrefixes": [
                          "https://<Your BucketName>.s3.amazonaws.com/Sample-Data/"
                 ]
             }
          ],
        "globalUploadSettings": {
                 "format": "TSV"
           }
   }

2. On the QuickSight console, choose New data set on the Datasets page.
3. Choose S3.
4. For DataSource, enter a name.
5. Choose Upload and upload the JSON file.
6. Choose Connect.
7. Choose Visualize.

The following screenshot shows your imported sample data:

Create visualizations in QuickSight

Let’s visualize the average number of cars by various ethnic groups. For more information about the fields, refer to the Key Data Points section on the AWS Marketplace listing.

1. Choose the sheet and choose the vertical bar chart under Visual types.
   
2. From the Fields list, drag EthnicIQ_v2 to X axis and Number_of_Autos to Value.
3. Choose Aggregate as Average.
   

Now you can create a visualization for urgent care visits by ethnic groups.

4. Choose +Add, and choose Add Visual.
5. Choose a pivot table under Visual types.
6. From the Fields list, drag EthinicIQ_v2 to Rows and HW_Urgent_Care_Visits_SC to Values.
7. Choose Aggregate as Average.
8. Choose the HW_Urgent_Care_Visits_SC field in the pivot table, and choose Sort descending.
   

Similarly, you can add more visualizations as shown in the following images.

From these visualizations created from sample data, you can see that a person’s use of healthcare services reduces when they have less access to transportation. The AA ethnic group has fewer cars compared to the other groups. The wellness score for the AA group is low when compared to the others. Transportation barriers could be a major factor here. Job satisfaction also contributes to wellness levels. Furthermore, the sample data indicates that the Hispanic community has the highest likelihood of recent urgent care visits. Does this mean these groups aren’t getting enough preventative care, leading to more urgent care visits?

Sleep and job satisfaction play a critical role in affecting stress levels, as well as overall health. This would be a critical factor for people who work shifts. What measures can be taken to increase the sleep quality for that set of people?

These are just few of the innumerable valuable analyses that you can create from the AnalyticsIQ Social Determinants of Health Sample Data dataset. These insights are valuable for various groups of people, such as health professionals, preventative care, employee care, scientists, and governments, to empower communities and help build better public health and social determinant solutions.

Clean up

To avoid incurring ongoing charges, complete the following steps to clean up your resources:

1. On the QuickSight console, on the Analyses page, choose the details icon on the analysis you created, and choose Delete.
2. On the QuickSight start page, on the Datasets page, choose the dataset that you created earlier, then choose Delete Data Set.
3. On the Amazon S3 console, on the Buckets page, select the option next to the name of your bucket, and then choose Delete at the top of the page.
4. Confirm that you want to delete the bucket by entering the bucket name into the text field, then choose Delete bucket.

Conclusion

In this post, we showed you how you can use the AnalyticsIQ Social Determinants of Health Sample Data dataset to gain insights into society’s health and wellness. We also showed you how you can generate easy-to-understand visualizations using QuickSight. Amazon QuickSight allows dashboards to be shared with 1000s of users without any servers, and with pay-per-session pricing. QuickSight dashboards can also be easily embedded in SaaS applications or corporate portals for sharing insights with all users. You can explore the AnalyticsIQ dataset more on the AWS Data Exchange console. For queries related to the AnalyticsIQ dataset, you can reach out directly to the support team at [email protected].To learn more about the features of QuickSight, refer to Amazon QuickSight Features.

About the Author

Sumitha AP is an AWS Solutions Architect based in Washington DC. She works with SMB customers to help them design secure, scalable, reliable and cost effective solutions in the AWS cloud.

Post Syndicated from Emily Zhu original https://aws.amazon.com/blogs/big-data/add-comparative-and-cumulative-date-time-calculations-in-amazon-quicksight/

Amazon QuickSight recently added native support for comparative (e.g., year-over-year) and cumulative (e.g., year-to-date) period functions which allow you to easily introduce these calculations in business reporting, trend analysis and time series analysis. This allows authors in QuickSight to implement advanced calculations without having to use complicated date offsets in calculations to achieve such datetime-aware comparisons.

In this post, we introduce the new period functions and their capabilities, and demonstrate several typical use cases. We also discuss several scenarios to extend the usage of the period functions, which will be useful in more advanced situations.

New period functions

Before we demonstrate use cases, let’s go over the new period function suite and see what new functions we now support. We can divide period functions into two main groups: comparative (period over period) functions and cumulative (period to date) functions.

Comparative (period over period) functions

You can use period over period functions to compare measures at different time periods, such as year, quarter, and month. For example, you can compute a year-over-year increase in sales, or week-over-week percentage revenue changes.

A typical comparative period function has the syntax periodOverPeriodDifference(measure, date, period, offset), with two optional arguments: period and offset.

You can use the period argument in the function to define the period granularity of the calculation. The granularity of YEAR means year-over-year computation, Quarter means quarter-over-quarter, and so on. If the period argument is left empty, the calculation changes based on the period granularity that is chosen (in the field well) to be displayed in the visual.

You can also use the offset argument to specify how many periods apart you want to compute the comparison. For instance, a period of a quarter with an offset of 2 means comparing against the previous two quarters.

Note that period and offset have to be both specified or both left empty. You can’t specify just one of them.

The following table summarizes the three available period over period functions.

Cumulative (period to date) functions

You can use period to date functions to calculate metrics within a given period-to-date window. There are two main types of cumulative functions.

• “OverTime” functions, which are table calculations and return outputs for each row in the visual.

E.g., you can use periodToDateCountOverTime with WEEK granularity to compute a series of week-to-date new customer counts to track the fluctuation of customer engagement.

• Aggregation functions, which output an aggregated value for a fixed period-to-date time range.

E.g., periodtoDateSum with YEAR granularity returns a single value for the total of the metric from the beginning of the year to the endDate provided in the formula. If left blank, the default endDate takes now(), which is the moment when users load the dashboard.

The following table summarizes the period to date functions.

Use case 1: Using a period over period function to analyze sales growth in fixed and dynamic granularity

Let’s dive into how period over period functions can support typical business and financial use cases. The following example uses periodOverPeriodDifference to calculate YoY sales growth. Because we have Segment and Region in the visual, the YoY sales is calculated for each segment and region.

We define the measure of YoYSales with the following formula: YoYSales=periodOverPeriodDifference(sum(Sales),{Order Date},YEAR,1)

The first argument, sum(Sales), tells the function to calculate based on this measure. The second argument, Order Date, specifies the date/time column from which Year information is extracted. The third argument, YEAR, fixes the granularity of this calculation. When this optional argument is specified, this measure always returns YoY (not QoQ or MoM) no matter how Order Date is selected (in the field well) to be displayed in the visual. The fourth argument, 1, specifies the offset of the comparison. In this example, it means we want to compare the sales of each order date with the same date of the previous year. The measure returns empty for order dates of 2018, because no previous periods exist to be compared with.

The period functions are working with totals and subtotals. By adding the total for columns into the visual, you can see the total sales and total YoYSales for each region.

If you leave the optional argument of period granularity empty, meaning change the formula to PoPSales=periodOverPeriodDifference(sum(Sales),{Order Date})as shown in the following example, the time period of the calculation is then determined by the granularity of Order Date displayed on the visual. In the following example, Order Date is chosen to display at quarter level (in the field well), so PoPSales dynamically calculates the QoQ sales growth. Changing Order Date to the monthly level updates the measure to calculate MoM. For PoPSales, only Q1 2018 returns empty because that’s the only quarter that doesn’t have a previous quarter to compare with.

If we add YoYSales from the previous example to this visual, it calculates YoY sales growth at the quarter level (compares sales of Q1 2019 with Q1 2018). This demonstrates the difference between a fixed granularity and a dynamic granularity of period over period functions.

The period over period functions can differentiate between a positive change (increase) and negative change (decrease). Therefore, when we add the conditional formatting to the visual, it’s very straightforward to see the financial performance of each period (green is good, red is bad).

Similarly, you can use periodOverPeriodPercentDifference to calculate relative sales growth over time. You can add dimensions into the visual to dive further into business insights, such as analyzing the breakdown of each business segment’s sales change by quarter, and their contribution to the total sales increase. We use the formula PoPSales%=periodOverPeriodPercentDifference(sum(Sales),{Order Date}).

Use case 2: Using a period to date function to track YTD sales in table calculations and aggregations

Similar to period over period functions, the period to date function suite provides a quick and easy way to calculate year-to-date (YTD) or quarter-to-date (QTD) metrics. In the following example, we use the formula of YTDSumoverSales=periodToDateSumOverTime(sum(Sales),{Order Date},YEAR), and YTDtotalcountSales=periodToDateSumOverTime(count(Sales),{Order Date},YEAR) to calculate YTD sales and YTD total number of transactions.

Contrary to period over period functions, the third argument of period to date functions, period, isn’t optional. Therefore, the calculation granularity is always fixed. In this example, with the granularity defined as YEAR, this measure always calculates YTD, instead of QTD or MTD. Because Order Date is displayed at the monthly level, this calculation outputs the YTD sales of each month, and starts over again in January for the next year. As shown in the result table, YTDSumoverSales of January 2018 is the monthly sales of January 2018, and YTDSumoverSales of February 2018 is the monthly sales of January 2018 plus that of February 2018. And YTDSumoverSales of January 2019 goes back to the monthly sales of January 2019.

You can further dive into the details by populating the calculations in a line chart, and adding more dimensions into the analysis. The following example shows the YTD weekly sales growth trend for each region along the past four years, and uncovers some interesting sales competition between AMER and EMEA in year 2021.

In addition to the table calculations, the aggregation period functions are particularly useful when you need to build KPI charts to evaluate YTD metrics in a real-time manner. In the following example, we use the aggregation period to date functions to build two KPI charts to track the YTD total sales, and YTD total number of transactions. For the date December 26, 2021, the timestamp results match the corresponding table calculations for the date of December 26, 2021 in the table. The following table summarizes the formulas.

Advanced use case 1: Date/time awareness with period functions

Period functions are not only easier to define and read, they’re also date/time-aware, meaning the functions are calculated based on a date/time-based offset instead of a fixed number of rows. It can solve two major problems that were not possible to be addressed before.

Period functions can handle varying period duration

If you want to calculate the daily MoM sales increase, you can’t use a fixed offset on each month because the number of days of each month are different (31 days for January and 28 or 29 days for February).

Period functions are calculated based on calendar dates instead of a fixed offset. In the following example, we use the formula MoMsalesDiff=periodOverPeriodDifference(sum(Sales),{Order Date},MONTH,1). The daily MoM increase is calculated correctly based on the day of the month. The sales of the first day of the month are compared with the first day of the previous month, and the same applies to all other days. (Visuals are duplicated for demonstration purposes.)

Period functions can handle sparse (missing) data points

Not all datasets can guarantee a complete set of dates. In the previous example, sales data of January 1, 2018, is missing. Using the workaround based on a fixed offset can cause a problem here because we compare February 1, 2018, with a different date instead of January 1, 2018. Period functions always compare measures by date/time offsets so that only desired dates are compared. In the previous example, MoMsalesDiff shows empty for February 1, 2018, because of the missing data of January 1, 2018.

Advanced use case 2: Nesting period functions with other calculations

Now that we can use period over period and period to date functions to create calculated fields, we can nest these functions with other calculations to drive more advanced analysis.

For example, you may want to know for each year, what are the top 10 weeks of the year in regards to week-over-week sales growth. You can do this by calculating WoWSales first: WoWSales=periodOverPeriodDifference(sum(Sales), {Order Date}, WEEK, 1). Then you nest it with the denseRank window function: RankWoWSales=denseRank([WoWSales DESC],[{YEAR}]). This wouldn’t be possible using the fixed-row based workaround, which is implemented using on-visual calculations instead of calculated fields. In the following visual, the top 10 weeks of each year with the highest sales growth are fetched by a simple filter on RankWoWSales.

You can even nest the period functions with other period functions to generate interesting insights. For example, you can calculate a monthly YoY growth based on the monthly YTD number of transactions. The following formula demonstrates the capability of nesting a YTD calculated field inside a YoY calculated field:

YTDtotalsountSales=periodToDateSumOverTime(count(Sales),{Order Date},YEAR)
YoYYTDSalesCount=periodOverPeriodDifference(YTDtotalcountSales,{Order Date},YEAR,1)

The results in the following visual show a YoY growth based on a YTD accumulated number of transactions instead of the absolute monthly numbers.

Advanced use case 3: Partial period comparisons

Finally, we discuss a third advanced use case: partial period comparison. Imagine it’s November 15, 2021 (which is the 46th day of the last quarter of 2021), and you want to calculate 4 to compare the performance of this quarter with past quarters, but only using the first 46 days of each quarter instead of the whole quarter. This requires a calculated field using periodOverPeriodDifference nested with the sumIf() window function.

The following example demonstrates using a nested calculated field to address this use case:

QuarterToDate=periodToDateSumOverTime(sum(Sales), {Order Date}, QUARTER)
PartialQTDSales=sumIf(Sales, dateDiff(truncDate("Q", {Order Date}), {Order Date}, "HH") <= dateDiff(truncDate("Q", now()), now(), "HH"))
PartialQoQQTDSales=periodOverPeriodDifference(sumif(Sales, {Order Date} <= addDateTime(dateDiff(truncDate("Q", now()), now(), "HH"), "HH", truncDate("Q", {Order Date}))), {Order Date}, QUARTER, 1)

PartialQTDSales computes how many hours from the beginning of this quarter to the current date and uses sumIf() to calculate the total sales of that period of each quarter. partialQoQQTDSales then nests the periodOverPeriodDifference function with PartialQTDSales to find the partial QoQ differences. Such a comparison based on a partial period isn’t feasible without the new date/time-aware period functions.

Conclusion

In this blog, we introduced new QuickSight period functions which enable quick and powerful date/time-based calculations. We reviewed comparative and cumulative period functions (i.e., period over period and period to date), discussed two major use cases (fixed vs. dynamic granularity and table calculation vs. aggregation), and extended the usage to three advanced use cases. Period functions are now generally available in all supported QuickSight Regions.

Looking forward to your feedback and stories on how you apply these calculations for your business needs.

About the Authors

Emily Zhu is a Senior Product Manager at Amazon QuickSight, AWS’s cloud-native, fully managed SaaS BI service. She leads the development of QuickSight core analytics and calculations capability. Before joining AWS, she was working in the Amazon Prime Air drone delivery program and the Boeing company as senior strategist for several years. Emily is passionate about potentials of cloud-based BI solutions and looks forward to helping customers to advance in their data-driven strategy making.

Rajkumar Haridoss is a Senior Software Development Engineer for AWS QuickSight. He is the lead engineer on the Query Generation team and works on back-end calculations, query planning, and query generation layer in QuickSight. Outside of work, he likes spending quality time with family and 4-year-old.