Get Office 365 data in Power BI using Microsoft Graph API and ODBC

Introduction

In this article, we will get Office 365  data in Power BI using Microsoft Graph API and ODBC drivers. Specifically, we will get Excel file data in OneDrive, events from a Calendar and finally, a list in a note in OneNote and then load that data into a Power BI report. We will achieve that by creating an ODBC data source using ZappySys ODBC PowerPack and then querying corresponding Microsoft Graph API methods. ODBC PowerPack allows to query REST/SOAP APIs in an easy way and lets to view responses in a tabular format, which will be perfect to accomplish our goal.

 

Prerequisites

  1. A Power BI account.
  2. Wield basic Power BI skills.
  3. Power BI Desktop installed.
  4. ZappySys ODBC PowerPack installed.

Step By Step – Get Office 365 data in Power BI using Microsoft Graph API and ODBC

Register Application (OAuth2 App for Graph API)

Very first step to access any Office 365 API / Graph API is to register OAuth App. It's a few step process. After following steps you will get following 2 items to use for next section.
  1. Application Id
  2. Application Secret
Check this link to learn how to register your app.
Let's look at step by step how to register your App to access API.
  1. Visit ApplicationsList to register your new App. Login using your email which can be Active directory account (work account) or Office 365 account or personal account (e.g. hotmail / outlook.com)
  2. Once you login click "Add an App"
  3. On the next screen fill out App name / contact email and uncheck "Let us help you option" click next
  4. On the App configuration screen make sure following three things done
    1. Under Application Secret click "Generate Password". Save that password somewhere.
    2. Under Platform  section click Add and Select "Web"
    3. Once you add Web Platform you will see redirect URI. Enter following URL
      https://zappysys.com/oauth
    4. All other fields are optional (e.g. App logo, Product URL)
    5. Save page / app registration
Once the above steps are done you can proceed to use Graph API.

Open ODBC Data Source Administrator

  1. Hit Windows Start button and search for "odbc".
  2. You will see two versions of ODBC Data Source administrator64-bit and 32-bit. Choose:
      • 64-bit if your client application is a 64-bit program and you plan to create a System DSN.
      • 32-bit if your client application is a 32-bit program and you plan to create a System DSN.
      • 32-bit or 64-bit version if you plan to create a User DSN.

    Windows ODBC Data Sources

  3. Once you open, you should see a similar window:

    ODBC Data Source Administrator

Set up an ODBC data source based on ZappySys JSON Driver

Add a data source

  1. Go to System DSN and press Add to add a system DSN. If you use apps like Power BI which needs access only your User account then you can create User DSN.Create new ODBC DSN (System DSN Tab)
  2. Add a data source based on ZappySys JSON Driver: Add ZappySys JSON / REST Driver Connection

Configure the data source

  1. Once a window appears, give a name to the data source, e.g. System - JSON:
  2. Then input https://graph.microsoft.com/v1.0/me in the URL field.
  3. Select OAuth as the Connection Type.
  4. Click Click to Configure button/link to open OAuth configuration window.
  5. Select Custom as OAuth Provider.
  6. Then input Client Id, Client Secret which we got when created the Graph API application.
  7. Set https://login.microsoftonline.com/common/oauth2/v2.0/authorize as Authorization Url.
  8. Set https://login.microsoftonline.com/common/oauth2/v2.0/token as Access Token Url.
  9. In Scopes / Permissions section set these scopes, separated by a new line. E.g. offline_access, Files.Read.All, Calendars.Read, Notes.Read, User.Read
  10. In Advanced tab configure Callback/Redirect Url and set it to the one we configured in Graph API app:
  11. Finally, click Generate Token button to generate a token.
  12. Click OK to close the window.

Set up an ODBC data source based on ZappySys XML Driver

  1. Before adding another data source based on ZappySys XML Driver, copy the connection string of the data source we just created:
  2. Close the window and then add a new data source, but this time based on ZappySys XML Driver.
  3. Once a window is opened, give it a name, e.g. System - XML.
  4. Then press Load connection string button to load the connection string.
  5. Paste the copied connection string and replace JSON text to XML, so that you have { ZappySys XML Driver } in DRIVER property.
  6. Close the window.

Create a Power BI report

Reading files from OneDrive (getting Excel data)

Let’s say you have Stock.xlsx stored in Sample Documents folder, OneDrive with this kind of data and you want to show it in a Power BI report:

To have that data in a Power BI report, perform these steps:

  1. Open Power BI Desktop and create a new Power BI report.
  2. Create a data source based on System – JSON DSN (since we get a JSON response):
  3. Then in SQL statement area enter this query:
    SELECT * FROM $
    WITH(Src='https://graph.microsoft.com/v1.0/me/drive/root:/YOUR FOLDER/Stock.xlsx:/workbook/worksheets/SHEET NAME/usedRange'
    ,Filter='$.values[1:]'
    ,ArrayTransformType='TransformSimpleTwoDimensionalArray'
    ,ArrayTransColumnNameFilter='$.values[0][*]'
    )
    Using WITH clause lets us override the properties. You can generate the query above using the Query Builder:
    In this step we are overriding Url, Filter and array transformation fields you configured in JSON DSN. The Src value overrides the URL. The Filter property enables us to select the data we want to see in the report. We use [1:] in $.values[1:] to skip the first row because the first row contains the headers and we want only the data. You can configure this option in the Filter Options tab (read more about JSON path).We also have to make the transformation on the JSON and turn JSON arrays into rows. To accomplish that, we override ArrayTransformType. With ArrayTransColumnNameFilter property and [0][*] text, we specify that the first array of parent array will be the headers of the columns. You can read more about transformations in Parse multi-dimensional JSON array article.
    Note: Use Microsoft Graph API Explorer to make a call and see what response you get. You can try calling other OneDrive endpoints.
  4. Click OK.
  5. Drag a table into the report, select the columns and you should see a similar view:

