Introduction

You can use several 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:

See:

  Using OData connector with Excel (23.4) - AIQ Academy

Use OData Connector with PowerBI (23.3) - AIQ Academy

 OData Connection Setup 

 Creating an OData Connection 

 OData Excel Report Building FAQs‍ 

 OData with Excel Troubleshooting‍ 

Connecting to OData

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.
   
   

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:

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.
   
   
   
   
   
   
    

Adding Datasets

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.

Modifying and 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..
 

FAQs

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 Disable the Power BI, Excel OData, and endpoint 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 login credentials have been compromised) by logging into 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 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.