Open Data and Reporting (OData)¶

The dashboard downloads described in the previous pages produce one file at a time. For recurring reports, for example a weekly Power BI dashboard of Routine Immunization (RI) indicators, GTS also publishes its data through an Open Data service that follows the OData v4 standard. Reporting tools such as Power BI or Excel connect to it once and then refresh the data whenever needed, without manual exports.

This page is for data analysts and M&E officers who build reports. It describes what is published, how to get access and what to expect in terms of freshness and volume.

What is published¶

The Open Data service publishes, for every Tracking Round (TR) you are allowed to see:

Tracks

The GPS points recorded by field teams of the Field Activity during the dates of the TR: user name, team code, device (IMEI), GPS status, GPS and phone timestamps (UTC), latitude, longitude, accuracy and speed (m/s). All points are published, including those that are not valid for coverage (there is no validity column). If the TR has Targeted Areas, a point recorded inside a Targeted Area also gives that Targeted Area and its base layer areas (ID and label); these columns are empty for points outside the Targeted Areas.

ODK submissions

One table per ODK form and TR, for the forms that received at least one submission during the dates of the TR: submission date (UTC, as in the dashboard export, see ODK form submissions), upload date, device, user, team code, form, location, file name and all the answers of the form.

Indicators

One table per aggregation level of the TR: the whole area and, when the TR has Targeted Areas, each base layer (for example District, Commune, Fokontany, Locality in the MDG demo) and the Targeted Areas. Each row gives the area and its parent areas, its location, the number of Targeted Areas and Targeted Areas visited, the percentage visited, the teams reporting, the duration (in seconds) and the distance, plus the custom indicators of the TR (with numerator and denominator for percentages). At Targeted Area level, the rows also include the Targeted Area attributes and the first and last visit days. Figures are for the whole period of the TR (no daily or per-team breakdown).

Three index tables help you find the right table: track_table_names, odk_table_names and indicator_table_names. They list the available TRs, forms and aggregation levels with their names.

Note

Grouped indicators (for example by target type) are not published yet. The default “All tracking data” Tracking Rounds are not published either.

Who can use it¶

Open Data uses a regular GTS Dashboard Account (user name and password) that has API access:

  1. An Organization Focal Point opens the Organization in the administration, tab Dashboard Accounts.

  2. They create a dedicated account (for example api_user) or edit an existing one.

  3. In the Role(s) section, they turn on API access and save.

The account then sees, through Open Data, all the Tracking Rounds of all the Organizations it belongs to. Access cannot be limited to one Field Activity or one Tracking Round.

Tip

Use a dedicated account for reporting rather than a personal account, and share its password only with the people who maintain the reports. Anyone who has these credentials can read all the tracks and submissions of the Organization.

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

Connect Power BI¶

The step-by-step procedure, with screenshots, is described in the Administrator guide: How to use the GTS OData API with Power BI. In short:

  1. In Power BI Desktop, click Get Data and choose OData feed.

  2. Enter the service address: https://gts.health/fastapi/odata/v1/

  3. Choose Basic authentication and enter the user name and password of the account with API access.

  4. In the navigator, select the tables you need, for example indicator_table_names and the indicator table of your TR, then load them.

If Power BI keeps old credentials, clear them in File > Options and settings > Data source settings > Clear permissions and connect again.

Data freshness¶

Open Data is fed from the results computed by GTS. New tracks and forms therefore appear in Open Data after GTS has processed them (the “Last computed at” time shown on the map).

To keep reports fast, GTS prepares a snapshot of each table and compares it with the last computation of the TR:

  • if the last computation is less than one hour newer than the snapshot, the snapshot is returned;

  • between one and three hours newer, the snapshot is returned while GTS prepares a new one in the background;

  • more than three hours newer, GTS prepares the new snapshot before answering.

Snapshots are deleted every night (00:10 UTC). In practice:

  • data in Open Data can be one hour or more older than on the map while new data keeps arriving;

  • the first refresh of each table every day, or of a new TR, takes longer because the snapshot is prepared first;

  • a new form can take a few minutes to appear in odk_table_names, and a new TR up to about one hour in the index tables.

Volume limits¶

  • Large tables are returned in pages of 200,000 rows. Power BI follows the pages automatically.

  • To protect the platform, the tracks or the Targeted Area indicators of one TR are refused when their estimated size is above 20 GB (raised from 10 GB in Hotfix 3.9.1). The report then shows the error your request exceeded your quota limit. The same error can appear temporarily when the server is short of storage.

The size is estimated for the whole TR, before any filter: an OData $filter reduces the rows returned but does not avoid this error. When a TR is too large, use the whole-area or base layer indicator tables (they are not limited) or the dashboard track download.