Using Power BI to extract data from Office 365 Reporting web services #powerbi #office365

Office 365 supplies a web service that enables you to extract a lot of information on email and spam, antivirus activity, compliance status and Lync Online activities – you can read more about this here.

I am one of the administratosr of our companys Office 365 account , and decided to see if I could use the Power BI tools to extract the information.

The web service contains many different reports where you can extract a lot of information.

In this example, I will show you how to extract information about inbound and outbound mail traffic per mailbox.

Using Power Query

So open a blank workbook and switch to the Power Query tab and select “From Web”

The web service report I want to use is called “MailTrafficSummary” – details about Fields and OData options can be found here

https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select= Date,Name,Direction,MessageCount&$filter=AggregateBy%20eq%20’Hour’&$format=Atom

As specified in the parameters I want it returned as Atom meaning that we will get an xml file back from the web service. Because I am the administrator I will not get prompted for a username and password.

The file contains information about the message count per user and the structure can be seen below.

 

So right click the binary file and choose to open the file as XML

 

The XML is returned and we can begin to drill down for find the entry element we are interested in.

So after expanding and removing columns I have the nodes I am interested in

Then I use the transformation of the data to the proper datatypes and add columns for date and time values that could be relevant to group the data by.

Here are the steps applied

let

Source = Xml.Tables(Web.Contents(“https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select= Date,Name,Direction,MessageCount&$filter=AggregateBy%20eq%20’Hour’&$format=Atom”)),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“id”, type text}, {“updated”, type datetime}}),

#”Expand entry” = Table.ExpandTableColumn(#”Changed Type”, “entry”, {“content”}, {“entry.content”}),

#”Expand entry.content” = Table.ExpandTableColumn(#”Expand entry”, “entry.content”, {“Attribute:type”, “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”}, {“entry.content.Attribute:type”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” = Table.ExpandTableColumn(#”Expand entry.content”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”, {“properties”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”, {“http://schemas.microsoft.com/ado/2007/08/dataservices”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”}),

#”Removed Columns” = Table.RemoveColumns(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”,{“id”, “title”, “updated”, “link”, “entry.content.Attribute:type”, “http://www.w3.org/XML/1998/namespace”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices” = Table.ExpandTableColumn(#”Removed Columns”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”, {“Date”, “Name”, “Direction”, “MessageCount”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Name”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Direction”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, {“Element:Text”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date.Element:Text”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”, {“Element:Text”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount.Element:Text”}),

#”Renamed Columns” = Table.RenameColumns(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”,{{“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount.Element:Text”, “Messagecount”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Direction”, “Direction”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Name”, “Mailbox”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date.Element:Text”, “DateTime”}}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“DateTime”, type datetime}, {“Messagecount”, Int64.Type}}),

#”Inserted Day of Week” = Table.AddColumn(#”Changed Type1″, “DayOfWeek”, each Date.DayOfWeek([DateTime]), type number),

#”Inserted Hour” = Table.AddColumn(#”Inserted Day of Week”, “Hour”, each Time.Hour([DateTime]), type number),

#”Inserted Week of Year” = Table.AddColumn(#”Inserted Hour”, “WeekOfYear”, each Date.WeekOfYear([DateTime]), type number)

in

#”Inserted Week of Year”

 

After all these steps I get a nice clean table

 

This is returned to the data model of the workbook.

Then I add a measure to the model to summarize the MessageCount field.

 

And then we can start visualize the mail traffic in Excel …

 

So as you can see its fairly straight forward to get data from the usage of your Office 365 subscription into Excel and use the Power BI tools to analyze the data.

So check out all the other options/reports in the Office 365 Reporting web service and see if there is something you can use in your reporting.

 

 

 

 

 

 

 

 

 

 

8 thoughts on “Using Power BI to extract data from Office 365 Reporting web services #powerbi #office365

  1. Pingback: Decorating the X-mas tree using Power Query, Power Pivot and Power Map #PowerBI | Business Intelligence Info

  2. When I try this using the same query you have in your example above. I get the following error.

    DataSource.Error: Web.Contents failed to get contents from ‘https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select= Date,Name,Direction,MessageCount&$filter=AggregateBy%20eq%20’Hour’&$format=Atom’ (400): Bad Request
    Details:
    https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select= Date,Name,Direction,MessageCount&$filter=AggregateBy%20eq%20’Hour’&$format=Atom

    I am an administrator for our Office 365 account. Do I have to have the Office 365 BI subscription for this to work in Excel?

    Thank you,
    Brian

    1. Ok, figured that out. I have even managed to modify the OData options to get the fields I want and try to limit the date range. However I am seeing two things that do not make sense. Here is the URL I am using:
      https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select=Date,Name,Direction,MessageCount&amp$filter=AggregateBy%20eq%20’Day’%20and%20StartDate%20eq%20datetime'2015-06-18T00:00:00Z'%20and%20EndDate%20eq%20datetime'2015-06-24T00:00:00Z

      The results I get back have a date range of 6/10/2015 – 6/24/2015. not the expected 6/18/2015 – 6/24/2015. Also I am getting Names that repeat from a single day with different Inbound counts. For example:
      6/11/2015 DonaldDuck@somedomain.com Inbound 23
      6/11/2015 DonaldDuck@somedomain.com Inbound 2
      6/11/2015 DonaldDuck@somedomain.com Outbound 10

      Any idea why it is not totaling the Inbound for the mailbox for that day and why it is not limiting the results to the specified date range?

      Thank you,
      Brian

  3. Hi,
    I want to understand what kind of admin can access these reports? Is it only limited to global admin or can Exchange admin and Lync admin can also access these reports?

    1. Hi, I think you should look at the new Content pack that can be retrieved via the service. And you have to be an Admin to Update the data but not in ordet to use the reports/dashboard if you share Them via the power bi service
      BR
      Erik

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s