Using Power BI to view your Azure Usage

One of the challenges of understanding your Azure usage is to decipher the usage report from the Azure Portal. And I really needed that, since I was getting past my monthly cap consistently. Since I’m not an Enterprise user that can use this FREE amazing tool, I decided to figure out what was going on with my MSDN subscription. My first step was to download the usage report from the Azure account portal:

image

then:

image

Pick Version 2 – Preview.

Once done, the CSV file you download has two parts. The first shows the summary of utilization per Meter type.

image

Actually, it is based on these 3 items:

image

These 3 together are the key to find the utilization per resource. Column O has the Rate we need in order to find the final cost of single resource. But why is that necessary? Look at the example below, which comes from the second part of the CSV file:

image

Note that you don’t have a cost per line, only the Consumed quantity. So, how can we know? The answer is in columns D, E and F in the second table, which are exactly the same ones used in the first table:

image

Now, if I could grab the Rate from the first table and assign it to each line on the second one based on these 3 columns, wouldn’t it be great?

Enters a slight Excel tweak and Power BI. The first thing is to extract the first peace of the CSV file and turn into a separate tab. Let’s call it RateTable:

image

Now, the remaining rows need to be alone in another tab. Let’s call it azureusage:

image

Now let’s save the CSV as an Excel file and leave it ready.

If you don’t have Power BI Desktop, go here to get it. Once there, you can just add data to it:

image

Select Excel and point to your file:

image

You should see both tabs:

image

Now click on Edit and PBI will take you to the query editor view. There, we will need to execute a few steps to get the proper information out of our data.

1. The first thing is to remove blank rows from the RateTable query:

image

Also make sure you remove unused rows, like the Daily usage title that comes originally from the initial CSV file.

2. Next, we need to create a custom column on both queries, to create a unique key (just so we can relate both of them). We will start with the query we have opened. Select Meter Name, meter sub-category and meter zone columns, in this order, and select Merge Columns:

image

Give it a name:

image

You should now see a new column there:

image

3. Repeat the process for the azureusage query:

image

4. Now let’s create a relationship between the two queries. Click on Close and Apply to save your changes:

image

Once there, click on the relationship icon: image

Once there, you can try to detect the relationship. Click on Manage Relationship up-top and then Autodetect:

image

Isn’t it cool? Smile

You could have added it manually or even just connected the fields between the two tables:

image

6. Great. Back to Edit Queries.  In this step will add corresponding rate for each usage line, based on the type of meter (and meterkey) we have just created. To do that, go as follows:

– Click on Merge Queries up-top while in the azureusage query. This dialog will show:

image

Select as below (MeterKey on both of them):

image

This creates a new column. We don’t need all the tables returned. To select what we need (Rate), click on the arrows icon:

image

And select the Rate only:

image

Rename the column to Rate.

7. Now, all you need is something that calculates the cost for that entry, by multiplying the Rate by the Consumed Quantity. To do that, you click on Add Column:

image

And Add a Custom column:

image

Click Ok. Now set the type of the data in the column:

image

Now close and Apply.

8. Back in the main canvas, select on type of visualization and the Cost and Instance ID on the right side:

image

And there you have it: your cost per individual resource in Azure:

image

It is kind of a long tutorial, but might be a good way to visualize you detailed cost per instance.

 

Hope this helps!