Using Power BI with Skype for Business - Part III

Earlier this year I showed you how to create an automated UCMA time clock for Skype for Business.  This week I'll show you how to create some cool reports in power bi based on the data collected.  Let's begin with a doughnut graph of a user's status for the last 7 days.  First we will create a data connection to the SQL server in power bi, and use a sql command like this.

select top 100 state, sum(seconds)/3600 as hours from timeclock_table where sdate > getdate()-7 and uri='sip:desired.user@domain.com' group by state

Go ahead and save that query, then return to the power bi desktop main screen.  Add a doughnut visualization to the screen make your settings match the query shown here.

Use the state data for the Legend, and the hours data for the Values.  Once you do this you should see something like this.  Remember you can use a slicer if you want like we did last week, to allow you select any user in the list and dynamically show this graph.  To do so you would add the URI and create a group by in the sql command.

Let's say you wanted to make a bar graph to show the hours online for a group a employees.  The power bi setup is the same, create a SQL connection and use something like this for your query.

select uri,  sum(seconds)/3600 as hours from timeclock_table where edate is not null and state <> 'Away' and state <> 'Offline' and year(sdate)=year(getdate()) and month(sdate)=month(getdate()) and day(sdate)=day(getdate()) group by uri

This will ignore user time spent offline or away, giving you a pretty accurate representation of how long the user was active during the day.

Next week I'll show you how to generate some nice call history graphs from your LcsCDR database.

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