Table Endpoint

The /table endpoint allows you to submit table queries and receive the results. There is also:

A /table/saved endpoint that you can use to retrieve saved tables. Learn more.
A /table/sparse endpoint that returns only non 0 and non null values; this may be a better choice if you are generating large table queries that are likely to contain lots of null or 0 values. Learn more.

Overview

Endpoint	https://<server>/webapi/rest/v1/table
HTTP Method	POST

Request Headers

Accept-Language	The language that labels will be returned in (setting this is equivalent to changing the dataset and user interface language in SuperWEB2).	Optional. If not set, the server default language will be used.
APIKey	The API Key to use to authenticate this request. You can obtain your API key from the Account page in SuperWEB2.	Required in all requests.

Content-Type	Must be set to `application/json`.	Required in all requests.

Request Body

The body of the request contains your query. It must be in JSON format and contain the following objects:

Object	Description	Example
database	The URI of the dataset you want to query.	"database" : "str:database:bank"
measures	An array of IDs of all the measures (summation options) you want to return. For a count, the ID will take the form `str:count:<dataset>:<fact_table>` For a measure, it will be `str:statfn:<dataset>:<fact_table>:<measure>:<function>` where `<function>` is one of the following (depending on the statistical functions that have been enabled for the dataset): `SUM`, `MEAN`, or `MEDIAN`. You can obtain a list of available functions by passing the measure in to the `/schema` endpoint. See Schema Endpoint for more details.	"measures" : [ "str:count:bank:F_Customer", "str:statfn:bank:F_Customer:Cust_Profit:SUM" ]
recodes	(Optional). An object containing recodes for any of the fields specified in the request. Use this if you: Only want to see results for specific field values. For example, you are querying the Marital Status field and you only want results for the statuses of Single and Married (not Divorced, Unknown, or Not Applicable). Want to return results for combinations of field values. For example, you want to see results for the statuses Single and Divorced combined into a single cell (this is equivalent to creating a custom group in SuperWEB2). Want to request totals for any of the fields in the query. For each field you want to recode, you need to include the field's ID as a key and within that object set a `map` property to an array of all the field value IDs you want to return for this field. Each field value itself must be specified as an array: to combine field values into a custom group, simply specify multiple value IDs within that array. The example on the right shows a recode for the Marital Status field. In this example the API will return only two field values for the Marital Status field: one will be for the status Married (code: M), and the second will be the total of Single (code: S) and Divorced (code: D) combined into a single value. To request a total for a field, include the field's ID as a key and within that object set the `total` property to `true`. The example on the right requests a total for both the Gender field and the recoded Marital Status field. You can omit the `recodes` object entirely from your request, in which case all field values will be returned for all fields. If you choose to include the `recodes` object then you only need to specify the fields you actually want to recode or request a total for. Any fields that are included in your request in the `dimensions` object, but not specified in the `recodes` object will simply have all available field values returned. If you set `total` to `true` for a field, but do not include a `map` for that field, then you will get the totals for all values in that field.	"recodes" : { "str:field:bank:F_Customer:Marital_Status" : { "map" : [ [ "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:M" ], [ "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:S", "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:D" ] ], "total" : true }, "str:field:bank:F_Customer:Gender" : { "total" : true } }
udfs	(Optional). An object containing User Defined Fields (UDFs) that you want to include in the query. See Query for UDFs for more details about constructing a table query that includes UDFs.
dimensions	An array of IDs of the fields in each dimension (e.g., row, column, wafer) of your table. Each dimension must be specified as an array: to combine fields within an axis, simply specify multiple field IDs within that array (this will concatenate the fields on that axis).	"dimensions" : [ [ "str:field:bank:F_Customer:Marital_Status" ], [ "str:field:bank:F_Customer:Gender" ] ]

As shown here, all IDs specified in the request need to be in the ID format that is returned by the /schema endpoint.

The following is an example of a request to the API to obtain a count of Marital Status by Gender from the Retail Banking dataset:

CODE

{
    "database" : "str:database:bank",
    "measures" : [
        "str:count:bank:F_Customer"
    ],
    "recodes" : {
        "str:field:bank:F_Customer:Marital_Status" : {
            "map" : [
                [
                    "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:S"
                ],
                [
                    "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:M"
                ]
            ],
            "total" : true
        },
        "str:field:bank:F_Customer:Gender" : {
            "total" : true
        }
    },
    "dimensions" :
    [
        [
            "str:field:bank:F_Customer:Marital_Status"
        ],
        [
            "str:field:bank:F_Customer:Gender"
        ]
    ]
}

In this example:

The Marital Status field has been recoded so that only values for Single and Married will be returned.
No recodes are specified for the Gender field, so all available field values will be returned.
Totals have been requested for both fields.

Working with Hierarchies: Using Field or Valueset in Table Queries

When you specify fields in a table query, you can either use the str:field URI (as shown in the examples on this page) or the str:valueset URI.

