SSMS (SQL Server Management Studio) has a number of reports built into the tool. There are Reporting Services reports, and if you want to get Disk Usage by Table or Index Statistics in a report for a database, you can right click the database and view them. These are handy for limited yes, and if you want, you can even add custom reports. An example report is shown below:
These are fairly simple reports, however, showing data that you've set up, but these are essentially static reports. You can do minor things, like sort the table, but what we'd really want is a more interactive report. If you have SSMS v18.6+, however, there's another option.
Power BI is a fantastic visualization tool, and I've seen some great visualizations set up by DBAs that are looking to better understand what is going on in their databases. Ideally, what we'd like is to see some sort of report that has some interactivity, allowing us to drill into a particular item and better analysis a database. For example, if I were looking at table space, I might like to put a few tables together and analyze them like this:
Giving some interactivity in the visuals allows us to decide what and how we want to work with data. There is a lot of power in this.
Adding Power BI Reports to SSMS
There is a preview feature in SSMS that you can use to do this. There are a few restrictions, but if you meet these requirements, you can do this. You need:
- SSMS v18.6+
- PowerBI Desktop installed, v2.91
- Local Administrative rights
Once you have these items, you need to do a few things. First, create your report in Power BI by setting up an connection to your SQL Server and writing the queries you need. Then build the visuals that you want and configure them to meet your needs. There are lots of Power BI resources around, so use those to build your report.
Next, you need to alter the way SSMS runs. To do this, you need to create a shortcut to the SSMS executable. This is usually installed C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\. Use this as your shortcut target and add two parameters:
- enablepbi - This needs a double dash and that's it.
- pbipath - Use the doubledash here and add an ="path", where path is the Power BI Desktop path.
Once you do this, it will be slower to launch SSMS, but once it does get going, you can right click a database, go to reports and choose custom reports. You will have to change the filter to all files, but then locate your Power BI desktop report file. Choose it.
Then when you go back to the report list, actually, you'll get an error with the Power BI file. This is April 1, and this article is a joke. You can't add Power BI reports to SSMS. At least not in April 2021. Hope you enjoyed this and were dreaming about this capability. If you really want this, add a feedback item and get people to vote for you. Maybe they'll actually implement this one day.