Payload definition
The following describes all payloads of the WebSocket API exposed by ActivePivot.
base URL:
ws://<server>:<port>/pivot/ws/v4
version:v4
General format
All messages sent to server are made of action and data attributes. action identifies the type of the object, allowing
to interpret the content of data.
Each payload defines its own action type.
All messages sent by the server have the same syntax. All messages contain an attribute status, indicating if the message reports
an error or not.
For successful messages, status = "success" and the message contains the attributes type identifying the type of the payload
and data with the content of the payload. Additional attributes can be defined depending on the query.
Payloads
MDX queries
Client payload
The following listing details payloads that can be sent by a client of the API. See the server payload listing for the responses to expect.
Query registration
Message registering a new query to the server.
Example:
{
"action": "REGISTER",
"data": {
"streamId": "abc",
"queryId": "1",
"mdxQuery": {
"mdx": "<mdx query>",
"context": {
"queriesTimeLimit": -1
}
},
"initialState": "STARTED",
"ranges": {
"0": {
"from": 0,
"to": 50
},
"1": {
"from": 0,
"to": 2000
}
}
}
}
Action: REGISTER
Key attributes:
streamId: unique id identifying a query throughout all its changes of definition.
It is the role of the client to define this id. Then, it will help her sort out the results for this query from the various messages received in the WebSocket.
The value ofstreamIdmust not already exist for any query defined inside the open WebSocket. It is safe to reuse stream ids across multiple WebSockets.queryId: unique id identifying a particular version of the query.
It is the role of the client to define this id, to assign a query result to the version of the query that produced it. Particularly, when updating a query to add a filter for example, it is important to be able to make the difference between the filtered and unfiltered results.mdxQuery: definition of the query to execute.ranges[SELECT query only]: ranges to receive in the result.initialState: Initial state of the query. Can only be "STARTED" - starting a query in real-time - or "PAUSED" for one-time queries.
Default: "STARTED".
Query update
Message updating an existing query. This can be used to update the definition of the query or changing its context.
Example:
{
"action": "UPDATE",
"data": {
"streamId": "abc",
"queryId": "42",
"mdxQuery": {
"mdx": "<mdx query>"
},
"ranges": {
"0": {
"from": 0,
"to": 50
},
"1": {
"from": 0,
"to": 2000
}
}
}
}
Action: UPDATE
Key attributes:
streamId: unique id identifying an existing query.queryId: unique id identifying the new version of the query. The query id must be different of the current query id. It is strongly recommended but not necessary that every query id for a given query be different. One can reuse the same values, at the risk of mixing the results.mdxQuery: definition of the query to execute.ranges[SELECT query only]: ranges to receive in the result.
Query range update
SELECT query only
Message updating the display ranges for an existing query. When redefining ranges, the whole range definition must be sent.
Example:
{
"action": "RANGE_UPDATE",
"data": {
"streamId": "abc",
"queryId": "42",
"ranges": {
"0": {
"from": 10,
"to": 20
},
"1": {
"from": 1000,
"to": 1500
}
}
}
}
Action: RANGE_UPDATE
Key attributes:
streamId: unique id identifying an existing query.queryId: unique id identifying the new version of the query.ranges: ranges to receive in the result.
Query pause
Pauses an existing query. A query already paused cannot be paused again. This will produce an error message with the stream id.
Example:
{
"action": "PAUSE",
"data": "abc"
}
Action: PAUSE
Key attributes:
streamId: unique id identifying an existing query.
Query resume
Resumes a paused query. A query already in real-time cannot be resumed again. This will produce an error message with the stream id.
Example:
{
"action": "RESUME",
"data": "abc"
}
Action: RESUME
Key attributes:
streamId: unique id identifying an existing query.
Query refresh
Refreshes a paused query. This effectively recomputes the query. This always produces a full view of the query.
Example:
{
"action": "REFRESH",
"data": "abc"
}
Action: REFRESH
Key attributes:
streamId: unique id identifying an existing query.
Query stop
Stops a running query forever. If this call succeeds, the query is destroyed. It is not possible to update the query anymore, pause it nor resume it.
Upon success, the stream id of the query can be reused.
Example:
{
"action": "UNREGISTER",
"data": "abc"
}
Action: UNREGISTER
Key attributes:
streamId: unique id identifying an existing query.
Server payload
The following listing details payloads that can be received by a client of the API. See the client payload listing for the responses to expect.
Query result
Basic structure returned for every query result. Depending on the defined type, the content of data changes.
For all results, status is set to "success".
Example: update of a DRILLTHROUGH query
{
"status": "success",
"type": "drillthroughUpdateData",
"streamId": "abc",
"queryId": "4",
"data": {
"epoch": 28475,
"addedOrUpdatedRows": {
"51": [76, "DeskB", "CHF", 8, ...],
...
}
}
}
Key attributes:
type: Type of the result.streamId: unique id identifying the query.queryId: unique id identifying the version of the query that produced the result.data: content of the result.
Cellset result
Example:
{
"status": "success",
"type": "cellSetData",
"streamId": "abc",
"queryId": "2",
"data": {
"epoch": 73,
"cube": "<cube name>",
"axes": [
{
"id": 1,
"hierarchies": [
{
"dimension": "Currency",
"hierarchy": "Currency"
},
...
],
"positions": [
[
{
"namePath": ["AllMember", "EUR"],
"captionPath": ["AllMember", "Euro"],
"properties": {
"DISPLAY_INFO": 0
}
},
...
],
...
],
"maxLevelPerHierarchy": [2]
}
],
"cells": [
{
"ordinal": 0,
"value": 160,
"formattedValue": "160",
"properties": {
"FORE_COLOR": null,
"FONT_FLAGS": 0,
"BACK_COLOR": null
}
},
...
],
"defaultMembers": [
{
"dimension": "Measures",
"hierarchy": "Measures",
"path": ["contributors.COUNT"],
"captionPath": ["Count"]
},
...
]
}
}
Type: cellSetData
Key attributes:
epoch: id of the epoch with the update.cube: name of the cube on which the query is running.axes: list of MDX SELECT axes defined by the SELECT query.idid of the axis.hierarchies: list of hierarchies expressed along this axis. The order of the hierarchies in this list is important, because this order is reused bypositionsandmaxLevelPerHierarchy.hierarchy: name of the hierarchy.dimension: name of the dimension holding the hierarchy.
maxLevelPerHierarchy: array of the maximal sizes of member paths per hierarchy. This follows the order ofhierarchies.positions: list of members along this axis. Axis members are made of several parts, one for each hierarchy of the axis.namePath: path of the member, as an array of member names along the level. Considering a hierarchy with levels ALL \ L1 \ L2, a member on L1 has the path["AllMember", "m1"].captionPath: list of captions for member values. This is different fromnamePath, whose path contains the string representation of the member, while this list contains the explicit caption.properties: map of properties of the member
cells: list of cells in the cellset.ordinal: unique id of the cell. Ordinals are computed from the positions of the members along the axis, by iterating over axis positions, starting from the axis with the higher id to the one with the lowest, i.e. from SECTIONS to SLICER. See the dedicated section for detailed example.value: raw value of the cell.formattedValue: cell value formatted by the measure formatter.properties: properties returned for the cell, indexed by the property names.
defaultMembers: list of default members of the query. This lists all default members for every hierarchy whose default member is not AllMember.dimension: name of the member dimension.hierarchy: name of the member hierarchy.path: path of the default member.captionPath: list of the captions of the member values.
Cell update
Example:
{
"status": "success",
"type": "cellData",
"streamId": "abc",
"queryId": "3",
"data": {
"epoch": 468,
"cells": [
{
"ordinal": 11,
"value": -29806.86890563747,
"formattedValue": "-29,806.87",
"properties": {
"FORE_COLOR": 255,
"FONT_FLAGS": 0,
"BACK_COLOR": null
}
},
...
]
}
}
Type: cellData
Key attributes:
epoch: id of the epoch with the update.cells: list of updated cells with their properties.
Real-time Cellset notification
Message providing a notification that a cellset has changed after an update.
Example:
{
"status": "success",
"type": "cellSetData",
"streamId": "abc",
"queryId": "4",
"data": {
"epoch": 28475
}
}
Type: cellSetData
Key attributes:
epoch: id of the epoch with the update.
Drillthrough result
Message providing a full DRILLTHROUGH query result.
This message contains the list of columns displayed in the result, followed by the list of all rows in the result.
Example:
{
"status": "success",
"type": "drillthroughData",
"streamId": "abc",
"queryId": "4",
"data": {
"epoch":15127,
"result": {
"headers": [
{
"name": "BumpedMtmDown",
"caption": "BumpedMtmDown",
"type": "Object"
},
{
"name": "BumpedMtmUp",
"caption": "BumpedMtmUp",
"type": "Object"
}
],
"rows":[
[0.0, "0.0"],
[0.5, "0.5"],
...
]
}
}
}
Type: drillthroughData
Key attributes:
epoch: id of the epoch with the update.result: the drillthrough result.headers: list of columns requested by the query.name: name of the column.caption: caption of the column.type: type of the column values.
rows: array of values aligned with the columns.
Drillthrough update
Message providing the updates on a DRILLTHROUGH query. This offers the diffs between the previous result and the updated state following the changes of the update.
Example:
{
"status": "success",
"type": "drillthroughUpdateData",
"streamId": "abc",
"queryId": "4",
"data": {
"epoch": 28475,
"addedOrUpdatedRows": {
"51": [76, "DeskB", "CHF", 8, ...],
...
},
"removedRows": [12, 43, ...]
}
}
Type: drillthroughUpdateData
Key attributes:
epoch: id of the epoch with the update.addedOrUpdatedRows: list of the new rows or updated rows, indexed by their row id. Each of the row is completely detailed, not only the fields that changed. Field values are listed in the order of the columns, as defined in the query and the full result.removedRows: list of row indexes removed by the update.
Real-time Drillthrough notification
Message notifying of an update in the DRILLTHROUGH result, without providing the content of the update.
Example:
{
"status": "success",
"type": "drillthroughData",
"streamId": "abc",
"queryId": "4",
"data": {
"epoch": 28475
}
}
Type: drillthroughData
Key attributes:
epoch: id of the epoch with the update.
Query error
Example:
{
"status": "error",
"error": ...,
"streamId": "qwanty",
"queryId": "10"
}
Key attributes:
error: error details.streamId: unique id identifying the query that failed.queryId: unique id identifying the version of the query that produced the failure.
Common elements
The following section lists elements that are shared between the various client or server payloads. This avoids the burden of redefining the same attributes or elements for each payload element.
Error message
Standard object returned in case of error. The status is set to "error". The attribute error contains the
details of the error.
This base message is often completed with many attributes, giving contextual information about the source of errors. For example, errors on query extends this object.
Example:
{
"status": "error",
"error": {
"errorChain": [
{
"type": "MdxException"
"message": "Pnl.SUM does not exist"
},
{
"type": "MdxException"
"message": "Invalid query"
},
...
],
"stackTrace": "..."
}
}
Error details
Details on the error that occurred on server-side.
Example:
{
"errorChain": [
{
"type": "MdxException"
"message": "Pnl.SUM does not exist"
},
{
"type": "MdxException"
"message": "Invalid query"
},
...
],
"stackTrace": "..."
}
Key attributes:
errorChain: chain of the error stack that occurred on server-side. The elements in the chain are ordered from the root cause to the top-level error. For each chain item,typegives the name of the error, followed bymessageexplaining the reason for the error.stacktrace: developer attribute containing the detailed Java stacktrace of the error.
Query definition
Object defining an MDX query, SELECT or DRILLTHROUGH, that can be run by the server.
{
"mdx": "SELECT ... FROM [cube]",
"context": {
"queriesTimeLimit": -1,
"mdx.casesensitive": true
}
}
Key attributes:
mdx: query to execute.context: map of flat context values as[<context value name>] = <context value>. The value is converted on server-side to a real object.
Query Ranges
Object defining the ranges to provide in a result. This allows to paginate a cellset result.
Ranges are defined for each axis of a SELECT query. Ranges are indexed in the map by the axis id.
Example:
{
"0": {
"from": 0,
"to": 50
},
"1": {
"from": 0,
"to": 2000
}
}
Key attributes:
from: start index in the list of positions (included).to: end index in the list of positions (excluded).
List of axis ids
Enum of the indexes referring to MDX cellset axes.
- -1 = SLICER
- 0 = COLUMNS
- 1 = ROWS
- 2 = PAGES
- 3 = CHAPTERS
- 4 = SECTIONS
Note: the slicer axis is a special axis defined by the WHERE clause in a SELECT query.
Miscellaneous
Computing cell ordinals
Ordinals are computed from the positions of the members along the axis, by iterating over axis positions, starting from the axis with the higher id to the one with the lowest, i.e. from SECTIONS to SLICER.
Let's compute some ordinals using the following cellset. In this example, we have 2 measures on COLUMNS and 2 hierarchies on ROWS: Currency and City.
{
"data": {
"axes": [
{
"id": 0,
"hierarchies": [
{
"dimension": "Measures",
"hierarchy": "Measures"
}
],
"positions": [
[
{"namePath": ["Count"]}
],
[
{"namePath": ["pnl.SUM"]}
]
]
},
{
"id": 1,
"hierarchies": [
{
"dimension": "Currency",
"hierarchy": "Currency"
},
{
"dimension": "Geography",
"hierarchy": "City"
}
],
"positions": [
[
{"namePath": ["EUR"]},
{"namePath": ["New York"]}
],
[
{"namePath": [EUR"]},
{"namePath": [Paris"]}
],
[
{"namePath": [USD"]},
{"namePath": [Paris"]}
]
]
}
],
"cells": [...],
"defaultMembers": [...]
}
}
ROWS id is 1, COLUMNS id is 0. We must start iterating on columns.
The first position on COLUMNS is Count. The first position on ROWS is (EUR, New York),. Thus, the cell with ordinal
0 contains the value of Count for (EUR, New York).
Notice that the cell set automatically iterates over the hierarchies inside an axis. We don't have to manually iterate
over members of Currency and City.
For cell of ordinal 1, we move to the next position of COLUMNS. It is pnl.SUM. The cell with ordinal 1 is then
pnl.SUM for (EUR, New York).
There are no next positions on COLUMNS. We must look at the next member on ROWS, that is, (EUR, Paris). We also resume
the iteration on COLUMNS. The cell with ordinal 2 is then Count for (EUR, Paris).
Final example, cell of ordinal 4 is Count for (USD, Paris).