For non-hierarchical fields, using the valueset in the dimensions will return an identical table result compared to using the field. For example, the following queries will return the same results:

CODE

{
  "database" : "str:database:bank",
  "measures" : [ "str:count:bank:F_Customer" ],
  "dimensions" : [                  
        [ "str:field:bank:F_Customer:Gender" ]
  ]
}

CODE

{
  "database" : "str:database:bank",
  "measures" : [ "str:count:bank:F_Customer" ],
  "dimensions" : [                  
        [ "str:valueset:bank:F_Customer:Gender:C_Gender" ]
  ]
}

However, for hierarchical fields, using the valueset allows you to choose which level of the hierarchy you want to use in the query.

For a hierarchical field:

Using str:field in the dimensions will add all of the values at the top level in the hierarchy to the query. For example, using str:field:bank:F_Customer:Area will add all the states from the sample Retail Banking Area field.
Using str:valueset allows you to choose a level in the hierarchy. For example:
- str:valueset:bank:F_Customer:Area:C_Geography_0 adds all values at the postcode level
- str:valueset:bank:F_Customer:Area:C_Geography_1 adds all the values at the State Suburbs level
- str:valueset:bank:F_Customer:Area:C_State will explicitly add the states (and will therefore return an equivalent table compared to using str:field:bank:F_Customer:Area)

You can use recodes with str:valueset but you must use the same type of URI to refer to a given field in both the recodes and dimensions sections (so, for example, if you use str:valueset:bank:F_Customer:Area:C_State in the dimensions you must also use str:valueset:bank:F_Customer:Area:C_State in the recodes section).

When defining recodes with hierarchical fields, using the str:field allows you to use values from any valueset available for that field (for example you could combine states and postcodes). Using the str:valueset will limit you to values from that valueset only.

Response Headers

X-RateLimit

X-RateLimit-Schema

X-RateLimit-Table

The global, schema, and table rate limits (if configured).

The individual headers are only returned if the corresponding rate limit has been set. If none of these headers are returned then that indicates that no rate limiting applies.

X-RateLimit-Remaining

X-RateLimit-Remaining-Schema

X-RateLimit-Remaining-Table

The number of requests remaining for the current rate limiting period. If this value drops to 0 then you will not be able to submit any further requests using this API key until the limit resets.

The individual headers are only returned if the corresponding rate limit has been set. If none of these headers are returned then that indicates that no rate limiting applies.

X-RateLimit-Reset

X-RateLimit-Reset-Schema

X-RateLimit-Reset-Table

The time when the rate limit will next be reset. This is expressed as a UNIX timestamp in milliseconds (milliseconds since January 1st 1970).

The individual headers are only returned if the corresponding rate limit has been set. If none of these headers are returned then that indicates that no rate limiting applies.

Response Body

The response contains the results of your table query. It contains the following objects:

Object Returns

query

The query that was submitted. This object contains the database, measures, recodes, and dimensions objects from your query.

The recodes object is returned in the response even if you did not include it in the query, or did not specify recodes for all fields. The response will contain the full list of returned field values for any fields that were not recoded in the original request. This allows you to use the recodes object in the response to determine what the returned cell values represent.

For example, the above sample query requests a table containing Marital Status and Gender, but only specifies a recode for the Marital Status field. The recodes object in the response would therefore include all field values for Gender, as well as the two specified field values for Marital Status.

database

Details of the dataset that was queried:

id	The SuperADMIN ID of this dataset.
uri	The Open Data ID of this dataset. This matches the ID format this is returned by the `/schema` endpoint.
label	The display name for this dataset. This is the label that is displayed in SuperWEB2. If the dataset is available in multiple languages, then the label will be returned in the language you specified in the `Accept-Language` header.
annotationKeys	An array of keys to annotations for this dataset. If any annotations are available, their descriptions will be returned in the `annotationMap` object.

measures

An array containing all the measures (summation options) returned for this query. For each measure, the API returns:

id	(Only returned in certain situations with weighted datasets). A temporary ID associated with a measure in queries that contain both weighted and unweighted versions of the same measure.
uri	The Open Data ID of this measure. This matches the ID format this is returned by the `/schema` endpoint.
label	The display name for this measure. This is the label that is displayed in SuperWEB2. If the dataset is available in multiple languages, then the label will be returned in the language you specified in the `Accept-Language` header.
annotationKeys	An array of keys to annotations for this measure. If any annotations are available, their descriptions will be returned in the `annotationMap` object.
measure	The ID of the measure.
function	The statistical function.
weight	(Only returned in certain situations with weighted datasets). The weighting applied to this measure.

fields

Details of the fields that were queried:

uri

The Open Data ID of this field. This matches the ID format this is returned by the /schema endpoint.

label

The display name for this field. This is the label that is displayed in SuperWEB2. If the dataset is available in multiple languages, then the label will be returned in the language you specified in the Accept-Language header.

items

An array containing all the field values returned for this field:

