OData API Integration

What is OData?

OData (Open Data Protocol) is an open protocol with standardised descriptions of the data and data model for easy integration between data sources, applications, services and clients.

The OData connection allows SmartyGrants users to load data from their SmartyGrants account directly into software such as Excel and Power BI for reporting.

The SmartyGrants OData API provides the following functionality:

• Seamless data integration with tools such as Excel and Power BI.

• Simplifies data management and reporting.

• Uses a standardised data format that many users are familiar with.

Examples of use

• Generate comprehensive reports in PowerBI: Easily pull data directly from SmartyGrants to PowerBI to create detailed reports and dashboards.

• Data analysis in Excel: Load data into Excel for advanced data analysis, pivot tables and visualisations.

• Automate data updates: Set up automated data refreshes in your BI system to ensure your reports and dashboards always reflect the latest information from SmartyGrants.

• Cross-system data integration: Combine data from SmartyGrants with other data sources for a holistic view of your operations.

Features

• User-friendly: OData makes it straightforward to create and use data services using familiar web techniques which means quicker and easier development.

• Handles large data: OData is designed to work well with large amounts of data allowing users to gather only the necessary pieces and handle big datasets efficiently.

• Wide support: Many tools and software libraries support OData, making it easier to implement and use across different projects (e.g. Salesforce).

• Easy integration: OData helps different software systems talk to each other smoothly, making it easier to combine and use data from various sources.

• Consistency: It provides a standard way of accessing and managing data, so developers don’t need to create custom solutions for every new project.

OData data model

OData uses a relational data model. This means that data in SmartyGrants is organised and stored in various tables that relate to one another. The name of a table generally describes the subject of its fields/columns. 

For example, a model contains a Payee table with fields:

• payee id

• full name

• address

• organisation name

AND a Payment table with fields:

• payee id

• amount

• date

Tables can be joined through their common fields. In the above example by connecting the Payee and Payment tables through the payee id column, we can relate the data across both tables and draw insights.

See below for the Entity Relationship Diagram, OData Table Summary, and the Data Dictionary.

Entity Relationship Diagram and Data Dictionary

The available tables and the relationship between them can be seen in the Entity Relationship Diagram below:

For further information on each table, take a look at the data dictionary for the OData API here:

SmartyGrants OData Data Dictionary 3.0.0.xlsx

Table Summary

The SmartyGrants OData service provides the below tables:

Using OData with SmartyGrants

The OData service can be used in SmartyGrants for both single and multi-instance reporting:

Single instance reporting: users can connect directly to their preferred application without developer knowledge (e.g. Excel, PowerBI). This enables self-service reporting and customisation for their own needs, and immediate access to the latest data.

Multi-instance reporting: OData can support multi-instance reporting by setting up the same OData API key across multiple instances for a centralised process supporting enterprise-level analytics and business intelligence. If the same OData API key is used across different instances, a user can use OData to get data from all those instances in the same feed. This can help facilitate complex data transformations and aggregations.

Accessing OData (Authentication)

To access OData, users must have both “Administrator” access to the account and an OData API key. Users access the data using their username and password and the OData API key. An OData API key on its own will not grant access to the data.

OData API key

If OData has been enabled, your account settings will contain an API key. This is a 40-character string identifying your organisation’s account. 

Find your OData API key by navigating to Settings > General > API Keys:

The OData request URL and filtering

The OData Base URL needed for the connection is: https://bi.smartygrants.com.au/api/odata4/service/v1/.

To access metadata showing entity fields and relations, use the request URL: https://bi.smartygrants.com.au/api/odata4/service/v1/$metadata.

The request URL can be edited to filter the data down.

You can access data for a single entity by adding the entity’s name to the end of the request URL: https://bi.smartygrants.com.au/api/odata4/service/v1/{entity}.

For example, to access the entity “application”, you can use the following URL: https://bi.smartygrants.com.au/api/odata4/service/v1/application.

You can also view data for a single record by adding the “id” inside brackets.

For example, the URL below will only show application data for the record with “id” 70394: https://bi.smartygrants.com.au/api/odata4/service/v1/application(70394).

