Display Office 365 data using MS Graph API in Power BI

Learn how you can consume MS Graph API data in Power BI

Objective:

In this article, I will show how you can display Office 365 data of your organization in Power BI report using MS Graph API.

I will show how you can fetch your organization’s users with MS Graph API, and quickly create a report using Power BI Desktop. Once you learn to use MS Graph API in Power BI, you can explore more options to create some interesting reports on your own.

Background:

This article assumes you know something about MS Graph API and Power BI.

What is MS Graph API?

Excerpt from https://developer.microsoft.com/en-us/graph/docs/concepts/overview

You can use the Microsoft Graph API to interact with the data of millions of users in the Microsoft cloud. Use Microsoft Graph to build apps for organizations and consumers that connect to a wealth of resources, relationships, and intelligence, all through a single endpoint: https://graph.microsoft.com

For more on MS Graph API, please go to https://developer.microsoft.com/en-us/graph

What is Power BI?

Excerpt from https://powerbi.microsoft.com/en-us/

Power BI is a suite of business analytics tools that deliver insights throughout your organization. Connect to hundreds of data sources, simplify data prep, and drive ad hoc analysis.”

For more on Power BI, please go to https://powerbi.microsoft.com/en-us/

There is no coding involved to follow steps of this article. However, if you want to follow along the steps, then you will need things mentioned in “Prerequisites” section. If you don’t want to follow along and just want to read, you can jump to “Getting Started” section.

Prerequisites:

  • Office 365 developer account

You may have access to Office 365 thru your employer/organization account. However, it is strongly advised that you don’t use your live/organization account to follow steps of this article. Instead, use Office 365 developer account.

Read my blog on how to get Office 365 developer account for 1 year free here.

  • Power BI Desktop

You will need Power BI Desktop to create the report which will consume MS Graph API data.

Power BI Desktop can be downloaded free from this link:

https://www.microsoft.com/en-us/download/details.aspx?id=45331

Power BI Desktop getting started guide is here:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-getting-started/

 

Getting Started:

I assume you have Office 365 developer account and you have installed Power BI Desktop on your PC.

Let’s get started.

Open Power BI Desktop.

PowerBI1

Once open, you will see it’s welcome screen:

PowerBI2

Click on “Get Data” just below Power BI Desktop header on top left.

Alternatively, if you close this welcome screen, then you will also see “Get Data” icon at top left:

PowerBI3

Once click on “Get Data”, you will be presented with a “Get Data” dialog. Click on “Other” in left-middle section:

Get data

Once you click on “Other” link, on the right side of dialog you will see some options. Select “OData Feed”, then click on “Connect” button:

Odata Feed

You will see a dialog to enter OData feed URL:

Odata Feed blank

Why we choose this option?

MS Graph API is based on open web standards and it supports OData V4, and MS Graph API accepts and returns data in JSON format, making it easy to integrate with other applications and technologies.

We want to access all the users of an organization. The MS Graph API endpoint https://graph.microsoft.com/v1.0/users returns all users of an organization.

Enter https://graph.microsoft.com/v1.0/users in the textbox under URL and click OK:

Odata Feed2

Once you click on OK, you will see a dialog where you can specify your credentials to connect to MS Graph API.

Odata Feed3

Click on “Organizational account”, then click on “Sing in” button:

Odata Feed4

You will see Office 365 log in dialog:

O365 login

Don’t use your organization/live account to sign in. Use your Office 365 developer account to sign in.

Once you login successfully that dialog will close, and your status on OData feed dialog will change to singed in:

O365 login2

Click on Connect button to continue.

Once Power BI fetches the users using MS Graph API, it will show you in a dialog. For testing purpose, I have created some users using my developer account. I suggest you also create some test users in your Office 365 developer account using Office 365 Portal.

I have masked some sensitive data from my popup:

user data

You should also see your organization’s users here. Click on “Load” to continue.

You will see some messages in a popup while Power BI loads your data using MS Graph API:

loading

Once this dialog closes, on the right side you will see the data table under “Fields” section:

fields

What has happened here?

Power BI Desktop has received the JSON data from MS Graph API in response to the call to https://graph.microsoft.com/v1.0/users endpoint, and converted it to a data table for you. What you see under the Fields -> Query1 section is the list of all properties it got from MS Graph API.

Now we have the data we want from MS Graph API loaded into Power BI. Let’s create a report using it.

Click on “Table” icon under Visualizations on right side of screen:

table

You will see your screen will be loaded with an empty table:

empty table

Now we should select which fields we want to show in table. Under Fields section on right, select displayName, jobTitle and officeLocation fields in that order:

fields selection

As and when you select the fields, you will see the table being loaded with data:

table with data

Hurray, you have created your first Power BI report using MS Graph API!

What’s next?

The purpose of this article was only to show you how MS Graph API data can be consumed in Power BI, which I have shown above. Similarly, you can try by yourself creating some other reports/visualizations and calling some more MS Graph API endpoints.

If you want to share the report, you can do it using the Publish feature of Power BI:

publish

But this requires a Power BI account and it’s out of scope for this article. I suggest trying it on your own.

Meanwhile if you want to read more of my articles on MS Graph API, please go to https://nilesh.live/blogs/msgraph

