<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Mark Hartshorn on Medium]]></title>
        <description><![CDATA[Stories by Mark Hartshorn on Medium]]></description>
        <link>https://medium.com/@mark.hartshorn?source=rss-f38ba3e1e6c5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*dmbNkD5D-u45r44go_cf0g.png</url>
            <title>Stories by Mark Hartshorn on Medium</title>
            <link>https://medium.com/@mark.hartshorn?source=rss-f38ba3e1e6c5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 00:40:38 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mark.hartshorn/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Azure Cost Management —Cost Analysis]]></title>
            <link>https://medium.com/capgemini-microsoft-team/azure-cost-management-cost-analysis-78ad2b82db93?source=rss-f38ba3e1e6c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/78ad2b82db93</guid>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[microsoft]]></category>
            <category><![CDATA[finance]]></category>
            <category><![CDATA[software-development]]></category>
            <dc:creator><![CDATA[Mark Hartshorn]]></dc:creator>
            <pubDate>Wed, 14 Sep 2022 10:09:20 GMT</pubDate>
            <atom:updated>2022-09-14T10:09:20.648Z</atom:updated>
            <content:encoded><![CDATA[<h4>This is the second post on Azure Cost Management, the first post is <a href="https://medium.com/capgemini-microsoft-team/azure-cost-management-dd0f61a3a2d3">Azure Cost Management — Report Creation</a>.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zTxeLVq9XPFPYMxjyRSnCg.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@micheile">micheile dot com</a></figcaption></figure><p>Before I start on my approach, I should point out that Azure does an excellent job of providing recommendations on cost saving. This can be found under Cost Management / Advisor Recommendations. You may have seen this appearing as a pop-up window when you log into the Azure Portal.</p><p>Some of the analysis will require you to have experience in Azure and Azure products. It is also helpful if the development/support team are available to discuss why parts of a system were developed in a particular fashion. Some of the savings were achieved over a period of time as the development team had to make relevant changes and then deploy them through the various environments.</p><p>So, we now have several reports, the question is what can they tell us? Using both the Environment and Service reports I was able to look at the Azure Resource spend from two different sides. Using Power BI’s ability to sort reports just by clicking on the columns, I started with the Service report, sorted the monthly totals column descending and looked at any entry above £1000 per month.</p><figure><img alt="Service Costs over £1000 per month" src="https://cdn-images-1.medium.com/max/341/1*G5SiTBDdtuX7LCZE4bkKlg.jpeg" /><figcaption>Service Costs over £1000 per month</figcaption></figure><p>I should highlight that I started looking at services first as this allowed me to investigate possible savings that will be reflected across multiple environments.</p><h3>API Management</h3><p>On investigating the individual API Management service, I found the following:</p><figure><img alt="API Management Costs by Environment" src="https://cdn-images-1.medium.com/max/260/1*m-_2-clPMy8EWDXjYVhtDg.jpeg" /><figcaption>API Management Costs by Environment</figcaption></figure><p>Now this is where your Azure knowledge will have to come in to play. I know from previous projects that APIM has various Tiers, -(see Microsoft Docs: <a href="https://docs.microsoft.com/en-us/azure/api-management/api-management-features">Feature-based comparison of the Azure API Management tiers</a>) which have different monthly prices. In the above list environments dev, dmo, ext, sit, tst are using the Developer tier whereas uat1, prd and preprod are using the Premium tier. The Premium tier comes with a minimum cost of £2000 per month and is fully backed up with SLAs from Microsoft. The Developer tier has no SLA, and documentation indicates that Microsoft can update the service at any time.</p><p>Now, the production environment (prd) is live and requires the Premium tier for performance and SLAs etc, but do uat1 and preprod require the Premium tier? In this instance the answer isn’t as simple as yes or no. If uat1 is being used for user acceptance testing, then it should be Premium tier; but outside of these periods, could it be downgraded to Developer tier? The same logic also applies to Pre-Production (preprod). In the projects’ current development life cycle, it was acceptable to downgrade both uat1 and preprod to Developer tier and save £4000 per month. These would then be upgraded back to Premium tier when required.</p><h3>Virtual Machines — Sizing</h3><figure><img alt="Virtual Machine Costs by Environment" src="https://cdn-images-1.medium.com/max/258/1*priM5jBZyntvFjZzA6KIbw.jpeg" /><figcaption>Virtual Machine Costs by Environment</figcaption></figure><p>It is fair to say that the project has a significant number of virtual machines (VMs) all being used for different reasons. There were also a number of developers still working on the project using different VMs as development machines.</p><p>My approach to VMs was</p><p>1) All VMs: are they overpowered? An analysis was conducted to see if any VMs could be downsized. An example of this was the Azure DevOps self-hosted build machine. When the development team was at its largest, the VM was sized at 8 vCPU and 32GB memory. This has now been reduced to 4 vCPU and 16GB memory. When the system goes fully into support it could be reduced to a smaller size or even powered down (deallocated) if no deployments are expected.</p><p>2) Development VMs: Do they have automated start and stop times? If they are part of DevTest Labs, then they can be setup to run for 12 hours Monday through Friday, which is normally more than enough for developers even with different working patterns. This means you only pay for 2 ½ days per week and then save 4 ½ days costs. If a developer requires their VM for additional work over the weekend or later into an evening, then it is simple to change the times to allow it to be available. Note if you have developers across multiple geographic locations then you should create a separate DevTest Lab for each time zone.</p><p>3) All VM’s: Are they still required? Some VMs were powered down/deallocated but had not been deleted. Admittedly the bulk of the cost for a VM comes from Compute, but Disks and IP address costs can mount up over time.</p><p>4) Production VMs: assuming they have been sized correctly can they be changed to reserved instances?</p><p>Using the above approach over 12 months, I reduced the virtual machines costs from £5756 per month down to £2422.</p><h3>Functions</h3><figure><img alt="Function App Costs by Environment" src="https://cdn-images-1.medium.com/max/240/1*Jxr85IFZYM0TMNapxASTzQ.jpeg" /><figcaption>Function App Costs by Environment</figcaption></figure><p>On investigating Function App costs, I found that each environment had two ‘Application Service plans’ that different functions were assigned to. Originally some of the functions was going to be for internal use only, but this changed as the system was developed. I put forward a change that all functions should be deployed into a single ‘Application Service plan’ and the second plan could be removed. This would save £240 per month per environment. As we had eight environments (excluding SFTP) we would save £1920 per month.</p><h3>SQL Databases</h3><figure><img alt="Database Costs by Environment" src="https://cdn-images-1.medium.com/max/239/1*x3L0DPsJtpPaEuugBRAhxg.jpeg" /><figcaption>Database Costs by Environment</figcaption></figure><p>With the managed SQL Databases, I started looking at the performance details over the past few months. It was obvious that the production database was under-utilised except for a period overnight Monday to Friday. After discussing this with the development team, I was informed that a Function app was running at that time and extracting a very large file from the system. The database was set to its current level to ensure the file was exported within the default Function app timeout period (one hour). The timeout period was extended, and the database performance was reduced. If the Function app had issues and timed out, then it could be manually rerun the following day. This change was applied across all environments. This reduced the SQL expenditure from £4519 per month to a cost of £756 per month.</p><h3>Storage</h3><p>No changes were made from a direct storage point of view.</p><h3>Azure DDOS Protection</h3><p>Azure DDOS Protection standard had been originally specified for the programme. A review was conducted with the senior architects and the security architect, and it was decided that the default DDOS Protection service was sufficient. Therefore, the upgraded service was removed. This saved approximately £2000 per month.</p><h3>Virtual Machine Licenses</h3><p>These licenses are part of a third-party service (Firewall etc) and could not be reduced.</p><h3>Licenses — Microsoft 365 Services</h3><figure><img alt="License Costs" src="https://cdn-images-1.medium.com/max/602/1*s_4Xt3bsybppfEi_OPx_sA.jpeg" /><figcaption>License Costs</figcaption></figure><p>The report for licenses is useful for the Licenses timeline report and Difference Last Month report to identify which licenses have increased / reduced, but to identify potential cost reductions more granular detail is needed. So, the question is: how to try and reduce licensing costs?</p><p>As licenses are allocated to people, I looked at what Azure Active Directory could provide. I downloaded a list of users with the following information</p><p>· User Name<br>· Email<br>· Account Created Date<br>· Last Login Date<br>· Number of Login’s in the last 30 days<br>· Allocated Licenses</p><p>I sorted the report on ‘Number of Login’s in the last 30 days’ ascending and then ‘Last Login Date’.</p><p>Looking through the report I realised that I had several distinct groups of people</p><p>1) People who had never logged in.<br>2) People who had logged in infrequently (less than 5 times in the last 30 days)<br>3) People who are logging in on a regular basis</p><p>Group One: People who had never logged in</p><p>I decided that for people who had never logged in (and whose accounts were older than ninety days), we would disable their accounts and remove all licenses. If their account was required in future, we could re-enable it and re-allocate licenses. For people whose accounts had been created within the last ninety days, we enquired whether they would be using the allocated licenses. It was amazing to find out the number of teams who would take on a new person and instantly give them all licenses, even if they didn’t know what part of the system they were going to use.</p><p>Group Two: People who had logged in infrequently</p><p>Again, I enquired what licenses people would require. This also prevented us removing people’s licenses if they had been on holiday recently. When challenged most people accepted that we could save a significant amount of money per month (potentially up to £350 per person per month) and would happily give up their licenses knowing we could reallocate them very quickly — the project team looking after user/license administration for us have an SLA of 99% of requests completed with an hour. If you are in a situation where you need to raise a request and have (for example) a 5-day SLA with another team then this situation may not work as well and people may be reluctant to give up their licenses.</p><p>Group Three</p><p>I conducted an audit on each person and (if any) licenses looked odd, i.e. I wasn’t sure why they had them; then I contacted them to discuss. This did remove a few licenses but was limited in comparison to groups one and two.</p><p>This approach has now been adopted and licenses are reviewed on a bi-monthly basis. The first review saw a reduction of just over £2000 per month in licenses being removed.</p><h3>Conclusion</h3><p>Although Cloud computing is marketed as cheaper, it is very easy to quickly spend significant amounts of money. Although Azure provides recommendations on how to save money I found there is very little tooling available which allows you to drill down into costs and make comparisons over time. The report I developed provided me with the ability to see costs from an environment and an Azure service point of view. This is what was needed to get to the crux of consumption and resource spend and allowed me to focus on the relevant areas of the system.</p><p>The report has proved useful in allowing me to see differences from the previous month and hopefully this information will help the ongoing programme team in keeping their Azure costs down. Any significant increases can be investigated if they were not expected. You also have to be aware that some costs will be required, for example Security, third party license costs; and you may have limited if any chance of reducing them.</p><p>Certain aspects of cost saving can go against architectural design principles - for example two App Service Plans for the Azure Functions might make logical sense from an architectural point of view but comes with increased cost. Reviewing the design principle and redeploying the amended configuration saved a significant amount of money over the expected life of the system.</p><p>Although I have designed a way of replacing/allocating tags to amend tag details, it doesn’t remove the necessity of having a well-designed tagging policy.</p><p>On this project I was lucky that the development team was still present and some of the changes could be completed and deployed through to production quickly. Changes to a live system will take time before you see a reduction in costs. i.e. a code change can take months before it is published to production and you will only see the full cost benefit the following month.</p><p>Overall, I managed to reduce the monthly Azure spend from £39,700 per month to £18,750 per month in fourteen months, something I am extremely proud of. An additional benefit is that a decrease in cost will also reduce your C02 emissions, so is beneficial from a sustainability point of view as well.</p><figure><img alt="Costs Summary Report" src="https://cdn-images-1.medium.com/max/602/1*PRrLiUL1tBFw4ku-xU8ZFA.jpeg" /><figcaption>Costs Summary Report</figcaption></figure><p>This is the second post on Azure Cost Management, the first post is <a href="https://medium.com/capgemini-microsoft-team/azure-cost-management-dd0f61a3a2d3">Azure Cost Management — Report Creation.</a></p><p><em>If you want to learn more about the Microsoft Team here at Capgemini, take a look at our </em><a href="https://www.capgemini.com/gb-en/careers/job-search/?search_term=Microsoft&amp;show_posts=50&amp;utm_source=medium_blog&amp;utm_medium=referral&amp;utm_content=countryorganic_link_blog_medium_blog&amp;utm_campaign=other"><strong><em>open roles</em></strong></a><strong><em> </em></strong><em>and consider joining the team!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=78ad2b82db93" width="1" height="1" alt=""><hr><p><a href="https://medium.com/capgemini-microsoft-team/azure-cost-management-cost-analysis-78ad2b82db93">Azure Cost Management —Cost Analysis</a> was originally published in <a href="https://medium.com/capgemini-microsoft-team">Capgemini Microsoft Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Azure Cost Management]]></title>
            <link>https://medium.com/capgemini-microsoft-team/azure-cost-management-dd0f61a3a2d3?source=rss-f38ba3e1e6c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/dd0f61a3a2d3</guid>
            <category><![CDATA[budget]]></category>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[cloud-computing]]></category>
            <dc:creator><![CDATA[Mark Hartshorn]]></dc:creator>
            <pubDate>Tue, 19 Jul 2022 15:54:25 GMT</pubDate>
            <atom:updated>2022-07-19T21:14:53.079Z</atom:updated>
            <cc:license>https://creativecommons.org/licenses/by-nd/4.0/</cc:license>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/910/1*FKr8f29gcduXckOPM2gvDQ.jpeg" /><figcaption>Image by <a href="https://www.pxfuel.com/en/free-photo-eptzi">Pxfuel</a></figcaption></figure><h3>Introduction</h3><p>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.</p><p>The topic will be covered in two blog posts:<br>1) Azure Cost Management — Report Creation<br>2) Azure Cost Management — Cost Analysis</p><p>The system is built using the following technologies</p><figure><img alt="Azure System Architecture" src="https://cdn-images-1.medium.com/max/1024/1*lXWos3y-4Q_-p3sgSR48Fg.jpeg" /><figcaption>Azure System Architecture</figcaption></figure><p>The Azure tenant / subscription(s) are maintained under a Cloud Service Provider account (CSP) and were recently migrated to the new Azure Plan model.</p><p>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.</p><h3>Azure Cost Management — Report Creation</h3><h3>Approach</h3><p>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).</p><p>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.</p><p>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.</p><p>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.</p><h3>Azure Portal Costs Download</h3><p>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.</p><p>1) Login to portal.azure.com<br>2) Select relevant subscription</p><figure><img alt="Azure Subscription’s" src="https://cdn-images-1.medium.com/max/602/1*ZyHfULQc5G-cSlIZHYPRiQ.png" /><figcaption>Azure Subscription’s</figcaption></figure><figure><img alt="Azure Subscription Details" src="https://cdn-images-1.medium.com/max/602/1*Knp-HibRYo5XP3Yv0iNFHQ.png" /><figcaption>Azure Subscription Details</figcaption></figure><p>3) Select Cost Analysis</p><figure><img alt="Azure Subscription Cost Analysis" src="https://cdn-images-1.medium.com/max/602/1*Lt5BYHi5CihjlyMPgTcGwQ.jpeg" /><figcaption>Azure Subscription Cost Analysis</figcaption></figure><p>4) Change view to ‘Cost by resource’ and then change the date for the period you wish to extract</p><figure><img alt="Azure Costs by Resource — Resource and Resource group name have been truncated" src="https://cdn-images-1.medium.com/max/602/1*RRUkVQKCA6d30M42cMDJkg.jpeg" /><figcaption>Azure Costs by Resource — Resource and Resource group name have been truncated</figcaption></figure><p>5) Once the screen is refreshed select ‘Download’ and export the information as an Excel file.</p><figure><img alt="Cost Analysis Download Options" src="https://cdn-images-1.medium.com/max/131/1*ZxmyzolwFw1iVbTMA_4JhA.jpeg" /><figcaption>Cost Analysis Download Options</figcaption></figure><p>Azure will then download a file. This file will have a name like</p><p>“CostManagement_Visual Studio Enterprise 2022–01–04–1139.xlsx”</p><p>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”.</p><h3>Power BI — Importing files</h3><p>To import the files into Power BI I used the ‘Transform Data’ option to open the Power Query Editor. Once in the editor:</p><p>1) Click ‘New Source’ and select Excel</p><figure><img alt="Power BI ‘New Source’ Options" src="https://cdn-images-1.medium.com/max/602/1*d0v57m_WtdIczsdv1rc7lA.jpeg" /><figcaption>Power BI ‘New Source’ Options</figcaption></figure><p>2) In the open dialog select the relevant file and click ‘Open’</p><figure><img alt="New Source ‘Select File’ Dialogue" src="https://cdn-images-1.medium.com/max/252/1*4DfmkcXL7r-2gts-lCeksQ.jpeg" /><figcaption>New Source ‘Select File’ Dialogue</figcaption></figure><p>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.</p><figure><img alt="Excel Work Sheet Selection Dialogue" src="https://cdn-images-1.medium.com/max/305/1*ihCuddFKXOIFYz3NDZIFGg.jpeg" /><figcaption>Excel Work Sheet Selection Dialogue</figcaption></figure><p>Then click ‘Ok’</p><p>The file will be imported. Within Power BI I rename the imported file to the same name as the file.</p><figure><img alt="Power BI Data Import Query Dialogue" src="https://cdn-images-1.medium.com/max/602/1*lta4rgpgb9r797lAwpHH0A.png" /><figcaption>Power BI Data Import Query Dialogue</figcaption></figure><p>Note that during the import Power BI has analysed the data and created the following ‘Applied Steps’:</p><p>· Promoted Headers<br>· Changed Types</p><p>3) Add new Column</p><p>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).</p><p>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’</p><figure><img alt="Power BI Custom Column Dialogue" src="https://cdn-images-1.medium.com/max/290/1*k1L_Jtzh6aYZrQOmOppeEQ.jpeg" /><figcaption>Power BI Custom Column Dialogue</figcaption></figure><p>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.<br> <br> Steps 1 to 4 are then repeated for the other files.</p><p>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:<br> <br> = Table.Combine({Dev202012, Prod202012, Test202012, Dev202101, Prod202101, Test202101, Dev202102, Prod202102, Test202102, Dev202103, Prod202103, Test202103}) <br> <br> 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).</p><p>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.</p><h3>File Contents</h3><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/740/1*UgodqC7BlaWTdFtDC-qrxg.jpeg" /></figure><p>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.</p><h3>Azure Resource File / Azure Tags</h3><p>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.</p><p>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.</p><p>To resolve this issue, I created another Excel spreadsheet called RGInfo which was imported into the report with the following columns</p><p>· Subscription<br>· ResourceGroupName<br>· Override<br>· Environment</p><p>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.</p><p>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.</p><p>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</p><p>1) Source: Combine all files from the different subscriptions.<br>2) New Column: Environment Tag — contains the environment tag from the Tags field.<br>3) New Column: Subscription-GUI — contains the subscription GUI extracted from the Resource ID field.<br>4) New Column: Subscription Name — contains a text description of the tenant. Calculated using If Else statements.<br>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’<br>6) Expand Fields: The RGInfo fields Environment and Override are added to the Data file.<br>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.<br>8) New Column: DateEnvironment — merged field of Date and new Environment field.<br>9) New Column: DateServiceName — merged field of Date and Service Name field.</p><p>The DateEnvironment and DateServiceName columns will be used for two views from the Azure resource Files.</p><h3>Azure Licensing File</h3><p>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:<br><a href="https://docs.microsoft.com/en-us/partner-center/common-billing-scenarios-monthly">https://docs.microsoft.com/en-us/partner-center/common-billing-scenarios-monthly</a></p><h3>Sample monthly billing scenarios for new subscriptions, changing license amounts, or suspensions</h3><p>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.</p><p>1) Source: Combine all Licensing files<br>2) Remove Columns: remove various fields which are not useful for reporting (“MpnId”, “OrderId”, “SubscriptionId”, “SyndicationPartnerSubscriptionNumber”, “OfferId”, “DurableOfferId”)<br>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.<br>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.<br>5) Add Column: End Month Cost. This is a calculation of the End Quantity and End Unit Price.</p><p>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.</p><h3>Cost Reports</h3><p>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:</p><p>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.</p><figure><img alt="Environment Report" src="https://cdn-images-1.medium.com/max/602/1*fi4QttxQl10lxkK-MYwmKQ.jpeg" /><figcaption>Environment Report</figcaption></figure><p>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.</p><figure><img alt="Environment Report Drill Down" src="https://cdn-images-1.medium.com/max/602/1*3IAF_AkUkd_fAP11jPUBNg.jpeg" /><figcaption>Environment Report Drill Down</figcaption></figure><p>2) Service: This report displays a list of costs against the Azure Service for each tenant / subscription.</p><figure><img alt="Azure Service Report" src="https://cdn-images-1.medium.com/max/602/1*JVic_pMORa_UJXmKP3eFaQ.jpeg" /><figcaption>Azure Service Report</figcaption></figure><p>Again, with the same drill down functionality:</p><figure><img alt="Azure Service Report Drill Down" src="https://cdn-images-1.medium.com/max/602/1*pXgrh3ZMOkIDClCDPRfv8g.jpeg" /><figcaption>Azure Service Report Drill Down</figcaption></figure><p>3) Licenses: This report displays a list of licenses and costs for the month.</p><figure><img alt="Licenses Report" src="https://cdn-images-1.medium.com/max/602/1*s_4Xt3bsybppfEi_OPx_sA.jpeg" /><figcaption>Licenses Report</figcaption></figure><p>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.</p><p>4) Time: As more data becomes available a time report was produced for each of the original reports.</p><p>Environments:</p><figure><img alt="Environment Costs Timeline" src="https://cdn-images-1.medium.com/max/602/1*c5XxSVVht9E_YPYg3Cys5A.jpeg" /><figcaption>Environment Costs Timeline</figcaption></figure><p>This report allows you to drill down into individual environments:</p><figure><img alt="Environment Costs Timeline Drill Down" src="https://cdn-images-1.medium.com/max/602/1*2YGwAwSPecgOYkZ2pRk3_g.jpeg" /><figcaption>Environment Costs Timeline Drill Down</figcaption></figure><p>Service Name:</p><figure><img alt="Service Costs Timeline" src="https://cdn-images-1.medium.com/max/602/1*KOzeCZEx7ZUUENkZKEuoLg.jpeg" /><figcaption>Service Costs Timeline</figcaption></figure><p>Again, this report allows you to drill down into individual services</p><figure><img alt="Service Cost Timeline Drill Down" src="https://cdn-images-1.medium.com/max/602/1*P0EVJcOwvLxPnEBZNp1Pzw.jpeg" /><figcaption>Service Cost Timeline Drill Down</figcaption></figure><p>Licenses</p><figure><img alt="License Costs Timeline" src="https://cdn-images-1.medium.com/max/602/1*L2ywTXAAp1KZVO8w6nDmKA.jpeg" /><figcaption>License Costs Timeline</figcaption></figure><p>This will allow you to drill down into individual licenses</p><figure><img alt="License Costs Timeline Drill Down" src="https://cdn-images-1.medium.com/max/602/1*aUWJIZgI7ylMKhiLzctq_Q.jpeg" /><figcaption>License Costs Timeline Drill Down</figcaption></figure><p>5) Difference last month<br> <br>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. <br> <br>Environments (DLM)</p><figure><img alt="Environments Costs Different Last Month Report" src="https://cdn-images-1.medium.com/max/602/1*TQFF_sQu4tGaSdV8IA-dlQ.jpeg" /><figcaption>Environments Costs Different Last Month Report</figcaption></figure><p>Services (DLM)</p><figure><img alt="Services Costs Different Last Month Report" src="https://cdn-images-1.medium.com/max/602/1*xwmLfh_iqsQaC-C4Yqxfag.jpeg" /><figcaption>Services Costs Different Last Month Report</figcaption></figure><p>Licenses (DLM)</p><figure><img alt="Licenses Costs Different Last Month Report" src="https://cdn-images-1.medium.com/max/602/1*u_jU7os3ESP_HRIEjW-nnQ.jpeg" /><figcaption>Licenses Costs Different Last Month Report</figcaption></figure><p>6) Summary Page</p><figure><img alt="Costs Summary Report" src="https://cdn-images-1.medium.com/max/602/1*vpZc86JBLGM3phiOfXKM_g.jpeg" /><figcaption>Costs Summary Report</figcaption></figure><p>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.</p><p>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.</p><p><em>If you want to learn more about the Microsoft Team here at Capgemini, take a look at our </em><a href="https://www.capgemini.com/gb-en/careers/job-search/?search_term=Microsoft&amp;show_posts=50&amp;utm_source=medium_blog&amp;utm_medium=referral&amp;utm_content=countryorganic_link_blog_medium_blog&amp;utm_campaign=other"><strong><em>open roles</em></strong></a><strong><em> </em></strong><em>and consider joining the team!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dd0f61a3a2d3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/capgemini-microsoft-team/azure-cost-management-dd0f61a3a2d3">Azure Cost Management</a> was originally published in <a href="https://medium.com/capgemini-microsoft-team">Capgemini Microsoft Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>