This post original appeared on LinkedIn 01/28/2019

This post will build upon Part 1: Getting the Data. This post will outline one of several ways the data can be interpreted and presented to others within your organization. The value of visualizing your organization’s Azure spend should not be underestimated. Armed with visualized data we have seen success cases where DevOps teams were able to reduce their Azure spend by more than 2,000%, and that was with only a single application.

One of the first question an organization may ask is “How much is application X costing us in terms of hard Azure spend?”. aka How much are we paying Microsoft to host all parts of our application in the cloud ?

When looking at the data the question should arise, “How can these data points be tied to one another for reporting purposes?” One of the traditional approaches is to group all related resources into a single resource group. This is simple enough, as Consumption Insights provides reporting on the resource group out of the box. However; some organization may split their resource groups out differently. For instance, an organization may split an application’s data into a separate resource group from the hosting or from any additional PaSS offerings. One reason for doing this might be to accommodate additional data or retention needs. The crux of the need is how to tie resources that exists in different resource groups and/or regions together. Fortunately, Microsoft has provided us the ability to add custom key value pairs in the form of Azure Tags

Tags are not a new concept for those that are familiar with such Microsoft offerings as SharePoint Online and Team Foundation Server (TFS aka…VSTS…..aka Azure DevOps). Those who have used them before are quick to point out both the pros and the cons of leveraging tags. Pros: easy to use, repeatable, and quick to run queries off of. Cons: manually have to enter them, maintenance. One benefit that differentiates Azure Tags over say the tags in TFS is the key value pair concept. In another words I can only assign one value per tag name.

Notice that even in the screenshot Microsoft even calls out that

“Tags are name/value pairs that enable you to categorize resources and view consolidated billing by applying the same tag to multiple resources and resource groups.”

At the time of this writing, there is at least one short coming that is not heavily advertised. Microsoft does not support tags on all resources. A complete list of supported tags is here. To help summarize this information here is a list, from Microsoft, of services not currently supporting tags.

In conversations with Microsoft this list is constantly being updated with the hope of having tags supported by the end of Q1 of 2019.

Now that there is one proposed method on how the data can be enriched, the discussion turns to how this tag data will appear in our Power BI queries.

After refreshing Consumption Insights, the “Tags” field might be populated like this.

Those with more of a technical background might be quick to point out that this is in a JSON object format and they’d be correct.

However, this JSON still needs to be parsed in order to isolated the key value pairs from one another. For this example the key value pair is “ProjectName”.

Luckily for those that aren’t as technical Power BI can do this a number of ways. This article will outline a few various ways that this can be accomplished for both non-technical and technical folks.

Power BI being a powerful Business Intelligence tool has a feature for adding columns based upon examples of what the data should look like. This function is called “Column From Example” and is located on the Add Column tab.

We enter what we’d like the value to be in the new column and Power BI will attempt to build a formula that matches our expected output. In the screenshot below the text in black is what was entered as the expected output and the text in gray is what Power BI is calculating as our desired result.

Pretty cool right? This functionality can be applied on any column(s). If it needs to be refined further this can be accomplished by hitting the gear icon next to the newly created step and Power BI will provide a UI that shows the transformations which are being applied:

Of course this transformation is being translated to the Power Query M language behind the scenes. This code looks like what is below and can be just copied and inserted into the Power BI query on the Advanced Editor screen.

newStepName = Table.AddColumn(#"Previous Step Name", "New Column Name", each Text.BetweenDelimiters([Tags], """TagName"": """, """"), type text),

With this step now in place when the data is refreshed the Power BI query will parse through the tag deliminators that are setup and create additional data columns that are relevant for what an organization may want to track. Some examples of additional data besides a Project Name might be an indicator if the resource is being shared by multiple resources or perhaps a department name.

For this example the new column, Project Name, has been created. Now visualizations and data tables can be created leveraging this new field. Here’s a quick one using a Treemap visualization where the cost of various components can be displayed in one simple visualization.

Now that have this information is available enterprise executive level dashboards can be created. Here is one that shows an organizations top five spend by project over the past month:

The blank Project Name in the chart is the Uncategorized resources. Since not all resources in Azure support tagging this creates a slight gap in reporting which Microsoft is working on addressing as discussed earlier.

Where this comes in handy in today’s DevOps model is empowering teams to see how much their applications are costing and letting them take corrective action to reduce costs. Here is an example of how much of an impact being able to visualize spend can have:

In this example the team realized their spend was $76.03 a day and through proper scaling and scheduling of resources was able to bring down their application costs to $.03 a day. That’s a cost savings of 2,534%, which also serves as a reminder that their are ways out there to efficiently build your applications. Just sometimes the ability to visualize the Azure footprint can lead to new discoveries and insights.