search

aws-samples / aws-cudos-framework-deployment

Command Line Interface tool for Cloud Intelligence Dashboards deployment
https://catalog.workshops.aws/awscid
MIT No Attribution
409 stars 165 forks source link

unblended_cost values different on summary_view and resource_view #891

Closed work-bw closed 4 months ago

work-bw commented 4 months ago

summary_view/unblended_cost= 493.91

filters:
usage_date= previous month
account_id= "my account id"
value:
unblended_cost (Sum)

resource_view/unblended_cost= 715.15

filters:
usage_date= previous month
account_id= "my account id"
value:
unblended_cost (Sum)

Athena query for unblended_cost= 783.07

SELECT SUM(line_item_unblended_cost) AS cost
FROM "cid_cur"."cur" WHERE
line_item_usage_account_id= 'my account id' and
line_item_usage_start_date >= timestamp '2024-06-01 00:00:00.000' and
line_item_usage_end_date < timestamp '2024-07-01' and
(line_item_line_item_type= 'Usage' or line_item_line_item_type= 'SavingsPlanCoveredUsage')
;

My bill shows Charges by Service 608.26 and Savings Plan Discounts (175.07).

I would expect my unblended cost to be 608.26 + 175.07 = 783.33.

Can you tell me why unblended cost for summary_view or resource_view don't match up with what Athena says is unblended cost? The Athena query seems to be the correct value. Shouldn't my unblended costs be 783.33 in resource_view, summary_view and Athena?

Thanks.

iakov-aws commented 4 months ago

Hello, please can you provide your summary view query? The resource view is limited to 30 days so it is not a good candidate for comparison.

work-bw commented 4 months ago

Summary view:

CREATE OR REPLACE VIEW "summary_view" AS 
SELECT
  "year"
