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:
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.
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: “ 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 “”.
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.
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.
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.
If you want to append more DMA result into the same DMAWarehouse database, please consider the following:
TRUNCATE TABLE [DMAReporting].[dbo].[AzureFeatureParity]
TRUNCATE TABLE [DMAReporting].[dbo].[ReportData]
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
The rest of the steps are the same.