August 09, 2018
Persistent Chat allows the Skype For Business users to create topic-based discussion rooms that persist over time. This feature allows users within an organization to discuss either casual or business related subjects in chatrooms dedicated for a desired purpose. Logically, a lot of business relevant data for an organization is generated in those chatrooms so it would be great if we could extract that information and visualise it in some modern and cool charts, wouldn’t it?
In this post we are going to show how easy it is to use Power Bi tool and to visualise some interesting statistics about the Persistent Chat usage in one of our dev environments. The goal with this exercise is to be able to display the 5 top users for any desired chatroom in a classic bar chart.
Microsoft Power BI is a business intelligence cloud service that provides business users with tools for aggregating, analysing and visualising data. It did not take too long to get used to its simple interface as it is quite similar to other widely used Microsoft products.
One great thing about this service is that it is possible to get data from multiple sources. For instance:
Another very good aspect about Power Bi is that the user has a very wide range of clean, neat and customisable charts to choose from.
Bar, Column, Stacked, Clustered, Waterfall, Donut, Filled Map, Gauge, HeatMap, Word Cloud, amongst many others, can be easilly selected to represent the data that you want to visualise. Not surprisingly, many more type of charts can be downloaded from the Marketplace.
There are two different versions: professional and free. The professional version has a paid subscription attached to it and it offers more storage, more frequent data refresh cycles and much more streaming data consuption.
Going back to Persistent Chat for Skype For Business, chat room and user data is persisted in a SQL Server database. Let’s look at some important tables for Lync Group Chat 2010, one of the predecessors of Skype For Business Persistent Chat, which has a very similar structure to Skype For Business 2015 Persistent Chat.
Shall we start doing our little proof of concept?
Let’s start by opening the PowerBi Desktop application, which you can easilly download for free from the Microsoft website. First thing to do is to authenticate against it with an Office365 account.
After authenticating the user is guided to the start page which has a form to create a new project.
As expected the user is prompted with a menu that allows it to choose where to get its data from. In this case we want to get the data from the Persistent Chat database in our Skype For Business 2015 dev environment. After selecting SQL Server database option the application ask the user to fill in the server details and the database name. In addition it is required to specify which Data Connectivity mode we prefer:
Import - extracts the data from your database and pulls it into the Power BI Desktop :
Direct Query - leaves the data in the database and sends queries to pull the information:
In this example we suggest to select the Import option as we want to be able to do transformation and data manipulation.
Ok so now it’s time to specify the credentials to access the database and then press Connect.
Now that we have tested that we can connect to the Persistent Chat database we are prompted with a navigator that allows the user to select which tables they want to import. We will select the three tables that were mentioned in the previous section: tblNode, tblPrincipal and tblChat. Since tblChat has thousands and thousands of rows it will take a while to load all the rows.
Great! Once all the tables are loaded we will be taken to the project’s main page and on the left hand side there will be 3 icons:
Before doing anything else, maybe now it’s a good time to save your project.
If you click on the Data icon you’ll be able to see imported tables and their respective data.
But first we need to define the relationships between the tables! So click on the Relationships icon.
We can see the three tables that we’ve loaded from our database connection. The goal is to clearly specify that:
tblChat’s userId tells us which user sent the respective message so the userId in tblChat will correspond to the prinId in tblPrincipal. In order to establish that relationship just drag the prinId field in tblPrincipal to userId field in tblChat. Now for a given userId in tblChat we will be able to know more about that user - more specifically - the prinName which corresponds to the user name that will be displayed in the chart.
tblChat’s channelId is referring to what group was the message sent in so in order to get the information about that group we need to drag the tblNode’s nodeId to tblChat’s channelId field.
It should look like this:
Now that the data is there we can start creating the report which will include a chart and a dropdown to select the chatroom that we wish to see the top 5 users that have sent more messages. First thing to do is to rename the the page for that report from Page 1 to Top 5 Users in Chatroom.
By pressing the Reports icon we are taken to the area where we can drop the slicer that will contain all the chatrooms in our Persistent Chat environment. The slicer can be formatted to look like a dropdown.
The Visualizations navigator has a list of predefined objects so we need to press the slicer icon.
There are several properties that need to be filled so that the slicer displays all the chatrooms in a dropdown.
First the filters:
Page level filters: This affects which rows will be shown in the dropdown.
And then the Formatting:
Here you can define the visual aspects of the slicer.
To behave like a dropdown instead of a list hover the slicer’s header and there is the possibility to choose between list or dropdown.
Now you should be able to see the dropdown with all the valid chatrooms!
It’s time to add the chart that will show us the top 5 users for a chatroom!
The Clustered column chart will do just fine so in order to add it to our report simply press the report page and then click on the corresponding icon and place it next to the dropdown.
Ok so now it is time to configure the chart!
Our first goal is to link the chart with the dropdown so that every time we choose a different chatroom the chart component displays the top 5 users for the selected chatroom.
In order to achieve this we need to :
Highlight the slicer component.
Select the Format tab (in between Help and Data/Drill tabs).
Switch on Edit Interactions.
Filter controls are represented by the funnel icon on top of each component.
Highlight both components and ensure the filter controls are selected for both.
Now we need to configure the chart accordingly:
Highlight the chart component and check the fields section on the right hand side.
At the moment you should be able to change the chatroom and see a new graph being drawn every time you select a new chatroom. It is showing the number of messages sent by each user in the given chatroom. Now we want to limit it to only show 5 users and improve the looks of that chart!
Visual level filters:
Here it is where you can change the layout of the chart. For instance you can move the legend to be at the bottom instead of being at the top or you can add a title to any of the axis.
More visual aspects can be changed such as the colour of the bars or add some nice labels that indicate the absolute number of messages sent to them.
Now that we have done what we wanted we need to publish the report that we have just done to the cloud!
In order to achieve that go to File and click on the Publish button!
After the success dialog click on the link provided and you will be redirected to the PowerBi portal so sign in using the same credentials as before and you should be able to see this report in your workspace.
If you want to publish it to the Internet or to embed in a SharePoint or to a Powerpoint simply go to File and those options will be available!
This was a very basic example on how we can combine a powerful visual tool like the Power BI with the Skype For Business Persistent Chat database to show us interesting information about a chatroom usage.
Another good example that I was also working on was to create a word cloud chart that is built of the most used words in a chatroom but unfortunately that is still work in progress as it requires a bit more advanced algorithms to gather the meaningful words present in messages that are sent in a chatroom.
My experience with Power Bi was very positive as I found the tool quite powerful and easy to integrate with existing systems. It allows the user to get data from a long list of data sources and it offers a wide range of different charts that display the information the users need. With the R Script Editor,which I did not cover on this example, the more technical user will feel more empowered to use their scripting abilities to create and use their own Power Bi visuals.
As a quick and last note I am definitely not an expert on Power Bi so if you found something that was wrong or could be explained better please leave a friendly comment :)
Hopefully this article has sparked some curiosity about this tool!
Written by Gonçalo Ferreira.
I believe in Ripple XRP and so should you.