In the second part of this series, I’ll take you through the last few steps to visualise your Azure Migrate data using Power BI. If you haven’t read part one and would like to, click here.
Tables
In the previous blog post, I didn’t go into great detail about how the combined data sources from the Azure Migrate workbooks should be formatted as tables in Power BI. This is particularly important as the Assessment Summary and Assessment Properties worksheets are formated with information on rows rather than columns. To help with your formatting, here are the queries I used for each table.
All Assessed Machines
All Assessed Disks
Assessment Properties
Assessment Summary
As you can see in each of these queries, one of the four custom functions I created previously is used. This function makes it possible to pull data from all the worksheets and merge them into one table from the source directory folder.
Data Model
Power BI will automatically try to match the tables and columns to build a model that works once you have created your tables. Sometimes this can be spot on, but in some circumstances, some manual intervention is needed.
With the data tables from Azure Migrate, it’s possible that the mapping for All Assessed Disks may get confused and try to map on the Group name. You don’t want this, as assessed disks should match on the associated machine they are attached to. To correct this, change the data model by mapping the following relationships as per the table below.
| Table | Column | Relationship | Column | Table |
|---|---|---|---|---|
| Assessment Summary | Group name | 1 to 1 (both direction) | Group name | Assessment Properties |
| Assessment Summary | Group name | Many to one (single direction) | Group name | All Assessed Machines |
| All Assessed Machines | Machine | Many to one (single direction) | Machine | All Assessed Disks |
Visuals
With your data model ready, you’re now at the point of creating the visuals you need to assess the suitability for migrating workloads to Azure. This is a relatively straight forward task, dragging and dropping the visualisations you’re after in the report.
When creating your reports, consider using slicers and filters as they will help you narrow in on the assessments you created in Azure Migrate while also giving you the ability to get a holistic overview. To demonstrate this, have a look at the screenshots below.
Expanding the data model with your own data
The great thing about using Power BI for visualising this data is that it’s now possible to map other data sources in your data model to enrich your dataset, something that isn’t possible in Azure Migrate right now. For example, suppose you can grab an extract from your CMBD or service register. In that case, you should be able to map business-related information like Server/Application Owner to the Azure Migrate data by either using the assessment group name or to the machine names.
Conclusion
I hope you found this two-part blog series interesting on how you can take Azure Migrate data and enrich it with Power BI and potentially other data sources. If you’re interested in trying this yourself, the Power BI template (PBIT file) can be downloaded here.