Using Power BI with Skype for Business - Part I

In this series I am going to show you how to use Microsoft's business intelligence reporting tool Power BI to quickly create reports from your Skype for Business or Lync data.  If you are not familiar with SQL permissions, queries, or your Skype for Business SQL configuration, I would recommend you stop reading, as these things are vital to system function and should not be tinkered with in a production environment by amateurs.

Power BI

Microsoft Power BI comes in 2 flavors, basic and pro.  Pro is included in the E5 O365 license, or can be purchased separately for $10 a month.  You can get a full list of the differences here.

We are going to use pro in our example because it works with live data.  That means it can use an personal or enterprise gateway to connect and query you live running Skype for Business data, rather than you have to import it and republish every time you want to view a report.  To begin download the Power BI desktop application.

Skype for Business SQL

The data we are going to query today is going to reside in the RTCLOCAL Sql instance on our front end server.  Make sure the user you are going to attach was has the proper rights, this instance is Windows Auth only by default.  Once you have confirmed data access with SSMS or some other tool return to Power BI Desktop.

Setting up our data

Under the get data section in Power BI Desktop we have to choose SQL Server Database.  Inter your sql instance name in the server field (yourserver\rtclocal) and let's put rtcdyn in the database optional field.  Make sure you have directquery selected and put this in your query.

Select top 1000 (cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost as UserName, FE.Fqdn
From rtcdyn.dbo.RegistrarEndpoint RE
Inner Join rtcdyn.dbo.Endpoint EP on RE.EndpointId = EP.EndpointId
Inner Join rtc.dbo.Resource R on R.ResourceId = RE.OwnerId
Inner Join rtcdyn.dbo.FrontEnd FE on EP.RegistrarId = FE.FrontEndId
Order By ClientVersion, UserName

Normally I would not add the top 1000 part, but BI gets picky if you don't have top something, so make it larger than the number of connected clients you have.

Now that we have our query built we can return to the main page in Power BI desktop.  Let's pick our data, and select a pie chart.

Once we have made the following settings, BI will automatically generate us a pie charge breakdown of what type of clients we have currently attached.  As you can see I have a rather large UCMA program named Jarvis that is creating most of my authenticated Skype for Business endpoints.

Next week we will continue with some more advanced reporting, I hope this gives you enough information to get started making reports on your own.


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