Data query API examples

This module provides a set of examples demonstrating how to make queries against the GHO data webservice, athena.

Example 1 - Retrieving the list of available dimensions
Example 2 - Retrieving a list of available codes for a specific dimension
Example 3 - Retrieving data
Example 4 - Filtering data
Example 5 - Specifying an output format
Example 6 - Specifying alternate versions of a download format
Example 7 - Downloading data as an Excel file
Example 8 - Retrieving multiple targets
Example 9 - Using an alternate file name
Example 10 - using JSON
Example 11 - Retrieving only country or regional data
Example 12 - Retrieving data for countries in a specific region
Example 13 - Retrieving data using dimension member attributes
Example 14 - Retrieving a single dimension member's metadata
Example 15 - Generating cross tables
Example 16 - Adding a filter to a cross table
Example 17 - Exposing a dimension member attribute as a fact dimension

Example 1 - Retrieving the list of available dimensions

Simply specifying the athena/api/ URL will provide you with the list of available dimension codes. The default result is returned in the Observatory's XML schema. Dont forget to include the trailing slash (/) character.

http://apps.who.int/gho/athena/api/

Example 2 - Retrieving a list of available codes for a specific dimension

By appending a dimension code to the Athena data URL, you can retrieve the list of codes associated with that specific dimension. For example to retrieve the list of indicator codes, stored in the dimension GHO, use the following link:

http://apps.who.int/gho/athena/api/GHO

Example 3 - Retrieving data

Specify a target to download by specifying both a dimension and a code within that dimension. This will return all associated data for the target in the Observatory's XML format. Building on the previous example, take the code WHOSIS_000001, "Life expectancy at birth" and retrieve the data for this indicator using the following URL:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001

Example 4 - Filtering data

You can filter the data returned by the web service by identifying specific dimensions and dimension codes that you wish to include or exclude. Use the URL parameter filter with a list of fully qualified dimension codes separated by semicolons. A fully qualified dimension code consists of the dimension label, a colon, and the code for a member of that dimension. For example, the fully qualified code for the country of Botswana is

COUNTRY:BWA

Continuing to build on the query from the previous example, if you wish to see the life expectancy at birth data for Botswana, the query is:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001?filter=COUNTRY:BWA

This query still returns a lof of data, you can filter it further, for example if we wish to know the life expectancy in Botswana for both combined sexes for the year 2011, we will add the following codes to the filter:

YEAR:2011;SEX:BTSX

Giving us this query:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001?filter=COUNTRY:BWA;YEAR:2011;SEX:BTSX

Example 5 - Specifying an output format

There are two ways of specifying an output format. You can

Add an extension to the target object(s) you are retrieving, or,
You can specify the format query string parameter on the URL. If you specify both an extension, and, a format parameter, the format parameter will take precedence.

This allows you to download data in various formats, such as CSV:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001?format=csv

or HTML:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.html

You can combine this with the filter parameter, and any other parameter understood by the system to enable download of search results in whichever format you need. The query from example 4, life expectancy data for Botswana, rendered in CSV, becomes:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.csv?filter=COUNTRY:BWA

Example 6 - Specifying alternate versions of a download format

The web service allows you to specify different versions of an ouput format using the profile parameter. The available profiles depend on the particular format you wish to use. For example, the CSV format has in addition its default profile (that only shows the codes, and not the display text) a text profile that only shows the display text rather than the codes, and a verbose profile that shows both.
The query from the previous example can be modified to give is a CSV file with display text rather than codes as follows:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.csv?filter=COUNTRY:BWA&profile=text

Example 7 - Downloading data as an Excel file

You can download data as an Excel file in SpreadsheetML. This is selected using the excel profile of the xml format. Continuing on with our query, we get:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.xml?filter=COUNTRY:BWA&profile=excel

On some instances of windows, depending on version and configuration, the above link may not open into Excel and may instead only give you the option to download the file. You can work around this by using the precedence of the format parameter and giving an arbitrary extension to your target. Our query can be modified to look like it is downloading an Excel XLS file by changing the xml extension to xls and adding the URL query parameter &format=xml:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.xls?filter=COUNTRY:BWA&profile=excel&format=xml

Example 8 - Retrieving multiple targets

You can retrieve multiple target codes from a single query, as long as those target codes come from the same dimension, such as GHO Measure (GHO). Simply append the additional codes to the first target, separating them with commas (','). For example, we can retrieve both life expectancy at birth, WHOSIS_000001, and life expectancy at 60, WHOSIS_000015 using the query:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001,WHOSIS_000015

Example 9 - Using an alternate file name

