Azure Cost Management
Introduction
Over the last year I have been managing costs incurred on a project built using Microsoft Azure technologies. In this blog post I want to highlight some of the cost management techniques we used, which resulted in the project realising a significant saving of over 50% in monthly costs.
The topic will be covered in two blog posts:
1) Azure Cost Management — Report Creation
2) Azure Cost Management — Cost Analysis
The system is built using the following technologies
The Azure tenant / subscription(s) are maintained under a Cloud Service Provider account (CSP) and were recently migrated to the new Azure Plan model.
The costs shown within the reports reproduced in this post were correct at the time of generation. All costs are subject to change by Microsoft.
Azure Cost Management — Report Creation
Approach
As the system contains multiple subscriptions, the initial approach to cost management was to download the resource costs from the Azure Portal Cost Management section and import them into an Excel spreadsheet. The licensing costs would be provided by the CSP team (also in Excel).
After importing 4 files (3 subscriptions resource cost files / 1 license cost file) it was obvious that Excel wasn’t going to be the best tool for the job and I decided on using Power BI instead.
I originally tried linking Power BI directly to Azure Cost management, which according to various articles on Microsoft Docs is possible. Unfortunately, after contacting Microsoft it seems this is not the case with a CSP account which has just been migrated to the Azure Plan Model. Based upon this information I will continue downloading cost information from the Azure Portal.
One aspect of cost saving everyone should be aware of is how long it takes before the full effect of a change can be seen. For example if you make a change in January, then you will see a reduction immediately within the Azure Portal. From my Power BI report’s point of view I will see the immediate benefit once I have loaded January’s figures, but will not see the full reduction until I have the following months figures (February’s figures in this example) which will show the full effect of any changes which have been made.
Azure Portal Costs Download
Under a CSP account, the best way I have found to download cost information is to go to the subscription first. I know you can go straight to Cost Management, but this will default to a Management Group Scope rather than the subscription, so it’s easier to go direct to the subscription. The example shown below is from my MSDN account.
1) Login to portal.azure.com
2) Select relevant subscription
3) Select Cost Analysis
4) Change view to ‘Cost by resource’ and then change the date for the period you wish to extract
5) Once the screen is refreshed select ‘Download’ and export the information as an Excel file.
Azure will then download a file. This file will have a name like
“CostManagement_Visual Studio Enterprise 2022–01–04–1139.xlsx”
I rename this immediately to something more meaningful. In the above example I would rename it to “VSE 202112.xlsx” as I was downloading December 2021 costs. On the project I have three subscriptions, one for Development, Test and Production. For this the first files available was from December 2020 so I downloaded and renamed the files to “Dev 202012.xlsx”, “Test 202012.xlsx” and “Prod 202012.xlsx”. The license file I received from our CSP admins was named “CSP Licensing Dec 2020.xlsx”.
Power BI — Importing files
To import the files into Power BI I used the ‘Transform Data’ option to open the Power Query Editor. Once in the editor:
1) Click ‘New Source’ and select Excel
2) In the open dialog select the relevant file and click ‘Open’
The Azure cost files normally have two worksheets: one being a summary, the other the data sheet. Select the data sheet. Normally the example below would have a sheet named ‘Data’. As I have previously imported this file the name has been changed to reflect the file name.
Then click ‘Ok’
The file will be imported. Within Power BI I rename the imported file to the same name as the file.
Note that during the import Power BI has analysed the data and created the following ‘Applied Steps’:
· Promoted Headers
· Changed Types
3) Add new Column
Azure data resource files do not contain any date information. To work around this and to allow me to merge all resource files into a single table/entity I add a new column called ‘Date’ and set it to a relevant value. In this example I set it to “202012” (format is YYYYMM).
Select ‘Add Column’ and then click ‘Custom Column’, change the new column name to ‘Date’ and the value in the ‘Custom column formula’ then click ‘Ok’
4) Right click on the file name and uncheck ‘Enable Load’. This will prevent the file being loaded into the Power BI Model. Remember all imported data will be merged into either the resource ‘data’ or the ‘license data’ file.
Steps 1 to 4 are then repeated for the other files.
5) As mentioned above I will merge all files into a single file for reporting. For the Azure resource files I will combine them into a single file called ‘Data’ and for the license data I will combine them into a file called ‘License Data’. This is achieved in the first applied step for each file. An example of the Azure resource file source step is:
= Table.Combine({Dev202012, Prod202012, Test202012, Dev202101, Prod202101, Test202101, Dev202102, Prod202102, Test202102, Dev202103, Prod202103, Test202103})
Another reason for having a single merged file is that any additional changes (steps) I require will only have to be performed on the merged file(s).
Note: cost files downloaded from Azure are only finalised once Microsoft have produced an invoice (CSP Model). i.e. assuming your subscription process date is the 4th of the month. If you download the cost file on the 1st you may have incorrect figures as they could be updated by Microsoft prior to the 4th.
File Contents
After importing the files into Power BI (via Transform Data) you instantly become aware that the licensing file and the Azure resource files have a different schema layout and are also reporting information at a different level. Licensing is at tenant level, whereas the resource files are at subscription level.
After seeing this I immediately realised that I would have at least two different reports to produce and analyse. Although the Licensing file does have date information available, I added a new ‘Date’ column in the same way as the Azure resource file for consistency.
Azure Resource File / Azure Tags
Anyone who has been involved in Azure development for any period of time will realise the benefit of tagging resources. After checking the merged Azure Resource files, I found that some resources had not been initially tagged correctly. This had been corrected quickly but meant that I had some historical costs with incorrect tags.
The only real useful information source for determining what a historical Azure Resource could have been used for is from its resource group name. Luckily the project has multiple environments i.e. Development, SIT, UAT etc and this has been reflected in the resource group name.
To resolve this issue, I created another Excel spreadsheet called RGInfo which was imported into the report with the following columns
· Subscription
· ResourceGroupName
· Override
· Environment
This would contain a list of resource group names which included the resources with incorrect tags in the historical data. The environment field would then be set to a default tag for that resource group e.g. Development for any resource groups which was development environment specific.
The Override option was added during my initial review of the data. I found some tags made sense at the Azure resource level but from a cost point of view it was starting to fragment information to a lower level than required. For example, a tag could be ‘dev-apim’ to represent an Azure APIM resource for the development environment. By using the override option, I could allow the report to treat the tag as Development and include the resource group within the ‘Development’ environments costs.
The main data file has a number of ‘Applied Steps’ which occur after the merging of the original Azure Resource Files. For information these steps are explained here
1) Source: Combine all files from the different subscriptions.
2) New Column: Environment Tag — contains the environment tag from the Tags field.
3) New Column: Subscription-GUI — contains the subscription GUI extracted from the Resource ID field.
4) New Column: Subscription Name — contains a text description of the tenant. Calculated using If Else statements.
5) Merge Queries: Merge the main data file and the RGInfo file. This was an outer join from the Data file to the RGInfo file on the ‘ResourceGroupName’
6) Expand Fields: The RGInfo fields Environment and Override are added to the Data file.
7) New Column: Environment — updated field based upon ‘Service Name’ or if the Override field is set to True or the Environments Tag is blank.
8) New Column: DateEnvironment — merged field of Date and new Environment field.
9) New Column: DateServiceName — merged field of Date and Service Name field.
The DateEnvironment and DateServiceName columns will be used for two views from the Azure resource Files.
Azure Licensing File
The Azure Licensing file provides us with the cost of licenses incurred for the month. This is at tenant level rather than subscription. It is worth mentioning that the Azure Resource File shows resource usage on individual resources under an Azure Resource Group; whereas the licensing file shows the cost of licensing across the billing period where the actual number of licenses may have fluctuated on a daily or even hourly basis. This can cause interesting conversations when you present a report which (for example) shows 20 licenses @ £5.00 per month but your actual spend is higher (example £120.00) because licenses that were in use have been removed. The above figures are very simplistic, but Microsoft do provide a very good explanation of the prorate issue here:
https://docs.microsoft.com/en-us/partner-center/common-billing-scenarios-monthly
Sample monthly billing scenarios for new subscriptions, changing license amounts, or suspensions
In a similar fashion to the Azure Resource file the Licensing file has several ‘Applied Steps’ which occur after the merging of the various licensing files.
1) Source: Combine all Licensing files
2) Remove Columns: remove various fields which are not useful for reporting (“MpnId”, “OrderId”, “SubscriptionId”, “SyndicationPartnerSubscriptionNumber”, “OfferId”, “DurableOfferId”)
3) Add Column: End Quantity. This field is set to zero if the ChargeType (as specified in the MS document above), is equal to ‘Cycle instance prorate’. This is an amendment record which shows how many licenses have been added / removed on a particular date/time in the month.
4) Add Column: End Unit Price. This field is set to zero if the ChargeType (as specified in the MS document above), is equal to ‘Cycle instance prorate’. This is an amendment record which shows how many licenses have been added / removed on a particular date/time in the month.
5) Add Column: End Month Cost. This is a calculation of the End Quantity and End Unit Price.
The ‘End’ columns above are used to calculate what the current number of licenses and cost are at the end of the month. This was used to remove the prorate records from the calculation. To fully understand how the licensing cost report is calculated you must understand the prorate issue discussed in the Microsoft link above.
Cost Reports
So, I now have all this data loaded into the Power BI model and can add future data as time progresses. The main question is: what do I report on? After some initial thinking I came up with the following reports:
1) Environment: This report displays a list of costs against the final calculated environment for each tenant / subscription. It also allows you to select which month you wish to view.
The report allows me to drill down into specific environments. For example, when I click on the ‘dev’ environment the table to the right auto-filters to show just the relevant records.
2) Service: This report displays a list of costs against the Azure Service for each tenant / subscription.
Again, with the same drill down functionality:
3) Licenses: This report displays a list of licenses and costs for the month.
In this report the forecast cost shows the number of ‘current licenses’ multiplied by the ‘Unit Cost’ whereas the Current Cost includes any prorate costs for the month.
4) Time: As more data becomes available a time report was produced for each of the original reports.
Environments:
This report allows you to drill down into individual environments:
Service Name:
Again, this report allows you to drill down into individual services
Licenses
This will allow you to drill down into individual licenses
5) Difference last month
After using the reports to analyse data for a few months, I saw an increase in costs when I was expecting the total cost to reduce. I found trying to identify what had increased that month compared to the previous month was time consuming and started to investigate if there was an easier way. After some research I constructed a DAX (data analysis expressions) expression to calculate the difference between the current month’s value and the previous months. This proved invaluable in highlighting what had increased. Difference last month reports are identified with a DLM suffix.
Environments (DLM)
Services (DLM)
Licenses (DLM)
6) Summary Page
This Summary page provides a running total and graphical representation. The ‘ADC’ charge is project-specific, and not related to Azure resource or license costs.
This completes the first blog post. In the next post Azure Cost Management — Cost Analysis, we will see which areas we are incurring costs within Azure and hopefully look at how we can reduce the spend going forward.
If you want to learn more about the Microsoft Team here at Capgemini, take a look at our open roles and consider joining the team!
