Using an OData Connection

Written By Russell Cooper (Administrator)

Updated at January 20th, 2022

To find out how to set up an OData Connection and to understand how to access the OData credentials from the Data Connection Setup page, please see the Creating an OData Connection guide here.

You can use a number of different tools to connect to OData, including Microsoft Excel and Power BI. 

We will describe how to connect via Microsoft Excel in this article, but the same steps apply to the other tools. Watch the following video to learn how to connect via Power BI.

 

1. Connecting to OData

In Excel, open a new workbook, click on Data>Get Data>From Other Sources>OData feed


Select Basic and enter the URL that you copied from the Data Connection Setup page (details here).


Then 

  • Select Basic from the left hand side 
  • Enter your AIQ username
  • Enter the OData Connection password that you copied from the Data Connection Setup page (NOT your AIQ system password)
  • Select the same URL from the final drop down menu as the OData Connection URL
    • This will help you manage multiple connections to different databases
  • Click on Connect
Delete

2. Simple Data Queries using Excel

Now that you have created a connection, data can be fetched from AccountsIQ.

There are 2 types of datasets available:

  • Straight datasets (e.g. BI Codes or Customers below). 
    • Click on these to preview the dataset.
  • Functions (e.g. BalanceSheet or CustomerAgeingDetailed below). 
    • You will need to enter additional parameters such as date when selecting these function datasets.



Select the datasets you want to query and click on "Load to" to choose how you want to show the data in Excel.


Select PivotTable Report to load the data in to an Excel Pivot Table.



Combining data from multiple datasets may require these datasets to be joined using a relationship. Excel helps by prompting to auto-detect this relationship.





Delete

3. Adding Datasets

You can add more datasets to the workbook by clicking on Get Data>From Other Sources>From OData Feed.


Enter the OData URL for the company you want to extract data from. This can be the same company that you're already querying or you can extract data from a different company by providing the URL from that company's OData Connection Setup screen.

Delete

4. Modifying and Refreshing Queries

The Queries and Connections panel on the right hand side allows you to rename your queries or refresh one or more queries by selecting the query(ies) from the list, right clicking and selecting the relevant option.


To refresh all queries at the same time, select Refresh All from the top menu bar

Delete

FAQs

What are the Benefits of OData compared to Excel Add-in?  

  • OData is supported by products beyond Excel: Power BI, for example.
  • There is no need to install any software locally, thus avoiding issues for those without Admin permissions for their PC.
  • OData supports role-based permissions, unlike the excel add-in, and access can be enabled/disabled with a single click
  • Using OData you can extract data from multiple data sources (e.g. companies) into a single workbook.

How do I revoke user access to OData?

  • As an Administrator, you can revoke access for all users to a particular company , by going to Setup>Company Details & Settings>Integration and clicking on Disable the Power BI, Excel OData and end point configuration.
  • As a profile administrator, you can revoke access to specific datasets within an entity by selecting "Maintain Menu Profiles" for a specific entity, selecting the OData tab on the resulting screen and de-selecting the datasets for which you'd like to revoke access.
  • As a user, you can revoke your own access (for example if you are worried that your logon credentials have been compromised) by logging onto the company, clicking on Analysis>Data Connection Setup and clicking Revoke for the password for which you would like to disable the connection.

Why can't I see function datasets?

If you are only able to see the metric datasets and not the functions, you can check whether it is a problem with your Microsoft software by connecting to this public test URL http://services.odata.org/TripPinRESTierService/ via Excel or Power BI.

You should see the following list of functions:

If you see both the Metric and Function Datasets above, then your software version / Microsoft Subscription supports the full set of datasets. If you are unable to see these when connecting to your AccountsIQ connection point, please contact support@accountsiq.com for further help.

If you can only see the Metric Datasets above, then your software version / Microsoft Subscription does not support the full set of datasets. If this affects you, contact Microsoft or your local IT Support for further help. Alternatively, some users have found switching to a different method of connection allows them to see the full list of datasets. 
  • If you connect via Excel, try connecting using Power BI 
    • This is included in most Microsoft Office subscriptions but also can be downloaded for free from Microsoft
  • If you connect via Power BI, try connecting using Excel

How can I get help building reports?

The data feeds provide a rich source of data that can be used to build simple data extracts.

They can also be used to build powerful reports similar to the Power BI report template provided with the product (see here). For this type of reporting, you will need report building skills. There are many resources available online that can guide you through the report writing process. Three examples are Microsoft's Power BI YouTube channel, Udemy and Coursera.

I have forgotten my login credentials or password

To connect to the OData feeds via excel or Power BI, you will need:
  • The OData Connection URL
  • Your Username
  • The connection password

Your URL and Username can be viewed from the Data Connection Setup screen.

The password can only be viewed when it is created. If you did not save your password to a password manager then you will need to generate a new one. We would recommend disabling the old password when you do this to keep your account secure. You will be prompted to enter the new password when refreshing any existing reports after disabling the old password.