Your browser will generally try to use specified target as the filename of what you are attempting to download. If you want to assign a specific file name, you can specify the target using the query string parameter target and use which ever filename you need in the target part of the URL. For example, if we wish to download the previous query as a file called life_expectancy, make the following query:

http://apps.who.int/gho/athena/api/GHO/life_expectancy?target=GHO/WHOSIS_000001,WHOSIS_000015

Note that you must still use a dimension code in the URL path in order for the URL to be properly parsed. In this above example with have kept the GHO dimension code but it is in fact a dummy value and any similar text can be used, for example, we can replace the GHO value with the text download:

http://apps.who.int/gho/athena/api/download/life_expectancy?target=GHO/WHOSIS_000001,WHOSIS_000015

You can combine the format, profile, and target parameters to make clean download links for constructs like Excel files. Continuing with our current example, this gives the query:

http://apps.who.int/gho/athena/api/download/life_expectancy.xls?target=GHO/WHOSIS_000001,WHOSIS_000015&format=xml&profile=excel

Example 10 - using JSON

The web service currently provides basic JSON support in the form of two formats. The default format (no profile needs to be specified) provides a JSON structure that is similar to the XML document structure. The second format provides a simplified view for data queries that resolves all of the dimension components of facts to proper display strings. The profile code for this is simple.
You can query the system for a metadata structure summary by simply invoking the json output format for the query described in Example 1:

http://apps.who.int/gho/athena/api/?format=json

You can query for the codelist of a dimension, as in Example 2. For example, you can retrieve the GHO COUNTRY list with this query:

http://apps.who.int/gho/athena/api/COUNTRY?format=json

And of course you can query for data and obtain the output in json. Continuing with the life expectancy example, we have:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.json

In order to use the simplified version of json, set the profile parameter to simple. Note that this only gives you a useful result on data queries, if you are querying for metadata only, you will not receive anything useful. Make a query as follows:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.json?profile=simple

If you need to use JSONP, so that the json text is wrapped in a function, use the query string parameter callback or jsonp and provide the name of the function you wish to use. to return the above query in a function called search_results use the query:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.json?callback=search_results

Example 11 - Retrieving only country or regional data

You can retrieve only country data and exclude regional data using the filter parameter. In addition to specifying specific fully qualified dimension member codes, you can also specify two special cases:

You can specify that a dimension must be set for a fact to be returned for a query by using an asterisk, '*', for example, filter=COUNTRY:* requires that any returned fact must have a country dimension
You can specify that a dimension must not be set for a fact to be returned for a query by using a hyphen, '-', for example, filter=COUNTRY:- requires that any returned fact must not have a country dimension.

If we wish to retrieve only country data for our previous example query, use COUNTRY:* as follows:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001?filter=COUNTRY:*

If we wish to retrieve only regional data for the same indicator, use COUNTRY:-;REGION:* as follows:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001?filter=COUNTRY:-;REGION:*

Example 12 - Retrieving data for countries in a specific region

You can obtain the list of WHO Region codes with the following query:

http://apps.who.int/gho/athena/api/REGION

You can then use one or more of these codes to restrict query results to countries belonging to the specified region(s). For example, if we to retrieve the life expectancy data for countries in Europe, we use the region code REGION:EUR and also specify COUNTRY:* because we are only interested in the country data itself and not the regional aggregate for this example:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001?filter=COUNTRY:*;REGION:EUR

Example 13 - Retreiving data using dimension member attributes

Filters can be specified to select dimension member codes based on their attribute values, using by specifying a filter of the form:

&filter=DIMENSIONCODE:*,ATTR1=VALUE1[,ATTR2=VALUE2]

For example, if you wish to retrieve data, say for life expectancy, for all countries whose ISO code starts with an A, use the following query:

http://apps.who.int/gho/athena/data/GHO/WHOSIS_000001.xml?filter=COUNTRY:*,ISO=A__

You must specify attribute codes that are recognized for the dimension you with to use. Several attributes can be specified by seperating them with a comma character. Multiple attributes will be ANDed together in order to derive the final list of dimension member codes for the specified dimension.

Example 14 - Retrieving a single dimension member's metadata

Metadata for a single dimension member can be queried by specifying the fully qualified code to the web service. A fully qualified dimemnsion member code consists of the dimension code, followed by a colon (':'), followed by the specific dimension member code. For example, Mexico is COUNTRY:MEX.

http://hqsudevlin.who.int:8086/athena/data/COUNTRY:MEX

The web service will return a complete metadata block that fully describes the requested code, including all attributes and a description of of the containing dimension (without including all of the other dimension members)

