Skip to content
November 10, 2014 / Erik Svensen

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.

 

 

 

 

 

 

 

 

 

 

Advertisements

6 Comments

Leave a Comment
  1. Brian Wagner / Jun 24 2015 5:42 pm

    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

  2. That is really interesting, You are a very skilled blogger.
    I have joined your rss feed and sit up for looking for more of your fantastic post.
    Also, I have shared your web site in my social networks

Trackbacks

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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: