Jump into #PowerBI

This months T-SQL Tuesday is hosted by Jorge Segarra (b|t|l) and the invitation is found following this link.

T-SQL Tuesday was started by Adam Machanic (b|t), and this is the SQL Server community’s monthly blog party, where everyone is invited to write about a single common topic. This time, the topic is PowerBI.

I am hoping this blog post makes it into the summary, by Jorge. Admitted, I am a bit late. I only saw the invitation tweet today, and I was kind of lucky it was on topic, with what I was currently working with.

SSAS Performance Dashboard

My story with this half-baked product (the Dashboard you are about to see), is that I needed some way of tracking performance on a couple of Analysis Services (SSAS) query servers. There are a lot of good posts and talks about how to collect and store performance counters and SSAS logs out there, and I suggest you look into this, this or that, if you need inspiration.

The current data set is about 200K rows, as I am sampling each server every 5th minute.

The reason why I say this is half-baked, is that the querying part of the equation is missing. Currently I am only logging/storing Windows performance counters, and due to release procedures, I have not been able to implement the SSAS eXtended Events that gives us the link, to the queries run at any given time. Windows performance counters by themselves are not that interesting, because we can’t correlate them with specific queries. So we can’t really tell, what makes the servers go bunkers.

By Date

The By Date report is intended to let the end-user browse the data, based on the calendar approach to data. This is to identify peak hours, days etc.

By Server

The By Server report is to let the end-user easily distinguish which work load is related to what server. The rest of the break down is again based on calendar.

Brush

In this example the Brush Chart isn’t really fed the proper kind of data, but I put it in there, to let you see the possibilities with it.  Mark the lower chart to zoom in on the upper chart.

StreamGraph

This is also a very cool visualization, not sure it has any relevance to my data, but it looks awesome!

Final Thoughts

What I really miss for this kind of event based reporting, is a chart type that allows me to have a line for say CPU Utilization and on top of that mark events and their duration, by ie. a broken line chart or similar. Not sure how to correctly describe this, but kind of Gant-style on top of a line chart.

I have been working with PowerBI since it emerged, and I have been challenged to keep up with all the features and changes the PowerBI team has released during the last year. I am really looking forward to see what will be served the next year, even more so, because I will be spending more time with PowerBI now than before.