Example 15 - Generating cross tables

Cross tables are the basic unit of display in the system. The are transformed output from the XML generated by the web service and are accessed by specifying the format html and the profile xtab. In addition you must also supply three additional parameters:

x-sideaxis contains the dimensions that comprise the side axis of the table. These are rendered in the order that they are provided. Most tables in the GHO render countries on the side, this is accomplished by using x-sideaxis=COUNTRY.
x-topaxis contains the dimension that comprise the top axis of the data table. These are rendered in the order that they are provided. Most tables in the GHO render the indicator and year on the top axis. This is accomplished by using x-topaxis=GHO;YEAR
x-title is the name of the table. This is generally not displayed in the final output so you can put a single word token. Most GHO tables use x-title=table

A query that renders a basic version of the table showing life expectancy for all countries is:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.html?filter=COUNTRY:*&profile=xtab&x-sideaxis=COUNTRY&x-topaxis=GHO;YEAR&x-title=table

Notice that the cells contain more than one value. That's because the data that is retrieved by this query is disaggregated across more dimensions than simply COUNTRY and YEAR. If you click on "Details: off", you will see additional markup showing the extra dimension codes relevant to each fact. You can force the cross table to display in this mode by adding the parameter x-details=true:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.html?filter=COUNTRY:*&profile=xtab&x-sideaxis=COUNTRY&x-topaxis=GHO;YEAR&x-title=table&x-details=true

For production data such as we are accessing, the PUBLISHSTATE dimension code will always be PUBLISHED. The REGION code is also always linked to the country or if there is no country is data for the specified region. This leaves us the dimension SEX across which we disaggregate the data. We can add this to the top axis:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.html?filter=COUNTRY:*&profile=xtab&x-sideaxis=COUNTRY&x-topaxis=GHO;YEAR;SEX&x-title=table"

or if we want, we can add it to the side axis:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.html?filter=COUNTRY:*&profile=xtab&x-sideaxis=COUNTRY;SEX&x-topaxis=GHO;YEAR&x-title=table"

Ordering of the various elements is determined by metadata from the database.

Example 16 - Adding a filter to a cross table

You can add a filter to a cross table or any any other output by using the format html and the profile filter. You then specify the format and profile of the desired filtered output using the parameters x-format and x-profile. If the filtered output format itself requires parameters, you must specify them in the original query. These extra parameters will always be prefixed with an x-. To put a filter on the cross table that have generated for life expectancy data, use the following query:

http://apps.who.int/gho/athena/api/GHO/WHOSIS_000001.html?filter=COUNTRY:*&profile=filter&x-format=html&x-profile=xtab&x-sideaxis=COUNTRY&x-topaxis=GHO;YEAR;SEX&x-title=table

The filter is completely metadata driven and does not require any configuration.

Example 17 - Exposing a dimension member attribute as a fact dimension

You can expose a dimension member attribute, for example the WHO_REGION label attribute in the GHO's COUNTRY dimension members as a dimension in a web service query response. The new dimension will be created in the response's Metadata element and all facts will be updated as appropriate to include a element to represent the attribute as a dimension. This does not modify the content of the database in anyway. Since the new dimension appears at the XML level, all existing transformations will simply treat it as a regular dimension, allowing you to use it in crosstables, for filters, downloads, and graphs as desired. In order to expose one or more attribtues as dimensions, use the parameter ead=<attr1>;<attr2>;... <attr>. This is a semicolon delimeted list of fully qualified list of dimension attributes - these are denoted by the dimension label, followed by a '@' character, then the attribute label. For example in the GHO, the COUNTRY WHO region is denoted by the attribute WHO_REGION, so the folly qualified code is COUNTRY@WHO_REGION.
If you must make a reference to an exposed dimension, for example if you wish to use it in a crosstable axis definition, the name of the dimension is the dimension label, followed by an underscore '_' followed by the attribute label. The example above produces the dimension COUNTRY_WHO_REGION
Some examples:
Exposing the WHO coutry region label as a dimension:

http://hqsudevlin.who.int:8086/athena/data/GHO/WHOSIS_000001.xml?filter=COUNTRY:MEX&ead=COUNTRY@WHO_REGION

Now using the new dimension as part of a crosstable definition:

http://hqsudevlin.who.int:8086/athena/data/GHO/WHOSIS_000001.html?profile=xtab&x-sideaxis=COUNTRY;COUNTRY_WHO_REGION;YEAR&x-topaxis=GHO;SEX&filter=COUNTRY:MEX&ead=COUNTRY@WHO_REGION