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
For Advanced Calculations (MoM/Cumulative Usage) check Part3
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……
Extending Usage Analysis by Tags
When it comes to personalize usage analysis based on your company’s need , Azure Tags comes to the rescue. Tags help us to group cost items in our own specific preference. Here is how we can utilize Azure Tags in Consumption API connector. Here I assume you already tagged the Azure resources based on your reporting needs.
Extracting Tag data and add as new column
To extract Tags go to Edit Queries , select Usage and scroll to find Tags column. This column is a JSON data but Power BI categorize this column data type as text. Before we transform this data to JSON first we need to handle the resources with no Tags otherwise transform will generate error messages.
Sample Tag
{ “Environment”: “PROD”, “Platform”: “Windows”}
To extract the Tags ;
- Step 1 – Right Click on Tags and Select Replace Values
Now all empty Tags should be replaced by
{ “TagInfo”: “N/A” }
Later we can use this TagInfo column to find the resources that does not have any tag.
- Step 2 – Right Click on Tags and Select Transform/JSON
Now all of our Tags should be converted to Records
- Step 3 – Right Click and select duplicate column as when we expand we will lose the original Tags column. Having a copy of it becomes handy if we need to expand more tags later on.
- Step 4 – Click on Expand Icon on Tags Column and Select tags to expand , select Tags you want to expand and click OK
M Query Step definition for expanded tags are in this format
= Table.ExpandRecordColumn(#”Duplicated Column”, “Tags”, {“Platform”, “Environment”, “Business Unit”, “TagInfo”}, {“Tags.Platform”, “Tags.Environment”, “Tags.Business Unit”, “Tags.TagInfo”})
Don’t forget to replace these with your own tags
- Step 5 – Close and Apply
Here I expand 4 tags Platform,Environment,Business Unit, TagInfo and add them as new columns to my data.
Now we can use these new columns in our calculations
Analyzing cost by Platform
In this example our Azure resources are tagged based on the platform they belong to ( Infra/Databases/Staging etc). Since we have Platform tags as columns, now we can utilize them in our calculation and replicate what we have done in CostbyService page to create CostbyTag page.
More Measures based on Tags
Now we can calculate the overall usage vs Platform Usage and find our cost distribution by platform.
To calculate the total cost
TotalUsage = SUMX(
ALL(Usage),Usage[Cost])TotalUsageSelected = SUMX(
ALLSELECTED(Usage),Usage[Cost])
Here we can use ALL / ALLSELECTED functions to decide if we want to ignore all filters (ALL) or accept outside filters (ALLSELECTED) while calculating the cost.
If we want to calculate the usage by Service within Infra platform. We can use this to step approach
Calculate the total usage for Infra platform
TotalUsagePlatformInfra = SUMX(
FILTER(
ALLSELECTED(Usage), Usage[Tags.Platform]=”Infra”),Usage[Cost])
Using similar measures we can visualize different dimensions of overall usage and actively track them.
Analyzing cost for Resources with no Tags
If we start assigning Tags like Project / Department / Cost Center, by utilizing techniques explained here we can track the usage based on our own point of view.
As part of this process we need to go back and check the resources without Tags to check if we are missing any critical cost item.
Cost Distribution No Tags page of the template shows the Daily Usage for resources with no tags , recourse types and resource list with total cost.
We need to keep an eye on these items and assign Tags as necessary or better utilize Azure Policies to automatically tag the resources.
