Are you looking for Business Insight? Do you already use an industry standard Business Intelligence tool like Power BI or Tableau? Or maybe you're just an Excel-lover (who isn't?). 

With Prospect'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 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 don't know how to create a PAT (Personal Access Token) please take a look at this article. Once you've got a PAT, connecting Power BI to Prospect is pretty straight forward.

Using your PAT to Connect Power BI to your Prospect Data

Step 1: Download Power BI Desktop App

If you haven't already done so, download the Power BI Desktop App from https://powerbi.microsoft.com/en-us/desktop/.

Step 2: Get Data

Open Power BI Desktop, and click 'Get Data' from the ribbon bar.

Step 3: Choose OData Feed

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

Step 4: Enter HTTPS URL

Power BI then asks for the URL for your OData service. Enter this as:

HTTPS://api-batch-v1.prospect365.com

Make sure you enter HTTPS and not HTTP. Without HTTPS (S being for Secure), your connection will be rejected...after all, no-one wants their customer data transmitted over the Internet without it being encrypted!

Step 5: Authenticate

On the next screen, Power BI asks you how you want to authenticate. Prospect doesn't allow anonymous access to your data (phew!), so choose 'Basic'. Please note: the term 'Basic' doesn't relate to the level of security - merely the method of security - i.e. we're 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 'User name', and your PAT as the 'Password'. You CANNOT use your standard Prospect 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.

Step 6: You're Connected!

If you have accurately copied and pasted your PAT into the 'Password' field, clicking connect will (not surprisingly) connect your Power BI app to the Prospect OData service and let you start navigating and accessing your Prospect 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 Power BI 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.


That's it! You're now connected to the Prospect OData service. You can go ahead and create Power BI queries, reports and dashboards!

Obviously, there's 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 CRM-related reports, look out for our follow-on article "writing your first Power BI query with Prospect". Or, if you want some instant gratification, download one of our content packs from the Power BI store here.      

Or, contact our Customer Success Team and ask them about our Consultancy Services and how we can help you get the reporting and analysis you want.

Using your PAT to Connect Excel to your Prospect Data

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

Step 1: Open Excel

First step is to open a new spreadsheet in Excel.

Step 2: Create New Query

Next, choose 'New Query' from the Data ribbon bar, and then choose 'From Other Sources', and 'From OData Feed'.

Step 3: Enter OData Feed URL

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 Feed URL:

Step 4: Enter Credentials

Then enter your credentials. Again, select 'Basic' credentials (see Power BI section above for details of what this means). The 'User name' is the email address you use to login to Prospect, but the password is NOT your Prospect 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'll 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.

Of course, connecting to the Prospect OData service is only the first step. Understanding the data and using best practice to get the right data and optimise your query is the next step. For that, get in contact with us to find out about our Consultancy Services.

Using your Personal Access Token with other Industry-Standard BI & Query Tools

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 example below, you'd enter the server as HTTPS://api-batch-v1.prospect365.com and select 'use a specific username and password'. Then, just as in the Power BI and Excel examples above, use your Prospect username, but instead of your normal Prospect password, use the Personal Access Token that you generated at the start of this article. Once authenticated, you'll 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?