📝 Scenario

As a FinOps practitioner, I need to leverage new FOCUS 1.2 columns in order to account for commitment discount flexibility and track capacity reservations

💎 Solution

Implement FOCUS 1.2

📋 Tasks

1. Don't break when 1.2 exports show up
   • Create new schema mapping file for the Cost Management export dataset version in the schemas folder
   • Add file to file upload list in storage.bicep
   • Add new columns to the *_raw tables per dataset in IngestionSetup_RawTables.kql
   • Update Costs_transform_v1_0 to handle 1.2 to 1.0 conversion
   • Update the storage reports to account for 1.2 differences
2. Update ingestion database scripts
   • Save a copy of the latest version of the IngestionSetup_vX_Y.kql using the latest FOCUS version
     • If updating the same version, increment the release number (e.g., r2)
   • Rename all functions, tables, and policies in the new file to the new version (leave the old as-is)
   • Update the *_final_vX_Y tables to account for any new columns
   • Update the *_transform_vX_Y functions to account for any new columns
   • Update the script to delete the old update policy for the *_raw tables
   • Confirm the new file has the update policy set to use the latest version of the transform function and final table
3. Update hub database scripts
   • Add new FOCUS version section after the latest version section and before existing version sections
   • Create new *_vX_Y functions per dataset that transforms older data to the new FOCUS version
   • Update the unversioned functions to use the new *_vX_Y functions
   • Update older versioned functions to also pull from the new *_vX_Y functions and transform to the old schema
4. Update reports and dashboards
   • Update the KQL reports to use the new versioned functions
   • Update the ADX dashboard to use the new versioned functions
   • Update the FOCUS queries in the best practices library to use the new versioned functions
   • Update the Power BI connector report to FOCUS 1.1 (TBD)
5. Update FOCUS documenation
   • FOCUS overview
   • FOCUS mapping docs
   • FOCUS metadata doc
   • FOCUS 1.2 conformance summary/report
   • Create a doc that summarizes changes in each version
6. Update other toolkit tools and resources
   • Add FOCUS 1.2-preview metadata file
   • Add FOCUS 1.2-preview open data sample
   • Update New-FinOpsCostExport command to support 1.2-preview
7. Stretch goals
   • Change all decimal columns to real
   • Update Recommendations schema
   • Duplicate columns instead of replacing values
   • [Hubs] Support Tencent FOCUS data #1274
   • Verify if there are any cloud provider version updates
   • Promote PBI/ADX dashboard queries into the main tables
   • Fix Prices.CommitmentDiscountSpend/UsageEligibility
   • [Hubs] Data ingestion transform changes #1111

ℹ️ Additional context

1. Rename the following columns:
   1. x_InvoiceId → InvoiceId.
   2. x_PricingCurrency → PricingCurrency.
   3. x_SkuDetails → SkuPriceDetails.
      • “CoreCount” for number of CPUs/vCPUs/cores/vCores.
      • “DiskMaxIops” for disk I/O operations per second (IOPS).
      • “DiskSpace” for disk size in GiB.
      • “DiskType” for the kind of disk (e.g., SSD, HDD, NVMe).
      • “GpuCount” for the number of GPUs.
      • “InstanceType” for the resource size/SKU (e.g., ArmSkuName).
      • “InstanceSeries” for the size family/series.
      • “MemorySize” for the RAM in GiB.
      • “NetworkMaxIops” for network I/O operations per second (IOPS).
      • “NetworkMaxThroughput” for network max throughput for data transfer in Mbps.
      • “OperatingSystem” for the OS name.
      • “Redundancy” for the level of redundancy (e.g., Local, Zonal, Global).
      • “StorageClass” for the tier of storage (e.g., Hot, Archive, Nearline).
      • Prefix all other properties with “x_”
   4. x_SkuMeterName → SkuMeter.
2. Add the following new columns for VM on-demand capacity reservations:
   1. CapacityReservationId = AdditionalInfo.VMCapacityReservationId.
   2. CapacityReservationStatus which will need to copy data out of AdditionalInfo.
3. Add the following new columns for commitment discount utilization and coverage:
   1. CommitmentDiscountQuantity
      1. Savings plans = EffectiveCost / ExchangeRate.
      2. Reservations = PricingQuantity / x_PricingBlockSize * x_CommitmentDiscountNormalizedRatio.
   2. CommitmentDiscountUnit
      1. Savings plans = x_PricingCurrency (e.g., “USD”, “CNY”).
      2. Reservations w/o ISF = PricingUnit (e.g., “Hours”, “GB”).
      3. Reservations w/ ISF = "Normalized {PricingUnit}".
   3. x_CommitmentDiscountNormalizedRatio = AdditionalInfo.RINormalizationRatio ?? null.
   4. x_CommitmentDiscountSpendEligibility = “Covered” / “Eligible” / “Not Eligible”
   5. x_CommitmentDiscountUsageEligibility = “Covered” / “Eligible” / “Not Eligible”
   6. x_AmortizationClass
      • “Principal” for commitment discount purchases and refunds.
      • “Amortized Charge” for commitment discount usage rows.
      • (null) for all other rows.
4. Add the following other new columns:
   1. ServiceSubcategory with a new mapping.
   2. x_ServiceModel with a new mapping.
   3. x_SkuPlanName = PlanName (marketplace meter metadata).
   4. x_SkuSize = ArmSkuName (meter metadata).
5. Update the following columns to fix known bugs:
   1. BillingPeriodEnd is showing Oct 2 (as of Sep 26).
   2. ContractedUnitPrice/ContractedCost = UnitPrice for on-demand prices based on MeterId and PartNumber (EA) or PSMT (MCA).
   3. ListUnitPrice/ListCost = MarketPrice for on-demand prices based on MeterId/PartNumber (EA) or PSMT (MCA).
   4. SkuId = ProductId (same value for EA and MCA) and replace -1 with null.
   5. SkuPriceId = “{PriceType}{Term}{ChargeCategory}{MeterId}{ProductId}{SkuId}{MeterType}{TierId}{OfferId}_???”.
   6. SubAccountName = Replace "Unasigned" with null.
   7. x_AccountId = Replace "-2" with null.
   8. x_AccountName = Replace "Unassigned" with null.
   9. x_AccountOwnerId = Replace "Unassigned" with null.
   10. x_InvoiceSectionId = Replace "-2" with null.
   11. x_InvoiceSectionName = Replace "Unassigned" with null.
   12. x_PricingUnitDescription = Replace "Unassigned" with null.
   13. PublisherName is null for reservation usage, reservation purchases, and savings plan unused charges.

For additional information about what’s included in the FOCUS 1.1 release, refer to the FOCUS changelog.

🙋‍♀️ Ask for the community

We could use your help:

1. Please vote this issue up (👍) to prioritize it.
2. Leave comments to help us solidify the vision.