Appendix D: Visualize Data in Amazon QuickSight - AWS Trusted Advisor Explorer

Appendix D: Visualize Data in Amazon QuickSight

Configure Amazon QuickSight

Use this procedure to visualize the data this solution collects.

Before you begin, your account must be registered for Amazon QuickSight. For more information, refer to Setting Up Amazon QuickSight.

  1. Navigate to the Amazon QuickSight console.

  2. Choose your username on the top right of the console, then select Manage QuickSight.

  3. Choose Security & Permissions.

  4. Under QuickSight access to AWS services, choose Add or Remove.

  5. Select Amazon S3. If this option is already selected, uncheck and recheck the option.

  6. Select the specific Amazon Simple Storage Service (Amazon S3) solution bucket listed in the output section of the AWS CloudFormation deployment from the list of Amazon S3 buckets. Select Write permission for Athena Workgroup.

  7. Choose Finish.

  8. Choose Update.

Create a Data Source and Import the First Data Set

Use the Creating a Data Set Using Amazon Athena Data topic in the Amazon QuickSight Developer Guide to create a new data source and import the first data set into QuickSight.

For Step 9 in the Creating a Data Set Using Amazon Athena documentation, choose the aws_trusted_advisor_explorer_db database.

For Step 10 in the Creating a Data Set Using Amazon Athena documentation, select any table ending with _view (for example, amazonrdsidledbinstances_view).

Creating a Data Set Using an Existing Amazon Athena Data Source

Use the Creating a Data Set Using an Existing Amazon Athena Data Source topic in the Amazon QuickSight Developer Guide to create the remaining Athena views as new Data Sets in Quicksight.

For Step 5 in the Creating a Data Set Using an Existing Amazon Athena Data Source documentation, select tables ending with _view (for example, amazonrdsidledbinstances_view) and repeat the process until you create a data set for all the Athena views tables.

Adding All the Data Sets to an Analysis

After you create the data sets for the Athena views tables, you must add them to an analysis.

Use the Add or Edit a Data Set topic in the Amazon QuickSight Developer Guideto add a data set to an existing analysis.

You can delete unwanted analysis that were created as part of the data set creation.

You can create a dashboard that visualizes the AWS Trusted Advisor Explorer data. For more information, see Working with Data in Amazon QuickSight and Working with Analyses in the Amazon QuickSight User Guide.

Columns Appended & Modified to aid Amazon QuickSight Visualization

In the summary_view table, the following additional columns have been added/modified:

  • date_time: Use this column in Amazon QuickSight for any visuals requiring date-time measure. This column is adjusted to reflect as a Date datatype field in QuickSight.

  • optimizationPercent: The Optimization percentage per checkID; This field can be used to determine how optimized the specific check is for an account. This can also be used to understand the cost savings opportunity percentage by account.

    Formula: (1-(resourcesflagged/resourcesprocessed)) *100
  • trueoptimizationPercent: This field has the same function as optimizationPercent but it excludes resources marked as ignored and suppressed.

    Formula:(1-(resourcesflagged-(resourcesignored+resourcessuppressed)/resourcesprocessed)) *100

In the amazonrdsidledbinstances_view, idleloadbalancers_view tables, the following additional columns have been added/modified:

  • date_time: Use this column in QuickSight for any visuals requiring date-time measure. This column is adjusted to reflect as a Date datatype field in Quicksight.

  • estimated_monthly_savings: Use this column in QuickSight for any visuals requiring monthly savings measure. This column is adjusted to reflect as a Decimal datatype field in QuickSight.

In the unassociatedelasticipaddresses_view, route53latencyresourcerecordsets_view tables, the following additional column has been added/modified:

  • date_time: Use this column in QuickSight for any visuals requiring date-time measure. This column is adjusted to reflect as a Date datatype field in Quicksight.

In the ec2reservedinstanceleaseexpiration_view table, the following additional columns have been added/modified:

  • date_time: Use this column in QuickSight for any visuals requiring date-time measure. This column is adjusted to reflect as a Date datatype field in QuickSight.

  • current_monthly_cost: Use this column in QuickSight for any visuals requiring current monthly costs measure. This column is adjusted to reflect as a Decimal datatype field in QuickSight.

  • estimated_monthly_savings: Use this column in QuickSight for any visuals requiring monthly savings measure. This column is adjusted to reflect as a Decimal datatype field in QuickSight.

  • expiration_date: The expiration date field from the original data is modified to reflect as a Date datatype field in QuickSight.

In the ec2reservedinstancesoptimization_view table, the following additional columns have been added/modified:

  • date_time: Use this column in QuickSight for any visuals requiring date-time measure. This column is adjusted to reflect as a Date datatype field in QuickSight.

  • estimated_savings_with_recommendation_monthly, upfront_cost_of_ris, estimated_cost_of_ris_monthly, estimated_on-demand_cost_post_recommended_ri_purchase_monthly: These fields from the original data are modified to reflect as a Decimal datatype field in QuickSight.

In the lowutilizationamazonec2instances_view table, the following additional columns have been added/modified:

  • date_time: Use this column in QuickSight for any visuals requiring date-time measure. This column is adjusted to reflect as a Date datatype field in QuickSight.

  • estimated_monthly_savings: Use this column in QuickSight for any visuals requiring monthly savings measure. This column is adjusted to reflect as a Decimal datatype field in QuickSight.

  • average_cpu_utilization_14_days, average_network_i/o_utilization_14 days: These fields from the original data are modified to reflect as a Decimal datatype field in QuickSight.

In the underutilizedamazonebsvolumes_view table, the following additional columns have been added/modified:

  • date_time: Use this column in QuickSight for any visuals requiring date-time measure. This column is adjusted to reflect as a Date datatype field in QuickSight.

  • Monthly_Storage_Cost: This field from the original data is modified to reflect as a Decimal datatype field in QuickSight.

Refreshing a Data Set on a Schedule

You can configure an automatic refresh for all of your imported data sets. Use the Refreshing a Data Set on a Schedule topic in the Amazon QuickSight Developer Guide to set up a refresh schedule for all of your imported data sets.

Note

Ensure that your QuickSight refresh schedule is aligned with your Glue crawler schedule. The QuickSight data set refresh must run after the Glue crawlers finish running.