How to use Microsoft Graph Provider in Visual Studio Connected Services

In this article, I am introducing the newly added Microsoft Graph Provider in Visual Studio 15.3.1. This article demonstrates how a developer can use this feature to work with Microsoft Graph API.

If you don’t know anything about Microsoft Graph API, then I suggest you read something about it here.

To make this article meaningful, I am explaining it using a new Excel add-in project. There is no coding involved though.

Prerequisites:

There are some prerequisites if you want to follow the exercise I have shown in this article. If you just want to read and don’t want to follow any steps by yourself, then please jump to Let’s Start section in this article.

  • Visual Studio 15.3.1 with Office Development Tools

On August 18, 2017, Microsoft has released Visual Studio 2017 version 15.3.1. For more on this release see release notes here.

This article assumes that you have already installed Visual Studio 15.3.1, if not then you can download it here.

For more specific content on how to install Visual Studio, please read my article here.

You will also need to have Office Development tools installed while you install Visual Studio. Please read my article on that here.

  • Office 365 Developer account

You may have access to Office 365 thru your corporate account provided by your employer. But it is strongly advised that you don’t use that account. Instead of that, get an Office 365 developer account here for free and enjoy 365 days of Office 365!

For more info on that, please read my article on Office 365 developer program here.

Before moving further, let’s make sure that you are using the correct version of Visual Studio for following instructions in this article, otherwise it will not work. Multiple versions of Visual Studio can be installed side by side, including preview version, so it’s necessary to make sure of correct version you are working with.

In Visual Studio, Open the menu Help -> About Visual Studio:

About Visual Studio

Please make sure 15.3.1 is the version of your visual studio:

VS version

If that’s the version of your Visual Studio, then let’s start.

 

Let’s Start

Open Visual Studio 15.3.1 and click on “Create New Project…”

In the “New Project” window, Select “Visual C#“ -> “Office/SharePoint” -> “Add-ins” -> “Excel Web Add-in”:

new project

Click on “Ok” to create a new project.

In the next window that appears, select the option “Insert content into Excel spreadsheets”, then click “Next”

office addin 1.png

Select “Basic Add-in”, then click “Finish”

office addin 2

Visual Studio will create a new Excel add-in project for you.

Once Visual Studio is ready, open Solution Explorer, find “Connected Services” under your project and double click on it:

connected services 1

You will see the following screen:

connected services 2.png

Click on “Access Office 365 Services with Microsoft Graph” which is the core of this article.

You will be shown a window to configure access to your Office 365 services:

connected services 3

If you have your Office 365 developer account, then enter the domain name you selected in the “Domain” textbox. Don’t forget to add “.onmicrosoft.com” too.

connected services 4

I have entered my developer account domain, but masked it for security.

If that was the correct domain you entered, the you will be shown a sign-in window where you will enter the details you used while registering for Office 365 developer account.

connected services 5

If your login information is correct, you will be shown “Configure Application” section:

connected services 6

Let it be “Create a new Azure AD application”, and click “Next”.

Now the wizard will ask you for permission to different Office 365 sections. It’s up to you what permissions you want to assign. You can change it later.

connected services 7

Following are the permissions I selected under “User” tab:

connected services 8

Similarly, I also selected some permissions in other tabs.

Once you are done selecting permissions, then click “Finish”:

connected services 9

It will take some time for Visual Studio to configure the access. Meanwhile you will see a window like below:

connected services 10

Once Visual Studio is done with configuring access to Office 365, that window will close automatically. If there was any error, it will be reported to you. Otherwise you will see success message in output window:

output window

Also, if you note – under “Connected Services” in Visual Studio, Office 365 Services will have a green tick mark:

green mark

There is also something more which happened.

Go to Solution Explorer. Do you see under “Connected Services” a new folder of “Office365” has been added?

solution explorer

Those two files under Office365 folder are not too important programmatically. One is a link to Microsoft Graph documentation, another is a JSON file with link to getting started documents. But we will leave those untouched as of now.

Open the Web.config file and see what’s changed here. You will see under the appSettings tag, Visual Studio has added some configuration details:

config details

You will see client id, client secret, tenant id and domain are automatically inserted in this file based on the information you provided while configuring access to Office 365.

Visual Studio also created an application under Microsoft Azure Application Registration for you.

To check that, login to portal.azure.com with the same account as you used for configuring access to Office 365 above.

Once logged-in to Azure, under search type “app reg”:

app reg 1

Click on “App registrations” link.

Under “app Registrations” you will see an app with the same name as your project in Visual Studio:

app reg 12

The GUID under application id is what you will see in your web.config file. You will not see the client secret anywhere though.

Also, if you note under package manager, Visual Studio has already added references to MS Graph SDK:

nuget

See how easy it has been made by Visual Studio now to kick start your Office 365 development using Microsoft Graph?

This article’s purpose was only to make developers aware of the addition of Microsoft Graph under connected services in Visual Studio. I will cover the actual code to be written in a project to call Microsoft Graph API in another article some time later.

Meanwhile if you want to see by yourself how you can call Microsoft Graph using Visual Studio, please go to this link.

For my more articles on Microsoft Graph, please visit here. Please also visit my home page nilesh.live for list of all my blogs.

Header image courtesy: Microsoft