Analyzing the usage and tuning resources is a key responsibility in Cloud Management. We need to understand where we spent , what are the trends and where we can tune our spending. When it comes to analyzing Azure Cost Microsoft offers different tool set with different capabilities;
- Cloudyn
- New Azure Cost Management
- Azure Consumption API Connector for Power BI
- In this blog post I would get into details of using Consumption API to bring cost metrics into Power BI and we will be using Power BI capabilities for analyzing the cost. Power BI gives you the most flexibility when it comes to reporting and customization. In this blog post series I will get into MoM /YoY analyses, distributing the cost between departments / sub companies , custom charge back options etc.
For initial setup and Power BI template check Part 1
For extracting tags and bring them into Usage Analysis check Part 2
In part 3 I’ll focus on Month over Month / Week over Week usage analysis , rolling usage and distributing usage among departments/customers/companies or any other category we want.
Disclaimer :I’m not a Power BI expert. This work is mainly collection of exercises we have gone through with our customers. There are multiple ways of achieving the results in Power BI and these are the ones we have seem to make this work……
Using Time Intelligence MoM Usage Analysis
To calculate the Month over Month , Week Over Week, we can utilize measures in Power BI .
Here is how to calculate the cost for the previous month using calculate function ;
PrevMonthCost = CALCULATE(SUM(Usage[Cost]),PREVIOUSMONTH(Usage[Month]))
Now we can use this measure together with Usage[Month] field to visualize increase/decrease in usage Month over Month.
Note that Month over Month calculation gives us the right values only for completed months. You can use month slicer on top to remove last month from the calculation.
Calculating Cumulative Usage
To calculate rolling sum for usage, we again utilize measures. Lets create a new measure as follows;
Cumulative Usage = CALCULATE( sumx(Usage,Usage[Cost]), FILTER( ALLSELECTED(DateKey),DateKey[Date]<=Max(DateKey[Date])))
This will sum up the cost for the dates before the current value .
Now we can use this new measure instead of Cost field to visualize UsagetoDate.
Distributing Cost based on Static Mapping
In some cases when we manage the EA usage , we need to show usage distribution for specific departments , customers or companies we host resources for. If those customers are sharing the resources line a single VM or storage account or a SQL instance in Azure , there is no built in way to distribute usage among those customers. Here first, I’ll show a way to distribute the resource usage using a static mapping table. For example, lets assume we share our platform to host workloads for 5 companies. By using a static table we will show how usage is distributed among these 5 companies.
To fill the table
- Step 1 – Click Enter Data
- Step 2 – Rename Column1 to Platform and add the platforms we need to distribute the usage
- Step 3 – Add new columns and name them as your customers/Departments/Companies etc and will the values as percentage of the cost which should be reflected by that customers/Department/Company
- Step 4 – Rename Table to CostMapping
Here out mapping will look like
Now we can create new measures to calculate usage for specific customer/department/company
UsageCompany1 = [Cost] * AVERAGE(‘CostMapping'[Company1])/100
Repeat same for all 5 companies to show how usage is divided among these 5 companies .
Now we can utilize our platform tags and company usage distribution to find the cost allocation ;
Distributing Cost based on Dynamic Values
Just like static mapping we can utilize App Usage / SQL Record Counts / Transactions to estimate % of the cost distribution for respective companies / departments. For example you can use asset intelligence to count page view for the respective month and map it with company information. Power BI has many connectors to go fetch the data from Azure SQL , Log Analytics or Application Insights , we can bring all this data together and correlate all of them.
