Month: August 2017

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.

“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”

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 freehere.

Power BI Desktop

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

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

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:

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:

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

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:

You will see your screen will be loaded with an 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:

As and when you select the fields, you will see the table being loaded 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:

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

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 the next window that appears, select the option “Insert content into Excel spreadsheets”, then click “Next”

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

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:

You will see the following screen:

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:

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.

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.

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

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.

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

Similarly, I also selected some permissions in other tabs.

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

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

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:

Also, if you note – under “Connected Services” in Visual Studio, Office 365 Services will have a green tick 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?

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:

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”:

Click on “App registrations” link.

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

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

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.