Subscribe to Blog feed
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.
Dan English's BI Blog 
fred said
simply amazing
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’.”
Thanks
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.
–TableInformation
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”)
BEGIN
–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 = ””
END
‘
EXEC sp_MSforeachdb @Cmd;
SELECT DatabaseName,
TableName,
Rows,
ReservedSpace,
DataSpaceUsed,
IndexSpaceUsed,
UnusedSpace
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.
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… 🙂
denglishbi said
My suggestion would be to see if you can actually run this in SQL Server Management Studio. The code that I posted in the comments most likely will not run as is because of the font that gets used for the quotes. I uploaded the sql file that includes all four queries that you should be able to access from here to see if that helps – https://drive.google.com/open?id=1ZQSNZkQZUDyb2x6AikQ0PTWVaEh0pi1-
Tom said
Got it to run by commenting out line 2. I am still running SQL Server 2008. So I think I’ll initialize the table and then replace line 2 with truncate and see how that works. Thanks again for sharing this!
Tom said
That’s what I am trying to do and see now that its a temp table, so no need to truncate. Just wonder why the if exists is not working
denglishbi said
Ah, that makes sense. the DIE (DROP IF EXISTS) was introduced in SQL 2016. I am using SQL 2017:)
Anthony Robinson said
Dan…
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!
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!