Fixing Data Migration Assistant Data NULL Value Issues

In my last post, I explained how to consolidate DMA assessment data into a SQL database and then presentation using PowerBI for better analytics and reporting.

This post will focus on some issues that you may encounter during the process of consolidating the JSON files for uploading to PowerBi. Three issues that I have come across that can be frustrating but are easily fixed if you follow a few steps outlined below are:

  1. NULL value issues when populating DMAWarehouse database.
  2. Adding SQL Server 2019 as migration target option into the report.
  3. Multiple consolidation runs which appends data to the same databases.

In my previous post I showed you how I use TSQL script from Microsoft (Prerequisites item 5 in that post) to load data from DMAReporting database into DMAWarehouse database. Let’s have a look at some possible issues during this process.

NULL value issues:

When running step 3: Load data to DMAWarehouse (as outline in the previous post).

You may have NULL values from the SELECT command which has to be fixed before inserting the data into DMAWarehouse database.

Example of NULL values from the SQL select command:

Looking into the complicated joins for the select command, this is caused by this join:

When the rule title generated by DMA does not have the same rule tile from the DMAWarehouse database’s table “dimRules table”, you will have this NULL value issue.

For this example, rule tile: “[71627] Database users mapped with Windows authentication (integrated security) not supported in Azure SQL Database Managed Instance” in DMAReporting.dbo.ReportData table, does not have the same rule tile in the DMAWarehouse.dob.dimRules table.

What DMAWarehouse.dob.dimRules table has is “Database users mapped with Windows authentication (integrated security) not supported in Azure SQL Database Managed Instance” which is missing the “[71627]”.

To fix this issue, update DMAReporting.dbo.ReportData table’s rule tile using the value from DMAWarehouse.dbo.dimRules table.

The following table shows the differences I found between these two tables:

Once both tables have the same ruleID and title, there will be no NULL values and you can load data into DMAWarehouse database.

Adding SQL Server 2019 support into the report

DMA 5.0 was released in Nov 2019, which introduced SQL Server 2019 as an assessment target type. However, the consolidation process from Microsoft still does not support SQL Server 2019 as new target type.

Here are two ways to add SQL Server 2019 in the DMAWarehouse consolidation process:
1. Update the DMAWarehouse dim tables after it is created by Power Shell script.
2. Update Power Shell module createDMAWarehouse.psm1 file.

If you want to update the DMAWarehouse database after it is created, there will be 3 dim tables that will also need to be updated.

Notes: SQL Server 2019 compatibility level is CompatLevel150. That will be the value that will need to be inserted into the tables.

dbo. dimAssessmentTarget:

dbo. dimSourceCompatibility:

dbo. dimTargetCompatibility:

Note: We have not found any new object type from SQL Server 2019 at this stage. Therefore, table dbo.dimObjectType maintains 35 items.

If you want to add SQL Server 2019 when creating a DMAWarehouse database, open the createDMAWarehouse.psm1 file.

The following table shows the line numbers of code where updates are required in this PS Model file.

Multiple consolidation runs which appends data to the same databases.

If you want to append more DMA result into the same DMAWarehouse database, please consider the following:

  1. Truncate DMAReporting databases’ table to avoid duplicated data before each consolidation process.

TRUNCATE TABLE [DMAReporting].[dbo].[AzureFeatureParity]

TRUNCATE TABLE [DMAReporting].[dbo].[ReportData]

  • Update the power shell script to load JSON files as below. Notice the differences:
    • new line: import-Module -Name SqlServer
    • CreateDMAReporting -CreateDatawarehouse

 are both set to 0.

The above screenshot is formatted to help you understand, the below is the screenshot, but in text format.

import-Module -Name SqlServer
dmaProcessor -processTo SQLSERVER
-servername “localhost\sqlexpress”
-CreateDMAReporting 0
-CreateDataWarehouse 0
-databaseName DMAReporting
-warehouseName DMAWarehouse
-jsonDirectory “C:\temp\”

The rest of the steps are the same.

Nelson Jin
Nelson Jin
Senior Cloud Consultant