How to use the GTS OData API with Power BI

GTS publishes the tracks, ODK submissions and indicators of your Tracking Rounds through an Open Data service (OData v4). This page explains how an Organization Focal Point creates the account used by reporting tools and how to connect Power BI Desktop to it.

What is published, data freshness and volume limits are described for report builders in the User Guide: Open Data and Reporting (OData). This page only covers the account and the Power BI connection.

Tip

The screenshots below are based on Power BI Desktop version 2.124.1805.0 64-bit (December 2023). Menus may differ slightly in more recent versions.

Steps

1. Sign in to GTS and open the administration (Menu > Admin).

2. Open your Organization and the Dashboard Accounts tab.

3. Create a dedicated dashboard account, for example api_user (in lowercase), as explained in Creating Dashboard Accounts, or edit an existing account.

4. In the Role(s) section, turn on API access, then save.

5. Open Power BI Desktop and import the OData feed (see below).

6. Select the table(s) you need in your Power BI dashboard.

7. Build your Power BI report.

Role(s) step of a dashboard account: the API access switch is the last line

Important

• The account sees, through Open Data, all the Tracking Rounds of all the Organizations it belongs to (except the “All tracking data” rounds). Access cannot be limited to one Field Activity or Tracking Round.

• Anyone who knows the credentials can read all the tracks and form submissions of the Organization. Use a dedicated account, share its password only with the people who maintain the reports, and turn off API access when it is no longer needed.

• Turning API access on takes effect immediately. Other changes can take up to one hour to apply to Open Data: turning API access off, adding the account to or removing it from an Organization, creating a Tracking Round, or changing the password (the old password keeps working meanwhile).

Import an OData Feed

1. Click the Get Data button in the ribbon and select OData feed. If you do not see this option, click More… and type OData in the search bar at the top left.

2. Select Basic and enter the URL: https://gts.health/fastapi/odata/v1/

3. Select the Basic tab on the left and enter the user name and password of the account with API access. In the level field, select the same URL as in the previous step (https://gts.health/fastapi/odata/v1/). Then click Connect.

4. The data of your Organization is listed in the navigator. Three index tables help you find the right table:

   • indicator_table_names: the indicator tables (one per aggregation level) available for the Tracking Rounds of your Organization.

   • odk_table_names: the ODK submission tables (one per form and Tracking Round).

   • track_table_names: the track tables (one per Tracking Round).

Troubleshooting

• Power BI keeps old credentials: in Power BI, open File > Options and settings > Data source settings, click Clear permissions and connect again.

• “No tracking rounds allowed” or an authentication error: check the user name and password, that API access is turned on for the account, and that the account belongs to an Organization with at least one Tracking Round. Wait up to one hour after a change of Organization or Tracking Round.

• “your request exceeded your quota limit.”: the tracks or the Targeted Area indicators of the round are too large for the Open Data service (estimated above 20 GB for the whole round, typically the tracks of very large rounds), or the server is short of space. A filter in the standard track request does not avoid this error. For raw GPS tracks, see Export raw tracks with the GTS OData API. For indicator data, use the whole-area or base-layer tables.

• The first request on a table can be slow: GTS prepares a snapshot of the table first. See Data freshness in Open Data and Reporting (OData).