Finance needs to allocate costs and perform chargeback for charges for all applications that come from different geographic locations and are in different currencies.
This query uses the ProviderName column. ProviderName is deprecated in v1.3 and will be removed in v1.4.
Personas
Finance
Capabilities
Allocation, Invoicing & Chargeback
KPIs
Usage or Spend Apportionment Validation, Percentage of Unallocated Shared CSP Cloud Cost, Percentage of Costs Associated with Untagged CSP Cloud Resources, Percentage of Costs Associated with Unallocated CSP Cloud Resources, General Ledger Recharge Rate, General Ledger Recharge Rate per Cost Center, Fixed Percentage Apportionment Validation
Totals billed cost per application tag, provider, billing account and billing currency so multi-currency charges can be allocated per application. The sample has no ApplicationId tag; its application tag key is lowercase 'application', and rows without the tag are labelled '(untagged)'. About 8,000 application groups exist, so the 500 with the largest absolute billed cost are kept. All populated billing currencies in the sample are USD.
Column adaptations (1)
Tags["ApplicationId"] → tags->>'application': Same application identifier tag under the key this sample actually uses.
Billed cost by application and currency
Chart controls and notes
Showing the first 20 categories. Open full screen to explore all 499 categories.
Showing 100 of 500 rows. Scroll inside the table to see more.
Precomputed result
Inspect Postgres SQL and original FOCUS SQL
Postgres SQL, as run
select coalesce(tags->>'application', '(untagged)') as application_id,
provider_name as provider_name,
billing_account_id as billing_account_id,
billing_account_name as billing_account_name,
billing_currency as billing_currency,
sum(billed_cost) as total_billed_cost
from focus.cost_and_usage
where billing_period_start >= $1::timestamptz
and billing_period_end < $2::timestamptz
group by 1, provider_name, billing_account_id, billing_account_name, billing_currency
order by abs(sum(billed_cost)) desc nulls last, application_id, provider_name
limit 500
SELECT
Tags["ApplicationId"],
ProviderName,
BillingAccountId,
BillingAccountName,
BillingCurrency,
SUM(BilledCost) AS TotalBilledCost
FROM focus_data_table
WHERE
BillingPeriodStart >= ? AND BillingPeriodEnd < ?
GROUP BY
Tags["ApplicationId"],
ProviderName,
BillingAccountId,
BillingAccountName,
BillingCurrency