Visualize Amazon QuickSight costs using AWS CUR and cost allocation tags

Amazon QuickSight is a business intelligence (BI) solution that any organization can leverage to share data and insights to anyone in the organization. As a serverless BI tool, it offers a comprehensive set of advanced analytics features, and one core benefit is that pricing is consumption based. That being said, Quicksight can be leveraged for different use cases and each variant might require a different method to track and report on the cost of running QuickSight. For example, perhaps you have deployed QuickSight as your main enterprise BI tool with thousands of users across dozens of departments and would like to charge back by department. Or maybe you have integrated QuickSight visuals in your software as a service (SaaS) application using namespaces to separate your customers by tenant to deliver insights to thousands or millions of users. In that case you want to track cost by tenant. In any scenario, it’s important to treat the platform like any other enterprise asset and optimize for cost, track usage by feature/user, implement best practices for governance, and aim to achieve operational excellence.

Effective cost allocation and resource management is a key responsibility in the cloud infrastructure space. AWS Cost and Usage Reports (AWS CUR) serves as the foundation for any reporting on cost in AWS. It provides a comprehensive list of cost incurred in that account across all AWS Services. In addition, AWS cost allocation tags are an important feature that AWS Billing offers. You can use these tags in AWS CUR, acting as labels for resources and offering in-depth insights into AWS environment consumption trends. They can transform the way expenses are allocated and managed in cloud infrastructures while enabling internal and external chargeback mechanisms.

In this post, we explore how you can use AWS CUR and AWS tags to monitor how specific users are using QuickSight. We also discuss how these tags can help organizations implement cloud cost controls by providing the data needed to support custom chargeback reporting.

Solution overview

Let’s take AnyCompany Inc., a fictional independent software vendor (ISV), as an example. AnyCompany uses QuickSight to provide analytics to their customers within their cloud-based service. With AWS CUR they obtain detailed information on usage by namespace and are able to produce comprehensive cost reports by tenant. They can further customize by using QuickSight tags to track expenses and make chargebacks to their internal departments, cost centers, projects, or clients.

Prerequisites

Before you start applying tags and building QuickSight dashboards, complete the following prerequisite steps:

Deploy AWS CUR by leveraging AWS Data Exports. Either legacy or standard AWS CUR will work with this approach. Note that as of this writing, the cost and usage reports deployed on the AWS Billing console don’t contain resource IDs and don’t provide the level of detail that we desire.
As part of the previous step a job is created to Export the CUR file to Amazon Simple Storage Service (Amazon S3).
Because we’re using Amazon Athena to query the reports, the CUR data must be crawled and made available in the AWS Glue Data Catalog and/or via AWS Lake Formation.
Deploy QuickSight Enterprise if not already done.
Set up access in QuickSight to Athena and Amazon S3 so you can query the CUR file. For more information, refer to Accessing AWS resources.

An alternative to these prerequisites is to implement the CUDOS solution. This will deploy AWS CUR and make data available in Amazon S3 and Athena. For more information, refer to Cloud Intelligence Dashboards.

Tag users

The first step is to tag QuickSight users and assign key-value labels, which will be used in the CUR data. We use the AWS Command Line Interface (AWS CLI) and the tag-resource command. Complete the following steps:

From the AWS CLI installed local or using AWS CloudShell, tag a user with the following code:

aws quicksight tag-resource --resource-arn arn:aws:quicksight:<Region>:<AWS-Account-ID>:user/default/Admin/<QuickSight-User-Name> --tags Key=<key-name>,Value=<Value>

In this example, we use costcenter as our tag key.

2. On the AWS Management Console home page, search for “Billing and Cost Management” and choose it from the service list.
On the Billing and Cost Management console, under Cost Organization in the navigation pane, choose Cost Allocation Tags.

The tag key you created will be populated (it may take a few hours to be listed). By default, the tag will be in inactive.

3. Select your tag and choose Activate.

It may take 24–48 hours to populate the newly activated tags into the Athena table for CUR reporting.

4. Given the change in the schema of the CUR data by adding a new tag, schema in the table needs to be updated. If using AWS Data Exports, after 24-48hrs, the Crawler should be re-run to capture the new column. This is not necessary with CUDOS as pipeline and crawler run on a schedule.
To validate that the tag is available we can run a simple query in AWS Athena. In the Athena query editor, choose your data source, database, and table to confirm location of CUR data. Update below SQL with the correct database and table names. In this example, we use database athenacurcfn_aws_cost_and_usage_report and table ad_aws_cost_and_usage_report. Edit and then run the basic SQL script in the Athena editor to confirm tag is in the data and see the detailed cost breakdown for user, which can be filtered using the resource_tags_user_costcenter column for the QuickSight user tagged with the Finance value.
```
select * From “Database”.”Tablename” where line_item_product_code = 'AmazonQuickSight'
```

