Dan English's BI Blog

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

Archive for February 20th, 2018

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:

image

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.

image

image

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

image

image

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.

image

image

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.

image

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.

Posted in Power BI | Tagged: , , , | 13 Comments »