Data Generator-Calculated Split Cost Allocation

2.9.1 Description

FOCUS enables tracking of resources split by some internal consumption metrics. This is most common for resources supporting shared usage like compute nodes in a shared cluster (Kubernetes, databases) or storage engines that can share capacity between workloads.

2.9.2 Directly Dependent Columns

Resource ID Effective Cost Billed Cost Allocated Resource ID Allocated Resource Name Allocated Method Details Allocated Method ID

2.9.3 Supporting Columns

Charge Category Charge Period End Charge Period Start Service Provider Name Service Name

2.9.4 Example SQL Query (Find resources with a shared cost)

Example SQL Query (Find resources with a shared cost)

SELECT
  DISTINCT ResourceId
FROM focus_data_table
WHERE ChargeCategory='Usage'
  AND ChargePeriodStart >= ? AND ChargePeriodEnd <= ?
  AND AllocatedMethodId IS NOT NULL

2.9.5 Example SQL Query (Get total effective cost by ResourceId (ignore shared cost))

Example SQL Query (Get total effective cost by ResourceId (ignore shared cost))

SELECT
  ResourceId,
  SUM(EffectiveCost) AS TotalEffectiveCost
FROM focus_data_table
WHERE ChargeCategory='Usage'
  AND ChargePeriodStart >= ? AND ChargePeriodEnd <= ?
  AND AllocatedMethodId IS NOT NULL
GROUP BY
  ResourceId

2.9.6 Example SQL Query (Get total effective cost by AllocatedResourceId)

Example SQL Query (Get total effective cost by AllocatedResourceId)

SELECT
  AllocatedResourceId,
  SUM(EffectiveCost) AS TotalEffectiveCost
FROM focus_data_table
WHERE ChargeCategory='Usage'
  AND ChargePeriodStart >= ? AND ChargePeriodEnd <= ?
  AND AllocatedMethodId IS NOT NULL
GROUP BY
  AllocatedResourceId

2.9.7 Example SQL Query (Find total unallocated split costs by resourceId)

Example SQL Query (Find total unallocated split costs by resourceId)

SELECT
  ResourceId,
  SUM(EffectiveCost) AS TotalEffectiveCost
FROM focus_data_table
WHERE ChargeCategory='Usage'
  AND ChargePeriodStart >= ? AND ChargePeriodEnd <= ?
  AND AllocatedMethodId IS NOT NULL AND AllocatedResourceId IS NULL
GROUP BY
  ResourceId

2.9.8 Example SQL Query (Find how a single resource has been split)

Example SQL Query (Find how a single resource has been split)

SELECT
  ResourceId,
  COALESCE(AllocatedResourceId, 'Unallocated') AS AllocatedResourceId,
  SUM(EffectiveCost) AS TotalEffectiveCost
FROM focus_data_table
WHERE ChargeCategory='Usage'
  AND ChargePeriodStart >= ? AND ChargePeriodEnd <= ?
  AND AllocatedResourceId = ?
GROUP BY
  ResourceId,
  COALESCE(AllocatedResourceId, 'Unallocated')

2.9.9 Example SQL Query (Extract JSON from AllocatedMethodDetails)

Example SQL Query (Extract JSON from AllocatedMethodDetails)

SELECT
  resource_id,
  elements.allocated_ratio,
  elements.usage_unit,
  elements.usage_quantity
FROM
  focus_data_table,
  JSON_TABLE(
    AllocatedMethodDetails,
    '$.Elements[*]' COLUMNS (
      allocated_ratio DECIMAL(10, 2) PATH '$.AllocatedRatio',
      usage_unit VARCHAR(50) PATH '$.UsageUnit',
      usage_quantity DECIMAL(10, 2) PATH '$.UsageQuantity'
    )
  ) AS elements

2.9 Data Generator-Calculated Split Cost Allocation