I presented this topic as session at SQL Saturday Chicago and SQL Saturday Madison, and I thought converting it to a blog would be a useful exercise based on feedback. Power BI is a fantastic visualization tool, and with a few tips and tricks you can be on the road to quick decision making. Visualizations are powerful tools that can streamline initial troubleshooting and help in identifying possible issues that are harder to see in raw data. This blog is aimed at IT/Database folks to show you how you can leverage Power BI when analyzing databases and environments for problem areas or performance issues. This exercise presents an alternative to rows and rows of data, and gives some tools/ideas to create a quick report and make it repeatable across environments when analyzing SQL Server metrics.
This exercise will be completely done in the Power BI Desktop, which is a free tool, and can be downloaded here. This blog is designed to provide you with the tools needed to create your own reports, it isn’t designed to be copied and inserted into your environment. The three main areas that set you on the road to building your solution are: creating a connection, building a repeatable process, and visualizing the data. By the time we’re done, you will be well on the road to building a report to meet your needs.
The following report shows the examples I put together to show the different layouts and ways you can visualize your reports.
Creating a connection to SQL Server (on premises)
There are two methods to connect to SQL Server “Import” or “Direct Query”. As the naming implies, one scenario will pull all the data into the Power BI Desktop file, and the other will leave the data in the database. In this process we will import the data directly to the Power BI Desktop. We’re doing this because some of the queries require that we create / drop temporary tables or execute dynamic SQL. If you try to execute those types of scripts using the Direct Query approach you will get errors.
Let’s Begin:
Open your Power BI Desktop and click on “Get Data” in the header ribbon
- Select – SQL Server
- Enter: Server\Instance
- Enter: Database Name
- Select – Import
- (This will only appear with the first query, once you select a method of import or direct query all remaining queries will follow the same connection type. )
- Select – Advanced Options
- Enter: SQL Query
- Select - OK
- (A preview of your data will be displayed) Select – Load
Repeat this process for all the queries. As you progress, right click the default name and change it to the relevant query name. For example, for this set of queries, I have renamed my queries to the following
Here are the list of SQL Server metrics queries that I’ve used, I’m including some of the more complex one’s as links to other blogs in order for you to gain a better understanding of what they are doing if you have any specific questions related to the purpose.
Backups –
Buffer Pool –
Database Info –
DBCC CheckDB - http://www.sanssql.com/2011/03/t-sql-query-to-find-date-when-was-dbcc.html
Plan Cache –
Server Memory –
Wait Stats - http://www.sanssql.com/2011/03/t-sql-query-to-find-date-when-was-dbcc.html
Building a repeatable process
You may have noticed in the list of objects that there were two separate objects that are not included in the query list. These are “SQLServerInstanceName” and “SQLServerDatabaseName”. These objects are blank queries, and can be viewed as our parameters for declaring instance and database names so the report can be run against different instances easily.
To create these objects, follow the next series of steps:
- Select – Get Data
- Select – Blank Query
- (the query editor will pop up) Select – Advanced Editor
- Remove any text, Insert your instance name surrounded by quotation
- (example: “ServerName\InstanceName”)
- Select – Done
- Rename the query by right clicking on the name and replace with “SQLServerInstanceName”
Repeat the steps 1-5 for the database parameter, replacing server and instance with database name in step 4, and rename that new object to “SQLServerDatabaseName” in step 6.
Now that the two objects are created, we can follow these steps to replace the specific instance and database name information in each of the queries.
- From the Data section, Select Edit Queries
- Select – Advanced Editor
- For each “Source” section, replace the specific instance and database name to the blank query names you created.
- Example:
Current: Source = Sql.Database("Server\Instance", "databasename",
Replacement: Source = Sql.Database(SQLServerInstanceName, SQLServerDatabaseName,
Visuals
Now that we have the queries in a repeatable state, it is just a matter of building visuals that will serve to give us quick insights into what’s happening in our databases. Power BI is a great tool for end users. Just drag and drop fields onto the work surface and change the visuals to fit your needs. The pictures can be inserted and used within, and as backgrounds, to enhance a visual or page background. I’ll lay out the steps of how I created the first report (File Size), which will give you an idea of how the remaining reports were also created.
I find the quickest way to grab screenshots, or images is via the Snipping Tool. The background of the first report is a copy of the slide deck color I used for my presentation.
Clicking on the empty workspace in Power BI, you can click on the formatting (paintbrush) and by selecting “Page Background” you will see the option to load an image. I loaded a snippet of the background of my slide deck.
I then wanted a particular font style for my header, so I typed the “File Size” in Power Point and took another snippet of that text in white on the same background. Those are the two images used in this report.
The “Data and Log File Size” visual is a 100% Stacked Bar Chart, and the formatting of colors is all within the formatting options of that visual. You can add visuals by clicking on the visual icons, or by checking the box next to a field or dragging fields on to the work space.
The 3 separate elements outlining the Files Sizes and how many log files are larger than data files are all Card visuals with the Category Label turned off and Title enabled. The 3 cards are surrounded by an outline. The outline is a rectangle shape with no fill, and sent backward behind the cards. Finally, the table is just a table visual with 29% transparency and a light blue chosen in the Background formatting option.
As you can see in this and the other visuals, you can lay shapes and visuals over each other to create different looks. You can change visual types by just clicking them in the upper right, and format them just as easily. The other reports show very different examples to further explore different ways to visualize the data.
Power BI is an analytics tool for everyone, not just business users. There are so many different use cases, and I wanted to make a specific example for IT/DBA users only. I hope you find this useful, please feel free to comment, and let me know if you have any questions.