SQL Server and Power BI Collaboration

Earlier this week at PASS Summit 2017, Erin Stellato, Principal Consultant with SQL Skills, delivered a pre-conference session regarding SQL Server Query Store. I wasn’t able to attend PASS this year but as I understand it she may have referenced our work this summer on the use of Power BI to analyze and visualize Query Store data.

The essential concept of this work, which also included Extended Events, SQL Server Agent Jobs history, Performance Monitor Counters, and other administrative data sources, was that a well-designed Power BI solution could simplify performance monitoring and troubleshooting activities and extract greater insights from this data.

Power BI Report Examples:

• The following Power BI report (published to web) features Query Store data: link
• The following Power BI report features Extended Events data: link

Here are a few images as well (if you don’t use the links):

This slideshow requires JavaScript.

How it works

At a high level, a Power BI dataset was configured to retrieve from the SQL Server database and other sources via M queries. The data access pattern was similar to recent blog posts including source parameters (server, database). A few M transformations were applied to the source data to support the filtering by a Time dimension table and to limit the amount of data retrieved into Power BI.

With some fundamental modeling (relationships, basic DAX measures) the Power BI visualization layer was fairly easy to build. Given that the SQL statement was included in a column of the Extended Events source, simply clicking one of the bubbles would return the specific SQL statement associated with the other visuals. (Also note the second page of the Query Store report – it’s a very simple Top 10 table of queries but the value could be high to the user knowledgeable of the database or application).

Implementation details and supporting context are included in Chapter 10 of the Power BI Cookbook.

The Bigger Picture

The main idea from the different examples was the synergies possible via greater levels of teamwork between BI, DBA, and System Admin professionals. I think these partnerships will continue to increase and deepen in importance with improvements to DirectQuery for SSAS and Power BI models as well as greater use of SQL Server features such as Query Store.