Analyzing Azure EA Cost Using Power BI – Part 2 Tags

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” }

tagsreplace

Later we can use this TagInfo column to find the resources that   does not have any tag.

tagreplace2

  • Step 2 – Right Click on Tags and Select Transform/JSON
    Now all of our Tags should be converted to Records

TransformJson

  • 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

tagsexpand

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.

 

CostbyTags

 

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.

 

Usagenotags

Download link for the template…

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.