Are you looking for Business Insight? Do you already use an industry standard Business Intelligence tool like Power BI or Tableau? Or maybe you are just an Excel-lover (who isn't?). With Prospect 365's open architecture it's remarkably easy to dive into your data and gain powerful business insights. This article is intended to get you started with the Prospect 365 OData API, allowing you to query not just standard CRM data but custom data fields, entities and tables from any of the leading BI and reporting tools.

If you do not know how to create a PAT (Personal Access Token) please see our corresponding article. 

Using your PAT to connect Power BI to your Prospect 365 data: 

Once you have a PAT, connecting Power BI to Prospect 365 is pretty straight forwards. 

  1. If you haven't already done so, download the Power BI Desktop App from
  2. Open Power BI Desktop and click "Get Data" from the ribbon bar.

3. Choose the OData Feed as the type of data that you wish to retrieve. 

4. Power BI then asks for the URL for your OData service. Enter this as HTTPS:// (make sure you enter HTTPS and not HTTP as without that your connection will be rejected ... after all no-one wants their customer data transmitted over the Internet without it being encrypted!)

5. On the next screen, Power BI asks you how you want to authenticate. Prospect 365 doesn't allow anonymous access to your data (phew!) so choose "Basic" (note - the term "Basic" doesn't relate to the level of security - merely the method of security - i.e. we are going to use a highly secure password, rather than windows authentication or other methods or identity management). On this screen, enter your login email address as the username and your PAT as the password. You CANNOT use your standard Prospect 365 password on this screen - we don't allow that as we don't want you sharing your underlying password, even with Microsoft. Use the PAT token you generated earlier - so that even if you set it to never expire, you can still go back and revoke the PAT any time you choose.

6. If you have accurately copied and pasted your PAT, clicking connect will (not surprisingly) connect your Power BI app to the Prospect 365 OData service and let you start navigating and accessing your Prospect 365 data.

WARNING: At the bottom of the OData Navigator window (shown above) there is a "load" and an "Edit" button. On any decent sized dataset do NOT use the "Load" button. This causes PowerBI to load the entire dataset without any filters. Instead, choose "Edit". This opens a preview and allows you to add data filters in order to reduce the data being loaded to a sensible amount.

7. That's it ... congratulations ... you are connected to the Prospect 365 OData service. You can go ahead and create Power BI queries, reports and dashboards. 

8. Obviously there is a wealth of knowledge and advice out there on Power BI (just ask Google ... or Bing) but, if you want to get started quickly with some Prospect 365 related reports, look out for our follow-on article "writing your first Power BI query with Prospect 365", or if you want some instant gratification, download one of our content packs from the Power BI store (available soon).      

9. Or, if you can't be bothered with all of that, contact our Customer Success Team and ask them about our Professional Services and how we can help you get the reporting and analysis you want.

Using your PAT to connect Excel to your Prospect 365 data

Creating an Excel Power Query, to consume Prospect 365 data in Excel is almost identical to connecting Power BI to Prospect 365. In fact, the query generator for Power BI is the same software as Power Query in Excel.

  1. Open a new Spreadsheet in Excel.
  2. Choose "New Query" from the Data ribbon bar, and then choose "From Other Sources", and then "From OData Feed".

3. After selecting the OData Feed as your source, the screens are almost identical to those in Power BI (see above for more details on each step). First of all enter the OData Fee URL:

4. Then enter your credentials. Again, select "Basic" credentials (See Power BI above for details of what this means). The username is the email address you use to login to Prospect 365, but the password is NOT your Prospect 365 Password... never share this with other users or other applications. Use the Personal Access Token generated above as the password on this screen.

5. Assuming that you copy and pasted your PAT accurately (and that it hasn't expired or been revoked) you will then be presented with the Power Query OData Navigator, just like you see in Power BI.

WARNING: Do NOT use the "Load" button at the bottom of the Navigator window. Instead, use the "Edit" and apply filters to your data set.

6. Of course, connecting to the Prospect 365 OData service is only the first step. Understanding the data and using best practice to get the right data and optimise your query etc is the next step. For that, see our follow-on article, or contact us to find out about our Professional Services assistance.

Using your Personal Access Token with other industry-standard BI and Query tools

Of course there are plenty of great tools beyond the familiar Microsoft tools. Tableau and literally hundreds of other tools support OData. The connection process for each is similar to the process for Power BI and Excel above.

In the Tableau screenshots below, you would enter the server as HTTPS:// and select "use a specific username and password". Then, just as in the Power BI and Excel examples above, use your Prospect 365 username, but instead of your normal Prospect 365 password, use the Personal Access Token that you generated at the start of this article. Once authenticated, you will be able to navigate the OData information in a similar way to Power Query.  

As with Power Query, getting connected is only the first part of the challenge. For more help and information speak to Tableau or your other BI provider.

Did this answer your question?