Create a QuickSight cost analysis

With the CUR deployed and users tagged, we are able to build a QuickSight dashboard that provides insights into QuickSight cost consumption. You can use the steps in this section as the starting point to build a custom cost dashboard tailored to the needs of your organization.

It’s important to highlight where certain key data points are originating from. With the following SQL statement, we are building a slice of the base CUR table for the purpose of our analysis. You can further customize this SQL statement to meet the specific needs of your organization. The following are the key terms extracted by CUR via the custom SQL:

QuickSight feature – This column is created by a case statement in SQL and groups QuickSight cost elements by feature. It breaks out cost in the following categories: author and admin charges, reader charges, additional costs for Amazon Q in QuickSight, alerts, SPICE, and reporting.
Resource ID – This column originates from CUR and includes the resource ID responsible for the cost. In QuickSight data, it could represent a user ARN or dashboard ARN.
Resource ID category – This column is created by the SQL statement and categorizes resources based on the resource type.
User namespace – This column is created by the SQL statement and extracts the user namespace from the resource ID.
User name – This column is created by the SQL statement and extracts the user’s name from the resource ID.
Dashboard ID – This column is created by the SQL statement and extracts the dashboard ID from the resource ID.

Complete the following steps to build your dataset and analysis:

On the QuickSight console, choose Datasets in the navigation pane.
Choose Create new dataset.
Choose Athena as your data source.
Enter a name for your data source and choose Create data source.
Choose Use custom SQL to enter your sample SQL.

Update the schema name and table name in the following SQL Select statement to match the schema and table from the Data Catalog. If there are any tags, the column must be added to the SQL statement.

SELECT 
  bill_payer_account_id as "Bill Payer Account ID", 
  line_item_usage_account_id as "Usage Account ID", 
  bill_billing_period_start_date as "Billing period", 
  line_item_usage_start_date as "Usage Date", 
  line_item_resource_id as "Resource ID", 
  TAG NAME as tag,
  (
    CASE WHEN (
      LOWER(line_item_resource_id) LIKE '%analysis%'
    ) THEN 'Analysis' WHEN (
      LOWER(line_item_resource_id) LIKE '%dashboard%'
    ) THEN 'Dashboard' WHEN (
      LOWER(line_item_resource_id) LIKE '%user%'
    ) THEN 'User' WHEN (
      LOWER(line_item_resource_id) is null
    ) THEN 'No Resource' ELSE 'other' END
  ) as "Resource ID Category", 
  line_item_usage_type as "Line Item Usage type", 
  line_item_line_item_description as "Line Item Description", 
  Case when (
    LOWER(line_item_resource_id) LIKE '%user%'
  ) 
  or (
    LOWER(line_item_resource_id) LIKE '%anonymousUser%'
  ) then SPLIT_PART(line_item_resource_id, '/', 2) else NULL end as "User namespace", 
  Case when (
    LOWER(line_item_resource_id) LIKE '%user%'
  ) 
  or (
    LOWER(line_item_resource_id) LIKE '%anonymousUser%'
  ) then ELEMENT_AT(
    SPLIT(line_item_resource_id, '/'), 
    -1
  ) else '' end AS Username, 
  Case WHEN (
    LOWER(line_item_resource_id) LIKE '%dashboard%'
  ) 
  or (
    LOWER (line_item_resource_id) LIKE '%alert%'
  ) then ELEMENT_AT(
    SPLIT(line_item_resource_id, '/'), 
    -1
  ) else NULL end AS DashboardID, 
  (
    CASE WHEN (
      LOWER(line_item_usage_type) LIKE '%spice%'
    ) THEN 'SPICE' WHEN (
      LOWER(line_item_usage_type) LIKE '%alerts%'
    ) THEN 'QuickSight Alerts' WHEN (
      LOWER(line_item_usage_type) LIKE '%-q%'
    ) THEN 'QuickSight Q' WHEN (
      (
        LOWER(line_item_usage_type) LIKE 'qs-user-enterprise%'
      ) 
      OR (
        LOWER(line_item_usage_type) = 'qs-user-standard'
      )
    ) THEN 'Authors' WHEN (
      LOWER(line_item_usage_type) LIKE 'qs-reader%'
    ) THEN 'Readers' WHEN (
      LOWER(line_item_usage_type) LIKE '%spice'
    ) THEN 'SPICE' WHEN (
      LOWER(line_item_usage_type) LIKE '%alerts%'
    ) THEN 'Alerts' WHEN (
      LOWER(line_item_usage_type) LIKE '%qs-report%'
    ) THEN 'Reporting' ELSE line_item_usage_type END
  ) QuickSightFeatures, 
  line_item_usage_amount as "usage amount", 
  line_item_unblended_cost as "unblended cost", 
  line_item_blended_cost as "blended cost" 
