Assess an enterprise and consolidate assessment reports with DMA

This post has the same title as the official Microsoft guide Assess an enterprise and consolidate assessment reports with DMA. 

The page was lately removed but luckily I was able to find it on the WayBack Machine.

Microsoft is use to drop tools without forecast, like Database Diagram for example.

So if you need to migrate your SQL Server estate to Azure and you want to see the big picture through Power BI look no further: in this page you will find all the needed tools.

Prerequisites

You need to download these three tools:

• PowerShell-Modules: If the link is broken download it from here
• LoadWarehouse script: If the link is broken download it from here
• DMA Reports Power BI template: If the link is broken download it from here

Keep in mind that you can also find the three files in this discussion: https://techcommunity.microsoft.com/t5/microsoft-data-migration/assess-an-enterprise-with-data-migration-assistant-update/bc-p/2416149#M188 , until the post will be nuked by Microsoft

Setup the PowerShell-Modules

Download the .zip and put the content into the folder C:\Program Files\WindowsPowerShell\Modules\DataMigrationAssistant

You may need to unblock the PowerShell files after you save them to the WindowsPowerShell directory to ensure that the modules load correctly. To unblock a PowerShell file, right-click on the file, select Properties, select the Unblock text box, and then select Ok.

Importing the DMA output files

If you have used the DMA tool in bulk to assess your SQL Server estate you now have the output saved in JSON.

We are going to run this command to import them to SQL Server. This command is also going to create two databases DMAReporting  and DMAWarehouse

dmaProcessor -processTo SQLServer -serverName "localhost" -CreateDMAReporting 1 -CreateDataWarehouse 1 -databaseName DMAReporting -warehouseName DMAWarehouse -jsonDirectory C:\temp\

These are the parameters:

Parameter	Description
processTo	The location to which the JSON file will be processed. Possible values are SQLServer and AzureSQLDatabase.
serverName	The SQL Server instance to which data will be processed. If you specify AzureSQLDatabase for the processTo parameter, then include only the SQL Server name (don’t include .database.windows.net). You’ll be prompted for two logins when targeting Azure SQL Database; the first is your Azure tenant credentials, while the second is your admin login for the Azure SQL Server.
CreateDMAReporting	The staging database to create for processing the JSON file. If the database you specify already exists and you set this parameter to one, then objects don’t get created. This parameter is useful for recreating a single object that has been dropped.
CreateDataWarehouse	Creates the data warehouse that will be used by the Power BI report.
databaseName	The name of the DMAReporting database.
warehouseName	The name of the data warehouse database.
jsonDirectory	The directory containing the JSON assessment file. If there are multiple JSON files in the directory, then they’re processed one by one.

The import should take few seconds to execute but I’ve also seen it run for minutes.

The speed is determined by the size of the JSON files

If you incur in the error Error writing results for file C:\***** to database I have found a workaround: copy the JSON file to another machine and fire the import there.

This is definitely an environmental problem and I have seen it happen sometimes. Just use another computer.

Loading the data warehouse

Now that the two databases are created we need to run the LoadWarehouse script. This will take the data from the ReportData table in the DMAReporting database and load it into the DMAWarehouse.

As soon as you open the script you will face this suggestion:

So click CTRL+SHIRT+M and set the name of the two database you have created.

In my case I’m going to use the default names DMAReporting  and DMAWarehouse.

If you have chosen a different name it’s now time to change the variable

This trick will allow you to set the name of the two database all over the LoadWarehouse script.

As you can see the script is divided in 4 steps and they suggest to run each step individually.

1. Step: all good, it will run without problem
   
   -- Step 1 - cleans object types. (this should be a temp step until tool is fixed) UPDATE <Report Data Database, sysname, DMAReporting>..ReportData SET ImpactedObjectType = 'Database Options' WHERE ImpactedObjectType = 'DatabaseOptions'

   1 2 3 4

   -- Step 1 - cleans object types. (this should be a temp step until tool is fixed) UPDATE <Report Data Database, sysname, DMAReporting>..ReportData SET ImpactedObjectType = 'Database Options' WHERE ImpactedObjectType = 'DatabaseOptions'

