[Hubs] Data ingestion transform changes

[flanakin]

⚠️ Problem

Data transforms in FinOps hubs are a critical part of improving the data quality and filling gaps to facilitate FinOps operations. There are a number of issues and open questions that have come up over time about the data coming from various systems. This issue is used to track requested changes, ideas for consideration, and any open questions that need an answer.

🛠️ Solution

HubScopes

Beta Give feedback

1. Detect any scopes that are duplicated via the drop-by folder path
2. Create an array of missing data based on the configured retention setting
3. Add min/max data timestamps for each scope

CommitmentDiscountUsage (reservation details)

Beta Give feedback

1. What are these columns: ReservedHours, TotalReservedQuantity, UsedHours
2. Is SkuName the same as x_SkuSize?
3. What are the possible values for Kind?
4. Consider adding ServiceSubcategory (1.1)
5. Does this dataset include non-hour units (namely the ReservedHours column)?

Costs (FOCUS cost)

Beta Give feedback

1. Add ServiceModel
2. Add CommitmentDiscountSpend/UsageEligibility
3. Consider fixing PricingQuantity and ContractedCost with bad values due to the wrong PricingQuantity scale
4. Consider fixing "Other" ServiceCategory values
5. Consider replacing "Microsoft Corporation" PublisherName values
6. Fix FOCUS conformance gaps
7. ContractedCost/UnitPrice are 0 for commitment discount purchases
8. Savings plan purchases show a ChargeFrequency of "Usage-Based" but should be "One-Time" or "Recurring"
9. Savings plan purchases do not have a ServiceName
10. x_BilledUnitPrice is 0 for commitment discount purchases
11. Lowercase Microsoft 1.0-preview ResourceId values
12. Should commitment discount purchases have a time for ChargeperiodStart/End?
13. Should Adjustment costs be on List/ContractedCost?
14. PublisherName "Microsoft Corporation" -> "Microsoft"
15. File a bug on CM for ContractedCost being off by the x_PricingBlockSize
16. PricingUnit is incorrect for reservation purchases
17. Reservation usage has List/ContractedCost but Effective/BilledCost == 0
18. Create a pattern for identifying (and filtering out) commitment discount purchases (so they aren't dobule-counted)
19. PricingUnit is "Units" when it should be "Operations"
20. Consider setting BillingAccountType/SubAccountType for non-Azure datasets
21. Replace SkuId/SkuPriceId for Microsoft EA rows
22. Add SkuPriceIdv2 for Microsoft rows
23. Add warnings for any changes applied to the data
24. Add warnings (errors?) when there's a difference between meter metadata in cost and price datasets
25. Create alerts when there are differences between cost/price data (???)
26. Confirm the ProviderName value for AWS and GCP data and update it in the ProviderName backfill
27. What should the ListCost/UnitPrice and ContractedCost/UnitPrice be for rounding adjustments?
28. What should the ListCost/UnitPrice and ContractedCost/UnitPrice be for commitment discount purchases?
29. Why do price sheet savings plan usage prices not match cost effective prices?
30. Consider adding a unique charge ID per row
31. DQ: Some purchases (e.g., M365) have ChargeFrequency = "Usage-Based"

Prices (price sheet)

Beta Give feedback

1. Handle null MeterId
2. Consider adding x_CommitmentDiscountMinimum (as in the minimum commitment amount; e.g., 100TB, 1PB, 10PB for storage)
3. Consider adding x_CommitmentDiscountNormalizedRatio
4. Consider adding x_ServiceModel
5. Consider cleaning up x_SkuRegion
6. Consider creating new rows for reservation usage
7. SkuPriceId should not be "_" for unused savings plan rows
8. Why is MeterName missing for some records (e.g., reservations)?
9. Why are 309 meters missing all meter metadata for MCA?
10. Why is MarketPrice not the on-demand list price for savings plan prices?
11. Why is MarketPrice ~= UnitPrice (but not ==) for savings plan prices?
12. Why are there Az Stack and Az Comm Services meters with "Unassigned" ProductName, MeterCategory, MeterSubcategory, MeterName, MeterRegion?
13. Does ListUnitPrice/x_EffectiveUnitPrice match usage data for savings plans?
14. Does x_SkuMeterName match usage data?
15. Does the effective start date for reservation prices change based on the export date?
16. How can we differentiate between Microsoft and third-party meters?
17. How do we create a unique ID for third-party meters for MCA?
18. How do we differentiate dev/test for MCA?
19. Compare join vs. lookup performance for populating savings plan prices
20. Compare join vs. lookup performance for populating pricing unit columns
21. Compare join vs. current per-row approach for populating commitment discount eligibility

Recommendations (reservation recommendations)

Beta Give feedback

1. Move ProviderName upstream
2. Why is SubscriptionId not included?
3. Add support for ingesting multiple types of recommendations (not a transform issue)
4. Add Advisor recommendation columns

Transactions (reservation transactions)

Beta Give feedback

1. Consider looking up the billing account ID from cost data to fix the invalid BillingAccountId column
2. Is the Currency column the pricing or billing currency?
3. A "Cancel" ChargeClass is not valid in FOCUS -- should we track this differently or leave it as an acceptable conformance gap?
4. Move ProviderName upstream
5. Map BillingFrequency values to ChargeFrequency
6. Should we include ChargePeriodEnd?

🙋‍♀️ Ask for the community

We could use your help:

1. Please vote this issue up (👍) to prioritize it.
2. Are there any open questions or concerns you have about the Cost Management data? How can we improve it?
3. Leave comments to help us solidify the vision.