type	The field type (`RecodeItem`).
labels	The display name(s) for this field item. In most cases there will be a single label. However, if you have used the `recodes` in the query to combine multiple field items into a single value, then this will contain the labels of each constituent field value.
annotationKeys	An array of keys to annotations for this field item (or these field items, if you have combined multiple field items into a single value). If any annotations are available, their descriptions will be returned in the `annotationMap` object.
uris	The Open Data ID of this field item (or these field items, if you have combined multiple field items into a single value). This matches the ID format this is returned by the `/schema` endpoint.

annotationKeys

An array of keys to annotations for this field. If any annotations are available, their descriptions will be returned in the annotationMap object.

cubes

An array containing the results of the query.

There will be one item in this array for each measure you requested. This will contain both the values returned by the tabulation as well as the recommended precision for those values.

For example:

CODE

"cubes": {
    "<measure>": {
      "values": [ ... ],
      "precision": <precision>
    },
    "<measure>" {
      ...
    }
}

Please note that the API returns the full (unrounded) results of the tabulation, at the maximum available precision provided by the server. This is so that your application can perform any additional calculations without introducing rounding errors.

The precision value that is returned tells you the recommended number of decimal places that have been set by the system administrator for this measure in the display options catalog. If you want your application to match the values shown in SuperWEB2, then you should use the precision value to round the results accordingly before displaying them.

annotationMap

Any annotations that apply to this query. If there are annotations for the dataset or its fields, then the annotation keys and descriptions will be returned in this object.

For example, the following response is returned for the example query shown in the Request Body section above:

CODE

{
    "query": {
        "database": "str:database:bank",
        "measures": [
            "str:count:bank:F_Customer"
        ],
        "recodes": {
            "str:field:bank:F_Customer:Gender": {
                "map": [
                    [
                        "str:value:bank:F_Customer:Gender:C_Gender:M"
                    ],
                    [
                        "str:value:bank:F_Customer:Gender:C_Gender:F"
                    ],
                    [
                        "str:value:bank:F_Customer:Gender:C_Gender:U"
                    ],
                    [
                        "str:value:bank:F_Customer:Gender:C_Gender:-1"
                    ]
                ],
                "total": true
            },
            "str:field:bank:F_Customer:Marital_Status": {
                "map": [
                    [
                        "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:S"
                    ],
                    [
                        "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:M"
                    ]
                ],
                "total": true
            }
        },
        "dimensions": [
            [
                "str:field:bank:F_Customer:Marital_Status"
            ],
            [
                "str:field:bank:F_Customer:Gender"
            ]
        ]
    },
    "database": {
        "id": "bank",
        "uri": "str:database:bank",
        "label": "Retail Banking (Sample)"
    },
    "measures": [
        {
            "uri": "str:count:bank:F_Customer",
            "label": "Customers",
            "measure": "str:count:bank:F_Customer",
            "function": "COUNT"
        }
    ],
    "fields": [
        {
            "uri": "str:field:bank:F_Customer:Marital_Status",
            "label": "Marital Status",
            "items": [
                {
                    "type": "RecodeItem",
                    "uris": [
                        "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:S"
                    ],
                    "labels": [
                        "Single"
                    ]
                },
                {
                    "type": "RecodeItem",
                    "uris": [
                        "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:M"
                    ],
                    "labels": [
                        "Married"
                    ]
                },
                {
                    "type": "Total",
                    "labels": [
                        "Total"
                    ]
                }
            ]
        },
        {
            "uri": "str:field:bank:F_Customer:Gender",
            "label": "Gender",
            "items": [
                {
                    "type": "RecodeItem",
                    "uris": [
                        "str:value:bank:F_Customer:Gender:C_Gender:M"
                    ],
                    "labels": [
                        "Male"
                    ]
                },
                {
                    "type": "RecodeItem",
                    "uris": [
                        "str:value:bank:F_Customer:Gender:C_Gender:F"
                    ],
                    "labels": [
                        "Female"
                    ]
                },
                {
                    "type": "RecodeItem",
                    "uris": [
                        "str:value:bank:F_Customer:Gender:C_Gender:U"
                    ],
                    "labels": [
                        "Unknown"
                    ]
                },
                {
                    "type": "RecodeItem",
                    "uris": [
                        "str:value:bank:F_Customer:Gender:C_Gender:-1"
                    ],
                    "labels": [
                        "Not Applicable"
                    ]
                },
                {
                    "type": "Total",
                    "labels": [
                        "Total"
                    ]
                }
            ]
        }
    ],
    "cubes": {
        "str:count:bank:F_Customer": {
            "values": [
                [
                    59144,
                    53967,
                    10,
                    0,
                    113121
                ],
                [
                    30323,
                    43405,
                    8,
                    0,
                    73736
                ],
                [
                    89467,
                    97372,
                    18,
                    0,
                    186857
                ]
            ],
            "precision": 0
        }
    },
    "annotationMap": {}
}

These results are equivalent to generating a table similar to the following in SuperWEB2: