Using Power BI to Measure Skype for Business QoEMetrics Part I

In previous posts I have showed a bit of how to user Power BI to report on user configuration, and CDR with Skype for Business.  This series will focus more on mapping out some of call quality metrics provided by the Skype for Business environment.  These reports can be handy is getting a visual idea of the health of the audio calls that are taking place.  Today we will focus on 2 reports;

1.  Average packet loss by day.

2.  Overall MOS ( Mean Opinion Score ) by day.

To begin open your Power BI Desktop application and add a new data source.  Select SQL server, and provide the IP or host name of the location of your reporting sql server.  Select QoEMetrics as the database, and select direct query.  The great thing about direct query and the enterprise gateway is your reports will auto refresh themselves every 10-15 minutes or so, meaning you get fairly live data in your browser, metro, or mobile Power BI app.

Average Packet Loss By Day

For this report we are going to manipulate one of the detail views and glean only the information we would like to see.  Our SQL query should look something like this.  *Note adding a top X to a query will make Power BI play nice, otherwise you will most like receive an error.

select top 10000 convert(date,SessionTime) as [Date], avg(PacketLossRate) as [Packet Loss Rate] from AudioStreamDetailView group by convert(date,SessionTime) order by convert(date,SessionTime)

Once we have that set, return to the Power BI workspace and create an area graph.  Select Date as the Axis and Packet Loss Rate as the value.  You can add a trend line if you like, and your results should look something like this.

Overall MOS By Date

This report will be very similar to the packet loss report only changing the 2nd query item.

select top 10000 convert(date,SessionTime) as [Date], avg(OverallAvgNetworkMOS) as [Overall MOS] from AudioStreamDetailView group by convert(date,SessionTime) order by convert(date,SessionTime)

After you add this query return to the Power BI desktop app and create a similar area visual again using Date as the axis.  Your result should look something like this, note I added a trend and average line.

Next week will add some more reports and start building out our dashboard.

Doug Routledge, C# Lync, Skype for Business, SQL, Exchange, UC, Full Stack Developer  BridgeOC Twitter - @droutledge @ndbridge