FROM 
  [DATABASE NAME].[Table Name] c 
WHERE 
  (
    (
      line_item_product_code = 'AmazonQuickSight'
    ) 
    AND (
      line_item_line_item_type IN (
        'DiscountedUsage', 'Usage', 'BundledDiscount'
      )
    )
  )

Save and publish the dataset.

With the dataset prepared and available, we are able to build our analysis and dashboard. The following are some sample visualizations that you can build with a few clicks.

8. First, we visualize month-over-month QuickSight costs:
1. Add a new KPI visual to the dashboard.
2. Add line item cost as a value and line item usage start date to the trend group.
3. Change the aggregation to monthly.
Next, we visualize cost by product feature:
1. Add a pie chart visual.
2. Add line item cost as a value.
3. Optionally, filter for most recent month.
We can also visualize the monthly cost by user for authors and readers:
1. Add a pivot table visual.
2. Add Username to Rows, and Usage Date (by month) and unblended cost as a value.
3. To filter out non-user costs, add a filter and exclude the null user name.
4. Add QuickSight Features field to Rows to differentiate between authors, readers, and other cost categories.

For session-based pricing, usage will be the number of sessions. For user-based pricing, usage represents the cost incurred for the month.

11. Next, let’s visualize monthly cost by namespace:
1. Add a stacked bar chart visual.
2. Add usage date by month to the x axis, unblended cost as a value, and namespace to group/color.
3. Filter out null namespaces to ensure we see only namespace- and user-related costs.

The namespace is captured in the preceding custom SQL by parsing the resource ID for username type resources.

Lastly, we visualize the monthly cost for users by tag:
1. We need to add a tag column to the custom SQL, based on the tag name and table defined in AWS Glue by the crawler. For example, if your tag is labeled cost_center, you would add the following as a column in the SELECT statement: Resource_tags_user_cost_center as cost_center_tag.
2. Add a pivot table visual to the dashboard.
3. Add tag name and username to Rows, usage date and month to Columns, and unblended cost as a value.
4. To calculate month over month, change the calculated field to capture the percent difference using the following formula: periodOverPeriodPercentDifference(sum({blended cost}),{Usage Date}).

The following screenshot shows our final dashboard.

Clean up

To clean up your resources when you’re done with this solution. In AWS QuickSight, delete the dataset, analysis, and any dashboards that you created. In AWS Glue, delete tables or crawlers created. In AWS Billing, disable Data Export. In AWS S3, delete CUR data.

Conclusion

In this post, we showed how to set up tags, connect CUR data to Amazon S3, and build a QuickSight dashboard that provides detailed insights into QuickSight costs.

For many customers, this is just the beginning of what is possible with this solution. For example, as an analytics or BI administrator, now you have a way to send monthly pixel-perfect reports to department leadership with detailed QuickSight cost and usage for the departments that you manage. In addition, leadership could create alerts that track their cost compared to budgets. As a product owner in an ISV, now you have more precise tools to chargeback or monetize your BI offering. These possibilities provide BI admins with the detail needed to monitor and optimize the cost of all their QuickSight data products.

Get started by implementing Cost and Usage Reports and begin using QuickSight!

About the authors

Ramon Lopez is a Principal Solutions Architect for Amazon QuickSight. With many years of experience building BI solutions and a background in accounting, he loves working with customers, creating solutions and making world class services. When not working he prefers to be outdoors in the ocean or up on a mountain.

Ashok Dasineni is a Solutions Architect for Amazon QuickSight. Before joining AWS, Ashok worked with clients and organizations in Banking and financial domain, focusing on fraud research and prevention. He designed and implemented innovative solutions to improve business process, reduce cost and increase revenue, enabling companies around the world to achieve their highest potential through data.

AWS Business Intelligence Blog