Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

How to: Build a SQL Server Dashboard with Power BI

Posted by denglishbi on February 20, 2018

NOTE (2/25/18): one thing to mention is that this example will work with SQL 2016+. in my example the script I use to get the database table information uses the DROP IF EXISTS and that was introduced in SQL 2016. If you check the comments there is a link to the SQL file that contains the four scripts that are being used.

A couple of years ago when Power BI Desktop was released I did a blog post on How to: Build a SQL Server Dashboard with Power BI Desktop.  Today I will be presenting to the local MN SQL Server User Group on this very topic.

The example I will be doing is an enhanced version of the original blog post I did back in 2015, wow! (seems like just yesterday)

This by no means is a complete solution to cover everything a DBA or accidental DBA would need, but it is a starting point to show the possibilities and get you thinking.

The updated version is based on the February 2018 release of Power BI Desktop. I have included table information, current connections (based on last refresh), drillthrough, and bookmarks.

The Power BI Dashboard that I created in the Power BI Service is for the most part the same:


For the reports I made a modification to the Database Detail to included bookmarks and created a toggle button to switch between a table and chart. Nothing too fancy and I simply created the toggle button images with the Snagit Editor, so pretty basic.



Then I added two additional pages for Table Info (has Drillthrough filter defined for Database Name) and Connections.



For the Drillthrough if you are on the Dashboard or Detail pages you can right-click on a database in a visual and see the Drillthrough option that will allow you to jump to the Table Info page and automatically filter the results based on your selection.



The presentation materials as well as a Power BI Desktop Template file are available for you to download from my OneDrive here – Power BI SQL Dashboard files.

When you open up the Power BI template file you will be asked for the name of the SQL Server Instance you want to connect to, click load, and you might be prompted about encryption with the connection as well.


You will just need to make sure that you have the necessary permissions to run the queries I have included;)

Be sure to check out the presentation materials, I have included a bunch of good resources to check out as well at the end.

Enjoy and hope you like it and I hope it gets you thinking about the possibilities with using Power BI.

13 Responses to “How to: Build a SQL Server Dashboard with Power BI”

  1. fred said

    simply amazing

  2. guavaq said

    Hi Dan, great blog post. I downloaded and tried to update the Power BI Template file, but I am getting an error when getting the data to load for the TableInformation

    The error is: “DataSource.Error: Microsoft SQL: Incorrect syntax near the keyword ‘IF’.”


    • denglishbi said

      My guess is that it could possibly be a regional settings. If you haven’t been able to see the query yet, here is the one being used. You should switch your regional settings to English (United States) and try again.


      DROP TABLE IF EXISTS #spaceused;
      CREATE TABLE #spaceused (
      DatabaseName sysname DEFAULT(”),
      TableName sysname,
      Rows INT ,
      ReservedSpace VARCHAR(50),
      DataSpaceUsed VARCHAR(50) ,
      IndexSpaceUsed VARCHAR(50),
      UnusedSpace VARCHAR(50)

      DECLARE @Cmd VARCHAR(8000);
      SET @Cmd = ‘USE [?];

      IF ”?” NOT IN (”tempdb”, ”master”, ”model”, ”msdb”)
      –PRINT ”?”
      DECLARE @InnerCmd VARCHAR(8000)
      SET @InnerCmd = ”
      EXEC sp_spaceused ””” + CHAR(63) + ””””

      INSERT INTO #spaceused(TableName, Rows,ReservedSpace,DataSpaceUsed,IndexSpaceUsed,UnusedSpace)
      EXEC sp_MSforeachtable @InnerCmd

      UPDATE #spaceused SET DatabaseName = ”?” WHERE DatabaseName = ””

      EXEC sp_MSforeachdb @Cmd;

      SELECT DatabaseName,
      FROM #spaceused;

      • Tom said

        Dan, Thanks for sharing this. However, I get the same error, my regional settings are English. I opened up the query and tried to run in a sql editor and still get the same error. I checked the if statement and it looks good. I wonder if the issue is the quotes. It appears to my laymen eyes that the set @cmd = ‘Use [?]; is missing an ending quote.

  3. Tom said

    Forget my reply I see now that the missing ‘ is not actually missing, however still can’t get it to run. Which is bumming me out… 🙂

  4. Anthony Robinson said


    This may be a bit off topic, but I’ve been trying to reset my PassMN password and haven’t gotten an email reset link. I’ve also tried sending an email via the PassMN website, but that keeps erroring out.

    Is there something going on with the site?

    Other than that…great article!!

    • denglishbi said

      My apologies for the web site and registration, you are not the only one having issues. Our plans is to switch over completely to Meetup this year which should resolve these issues. I believe the emails take a while to send on the web site and if you email wasn’t confirmed to start with then you will possibly have issues getting the password reset. Go over to Meetup and join our group instead:) https://www.meetup.com/MN-SQL-Server-User-Group-PASSMN/ Thanks!

  5. Paul Kanterman said

    I, too, had problems running the table information query, but was able to remedy. I changed the first line “DROP TABLE….” to “IF OBJECT_ID(‘tempdb..#spaceused’) IS NOT NULL DROP TABLE #spaceused;” Worked fine afterwords

    Thank you!

    • denglishbi said

      Nicely done, sometimes putting together samples like this to share I tend to forget that some people might still be using older versions of the software;) Glad you were able to resolve the issue!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: