Gordon Douglass
SAP
This playbook provides guidance on how to measure the unused portion of committed discounts. Percentage of Commitment Discount Waste refers to the percentage of commitments which have not been applied to on demand spend. This playbook is NOT meant to stipulate what percent is right or good for a business, but rather how to generate this metric to make informed business decisions.
Formula
unused CB percentage = (Cost of CB Discount unused / total cost CB Discount) x 100
Any individual that is responsible for making commitment discount purchases, individuals that are responsible for creating measurements to determine the effectiveness of committed discounts or both.
Note: Individual involvement may vary from organization to organization depending on the maturity of the organization and skills and knowledge of the individuals.
This section provides information that contributes to the success of this Playbook; the information here may include specific datasources, reports or any relevant input
Links to tools, utilities and templates to use during the playbook.
sql
SELECT SUM(discount_amount) AS unused_discount_cost
FROM commitment_discounts
WHERE start_date <= DATE YYYY-MM-DD' -- Start date is on or before YYYY-MM-DD AND end_date >= DATE 'YYYY-MM-DD' -- End date is on or after YYYY-MM-DD
AND usage_amount = 0 -- No usage on YYYY-MM-DD
sql
SELECT SUM(cost) AS total_cost
FROM commitment_discounts
WHERE date = 'YYYY-MM-DD`'
>python
import requests# Set the Azure subscription ID
subscription_id = ‘<your_subscription_id>’
# Set the evaluation period start and end dates
start_date = datetime.datetime(YYYY, MM, DD)
end_date = datetime.datetime(YYYY, MM, DD)
# Format the dates in the required format (YYYY-MM-DD)
start_date_str = start_date.strftime(‘%Y-%m-%d’)
end_date_str = end_date.strftime(‘%Y-%m-%d’)
# Set the Azure Cost Management API endpoint
api_endpoint = f’https://management.azure.com/subscriptions/{subscription_id}/providers/Microsoft.CostManagement/query?api-version=2019-11-01′
# Set the query to retrieve the total cost of reserved instances
query = {
“type”: “ActualCost”,
“timeframe”: “Custom”,
“timePeriod”: {
“from”: start_date_str,
“to”: end_date_str
},
“dataset”: {
“granularity”: “Monthly”,
“aggregation”: {
“totalCost”: {
“name”: “PreTaxCost”,
“function”: “Sum”
}
},
“filter”: {
“and”: [
{
“dimension”: {
“name”: “ReservationId”,
“operator”: “NotIn”,
“values”: []
}
}
]
}
}
}
# Make the HTTP GET request
response = requests.get(api_endpoint, json=query)
# Check if the request was successful
if response.status_code == 200:
# Parse the response JSON
response_json = response.json()
# Retrieve the total cost from the response
total_cost = response_json[‘properties’][‘rows’][0][0]
# Print the total cost
print(f’Total cost of reserved instances: {total_cost}’)
else:
# Print the error message
print(f’Error: {response.text}’)
Make sure to replace `<your_subscription_id>` with your actual Azure subscription ID. Also, adjust the `start_date` and `end_date` variables to specify the evaluation period you’re interested in. Ensure you capture the output, for the final calculation after the next step.
>python
Make sure to replace `’your_subscription_id’`, `’your_resource_group_name’`, and the evaluation start and end dates with your own values. Also, ensure that you have the necessary
You can obtain your total daily CB cost from API and the console.
To obtain the total CB cost from the console, you can obtain the daily cost by accessing the GCP Console and generating a custom report, with a custom range, and group by SKU. CUDs are a SKU. Filter on Commitments in the SKU’s to obtain a consolidated list of only CB’s.
The filtered total will be shown at the bottom.
Capture this cost, to obtain the unused cost, if the total cost of the CUD is $100 and the unused cost is $30. The percentage of unused cost can be calculated as (30/100) x 100 = 30%.
In the GCP big query, you can use the below query to determine your unused cost of a CUD commitment for a single day.
SQL
SELECT
date(start_time) AS start_date,
date(end_time) AS end_date,
sum(usage_rate) x (end_time – start_time) AS total_usage_rate,
sum(commitment_unit_usage_rate) x (end_time – start_time) AS total_commitment_unit_usage_rate
FROM
`your_project.dataset.table_name`
WHERE
start_time >= ‘2023-07-01T00:00:00’ AND start_time < ‘2023-07-02T00:00:00’
GROUP BY
date(start_time),
date(end_time)
ORDER BY
total_usage_rate DESC;
The above query calculates the total usage and commitment unit usage rates for a specific date range within a dataset. It uses the ‘start_time’ and ‘end_time’ columns to filter the data and the ‘usage_rate’ and ‘commitment_unit_usage_rate’ columns to calculate the total usage and commitment unit usage rates, respectively. The results are grouped by date and ordered in descending order based on the total usage rate.
if the total cost of the CUD is $100 and the unused cost is $30. The percentage of unused cost can be calculated as (30/100) x 100 = 30%.
We’d like to thank the following people for their help on this Playbook:
We’d also like to thank our supporters: David Lambert, Taylor Houck , and Brian D’Altilio.
Rate Optimization 
Workload Optimization