Since “payment” is an entity linked to “application” within the EntitySet, you can access payment data for application 70394 through the URL: https://bi.smartygrants.com.au/api/odata4/service/v1/application(70394)/payment.

API data limit per request

Calls to the OData API are currently limited to 200 requests per minute. This ensures that the service remains stable and responsive for all users by capping the number of requests any single client can make within a specified time frame.

In this case, the OData service is configured to allow a maximum of 200 requests from your application every 60 seconds. If your application exceeds this limit, subsequent requests within that minute will likely be rejected or delayed.

Connecting OData feed with Microsoft Excel

Connecting Microsoft Excel with OData allows you to import and interact with data from an OData service directly within Excel.

Establishing the connection

Follow the steps below to connect your OData feed with Excel:

1. Open Microsoft Excel.

2. Create a new workbook or open an existing one where you want to import the OData feed.

3. Select the Data tab in the Excel ribbon:

   

4. Select Get Data (in newer versions of Excel) or From Other Sources:

   

5. In the data source list, select From OData Feed. This option allows you to connect to an OData service:

   

6. You will be prompted to enter the OData feed URL needed for the OData connection. Paste the URL into the provided field:

   

7. You will need to authenticate the connection. Select Basic as the Authentication kind, then enter in your credentials specific to the API Key and select Save:
   

   

Preview and load in the data

Once connected with the OData feed, Excel will fetch the metadata from the OData service and display a preview of the data. You can select the specific tables or views you want to import into Excel that are currently available in the OData feed.

For example, selecting the Application table will load in all of the application details from the fields in the Application table:

Once you have loaded in the data, You can now use Excel to create charts and dashboards. You can use the data like any other data in Excel. You might want to create charts, tables, pivot tables, and apply filters or other Excel functionalities.

Additional tips

Refreshing data

As the OData service provides live data, to automatically sync any new data from SmartyGrants, you can refresh the connection to get the latest data. Go to Data > Refresh All to update the data from the OData feed:

Editing queries

Using the Transform data feature in Excel gives you powerful tools to manipulate your data, making your analysis more robust and reliable.

You can edit the query to transform the data before loading it by using the Power Query editor. After connecting, select Transform Data to open the editor:

In the editor, you can apply various transformations using the options in the toolbar, the right-click menu, or directly by interacting with the data preview.

Key transformations include:

Each transformation you apply is recorded as a step in the Applied steps pane on the right. You can review, edit, or delete these steps.

Once you're done with the transformations, select Close & load to load the transformed data into Excel. You can choose to load it into a new worksheet, the existing one, or directly into a PivotTable or Data Model:

Connecting OData feed with Power BI

To connect your OData feed with Power BI, you will need:

• A Microsoft 365 for Business account, which includes Microsoft Power BI.

• Power BI Desktop, which is only available on Microsoft Windows.

Establishing the connection

Follow the steps below to connect your OData feed with Power BI:

1. Open Power BI.

2. Select File → Blank Report to open a new workbook.

3. Select File → Options and settings → Options → Data Load and ensure ‘Update or delete relationships when refreshing data’ and ‘Autodetect new relationships after data is loaded’ are both unticked:

   

4. Select Get Data from the menu ribbon and then select OData feed:

   

5. You will be prompted to enter the OData feed URL needed for the OData connection. Paste the URL into the provided field and ensure the Basic radio button on the left is selected:
   

   

6. You will need to authenticate the connection. Select Basic as the authentication kind, then enter in your credentials specific to the API Key and select Connect:
   

   

Preview and load in the data

Once connected with the OData feed, Power BI will fetch the metadata from the OData service and display a preview of the data. You can select the specific tables or views you want to import into Power BI that are currently available in the OData feed:

Selecting a table will provide a preview of the data:

Once you have selected the tables that you need, select Load. You will see the tables you have loaded in the right-hand pane of the main window under Fields (or Data in newer versions of Power BI). 

Determining the tables that you need 

Before loading your data, it’s essential to understand exactly what data you need and the specific purposes it will serve.

For example, you may wish to build a Power BI dashboard to analyse the funding allocated across various programs. This dashboard may include different charts segmented by funding rounds and responses to specific questions in forms.

