ADX Dashboard - Savings/Reservation calculation wrong

[AngelAlves123]

🐛 Problem

In 0.8 Azure Data Explorer Dashboard, Rate Optimizations page, the Reservation savings are not properly calculated, showing a negative number, while utilization of Reservations is 100%. This is happening with MCA contract type.

👣 Repro steps

1. Go to: Azure Data Explorer 0.8 Dashboard, "Rate optimization" page
2. Scroll down to: Monthly savings trend (as example)
3. Click on: "Type:Reservation:Savings
4. Report shows values in negative amounts

📷 Screenshots

ℹ️ Additional context

Confirmed that "ListCost" field has lower values than "EffectiveCost" for Reservation costs.

🙋‍♀️ 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.

[AngelAlves123]

An example directly from Costs_final_v1_0 table:

[flanakin]

Yes, this is a known issue with the data coming from Cost Management. There are a number of reasons this happens. Here's a query we used to identify the different scenarios. Can you share what this returns for you? That will help us know what scenarios you're seeing.

Costs
| extend EffectiveOverContracted = iff(ContractedCost < EffectiveCost, ContractedCost - EffectiveCost, decimal(0))
| extend ContractedOverList      = iff(ListCost < ContractedCost,      ListCost - ContractedCost,      decimal(0))
| extend EffectiveOverList       = iff(ListCost < EffectiveCost,       ListCost - EffectiveCost,       decimal(0))
| extend scenario = case(
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Usage', 'Reservation usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Purchase', 'Reservation purchase missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Usage', 'Savings plan usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Purchase', 'Savings plan purchase missing list',
    ListCost == 0 and ChargeCategory == 'Purchase', 'Other purchase missing list',
    isnotempty(CommitmentDiscountStatus) and ContractedOverList == 0 and EffectiveOverContracted < 0, 'Commitment cost over contracted',
    ListCost == 0 and BilledCost == 0 and EffectiveCost == 0 and ContractedCost > 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost should be 0',
    ListCost == 0 and ContractedCost == 0 and BilledCost > 0 and EffectiveCost > 0 and x_PublisherCategory == 'Vendor' and ChargeCategory == 'Usage', 'Marketplace usage missing list/contracted',
    ContractedOverList < 0 and EffectiveOverContracted == 0 and x_SourceChanges !contains 'MissingListCost', 'ListCost too low',
    ContractedUnitPrice == x_EffectiveUnitPrice and EffectiveOverContracted < 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost doesn''t match price',
    EffectiveOverContracted != 0 and abs(EffectiveOverContracted) < 0.00000001, 'Rounding error',
    ContractedOverList != 0 and abs(ContractedOverList) < 0.00000001, 'Rounding error',
    EffectiveOverList != 0 and abs(EffectiveOverList) < 0.00000001, 'Rounding error',
    ContractedCost < EffectiveCost or ListCost < ContractedCost or ListCost < EffectiveCost, '',
    EffectiveCost <= ContractedCost and ContractedCost <= ListCost, 'Good',
    '')
| project-reorder ListCost, ContractedCost, BilledCost, EffectiveCost, EffectiveOverList, EffectiveOverContracted, ContractedOverList, x_SourceChanges, ListUnitPrice, ContractedUnitPrice, x_BilledUnitPrice, x_EffectiveUnitPrice, CommitmentDiscountStatus, PricingQuantity, PricingUnit, x_PricingBlockSize, x_PricingUnitDescription
| summarize count(), EffectiveOverContracted = sum(EffectiveOverContracted), ContractedOverList = sum(ContractedOverList), EffectiveOverList = sum(EffectiveOverList), Type = arraystring(make_set(x_BillingAccountAgreement)) by scenario

[AngelAlves123]

Here it goes the outcome.

[flanakin]

@AngelAlves123 Looks like what you're seeing is reservations where the effective cost is higher than what you would've paid without the reservation. I checked a few instances of this and the data looks correct. I would suggest contacting support to confirm the prices and costs are correct. Feel free to reference me by name.

Here's a query to get the list of meters:

