Table - Open Data API

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

Overview

Endpoint	http://<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 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:

Object	Description	Example
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:

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.

Response Headers

X-RateLimit	The rate limit configured for this server. By default, this is set to 100.
X-RateLimit-Remaining	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.
X-RateLimit-Reset	The time when the rate limit will next be reset. This is expressed as a UNIX timestamp in milliseconds (the number of milliseconds since January 1st 1970).

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

Each item specifies the measure, and then provides the values for each cell in the cube for that measure.

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