By looking at the full data model, you can determine the required fields for inclusion in the Power BI dashboard. Specifically, you may need the ‘total_allocated’ field from the Application table, the ‘Name’ columns from the Program and Round tables, and the ‘field_name’ and ‘field_value’ columns from the Response table. Therefore, we should select the Application, Program, Round and Response tables. 

Additional tips

Refreshing data

As the OData service provides live data, to automatically sync any new data from SmartyGrants, you can refresh the connection to get the latest data. Go to Home > Refresh in the PowerBI ribbon to update the data from the OData feed:

Editing queries

Power Query Editor in Power BI is a tool that allows users to connect to your OData feed, and transform that data before loading it in for analysis and visualization.

Once you’ve connected to the OData feed, Power Query Editor opens allowing you to clean, reshape and transform your data.

Key transformations include:

After performing the desired transformations, select Close & Apply. This will load the transformed data into Power BI.

Troubleshooting

When connecting to an OData feed, you might encounter various issues. Below are some common troubleshooting steps to help resolve OData connection problems:

Authentication and connection issues

1. Check the OData Service URL: Ensure the OData service URL is correct and properly formatted. It should be https://bi.smartygrants.com.au/api/odata4/service/v1/.

2. Check you are using the correct credentials: Verify that you are using the correct username and password. The username is the same you use for SmartyGrants followed by # and your OData API key. For example, if your SmartyGrants username is jennyp@example.com and your OData API key is 5555, the username you should enter is jennyp@example.com#5555. The password is the same one you use to log in to SmartyGrants.

3. Check you are selecting the correct Authentication Type. The connection should be Basic.

4. Read error messages: Carefully read any error messages provided. They often contain clues about what’s wrong, such as issues with authentication, the service URL, or data retrieval.

Common Errors:

Clearing cache

If you have previous connection credentials you may need to clear any cached data.

To clear the cache in Power BI:

1. Select File > Options and Settings > Options.

In the new window select the Data Load tab on the left-hand menu bar, then select Clear Cache under Data Cache Management Options. 

Troubleshooting with Postman

If you're experiencing missing data when making requests to the OData API, you can use Postman, a popular tool for API testing, to troubleshoot the issue. You can download Postman here.

Accessing the API in Postman

To access the OData API in Postman, enter in your SmartyGrants username and password in the Authorization section when creating a new API request:

In the Headers section, enter your API key:

Once you have authorised using your credentials, you can get your API Response.

Querying through Postman

To see the list of entities that can currently be queried through OData, enter in the OData endpoint URL (https://bi.smartygrants.com.au/api/odata4/service/v1):

Data can be queried by editing the request URL. You can access data for a single entity by adding the entity’s name to the end of the request URL:  https://bi.smartygrants.com.au/api/odata4/service/v1/{entity} 

For example, to access the entity ‘application’: https://bi.smartygrants.com.au/api/odata4/service/v1/application 

You can also view data for a single record by adding the ‘id’ inside brackets. For example, the URL below will only show application data for the record with ‘id’ 70394: https://bi.smartygrants.com.au/api/odata4/service/v1/application(70394) 

Query options

Supported OData query options: 

Query options are optional constraints on a query. They are added at the end of a request URL following the string form ?{query_options} = {value}: 
https://bi.smartygrants.com.au/api/odata4/service/v1/{entity}?{query_options}. 

An example of two query options $count and $top applied to the entity ‘application’: https://bi.smartygrants.com.au/api/odata4/service/v1/application?$count=true&$top=2.

Examine response data

When examining response data in Postman, it is important to start by checking the response status code. For instance, a status code of 200 OK indicates the requested infomrmation was returned successfully. See below for a list of common Response Status codes:

After assessing the status code, it’s crucial to review the response body in Postman. This allows you to see the actual data returned and compare it against expected results to identify any discrepancies or missing information.

Check for request limits

The SmartyGrants API has a limit on the maximum number of rows you can retrieve in a single query. Here’s how you can troubleshoot and manage this limit:

• OData Pagination: If the data set of a table is larger than 1000 rows, the API will use pagination (e.g., returning a @odata.nextLink URL for the next set of data).

• Handle Pagination in Postman: If pagination is used, manually follow the nextLink in Postman to fetch subsequent pages of data.