Get historical usage and rates to enable cost forecasting
This query uses the ProviderName column. ProviderName is deprecated in v1.3 and will be removed in v1.4.
FOCUS Versions
v1.0
v1.1
v1.2
Context
While Finance will look at higher-level forecasting data such as amortized costs and total cashflow, a FinOps Practitioner needs to forecast costs from quantities of priced units and effective costs based on historical rates. Effective costs are used to account for the costs of commitment purchases.
This query extracts cost and usage data by Region and Service. More fine-grain extrapolation could be done based on further dimensions like Tags.
FOCUS SQL Query
SELECT
ProviderName,
BillingPeriodStart,
BillingPeriodEnd,
ServiceCategory,
ServiceName,
RegionId,
RegionName,
PricingUnit,
SUM(EffectiveCost) AS TotalEffectiveCost,
SUM(PricingQuantity) AS TotalPricingQuantity
FROM focus_data_table
WHERE BillingPeriodStart >= ? AND BillingPeriodEnd <= ?
GROUP BY
ProviderName,
BillingPeriodStart,
BillingPeriodEnd,
ServiceCategory,
ServiceName,
RegionId,
RegionName,
PricingUnit