microsoft / microsoft/finops-toolkit

[AOE] Reservation workbooks show absolute quantities on the wrong ratio scale

Open
#2,309 4 comments 0 reactions 1 assignee View on GitHub

@helderpinto is already working on this.

Since Sep 9, 2026.

Tool: Open data Tool: Optimization Engine Type: Bug 🐛
Dominant language
PowerShell
Stars
603
Forks
248
Avg merge
7d 11h
Merged PRs (30d)
11

Description

Problem

The reservation workbooks convert quantities into "smallest SKU" units by multiplying with the ISF Ratio:

| extend AvgRIsUsedInSmallestRatio = Ratio * AvgRIsUsedDaily
| summarize TotalReservedQuantity_s = sum(todouble(TotalReservedQuantity_s) * Ratio), ... by ISFGroup

That conversion only holds if the smallest SKU in a group has ratio 1. Since #2222 the workbooks read src/open-data/InstanceSizeFlexibility.csv, which comes from the Catalogs API and is not normalized — Microsoft's own ISF documentation states it plainly:

The raw ISF ratios from the API and powershell don't always start at 1 for the smallest SKU in a group. For example, the BS Series group starts at 0.25 and the Ddsv5 Series starts at 2.

Measured on the published dataset: 162 of 319 groups start at 1, 157 do not. The retired isfratioblob.csv the workbooks read before #2222 was normalized (432 of 433 groups started at 1).

Impact

Utilization percentages are correct. UsedQuantity / TotalReservedQuantity * 100 carries the factor on both sides, so it cancels.

Absolute quantities are wrong by each group's constant factor. AvgRIsUsedDaily derives from Quantity * RINormalizationRatio, so it is already in reservation units of the purchased SKU; multiplying by a non-normalized Ratio does not land in smallest-SKU units, which is what the column name claims and what the displayed TotalReservedQuantity implies.

Fix

Normalize inside the workbook queries — divide each group's ratios by that group's minimum:

let ISFGroups = externaldata(ISFGroup:string, ArmSKUName:string, Ratio:double)
    [@"https://raw.githubusercontent.com/microsoft/finops-toolkit/dev/src/open-data/InstanceSizeFlexibility.csv"]
    with(ignoreFirstRecord=true)
| extend ArmSKUName = tolower(ArmSKUName)
| summarize MinRatio = min(Ratio) by ISFGroup
| join kind=inner (...) on ISFGroup
| extend Ratio = Ratio / MinRatio;

Contained to AOE, leaves the open data and the Power BI models untouched.

Affected files, all reading the same CSV via externaldata():

  • src/optimization-engine/views/workbooks/reservations-usage.json
  • src/optimization-engine/views/workbooks/reservations-potential.json
  • src/optimization-engine/views/workbooks/benefits-simulation.json

Alternative

Publish the open data normalized instead, which would restore parity with the retired file for every consumer at once. That is a larger decision: it changes published values in 156 of 318 groups. It would not affect the Power BI models, which use the flexibility group but never the ratio.

Not a regression from #2300

Pre-existing since #2222 migrated the workbooks off the retired blobs. #2300 changed no published ratio — it only added rows.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.