2. Step: same here, no problems
   
   -- These scripts use 3 part names. Ensure you update your database names -- Step 2 - Capture any missing rules INSERT INTO <Warehouse Database, sysname, DMAWarehouse>..dimRules (RuleID, Title, Impact, Recommendation, MoreInfo, ChangeCategory) SELECT DISTINCT rd.Ruleid , rd.Title , rd.Impact , rd.Recommendation , rd.MoreInfo , rd.ChangeCategory FROM <Report Data Database, sysname, DMAReporting>..ReportData rd LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimRules dr ON rd.RuleId = dr.RuleID AND rd.Title = dr.Title WHERE dr.RuleID IS NULL -- Capture any missing object types INSERT INTO <Warehouse Database, sysname, DMAWarehouse>..dimObjectType (ObjectType) SELECT DISTINCT rd.ImpactedObjectType FROM <Report Data Database, sysname, DMAReporting>..ReportData rd LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimObjectType ot ON rd.ImpactedObjectType = ot.ObjectType WHERE ot.ObjectType IS NULL

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

   -- These scripts use 3 part names.  Ensure you update your database names -- Step 2 - Capture any missing rules INSERT INTO <Warehouse Database, sysname, DMAWarehouse>..dimRules (RuleID, Title, Impact, Recommendation, MoreInfo, ChangeCategory) SELECT DISTINCT rd.Ruleid , rd.Title , rd.Impact , rd.Recommendation , rd.MoreInfo , rd.ChangeCategory FROM <Report Data Database, sysname, DMAReporting>..ReportData rd LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimRules dr ON rd.RuleId = dr.RuleID AND rd.Title = dr.Title WHERE dr.RuleID IS NULL   -- Capture any missing object types INSERT INTO <Warehouse Database, sysname, DMAWarehouse>..dimObjectType (ObjectType) SELECT DISTINCT rd.ImpactedObjectType FROM <Report Data Database, sysname, DMAReporting>..ReportData rd LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimObjectType ot ON rd.ImpactedObjectType = ot.ObjectType WHERE ot.ObjectType IS NULL

3. Step: here comes the problems. If you run the third step from the official LoadWarehouse you will incur in a bug.
   You first have to go to the table dbo.FactAssessment > Design, and allow NULLs for the SourceCompatKey.
   Sometimes the TargetCompatKey might be affected too.
   
   You can now execute the third step after you uncomment the INSERT INTO
   
   -- Step 3 - Run select statement first to ensure no NULL keys (except dbowner which is expected to be null at this point). -- IMPORTANT - Once happy uncomment out the INSERT statement and run again -- INSERT INTO <Warehouse Database, sysname, DMAWarehouse>..FactAssessment(DateKey, StatusKey, SourceCompatKey, TargetCompatKey, Categorykey, SeverityKey, ChangeCategorykey, RulesKey, AssessmentTargetKey, ObjectTypeKey, DBOwnerKey, InstanceName, DatabaseName, SizeMB, ImpactedObjectName, ImpactDetail, AssessmentName, AssessmentNumber, ServerVersion, ServerEdition) SELECT dd.DateKey AS "DateKey" ,ds.StatusKey AS "StatusKey" ,sc.SourceCompatKey AS "SourceCompatKey" ,tc.TargetCompatKey AS "TargetCompatKey" ,dc.CategoryKey AS "CategoryKey" ,dsev.SeverityKey AS "SeverityKey" ,dcc.ChangeCategoryKey AS "ChangeCategoryKey" ,dr.RulesKey AS "RulesKey" ,AssessmentTargetKey AS "AssessmentTargetKey" ,ot.ObjectTypeKey AS "ObjectTypeKey" ,dbo.DBOwnerKey AS "DBOwnerKey" ,dma_rd.InstanceName AS "InstanceName" ,[Name] AS "DatabaseName" ,SizeMB AS "SizeMB" ,COALESCE(ImpactedObjectName, 'NA') AS "ImpactedObjectName" ,COALESCE(ImpactDetail, 'NA') AS "ImpactDetail" ,AssessmentName ,AssessmentNumber ,ServerVersion ,ServerEdition FROM <Report Data Database, sysname, DMAReporting>..reportdata dma_rd LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimDate dd ON CONVERT(CHAR(8),dma_rd.ImportDate,112) = dd.[Date] LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimStatus ds ON dma_rd.[Status] = ds.[Status] LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimSourceCompatibility sc ON dma_rd.SourceCompatibilityLevel = sc.SourceCompatibilityLevel LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimTargetCompatibility tc ON dma_rd.TargetCompatibilityLevel = tc.TargetCompatibilityLevel LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimCategory dc ON dma_rd.Category = dc.Category LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimSeverity dsev ON dma_rd.Severity = dsev.Severity LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimRules dr ON dma_rd.RuleId = dr.RuleID AND dma_rd.title = dr.Title -- there is a ruleid being used for 2 different titles LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimAssessmentTarget ast ON dma_rd.AssessmentTarget = ast.AssessmentTarget LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimChangeCategory dcc ON dma_rd.ChangeCategory = dcc.ChangeCategory LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimObjectType ot ON CASE WHEN dma_rd.ImpactedObjectType IS NULL OR dma_rd.ImpactedObjectType = '' THEN 'NA' ELSE ImpactedObjectType END = ot.ObjectType LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimDBOwner dbo ON dma_rd.InstanceName = dbo.InstanceName AND dma_rd.Name = dbo.DatabaseName where IsLoaded = 0

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

   -- Step 3 - Run select statement first to ensure no NULL keys (except dbowner which is expected to be null at this point).   -- IMPORTANT - Once happy uncomment out the INSERT statement and run again -- INSERT INTO <Warehouse Database, sysname, DMAWarehouse>..FactAssessment(DateKey, StatusKey, SourceCompatKey, TargetCompatKey, Categorykey, SeverityKey, ChangeCategorykey, RulesKey, AssessmentTargetKey, ObjectTypeKey, DBOwnerKey, InstanceName, DatabaseName, SizeMB, ImpactedObjectName, ImpactDetail, AssessmentName, AssessmentNumber, ServerVersion, ServerEdition) SELECT  dd.DateKey AS "DateKey" ,ds.StatusKey AS "StatusKey" ,sc.SourceCompatKey AS "SourceCompatKey" ,tc.TargetCompatKey AS "TargetCompatKey" ,dc.CategoryKey AS "CategoryKey" ,dsev.SeverityKey AS "SeverityKey" ,dcc.ChangeCategoryKey AS "ChangeCategoryKey" ,dr.RulesKey AS "RulesKey" ,AssessmentTargetKey AS "AssessmentTargetKey" ,ot.ObjectTypeKey AS "ObjectTypeKey" ,dbo.DBOwnerKey AS "DBOwnerKey" ,dma_rd.InstanceName AS "InstanceName" ,[Name] AS "DatabaseName" ,SizeMB AS "SizeMB" ,COALESCE(ImpactedObjectName, 'NA') AS "ImpactedObjectName" ,COALESCE(ImpactDetail, 'NA') AS "ImpactDetail" ,AssessmentName ,AssessmentNumber ,ServerVersion ,ServerEdition FROM <Report Data Database, sysname, DMAReporting>..reportdata dma_rd LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimDate dd ON CONVERT(CHAR(8),dma_rd.ImportDate,112) = dd.[Date] LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimStatus ds ON dma_rd.[Status] = ds.[Status] LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimSourceCompatibility sc ON dma_rd.SourceCompatibilityLevel = sc.SourceCompatibilityLevel LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimTargetCompatibility tc ON dma_rd.TargetCompatibilityLevel = tc.TargetCompatibilityLevel LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimCategory dc ON dma_rd.Category = dc.Category LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimSeverity dsev ON dma_rd.Severity = dsev.Severity LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimRules dr ON dma_rd.RuleId = dr.RuleID AND dma_rd.title = dr.Title -- there is a ruleid being used for 2 different titles LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimAssessmentTarget ast ON dma_rd.AssessmentTarget = ast.AssessmentTarget LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimChangeCategory dcc ON dma_rd.ChangeCategory = dcc.ChangeCategory LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimObjectType ot ON CASE WHEN dma_rd.ImpactedObjectType IS NULL OR dma_rd.ImpactedObjectType = '' THEN 'NA' ELSE ImpactedObjectType END = ot.ObjectType LEFT JOIN <Warehouse Database, sysname, DMAWarehouse>..dimDBOwner dbo ON dma_rd.InstanceName = dbo.InstanceName AND dma_rd.Name = dbo.DatabaseName where IsLoaded = 0