Costs
| extend EffectiveOverContracted = iff(ContractedCost < EffectiveCost, ContractedCost - EffectiveCost, decimal(0))
| extend ContractedOverList      = iff(ListCost < ContractedCost,      ListCost - ContractedCost,      decimal(0))
| extend EffectiveOverList       = iff(ListCost < EffectiveCost,       ListCost - EffectiveCost,       decimal(0))
| extend Scenario = case(
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Usage', 'Reservation usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Purchase', 'Reservation purchase missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Usage', 'Savings plan usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Purchase', 'Savings plan purchase missing list',
    ListCost == 0 and ChargeCategory == 'Purchase', 'Other purchase missing list',
    isnotempty(CommitmentDiscountStatus) and ContractedOverList == 0 and EffectiveOverContracted < 0, 'Commitment cost over contracted',
    ListCost == 0 and BilledCost == 0 and EffectiveCost == 0 and ContractedCost > 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost should be 0',
    ListCost == 0 and ContractedCost == 0 and BilledCost > 0 and EffectiveCost > 0 and x_PublisherCategory == 'Vendor' and ChargeCategory == 'Usage', 'Marketplace usage missing list/contracted',
    ContractedOverList < 0 and EffectiveOverContracted == 0 and x_SourceChanges !contains 'MissingListCost', 'ListCost too low',
    ContractedUnitPrice == x_EffectiveUnitPrice and EffectiveOverContracted < 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost doesn''t match price',
    EffectiveOverContracted != 0 and abs(EffectiveOverContracted) < 0.00000001, 'Rounding error',
    ContractedOverList != 0 and abs(ContractedOverList) < 0.00000001, 'Rounding error',
    EffectiveOverList != 0 and abs(EffectiveOverList) < 0.00000001, 'Rounding error',
    ContractedCost < EffectiveCost or ListCost < ContractedCost or ListCost < EffectiveCost, '',
    EffectiveCost <= ContractedCost and ContractedCost <= ListCost, 'Good',
    '')
| project-reorder ListCost, ContractedCost, BilledCost, EffectiveCost, EffectiveOverList, EffectiveOverContracted, ContractedOverList, x_SourceChanges, ListUnitPrice, ContractedUnitPrice, x_BilledUnitPrice, x_EffectiveUnitPrice, CommitmentDiscountStatus, PricingQuantity, PricingUnit, x_PricingBlockSize, x_PricingUnitDescription
| where Scenario == 'Commitment cost over contracted' 
| summarize by ListUnitPrice, ContractedUnitPrice, x_EffectiveUnitPrice, x_PricingBlockSize, x_SkuMeterName, x_SkuMeterId, x_SkuOfferId, SkuPriceId, x_EffectivePeriodStart = BillingPeriodStart, BillingAccountId = tolower(BillingAccountId)

[AngelAlves123]

Hi, using the last query, what I get is the 3 columns with the same amount (for all meters)

As part of our MCA, we don't have additional discounts on Reservation SKU's, but the information displayed seems right from my understanding:

