Dan English's BI Blog

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

Archive for February, 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:


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.

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

PASS Business Analytics Virtual Group Feb 21 “Using Self-service Analytics to Build Strong Data Teams”

Posted by denglishbi on February 15, 2018

The next PASS Business Analytics Virtual Group meeting will be next week on Wednesday, February 21.  We welcome Julie Hyman who will be talking to us about building strong data teams using self-service analytics.PASSBA_Logo

–> REGISTER NOW <– (Login with PASS account first to register for webinar)

February 21 – Using Self-service Analytics to Build Strong Data Teams

Session Abstract

We understand that organizations like yours are looking for more efficient ways to tap into the data generated by your marketing and sales efforts, customer engagement, back-end operations, and more. If you want your business to be data-driven, you’ve got to strategically synthesize and analyze all of this data to turn it into actionable business intelligence. During this webcast, Quest product manager, Julie Hyman will explain the differences between full-service and self-service analytics, making the case for self-service as a way to eliminate analytic silos and data bottlenecks.

And, you’ll discover how the Toad solution helps create strong data teams:

• Non-technical business users can connect to nearly any data source.

• Users can standardize best analytics practices using a collaborative platform.

• Data teams can publish workflows and share information.

• Analysts can present fully prepped data to the rest of the business.

Speaker Bio –

Julie Hyman is a Sr. Product Manager for Quest Software. With 20+ years of experience in Development, Project Management and Product Management, Julie currently manages Toad Solutions, including Toad Data Point, Toad Intelligence Central and the new Toad Edge, among others.

–> REGISTER NOW <– (Login with PASS account first to register for webinar)

Also, we will be doing a random drawing after the webinar and giving away seven $50 Amazon gift cards to anyone that attends and fills out the survey!

Posted in Training | Tagged: , , | Leave a Comment »

PASSMN February 2018 Meeting

Posted by denglishbi on February 12, 2018

The next Minnesota SQL Server User Group meeting is on Tuesday, February 20. This month I will be doing a presentation on how to use Power BI Desktop to build reports off SQL Server database information and creating a dashboard.

Be sure to register so that your name badge will be available for you at the Microsoft Technology Center when you arrive and so that we will have an accurate headcount for ordering food.PASSMNLogo

The sponsor for this month’s meeting is DallasDBAs.


Location: 3601 West 76th Street, Suite 600 Edina, MN 55437


  • 3:30-4:00 : Registration, Networking, and Food
  • 4:00-4:10 : Kickoff / Announcements
  • 4:10-5:25 : How to: Build a SQL Server Dashboard with Power BI (Dan English)
  • 5:25-5:35 : Closing

Please click here for meeting details and to RSVP for the event


How to: Build a SQL Server Dashboard with Power BI

Abstract: This session will provide an overview of Power BI tools including the Desktop, Service, and Mobile application. We will use Power BI to pull some information about SQL Server databases such as the count, sizes, last backup, recovery model, and file information. We will explore some of the recent capabilities that were added to Power BI Desktop like drill through and bookmarks. The example will be provided after the meeting so that you can review the code, try it out, and enhance the reports on your own.

About Dan: Dan is the Sr. Data Warehouse Architect at Constellation, Inc., a Microsoft Data Platform MVP, speaker, author, technical editor, and community leader. He has been developing with Microsoft technologies for over 20 years, and over 13 years with Data Warehousing and Business Intelligence. He has been working with SQL Server since version 6.5 and now using 2017! Dan has videos on YouTube and Vimeo, is blogger, and tweets. Dan is the group leader for the PASS Business Analytics Virtual Group and the Director of Technology for the MN SQL Server User Group.

Posted in Training | Tagged: , , , | Leave a Comment »