Report on your consolidated Data Migration Assistant Assessments With PowerBI
In a previous post How to consolidate Assessment Reports we looked at a method to consolidate the JSON output files from the DmaCmd.exe command line tool into SQL Server.
Now lets look at a method to put some basic reports on top of that data.
For this we will use PowerBi which you can download for free here.
Data Migration Assistant Consolidated Reporting with PowerBI
This PowerBI Pack is broken down into 5 reports: The Reports can be downloaded here: dma-reports-v3-1
- Dashboard
- This is a report with snapshot stats and a drill down report
- On Premise Upgrade Readiness
- Data comes from the UpgradeSuccessRanking view in the DMAReporting database. This shows the % upgrade success factor for your assessed databases
- On Premise Feature Parity
- Shows the feature recommendations for the target SQL Server version
- Azure SQL DB Upgrade Readiness
- Data comes form the UpgradeSuccessRanking view in the DMAReporting database. this shows the % upgrade success factor for databases assessed for Azure SQL DB migrations
- Azure SQL DB Unsupported Features
- Shows features that exist in your existing databases which are not supported in Azure SQL Database (V12)
Getting Started – Change Your Data Source
In order to get these reports working with your environment you will need to change the data source within Power Bi to point to your DMAReporting database.
In Power Bi select the down arrow next to “Edit Queries” and select “Data source settings”.
Select “Change Source…” and enter your Server/Database details”.
Click “Ok” then “Close”
Now refresh your reports:
Dashboard
The dashboard shows details about all of your assessments. You can use the slicers on the left hand side to filter by instance or database. The bar chart can be used to drill down into specific categories to see where the issues lie.
To drill down, enable the drill down feature by clicking the circle with the down arrow in the top right of corner of the bar chart:
The drilldown sequence is set as follows (under “Axis”). The change the sequence simply drag and drop columns to the desired order:
This view becomes even more powerful when first filtering by a specific database then drilling down into it’s specific category issues. For example, here I select the HR database from Instance SQL01 and look for all objects which are preventing migrations (Breaking Changes).
On Premise Upgrade Readiness
This report shows a snapshot of how ready your databases are to migrate to a higher version of SQL Server. The data in this report comes from the dbo.UpgradeSuccessFactor_OnPrem view in the DMAReporting database looking specifically at “Breaking Changes”.
Filtering by Instance / Database name you can see via the score cards at the top what version the database could be migrated too. For example if I filter by the AdventureWorks 2012 database we can see that the database is ready to move to all future versions. This is defined by ensuring there are no breaking changes for that database and compatibility level.
On Premise Feature Parity
This report can be used to highlight what new features can be used for the database on it’s target version.
By selecting the value in the funnel chart the data set at the bottom will highlight what objects would be affected by this feature. For example, here I'm filtering by the AdventureWorks2012 database and selecting the value for Stretch database for storage savings. This shows me the tables which could benefit from this feature.
Azure SQL DB Upgrade Readiness
This report shows the database readiness to migrate to Azure SQL Database V12. The data from this report comes from the dbo.UpgradeSuccessRanking view in the DMAReporting database looking specifically at “Migration Blockers”.
Azure Feature Parity
This report can be used to highlight what instance level features are not supported by Azure SQL Database V12.
By selecting the value in the funnel chart the data set at the bottom will highlight what instance / database feature is not supported. For example, here I'm filtering by the AdventureWorks2012 database and selecting the value for Always on Availability group configuration is not supported in Azure SQL Database. Here we can also get Impact details and more info on the issue.
Azure SQL DB Unsupported Features
This report highlights which features are not supported for a given database when it’s target is Azure SQL Database (V12).
Again, by filtering by the database name and value in the funnel chart we can see details on the unsupported feature like what object is affected and recommendations to remediating the problem (scroll right to see recommendations).
For example, filtering by the DTC database and Read-only databases cannot be upgraded, we can see a list of objects which are affected and a link to recommended remediation:
Get the Reports Here
Learn More
Data Migration Assistant Blog Data Migration Assistant Download Power BI Download