1. ListUnitPrice: Public Pricing for the SKU Reservation
2. ContractedUnitPrice: Contracted Pricing for the SKU Reservation (as we don't have discount, should be the same as "ListUnitPrice"
3. x_EffectiveUnitPrice: Price we paid (same as both above)

Is my understanding wrong?

It seems the missing information is the equivalent PAYG Price.

[flanakin]

ListUnitPrice is the public, retail price.

ContractedUnitPrice is what you would pay without reservations.

This is saying that you don't have any discounts, including commitment discounts. This shouldn't be returned with the "Commitment cost over contracted" scenario. Unless the cost doesn't match the unit prices 🤔

Did you export your prices? This might happen because we aren't able to populate the prices, so we copy the x_EffectiveUnitPrice into the other values. You can check the x_SourceChanges column to see if it has "MissingListUnitPrice" or "MissingContractedUnitPrice".

This query adds cost as well so you can see if those numbers are different and don't match the prices. That shouldn't happen here, but just in case.

Costs
| extend EffectiveOverContracted = iff(ContractedCost < EffectiveCost, ContractedCost - EffectiveCost, decimal(0))
| extend ContractedOverList      = iff(ListCost < ContractedCost,      ListCost - ContractedCost,      decimal(0))
| extend EffectiveOverList       = iff(ListCost < EffectiveCost,       ListCost - EffectiveCost,       decimal(0))
| extend Scenario = case(
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Usage', 'Reservation usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Purchase', 'Reservation purchase missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Usage', 'Savings plan usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Purchase', 'Savings plan purchase missing list',
    ListCost == 0 and ChargeCategory == 'Purchase', 'Other purchase missing list',
    isnotempty(CommitmentDiscountStatus) and ContractedOverList == 0 and EffectiveOverContracted < 0, 'Commitment cost over contracted',
    ListCost == 0 and BilledCost == 0 and EffectiveCost == 0 and ContractedCost > 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost should be 0',
    ListCost == 0 and ContractedCost == 0 and BilledCost > 0 and EffectiveCost > 0 and x_PublisherCategory == 'Vendor' and ChargeCategory == 'Usage', 'Marketplace usage missing list/contracted',
    ContractedOverList < 0 and EffectiveOverContracted == 0 and x_SourceChanges !contains 'MissingListCost', 'ListCost too low',
    ContractedUnitPrice == x_EffectiveUnitPrice and EffectiveOverContracted < 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost doesn''t match price',
    EffectiveOverContracted != 0 and abs(EffectiveOverContracted) < 0.00000001, 'Rounding error',
    ContractedOverList != 0 and abs(ContractedOverList) < 0.00000001, 'Rounding error',
    EffectiveOverList != 0 and abs(EffectiveOverList) < 0.00000001, 'Rounding error',
    ContractedCost < EffectiveCost or ListCost < ContractedCost or ListCost < EffectiveCost, '',
    EffectiveCost <= ContractedCost and ContractedCost <= ListCost, 'Good',
    '')
| project-reorder ListCost, ContractedCost, BilledCost, EffectiveCost, EffectiveOverList, EffectiveOverContracted, ContractedOverList, x_SourceChanges, ListUnitPrice, ContractedUnitPrice, x_BilledUnitPrice, x_EffectiveUnitPrice, CommitmentDiscountStatus, PricingQuantity, PricingUnit, x_PricingBlockSize, x_PricingUnitDescription
| where Scenario == 'Commitment cost over contracted' 
| summarize sum(ListCost), sum(ContractedCost), sum(EffectiveCost) by ListUnitPrice, ContractedUnitPrice, x_EffectiveUnitPrice, x_PricingBlockSize, x_SkuMeterName, x_SkuMeterId, x_SkuOfferId, SkuPriceId, x_EffectivePeriodStart = BillingPeriodStart, BillingAccountId = tolower(BillingAccountId)

[AngelAlves123]

In fact I haven't exported Price Sheet... I've exported it now, and all other datasets also exported, but it hasn't solved the issue:

[AngelAlves123]

output from the latest query you shared (with the refreshed data)

[flanakin]

In fact I haven't exported Price Sheet...

I've exported it now, and all other datasets also exported, but it hasn't solved the issue:

@AngelAlves123 Correct. The main scenario you're facing is likely using the correct costs, so exporting the prices won't change anything. That's why I suggested filing a support request. Either the effective cost coming from Cost Management is wrong or you're paying more for your commitment discount than you would without it. Filing a support request should give you the answer you need there.

Can you report back what happens with the support request? That would be very helpful for others who are facing the same thing.

In the meantime, we'll work on the other issues as part of #1111

[japyjaap]

@AngelAlves123 I'm running into the same sort of issue and am wondering if you filled a support request. Our Effective costs are higher then the contracted cost while we there are reported savings on RI's and saving plans shown in the dashboard. The difference between list cost and contracted seems to be correctly reflecting our ACD.

I've added of the query with the different scenarios. The main issue seems to be with the reservations, I'm not sure what to ask for in a support request.

I've also added a screenshot for the cost of a subscription where the ESR is relative high. As you can see the numbers aren't as expected

]

[peter-mogaka]

Created a support ticket and received this back. Is this something you're already doing? @flanakin

[japyjaap]

Any update on this? @arthurclares

According to the feedback from support a fix for MCA was in the making. Is there any progress or ETA for a resolution?