Several different tools can be used 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. 

See:

Using OData connector with Excel (23.4) - AIQ Academy

Use OData Connector with PowerBI (23.3) - AIQ Academy  
 

OData Connection Setup 

OData with Excel Advanced Report Building Tips 

OData with Excel Troubleshooting 

1. In Excel, open a new workbook.
2. Go to Data > Get Data > From Other Sources > From OData Feed.
3. Select Basic, enter the URL, and click OK.
4. Enter the following:
   • AIQ username.
   • OData Connection password that you copied from the Data Connection Setup page (NOT your AIQ system password)
   • The URL from the final drop-down menu on the OData Connection Setup screen. This will help you manage multiple connections to different databases
5. Click Connect.

Now that you have created a connection, you can fetch data from the system.

There are two types of datasets available:

• Straight datasets: Examples include BI Codes and Customers below. Click these to preview the dataset.
• Functions: Function datasets such as BalanceSheet or CustomerAgeingDetailed require you to enter additional parameters (e.g. a date) before data will load.
  

To Query a Dataset:

1. Select the datasets you want to query.
2. Click Load to to select how you want to show the data in Excel.
3. Select PivotTable Report to load the data into an Excel Pivot Table and click OK.  
4. Next, select the fields you want to display in the pivot table. If you are combining multiple datasets, you may need to join them using a relationship. Click Auto-Detect to get suggestions and Excel will then detect and create a relationship between the datasets. Alternatively, click Create to select the relationship manually.  
     

You can add more datasets to the workbook:

1.  Go to Get Data > From Other Sources > From OData Feed.
2. 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.

Refreshing Queries

Go to Data > Queries and Connections to rename or refresh queries. Select from the list, right-click, and select the relevant option.  

To refresh all queries at the same time, go to Data > Refresh All.