Connecting OData to Excel for Business Intelligence

Any number of applications can consume OData, but because of its ubiquitous presence, Excel is a common option. In this recipe, we will look at how to bring OData into Microsoft Excel for additional analysis.

Getting ready

Before we begin, ensure that OData has been set up and security has been set for the user that will log in. You can review the other OData recipes to validate the setup.

How to do it…

To bring OData information into Excel, follow these steps:

1. Open a new workbook in Microsoft Excel.
2. Select Data | From Other Sources | From OData Feed.

   Enter or copy and paste an OData URL and click OK. The simplest place to find one is in Dynamics GP in Administration | Setup | System | OData | Publish OData. In our example, I'm using https://MDPCPA.cloudapp.net:443/GPOData/T16R1/Accounts to return the chart of accounts.

3. If you are using the credentials you are already logged in with, select Use my Current Credentials. If you need to...