quicksight-cur-deployment-tool

Note:

• This guide is designed on Mac client. If you are using a Windows PC. Consider to run this on Amazon Linux or Cloud 9.

Prerequisites:

1.AWS CLI environment configured (at least aws-cli/1.18.79)
  https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-configure.html#cli-quick-configuration
2.Set up Amazon QuickSight for an existing AWS user
  https://docs.aws.amazon.com/quicksight/latest/user/setup-quicksight-for-existing-aws-account.html
3.Enable Enterprise Edition for QuickSight (navigate to QuickSight Admin to purchase Enterprise license).
  https://docs.aws.amazon.com/quicksight/latest/user/upgrading-subscription.html
  Note: If you do not use QuickSight before, this will add $18 or $24 subscription fee into your monthly bill
  https://aws.amazon.com/quicksight/pricing/
4.“jq” tool installed on your client

• Command to install jq on Amazon linux

sudo yum -y install jq

• Command to install jq on Mac

brew install jq

Steps:

1.Go through“Setting up Amazon Athena integration” to create S3 bucket/CUR(choose Parquet format) and set up Athena integration by CloudFormation.

[Optional]: If you want to analyze CUR created from China region, you need to sync CUR data between China and global region
a. Create two S3 buckets in China region and global region separately, use the same bucket name
b. Enable CUR and save to S3 bucket in China region
c. Run following command periodically, so that new data will be continuously synchronized to S3 bucket in global region

aws s3 sync s3://*S3-bucket-name* . --exclude "aws-programmatic-access-test-object" --exclude "*/cost_and_usage_data_status/cost_and_usage_data_status.parquet" —profile *china-iam-profile*
aws s3 sync . s3://*S3-bucket-name* --acl bucket-owner-full-control —profile *global-iam-profile*

d. Set up Athena integration by CloudFormation, then go on with step 2

Note：

• Change S3-bucket-name to your S3 Bucket Name，change *china-iam-profile * to your IAM user profile in China region， change *global-iam-profile * to your IAM user profile in global region
• If you want to synchorize CUR by a serverless architecture, please reference this blog
  https://aws.amazon.com/cn/blogs/china/lambda-overseas-china-s3-file/

2.Grant permissions on S3 bucket, so that QuickSight can access CUR files stored in it   https://docs.aws.amazon.com/zh_cn/quicksight/latest/user/troubleshoot-athena-insufficient-permissions.html
3.Visit this site，enter company name and AWS Account ID to authorize permissions for QuickSight template
4.Open Github ，click “Code → Download ZIP ” to download quicksight-cur-deployment-tool
5.Navigate to extracted folder and run “deployQSCUR.sh”

cd quicksight-cur-deployment-tool-master

bash deployQSCUR.sh

6.Type answers for following questions prompted by this script Note：If all steps according to the content of this guide，and plan to deploy QuickSight Dashboard in us-east-1，keep all the defauls is ok
a. Please enter the destination region to deploy this solution(same with Athena/QuickSight) [default:us-east-1]
b. If you know what SPICE is and want to use SPICE mode, type “spice”. [default:DIRECT_QUERY]
c. Please input the database name in Athena, which will be used to connect CUR data on S3
d. Please input the table name within database in previous step, which will be used to connect CUR data on S3
e. Please input the "Query result location" value from Settings in Athena console [default: s3://aws-athena-query-results-ACCOUNTID-REGION/].

7.Open your QuickSight dashboard to analyze cost

Customize your analysis:

If you want to have a customized view based on existing QuickSight dashboard, you can enable the "Save As" function in the console

a. Open the dashboard, then click "share" button from upper right corner
b. In the pop up window click “Manage Dashboard access”
c. On “Manage Dashboard access” page，tick "Save as" for your account
d. Click "Confirm" on “Enable save as” window, close “Manage Dashboard access”，then you will see a new "Save as" button appear on the upper right corner
e. Click "Save as" to create an analysis and name it f. Click the edit icon to edit data set g. Select your data set in "Data sets in this analysis" window to edit it h. Wait a few seconds to load all CUR columns in data set page, then click "Save&visualize" to update the latest CUR columns into data set. Then you can customize your analysis according to your needs.

Main cost of this solution:

1.QuickSight Enterprise Edition, $18 or $24 per month based on your subscription
  https://aws.amazon.com/quicksight/pricing/
2.Athena query cost   Take us-east-1 as example，$5.00 per TB of data scanned
  https://aws.amazon.com/athena/pricing/
3.S3 S3 standard storage cost
  Take us-east-1 as example
  $0.023 per GB for storage
  $0.0004 per 1,000 GET requests on CUR file
  https://aws.amazon.com/s3/pricing/
4.A recurring(2-3 times a day) Glue crawler that keeps your CUR table in Athena up-to-date
  Take us-east-1 as example, $0.44 per DPU-Hour, billed per second, with a 10-minute minimum per crawler run
  https://aws.amazon.com/glue/pricing/
5.A recurring(2-3 times a day) Lambda to trigger Athena table update
  Take us-east-1 as example，128MB, $0.000002083 per second
  https://aws.amazon.com/lambda/pricing/
6.If you run this script tool on EC2 or Cloud 9, will have additional cost based on you instance type
  https://aws.amazon.com/ec2/pricing/on-demand/
  https://aws.amazon.com/cloud9/pricing/

Minimal permissions :

The minimal permissions for “deployQSCUR.sh” are:
{
 "Version": "2012-10-17",
 "Statement": [
  {
   "Sid": "deployQSCURPolicy",
   "Effect": "Allow",
    "Action": [
    "EC2:DescribeRegions",
    "s3:GetBucketLocation",
    "s3:ListBucket",
    "s3:PutObject",
    "s3:GetObject",
    "glue:GetPartitions",
    "glue:GetDatabase",
    "glue:GetDatabases",
    "glue:GetTable",
    "glue:GetTables",
    "athena:ListDatabases",
    "athena:GetDatabase",
    "athena:ListTableMetadata",
    "athena:GetTableMetadata",
    "athena:StartQueryExecution",
    "athena:GetQueryExecution",
    "athena:GetQueryResults",
    "quicksight:ListUsers",
    "quicksight:CreateUser",
    "quicksight:CreateAdmin",
    "quicksight:DescribeDataSource",
    "quicksight:UpdateDataSourcePermissions",
    "quicksight:UpdateDataSetPermissions",
    "quicksight:PassDataSource",
    "quicksight:CreateDataSet",
    "quicksight:DescribeDataSet",
    "quicksight:PassDataSet"
    "quicksight:DescribeTemplate",
    "quicksight:CreateDataSource",
    "quicksight:CreateDashboard",
    "quicksight:DescribeDashboard",
    "quicksight:UpdateDashboardPermissions"
   ],
   "Resource": "*"
  }
 ]
}

More permissions necessary for “deleteAll.sh” are:
{
  "Version": "2012-10-17",
  "Statement": [
   {
    "Sid": "deleteAllPolicy",
    "Effect": "Allow",
    "Action": [
    "quicksight:DeleteDataSource",
    "quicksight:DeleteDataSet",
    "quicksight:DeleteDashboard"
    ],
    "Resource": "*"
   }
  ]
}