Introduction
Info: Prerequisites
To set up an OData Connection and access the OData credentials from the Data Connection Setup page, read OData Connection Setup.
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.
Info: Excel Versions
Excel 2010 supports OData, but Power Query needs to be downloaded as an Add-on for Excel 2010-16 versions. Excel 2016 has Power Query In-Built along with OData, but you may be prompted to enable this when first accessing Power Pivot Table.
What is the difference between downloading the data as an Excel Table vs as a Connection?
Excel
With Excel, typical constraints apply and if dealing with a large volume of data, updating may take some bandwidth each time. Excel row limit 2007 onwards: 1,048,576 Rows,16,384 Columns
Connection Only
With Connection only, the data is retrieved via the connection allowing for higher volume of data and speedier loading. Connection Row Limit Excel 2013 onwards: Tens of millions of rows and columns, technically, but limited by system performance, data source etc., as opposed to an Excel limitation.
See:
Using OData connector with Excel (23.4) - AIQ Academy
Use OData Connector with PowerBI (23.3) - AIQ Academy
Connecting to OData
- In Excel, open a new workbook.
- Go to Data > Get Data > From Other Sources > From OData Feed.
- Select Basic, enter the URL, and click OK.
- 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
- Click Connect.
Simple Data Queries using Excel
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: Examples include BalanceSheet or CustomerAgeingDetailed. You will need to enter additional parameters such as date when selecting these function datasets.
To Query a Dataset:
- Select the datasets you want to query.
- Click Load to to select how you want to show the data in Excel.
- Select PivotTable Report to load the data into an Excel Pivot Table and click OK.
- 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.
Adding Datasets
You can add more datasets to the workbook:
- Go to 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.
Refreshing Queries
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.