Power BI Reports - Part 3: Cost Management Data and Lab Services Data Together

Microsoft

Sep 14, 2021

In the first blog post of this series, we used PowerBI to visual data from Cost Management for your labs. In the second, we recorded and displayed information about the labs themselves.

Important! This blog post does build on the previous two parts, so please complete them before proceeding.

To do this, we’ll need to match up information from Lab Services to information from Cost Management. The goal of this blog post is to create a report that lists cost per student and costs for configuring templates and will look like the following report. With this report we will also be able to answer questions like “How much did it cost to setup the template machines for a class?” and “Did anyone start an unassigned vm?”

Since the Cost Management data is historical data, we need to access the historical data for Lab Services information. Using the most recent Lab Services information (as we did in the part 2 of this blog post series) will cause issues if labs, virtual machines, or users have been deleted.

A word of caution before we proceed-- the lab, virtual machine, and user information will show the most recent total usage for the life of the virtual machine because we get this information directly from Azure Lab Services. The cost data is historical. So, unless you have been exporting your costs from Cost Management since the beginning of the life of the lab, any calculations using both vm usage information and cost management data may show discrepancies.

Update the Model

First, we need a table with all the historical vm information from lab services.

We will add a connection, to the storage account and vminfo-dailyexports container from the part 2 of this series.

Rename the table to vminfo-historical to help use identify when we are using historical and not most recent data.
Since we are only keeping the historical data, keep only the files that do not equal the ‘vminfo-latest.csv’ file. (The ‘vminfo-latest.csv’ file has the most recent information for lab virtual machines and a duplicate of the the historical version of the data.)
Select combine files. PowerBI will automatically change data types for the date and duration columns.
Save transformations.

To answer to question “how much am I spending on each student?” we need to correlate the information from dailyexports Cost Management data (as configured in part 1 of this series) to the vminfo-historical Lab Services data. To do that, let’s create another table that has a list of all the unique vmIds and the correlating usernames and emails.

From Power BI Desktop, select Modeling tab, select New Table.

For the table formula, enter:

DistinctHistoricalVmUserInfo = DISTINCT(SELECTCOLUMNS('vminfo-historical', "VmName", [VmName], "VmId", [VmId], "UserName", [UserName], "UserEmail", [UserEmail]))

Filter out any rows that have and empty UserEmail. Rows with empty user emails are virtual machines that have not yet been assigned to a student. From the Data view, select Text filter -> Does not equals…

On the Text filters window, click OK. (Leaving the value blank will remove all the rows that are blank for UserEmail.)

Now we have a table that correlates every virtual machine registered and claimed by a student, even if the lab has been deleted.

Lastly, we need to tell Power BI about the relationship between our newly created DistinctHistoricalVmUserInfo table and the dailyexports table. We’ll need to create the relationship manually.

From the Model view of Power BI Desktop, select the Home tab, then select Manage Relationships.

Select New… on the Manage Relationships dialog.
Create a relationship between from the ResourceId column of dailyexports table to the VmId column of DistinctHistoricanVmUserInfo table.

In the Model view, the tables should look like:

Create the data visualizations

With both the lab information and cost data, we can analyze the cost of individual users. To see how much is spent on each student, we will use a table visualization. For the table, set the values to

‘DistinctHistoricalVmUserInfo’[UserEmail]
the sum of ‘dailyexports’[PreTaxCost]

That’s all we need to do to create our table! The relationships we set up previously allow use to match the user email to the vm id to the total cost associated with that vm id.

Notice the empty line in the first row? This is the total cost for running any VM not associated with a student like the template VMs. If someone started an unclaimed student VM, that is included in this total as well.

What if we want more information about this blank line total? It would be good to know how much templates cost. Are there unassigned VMs being started by the instructor? Let’s add that information to this view.

It will be useful to know the lab name and if the vm is a template or not in one assigned to a student. So, let’s add a column to the dailyexports table with that information in one field.

In the Data view of Power BI Desktop, select the dailyexports table.
In the Table Tools ribbon and select New Column.
For the column formula, enter

LabAndVMName = CONCATENATE(CONCATENATE(CONCATENATE(dailyexports[LabName], " ("), dailyexports[VmName]), ")")

This column will display ‘{lab name} ({virtual machine name})’ for a student VM or ‘{lab name} (template)’ for a template VM.

Let’s change the table visualization to a matrix visualization. Set the rows to:

‘DistinctHistoricalVmUserInfo’[UserEmail]
‘dailyexports’[LabAndVMName]

Set the value to:

the sum of ‘dailyexports’[PreTaxCost]

When I expand the blank line in the matrix, we can see how much configuring the templates costs me. I can also see that an instructor started one of the student lab VMs that is not assigned to a student. (Note, if an instructor starts a VM after it is assigned to the student, that cost will show under the students name as we are grouping all costs associated with a lab VM to its owner.)