4. Step: This step is optional and I never use it
   
   -- Step 4 - update database owners -- This step is optional. This will enable the powerbi reports to filter on specific teams / owners -- Populate database owners -- Repeat for every instance / database INSERT INTO <Warehouse Database, sysname, DMAWarehouse>..dimDBOwner (InstanceName, DatabaseName, DBOwner) VALUES ('ExampleInstance', 'ExampleDatabase', 'ExampleOwner') -- Once DBOwner dimension is populated update factassessment table UPDATE fa SET fa.DBOwnerKey = db.DBOwnerKey FROM <Warehouse Database, sysname, DMAWarehouse>..FactAssessment fa JOIN <Warehouse Database, sysname, DMAWarehouse>..dimDBOwner db ON fa.InstanceName = db.InstanceName AND fa.DatabaseName = db.DatabaseName;

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   -- Step 4 - update database owners -- This step is optional.  This will enable the powerbi reports to filter on specific teams / owners   -- Populate database owners -- Repeat for every instance / database INSERT INTO <Warehouse Database, sysname, DMAWarehouse>..dimDBOwner (InstanceName, DatabaseName, DBOwner) VALUES ('ExampleInstance', 'ExampleDatabase', 'ExampleOwner')   -- Once DBOwner dimension is populated update factassessment table UPDATE fa SET fa.DBOwnerKey = db.DBOwnerKey FROM <Warehouse Database, sysname, DMAWarehouse>..FactAssessment fa JOIN <Warehouse Database, sysname, DMAWarehouse>..dimDBOwner db ON fa.InstanceName = db.InstanceName AND fa.DatabaseName = db.DatabaseName;

DMA readiness with Power BI

And here we are at the last step.

Double click on the DMA Reports Power BI template that will prompt a window in which you can enter your ServerName and the Database Name:

The Power BI template will automatically load the data from the DMAWarehouse database.

Don’t forget you can select multiple databases by using CTRL + Left click.

If you right click on one database and go to Drillthrough > Remediation Plan , you will see all actions you need to take to finalize the migration.

 

Conclusion

I’ve been using Power BI since 2016 and it has been getting better every year.

I like the way we can easily create dashboards and share them. Much easier than SSRS.

Is good to see that Microsoft was able to create a simple template that allows you to check at a glance the job you have to do before migration. But don’t get fooled: you still have to check for false positive like you have to do for every software that does bulk assessments.

Unfortunately this useful template is no longer maintained and one day it will not work any more.

In exchange Microsoft is proposing the Azure Migrate option which is still in preview.

…and you know what “preview” means for Microsoft, right?