Skip to end of metadata
Go to start of metadata

The /table endpoint allows you to submit table queries and receive the results.

Overview

Endpoint
http://<server>/webapi/rest/v1/table
HTTP MethodPOST

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 Preferences 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:

ObjectDescriptionExample
database
The ID 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.
"measures" : [ "str:count:bank:F_Customer" ]
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
  }
}

 

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 table of Marital Status by Gender from the Retail Banking dataset:

{
    "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.

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:

ObjectReturns
query

The query that was submitted. This object contains the databasemeasuresrecodes, 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
annotationKeys
An array of keys to annotations for this dataset. If any annotations are available, their descriptions will be returned in the annotationMap object.
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.
measures

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

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.
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:

"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:

{
  "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",
    "annotationKeys": []
  },
  "fields": [
    {
      "uri": "str:field:bank:F_Customer:Marital_Status",
      "label": "Marital Status",
      "items": [
        {
          "type": "RecodeItem",
          "labels": [
            "Single"
          ],
          "uris": [
            "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:S"
          ]
        },
        {
          "type": "RecodeItem",
          "labels": [
            "Married"
          ],
          "uris": [
            "str:value:bank:F_Customer:Marital_Status:C_Marital_Status:M"
          ]
        },
        {
          "type": "Total",
          "labels": [
            "Total"
          ]
        }
      ]
    },
    {
      "uri": "str:field:bank:F_Customer:Gender",
      "label": "Gender",
      "items": [
        {
          "type": "RecodeItem",
          "labels": [
            "Male"
          ],
          "uris": [
            "str:value:bank:F_Customer:Gender:C_Gender:M"
          ]
        },
        {
          "type": "RecodeItem",
          "labels": [
            "Female"
          ],
          "uris": [
            "str:value:bank:F_Customer:Gender:C_Gender:F"
          ]
        },
        {
          "type": "RecodeItem",
          "labels": [
            "Unknown"
          ],
          "uris": [
            "str:value:bank:F_Customer:Gender:C_Gender:U"
          ]
        },
        {
          "type": "RecodeItem",
          "labels": [
            "Not Applicable"
          ],
          "uris": [
            "str:value:bank:F_Customer:Gender:C_Gender:-1"
          ]
        },
        {
          "type": "Total",
          "labels": [
            "Total"
          ]
        }
      ]
    }
  ],
  "measures": [
    {
      "uri": "str:count:bank:F_Customer",
      "label": "Customers"
    }
  ],
  "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:

  • No labels