Monday, August 4, 2014

Analyzing Yammer Activity with Microsoft Power BI

Data / Technology

As one of the premier Microsoft partners focused on Intranets and collaboration, Rightpoint is seeing an increasing desire by our customers to integrate enterprise social solutions, such as Yammer, into their collaboration environments. Now that Yammer is part of the Office 365 suite of solutions, it has become even easier to incorporate enterprise social.

Once Yammer has been implemented, customers have a natural desire to understand usage patterns and analytics.   Microsoft has indicated that Power BI will be the platform of choice for Yammer analytics. The purpose of this blog post is to show you how you can utilize Microsoft Power BI to analyze social collaboration data originating from an organizational Yammer network.

Yammer Data Export

For the purposes of this blog post we will use the data available from the Yammer data export functionality which is open to Yammer administrators.

1. Login to Yammer as a network administrator

2. Navigate to the Network Admin portal within Yammer

3. Select “Export Data” from the “Content and security” section of the side navigation

4. Select a start date for the export

5. Optionally include attachment and external networks


You can find further information on the Yammer data export by using this link:

Yammer Data Model

Once the Yammer data has been exported, the next step is create and populate a data model to support the analysis. Please keep in mind that as Microsoft migrates Yammer analytics to Power BI, the schema, entities and associated columns may change.

Microsoft tools such as Power Query and Power Pivot make creating a data model to support analytics a relatively painless and straight forward process.  The Yammer basic data export includes the following which we will use for this blog post:

· Users

· Messages

· Groups

· Topics

· Pages

In order to support analyzing Yammer data over time, we will supplement the basic Yammer data with Date and Hour entities. The Date entity contains a row for each date during the time period you wish to analyze. You can create this table yourself or download one from the Azure marketplace ( The hour entity contains a row for each hour of the day.

Load Power Pivot using Power Query

Power Query is Microsoft’s excellent new self-service BI tool for integrating data from multiple sources including online data, files and structured sources such as relational data bases. The illustration below shows how you can use Power Query to integrate and shape your Yammer data to support analytics, such as changes to the created_at column from Date/Time/Timezone type to Date/Time or Date depending on corresponding data type in your Date entity


After you have utilized Power Query to load the Power Pivot workbook, the next step is to create the data relationships and calculations necessary to support the analytics. For the purposes of this blog post, we performed the following:

· Marked Date entity as Date Table using Full Alternate Date Key as our key date column.

· Created a new calculated column in the Messages table using the following expression: =HOUR(Messages[created_at]). This allows us to relate the Hour table to the Messages table to view messages over time during the day.

· Created a new calculated field in the Messages table using the following expression: Count of Messages:=COUNTROWS(Messages). This is simply a count of the number of messages created.

· Created relationships between the tables in our model as shown below:


Creating Visualizations and Dashboards

Now that our model is complete, we can proceed to create the visualizations necessary to facilitate our Yammer analytics. Sample dashboards created using Power View are shown below.



Surfacing in Power BI

Once we complete our visualizations and dashboards locally, we can then share them with our colleagues using Power BI. Simply log in to your Power BI site and add the workbook. The visualization below shows the new HTML5 capability of Power BI so that your visualizations can be displayed properly in a variety of browsers and devices.


Enabling your workbook for Power Q & A allows you to ask natural language questions such as that shown below:


Hopefully, this blog post has shown you how you can quickly use Microsoft Power BI to analyze and gain key insights into your Yammer activity.