Azure Lab Services costs are integrated with Cost Management on the lab account level. However, sometimes it’s useful to create a custom report for your team. We can do this using the line item data from Cost Management. In this blog post we will use Power BI desktop to create a basic report that shows total cost, total number of virtual machines and total number of labs. The report will also include a table that shows cost per lab and cost per virtual machine.
To create this report, we need to complete four major tasks.
Get the data. We need to import data into PowerBI.
Transform the data. Each cost line item has all the information we need, but it will need to be separated, so we can work with lab and lab virtual machine information individually.
Create the data visualization.
Publish the report for others to see.
Get the data
There are couple options to import the Cost Management data into PowerBI. Which one to use will depend on your type of Azure agreement and your permission level.
In PowerBI Desktop, we will use the Azure Blob Storage connector to import this data. Select the usage detail data from the storage account container you used when scheduling the cost management data exports. Choose to combine the files when importing the CSV file data.
Transform the data
Each usage detail line item has the information for the full resource id of the virtual machine (either template or student) associated with the cost. As explained in cost management guide for Azure Lab Services, these resources will follow one of two patterns.
Delete InstanceId.1 to InstanceId.8. We already have the SubscriptionGuid and ResourceGroup columns, so the InstanceId.3 and InstanceId.5 columns aren’t needed.
Rename InstanceId.9 to LabAccount.
Rename InstanceId.11 to Lab.
Delete InstanceId.12 to InstanceId.14.
Rename InstanceId.15 to VirtualMachine.
Replace ‘null’ values with ‘template’. Any rows that don’t have a value for VirtualMachine are costs associated with running the template virtual machine for the lab.
Schema for the table should look something like the picture below. Depending on your Azure subscription type, there may be more columns not seen in this example.
Visualize the data
First, let’s create some cards for high-level information. Our cards will show the total cost, number of labs and number of virtual machines used.
The total cost is held in the PreTaxCost column. PowerBI already recognizes that PreTaxCost is number and will automatically add all the column values to create a sum. Add a card to the visual and add PreTaxCost to the Field property of the card visualization. Optionally change the name for the visualization from PreTaxCost to Total Cost.
For the most accurate reporting, we can’t just create a measure that counts all the distinct values in the Lab column because it is possible to have two labs with the same name in different lab accounts. So, for our measure named NumberOfLabs we will count the number of rows when grouped by all the identifying columns for a lab, which are subscription, resource group, lab account and lab name. Note, in this example the table name is dailyexports.
Creating a card for the total number of virtual machines used will be similar to creating a card for total number of labs. We need to create a measure that counts the unique combination of subscription, resource group, lab account, lab and virtual machine name. Our new measure is
Now let’s create a matrix visual to allow us to drill down into our data. For instructions how to create a matrix visualization, see create a matrix visual in Power BI. For our matrix visualization, we’ll add the Subscription, ResourceGroup, LabAccount, Lab, VirtualMachine for the rows. NumberOfLabs, NumberOfVMs, PreTaxCost and Currency will be our values. Note, for the currency column, the first value for currency will be shown with the matrix is collapsed.
After of renaming the columns for the visuals and applying some theming, our report now looks like the following picture. I’ve expanded the subscription, resource groups and the ‘enewman-demo’ lab account. Under the lab account you can see the two labs and total cost for each lab. As you can see by the plus sign next to the lab’s names, each lab could be expanded to list the virtual machines for the lab as well as the cost for each virtual machine.