SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Making Sense of Data Migration Assistant Assessments with Power BI

The Data Migration Assistant is a great tool developed to assess your SQL Server environment for a migration to a modern SQL Server platform such as SQL Server 2017 or Azure SQL Database. If you’re assessing a single SQL Server with only a few databases, reading the exported results (which can be in .JSON or .CSV format) may not be that difficult. But if you’re assessing dozens of environments including hundreds of databases, we need a better way to understand the results.Recently a customer reached out to me to help with the challenging task of understanding the assessment results of 61 SQL environments including over 500 databases  being considered for migrating to Azure SQL Database. Now there is already a great solution that exists for aggregating DMA assessment exports but it only works for assessment exports in .JSON format. The existing solution also requires that the assessment results be written to a SQL Server database.

So I built a solution that uses Power BI to parse the DMA assessment exports (.CSV format) and aggregate the data so it can be more effectively browsed and understood in a report. There are two different templates to use depending on if your assessing your SQL environment for migration to Azure SQL or SQL Server, so make sure you download the right file seen below.

Download the Assessment Analysis for Azure SQL template here.

Download the Assessment Analysis for SQL Server template here.

The difference between the two templates is that the Assessment Analysis for Azure SQL template includes a report page breaking down the feature parity information output from DMA. The Assessment Analysis for SQL Server template does not include this information so make sure you use the right template depending on what you’re doing with your SQL environment.

The Power BI templates have one parameter used to specify the location of the assessment files. When you open the template file you’ll be asked to specify the directory where the assessments are located. Keep in mind that your assessments should be in a .csv format for use with this template. Also, this template only works for assessments for migrating to Azure SQL DB but I’ll be updating it for assessments to upgrade to modern versions SQL Server.

imageMake sure that the AssessmentDirectory parameter ends with a slash, as seen above. Once you provide the AssessmentDirectory parameter value, click Load and your reports will light up. Below you can see the reports that are included with the template, but you can create any other reports you might need based on the dataset.

imageimageimageimageI hope that you find this useful for simplifying your migration to a modern SQL Server platform. Let me know if you found this useful or if you have a question by leaving a comment down below! Thanks for reading!


Download the DMA Assessment Analysis for Azure SQL template file

Download the DMA Assessment Analysis for SQL Server template file

Download the Data Migration Assistant to perform a migration assessment of your environment

Download Power BI Desktop

SQL with Dustin Ryan

Dustin Ryan is a Data Platform Solution Architect on the Education Specialist Team Unit at Microsoft. Dustin has worked in the business intelligence and data warehousing field since 2008, has spoken at community events such as Code Camp, SQL Saturday, SQL Rally, and PASS Summit, and has a wide range of experience using the Microsoft business intelligence stack of products across multiple industries. Prior to his time at Microsoft, Dustin worked as a business intelligence consultant and trainer for Pragmatic Works, a Microsoft partner. Dustin is also an author, contributor and technical editor of books such as Applied Microsoft Business Intelligence, Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX, and others. You can learn more about Dustin at http://SQLDusty.com.


Leave a comment on the original post [sqldusty.com, opens in a new window]

Loading comments...