Create SQL Server Assessment Dashboard in Under an Hour

We must periodically evaluate the state of our databases. Luckily for SQL Server, Microsoft provides us with a customizable assessment through their SQL Assessment API Repo and API Documentation. You can change the rules per database and output the results to a database to track history.

However, that will take more than an hour. Let’s create a dashboard with the default rules in under an hour. We will use Azure Data Studio (ADS) and Power BI Desktop (PBI). If you are not familiar with them, both are free. Azure Data Studio is automatically installed with SSMS 18.7 and higher. You can also install them individually.

Step 1 — Create the Assessment

From ADS, go to the Extensions and search for SQL Assessment and install the extension.

Connect to the server you want to perform the assessment on, right-click the server and select Manage.

Select the SQL Assessment and then press Invoke assessment.

You can also view the 300+ rules that will be run against the server and databases. The API will allow you to customize them. But, for now, we want a quick look at the system.

This displays a list of issues with severity and tags. We will export it for our dashboard.

From the report, press Create HTML Report. I recommend giving it a generic name to allow you to import it quickly after you rerun it. Changing the source file can cause issues with imports.

Once saved, you can open the report in a browser. You will get a section for the server and each database.

Step 2 — Import Assessment

In Power BI Desktop, Get Data from the web.

Instead of the URL, point to the file.

The next step is to match the HTML Tables to the server and database(s). Open the HTML report so we can compare it against the preview and map the tables to the database.

We see the report has 3 tables for the server. Since the server is at the top of the report, these are tables 1, 2, and 3 on the import.

Find and select the tables for the databases you want to put on the dashboard. Then press Transform Data.

Transform steps

• Use the first row as headers.
• Add a severity column for each table. Part of the report table heading.
• Reformat the Check ID column from camel case to title case. It makes it easier to read.
• Split Column by Lowercase to Uppercase
• Merge Columns with a Space separator. Name the column Category.
• Merge tables per server/database so all severity tables are in one output table. (Append Queries)
• For the appended queries, uncheck Enable Load on the table to hide in the reporting layer.
• Remove the Help Link column. The link is not imported.

After import, your tables should look like this.

Step 3 — Create Dashboard

Create a count measure for each database.

Sample code: Server Issue Count = COUNT(‘Server’[Message])

Create a dashboard and detail report. Because this is a sample database, we won’t get an average spread of issues.

Conclusion

Each of the three steps should take 15–20 minutes if you have some familiarity with ADS and Power BI. Now you have a quick dashboard to drill down on and create tasks to address the areas you are concerned about. If you find the assessment useful, you can implement and automate the full version of the assessment.