Reading calendar entries

Now, let’s say you use a calendar in Office 365 and you want your appointments to be visible in a Power BI report:

  1. To start, create a new Power BI report.
  2. Then add a data source based on System – JSON DSN (again, we get the response in JSON):
  3. In SQL statement section enter this query:
    SELECT * FROM $
    WITH(Src='https://graph.microsoft.com/v1.0/me/events?$select=subject,start,end,location'
    ,Filter='$.value[*]'
    ,Header='Prefer: outlook.timezone="FLE Standard Time"'
    )
    Here we also are overriding HTTP Header so that we get the event times not in UTC/GMT but for the specified timezone. It must be specified in Prefer: outlook.timezone=”{time-zone-string}” format (check an article on how to get a list of supported time zones).
    Note: Use Microsoft Graph API Explorer to make a call and see what response you get. You can try calling other Calendar endpoints.
  4. Click OK.
  5. Drag a table control into the report, select several columns and you should see a similar view:

Reading notes from OneNote

Suppose, you have these notes in OneNote and you want “Groceries list” to be shown in a Power BI report:

  1. First, we will need to find NOTE ID we are trying to get. To do that, open ODBC Administrator, find System – JSON data source and hit Configure.
  2. Then go to the Preview tab, and input this SQL query and hit Preview Data:
    SELECT * FROM $
    WITH (Src='https://graph.microsoft.com/v1.0/me/onenote/pages'
    ,Filter='$.value[*]'
    )
  3. Once you get a result set, find your note name and copy its ID:
  4. Then create a new Power BI report.
  5. Create a new data source based on System – XML DSN (we are using XML-based DSN because the response will be in HTML):
  6. In SQL statement section enter this query (use the NOTE ID you retrieved in the previous step):
    SELECT * FROM $
    WITH(ElementsToTreatAsArray='div,p'
    ,Src='https://graph.microsoft.com/v1.0/me/onenote/pages/NOTE ID/content'
    ,Filter='$.html.body.div[0].p[1:]'
    )

    Note: Use Microsoft Graph API Explorer to make a call and see what response you get. You can try calling other OneNote endpoints.
  7. Click OK.
  8. Drag and drop Multi-row card visual, select #text column and you should see a similar view:
  9. We finished! Now you can publish your Power BI reports to Power BI service.

Working with Gateways in Power BI (Schedule Import)

If the data needs to be updated, it is necessary to create a gateway on-premises. In this new section, we will install a Power BI Gateway and in the next section schedule it to update the REST API information.
  1. In the last section, we Published the report. Power BI may ask you to SIGN IN.
    Sign in Power BI

    Sign in in Power BI

  2. Select the Workspace and select Datasets
    Go do workspace and select dataset

    Go to workspace and dataset

  3. Right-click the report and select Settings.
    Define settings for Power BI report

    Specify settings for the report

  4. The system will ask for a Gateway. Stay here.
    add power bi gateway

    Add Power BI Gateway

  5. Use the following link to install a Data Gateway:
    https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem
  6. Run the installer and press Next
    Initial gateway window for installation

    Gateway installer

  7. Select the option On-premises data gateway (recommended). This option allows access to multiple users and can be used by more applications than Power BI.
    Choose Power BI gateway

    Choose Power BI option

  8. The installer will show a warning message.
    Warning message gateway installation

    Warning message during installation

  9. Select the path to install and check the I accept the terms.
    accept terms

    Accept terms

  10. Specify the email address to use the gateway.
    Register email in gateway installation

    Register email address

  11. After entering the email, write the gateway name and a recovery key. Make sure to confirm the recovery key.
    Specify name and recovery key

    Enter recovery key

 

Manage gateways and configure the schedule

Once that the gateway is installed we will configure it and add the connection strings.
  1. The next step is to go to manage gateway
    Power BI - Manage Gateway Setting

    Power BI - Manage Gateway Setting

  2. In order to get the connection string, we will need the connection string of the ZappySys JSON Driver. In the first section of this post, we explained how to configure it. Press Copy Connection String
    ZappySys connection properties

    Connection properties

  3. Once that the data is copied, add a New data Source. In Data Source Name, enter the Data Source Name of the ZappySys JSON driver in step 13 and in Data Source Type, select ODBC. In connection string copy and paste from the clipboard of the step 13 and press Add.
    Zappy connection properties

    ZappySys connection properties in Power BI

  4. Once added the gateway. You can see the schedule refresh to On and Add another time to add the time where you want to refresh the data.
    Schedule gateway

    Schedule gateway

 

Conclusion

Today we learned how to call Microsoft Graph API methods using ZappySys ODBC PowerPack and load response data into Power BI without any ETL procedures. Particularly, we used OneDrive, Calendar and OneNote Graph API endpoints to achieve that. We created two ODBC data sources based on JSON and XML ODBC Driver to query those endpoints, which let us read data directly in a Power BI report. Finally, we examined how to refresh data source on Power BI using on-premises Power BI gateway.

References

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

https://apps.dev.microsoft.com/

https://developer.microsoft.com/en-us/graph/graph-explorer/

https://docs.microsoft.com/en-us/graph/overview

https://docs.microsoft.com/en-us/graph/onedrive-concept-overview

https://docs.microsoft.com/en-us/graph/outlook-calendar-concept-overview

https://docs.microsoft.com/en-us/graph/integrate-with-onenote

 

 

Posted in JSON File / REST API Driver, ODBC App Integration, ODBC Drivers, ODBC PowerPack, Reporting - Microsoft Power BI, REST API, XML File / SOAP API Driver and tagged , , , , , .