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

[RolandKrummenacher]

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.

[RolandKrummenacher]

Context update: #2310 changes the ISF joins in reservations-usage.json to leftouter, touching the same queries this issue is about. It does not address the scale — unmatched reservations get Ratio = 1, matched ones keep the unnormalized API ratio — so this stays open and is best done on top of #2310 to avoid conflicting edits to the same lines.

[RolandKrummenacher]

Correcting my earlier comment: #2310 was closed and folded into #2308, which now also normalizes the dataset. So this is fixed there rather than left for a follow-up.

Each flexibility group is normalized to a smallest ratio of 1, which is the form the retired isfratioblob.csv published and the form AvgRIsUsedInSmallestRatio assumes. Of the 1,291 rows the two files share, 1,223 now agree; the other 68 are groups where the API no longer returns the historically smallest SKU (12 groups) or disagrees with the retired file on the proportions themselves (11 groups, all dedicated host).

Power BI is unaffected — the ratio appears in no DAX measure and no report visual.