In the last blog post, we used Power BI to visualize data from Cost Management for your labs. This blog post will focus on how to gather information about your labs and visualize that data. We’ll create a sample report that answers the questions “What is the total time a student has used a vm for a lab?” and “How many students have claimed a vm for a lab?”
To create this report, here are the
Most of the information we will need can be gathered from the Az.LabServices PowerShell module (preview). If you’ve never used the Az.LabServices, see the LabServices PowerShell Module blog post.
To gather the information, we’ll need to run a PowerShell script routinely to gather all the information. There are few options to choose from. You can create an Azure PowerShell function that has a timer trigger. Alternately, you can create a PowerShell runbook in Azure Automation . In this blog post, I’ll go over running a script in an Azure PowerShell function.
The Azure Function will navigate through the subscription and find
Information will be saved in CSV files in separate blob containers in Azure Storage. One blob container for lab related information, one for lab virtual machine related information, and one for lab user information.
wwwroot
| - MyFunction
| | - run.ps1
| | - function.json
| - Modules
| | - Az.LabServices
| | | - Az.LabServices.psd1
| | | - Az.LabServices.psm1
| - local.settings.json
| - host.json
| - requirements.psd1
New-ModuleManifest -Path Az.LabServices.psd1 -ModuleVersion "1.0" -Author "Microsoft Corporation" -RootModule 'Az.LabServices.psm1' -CmdletsToExport "*"
Important! Don’t forget to create a role assignment for the managed identity. I allowed contributor access to the subscription, but you can create more restrictive assignments.
To create a role assignment, click the Azure role assignments button, then choose Add Role Assignment (preview). One the flyout menu, set the Scope to Subscription, Subscription to your subscription and the Role to Contributor. Click Save. Note, it will take a few minutes for the permission to propagate.
The function is ready to go! Run the function to publish our lab data so we can start creating our Power BI report.
Note: There is some personally identifiable information being recorded, including user email and name. Information will be gathered from resources you specify and saved in storage accounts you specify. You have control over where data is stored, how it used, and when it is purged.
In Power BI Desktop we will use the Azure Table Storage connector to connect to the data. This can be done in the same report used in last week’s blog post or an entirely new report.
We now have data for labs, virtual machines and users. Let’s cleanup the data so we can create our reports.
The data from the blobs contains the historical and latest data for labs in the subscription. First thing we need to do is prune the data because the blob storage has both the latest and historical data. Optionally, you can create duplicates of all the tables and use one for historical data and one for the latest data.
For each table (labinfo-dailyexports, vminfo-dailyexports, and userinfo-dailyexports), transform the data as follows.
If you want to work with the historical data, keep only the files that do not equal the latest file.
Before creating any visualizations, let’s make sure Power BI has correctly detected how the lab, virtual machine and user tables are related. To do this, open the model view in PowerBI desktop. If working with historical data, all relationships will be many to many. If working with the latest data, relationships should be:
Source |
Relationship |
Destination |
labinfo-dailyexports[LabId] |
1 to many |
userinfo-dailyexports[LabId] |
labinfo-dailyexports[LabId] |
1 to many |
vminfo-dailyexports[LabId] |
The detected link only uses the LabIds of each table. Consider modifying some relationships manually to include all columns that match up. Depending on what type of reports are created, the minimum relationship description may not be enough. Model should look like the following.
Now, that we have all the relationships set, let’s cover just a couple of the questions we can answer with this information.
How many students have claimed a virtual machine for a lab?
We’ll create a table that shows the lab name, max number of users the lab can hold (lab capacity), number of users added to the lab, and number of users that have registered for the lab (count of claimed virtual machines). Note, in our data model, when a student is assigned a virtual machine by registering for a course, the virtual machine is considered claimed. Therefore, counting the IsClaimed property on a virtual machine will tell use how many students have a lab virtual machine for their exclusive use for a lab.
We need to create a measure on the table for count of the claimed virtual machines.
ClaimedVMs = CALCULATE( COUNTROWS( 'vminfo-dailyexports' ), 'vminfo-dailyexports'[IsClaimed] = TRUE() )
For the table, set values to
To see the total usage for a student over all their labs, we’ll use the virtual machine usage information. The total usage is stored as a duration type in the VmUsageString column. It’s important to note, that durations are visualized as the total number of days in decimal form. For our visualization, let’s display the usage in hours. We’ll need to create a new column in the vminfo-dailyexports table that converts the values from days to hours. Don’t forget to set the datatype to decimal before saving the column formula.
VMUsageInHours = 'vminfo-dailyexports'[VmUsageString] *24
For the table, set the values to
Rename the VMUsageInHours column to Usage (in hours) for this visual.
Last step is to publish the report! See Publish datasets and reports from Power BI Desktop for
Happing Reporting!
The Lab Services Team
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.