, "month"
, "bill_billing_period_start_date" "billing_period"
, (CASE WHEN ("date_trunc"('month', "line_item_usage_start_date") >= ("date_trunc"('month', current_timestamp) - INTERVAL  '3' MONTH)) THEN "date_trunc"('day', "line_item_usage_start_date") ELSE "date_trunc"('month', "line_item_usage_start_date") END) "usage_date"
, "bill_payer_account_id" "payer_account_id"
, "line_item_usage_account_id" "linked_account_id"
, "bill_invoice_id" "invoice_id"
, "line_item_line_item_type" "charge_type"
, (CASE WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN 'Running_Usage' WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN 'Running_Usage' WHEN ("line_item_line_item_type" = 'Usage') THEN 'Running_Usage' ELSE 'non_usage' END) "charge_category"
, (CASE WHEN ("savings_plan_savings_plan_a_r_n" <> '') THEN 'SavingsPlan' WHEN ("reservation_reservation_a_r_n" <> '') THEN 'Reserved' WHEN ("line_item_usage_type" LIKE '%Spot%') THEN 'Spot' ELSE 'OnDemand' END) "purchase_option"
, (CASE WHEN ("savings_plan_savings_plan_a_r_n" <> '') THEN "savings_plan_savings_plan_a_r_n" WHEN ("reservation_reservation_a_r_n" <> '') THEN "reservation_reservation_a_r_n" ELSE CAST('' AS varchar) END) "ri_sp_arn"
, "line_item_product_code" "product_code"
, "product_product_name" "product_name"
, (CASE WHEN (("bill_billing_entity" = 'AWS Marketplace') AND (NOT ("line_item_line_item_type" LIKE '%Discount%'))) THEN "Product_Product_Name" WHEN ("product_servicecode" = '') THEN "line_item_product_code" ELSE "product_servicecode" END) "service"
, "product_product_family" "product_family"
, "line_item_usage_type" "usage_type"
, "line_item_operation" "operation"
, "line_item_line_item_description" "item_description"
, "line_item_availability_zone" "availability_zone"
, "product_region" "region"
, (CASE WHEN (("line_item_usage_type" LIKE '%Spot%') AND ("line_item_product_code" = 'AmazonEC2') AND ("line_item_line_item_type" = 'Usage')) THEN "split_part"("line_item_line_item_description", '.', 1) ELSE "product_instance_type_family" END) "instance_type_family"
, (CASE WHEN (("line_item_usage_type" LIKE '%Spot%') AND ("line_item_product_code" = 'AmazonEC2') AND ("line_item_line_item_type" = 'Usage')) THEN "split_part"("line_item_line_item_description", ' ', 1) ELSE "product_instance_type" END) "instance_type"
, (CASE WHEN (("line_item_usage_type" LIKE '%Spot%') AND ("line_item_product_code" = 'AmazonEC2') AND ("line_item_line_item_type" = 'Usage')) THEN "split_part"("split_part"("line_item_line_item_description", ' ', 2), '/', 1) ELSE "product_operating_system" END) "platform"
, "product_tenancy" "tenancy"
, "product_physical_processor" "processor"
, "product_processor_features" "processor_features"
, "product_database_engine" "database_engine"
, "product_group" "product_group"
, "product_from_location" "product_from_location"
, "product_to_location" "product_to_location"
, "product_current_generation" "current_generation"
, "line_item_legal_entity" "legal_entity"
, "bill_billing_entity" "billing_entity"
, "pricing_unit" "pricing_unit"
, approx_distinct("Line_item_resource_id") "resource_id_count"
, sum((CASE WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN "line_item_usage_amount" WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN "line_item_usage_amount" WHEN ("line_item_line_item_type" = 'Usage') THEN "line_item_usage_amount" ELSE 0 END)) "usage_quantity"
, sum("line_item_unblended_cost") "unblended_cost"
, sum((CASE WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN "savings_plan_savings_plan_effective_cost" WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN ("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment") WHEN ("line_item_line_item_type" = 'SavingsPlanNegation') THEN 0 WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN 0 WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN "reservation_effective_cost" WHEN ("line_item_line_item_type" = 'RIFee') THEN ("reservation_unused_amortized_upfront_fee_for_billing_period" + "reservation_unused_recurring_fee") WHEN (("line_item_line_item_type" = 'Fee') AND ("reservation_reservation_a_r_n" <> '')) THEN 0 ELSE "line_item_unblended_cost" END)) "amortized_cost"
, sum((CASE WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') THEN -("savings_plan_amortized_upfront_commitment_for_billing_period") WHEN ("line_item_line_item_type" = 'RIFee') THEN -("reservation_amortized_upfront_fee_for_billing_period") ELSE 0 END)) "ri_sp_trueup"
, sum((CASE WHEN ("line_item_line_item_type" = 'SavingsPlanUpfrontFee') THEN "line_item_unblended_cost" WHEN (("line_item_line_item_type" = 'Fee') AND ("reservation_reservation_a_r_n" <> '')) THEN "line_item_unblended_cost" ELSE 0 END)) "ri_sp_upfront_fees"
, sum((CASE WHEN ("line_item_line_item_type" <> 'SavingsPlanNegation') THEN "pricing_public_on_demand_cost" ELSE 0 END)) "public_cost"
FROM
  "cur"
WHERE (("bill_billing_period_start_date" >= ("date_trunc"('month', current_timestamp) - INTERVAL  '12' MONTH)) AND (CAST("concat"("year", '-', "month", '-01') AS date) >= ("date_trunc"('month', current_date) - INTERVAL  '12' MONTH)))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34
work-bw commented 4 months ago

Resource view (I keep 90 days):

CREATE OR REPLACE VIEW "resource_view" AS 
SELECT DISTINCT
  "date_trunc"('day', "line_item_usage_start_date") "usage_date"
, "resource_tags_user_flyway_env" "tag_flyway_env"
, "resource_tags_user_flyway_line_of_business" "tag_flyway_line_of_business"
, "resource_tags_user_flyway_app_name" "tag_flyway_app_name"
, "bill_payer_account_id" "payer_account_id"
, "line_item_usage_account_id" "linked_account_id"
, "bill_billing_entity" "billing_entity"
, "product_product_name" "product_name"
, "line_item_resource_id" "resource_id"
, "line_item_product_code" "product_code"
, "line_item_operation" "operation"
, "line_item_line_item_type" "charge_type"
, "line_item_usage_type" "usage_type"
, "pricing_unit" "pricing_unit"
, "product_region" "region"
, "line_item_line_item_description" "item_description"
, "line_item_legal_entity" "legal_entity"
, "pricing_term" "pricing_term"
, "product_database_engine" "database_engine"
, "product_deployment_option" "product_deployment_option"
, "product_from_location" "product_from_location"
, "product_group" "product_group"
, "product_instance_type" "instance_type"
, "product_instance_type_family" "instance_type_family"
, "product_operating_system" "platform"
, "product_product_family" "product_family"
, "product_servicecode" "service"
, "product_storage" "product_storage"
, "product_to_location" "product_to_location"
, "product_volume_api_name" "product_volume_api_name"
, "reservation_reservation_a_r_n" "reservation_a_r_n"
, "savings_plan_savings_plan_a_r_n" "savings_plan_a_r_n"
, "sum"("savings_plan_savings_plan_effective_cost") savings_plan_effective_cost
, "sum"("reservation_effective_cost") reservation_effective_cost
, "sum"("line_item_usage_amount") "usage_quantity"
, "sum"("line_item_unblended_cost") unblended_cost
FROM
  "cur"
WHERE (((current_date - INTERVAL  '90' DAY) <= line_item_usage_start_date) AND (line_item_resource_id <> ''))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32
work-bw commented 4 months ago

Since the data I'm looking for is last month, I would have thought that summary_view or resource_view would both provide accurate values that match up with the Athena query as my resource_view keeps 90 days.

iakov-aws commented 4 months ago

Summary view includes all line_item_line_item_type/charge_type fields including all your discounts. This is why your Summary view sum is smaller. You can add identical filters in QuickSight (charge_type in ['Usage', 'SavingsPlanCoveredUsage'] ) to compare.

For performance reasons resource_view explicitly exclude all line items without resource_id so it should not be used for comparing unless you reproduce the same filter in Athena.