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. Prospect OData API allows 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 straightforward.

Power BI is Microsoft's Business Intelligence Tool that lets you visualise your CRM data and share insights across your organisation.

Power BI Desktop is a free, self-service data analysis and report authoring tool that you install on a Windows computer. You'd use the Power BI Desktop to produce reports and make them available to the Power BI service.

Power BI service is a secure Microsoft hosted cloud service that lets users view dashboards, reports, and Power BI apps using a web browser or via mobile apps for Windows, iOS, and Android. Click here to view Microsoft's Subscription prices for the Power BI Service.

Our CRM Report Builder gives you great insight into a list report format, but if you want to create your own custom visual graphs and dashboards, our Power BI Apps are a quick and easy way to get started using Power BI to analyse the data in your Prospect solution.

If you haven't already done so, download the Power BI Desktop App from here.

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

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

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

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!

On the next screen, Power BI asks you how you want to authenticate. It 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 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.

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 OData service and let you start navigating and accessing your 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 or "Select Related Tables". This causes Power BI to load the entire dataset without any filters. Instead, choose "Transform Data". 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 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). 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.

Alternatively, 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.

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

First step is to open a new spreadsheet in Excel.

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

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:

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 log in to your CRM, but the password is NOT your CRM password...never share this with other users or other applications. Use the Personal Access Token generated above as the 'Password' on this screen.

Step 5: Edit & Apply Filters

Assuming that you copied 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 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.

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 crm-odata-v1.prospect365.com and select 'Use a specific username and password'. Then, just as in the Power BI and Excel examples above, use your username, but instead of your normal CRM 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.