Payload definition

This section describes all payloads of the WebSocket API exposed by ActivePivot.

base URL: ws://<server>:<port>/pivot/ws/v5
version: v5

General format

All messages sent to the server have action and data attributes. action identifies the type of the object, which then allows the content of data to be interpreted.
Each payload defines its own action type.

All messages sent by the server have the same syntax. They each 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 on 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. The id then serves to sort out the results for this query from the various messages received in the WebSocket.
The value of streamId must 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. It is particularly important to be able to differentiate between the filtered and unfiltered results, for example when updating a query to add a filter.
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. You can use this to update the definition of the query or change 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 not be the same as the current query id. It is strongly recommended, but not necessary, that every query id for a given query be unique. Although you can reuse the same values, you risk mixing up 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, you must send the whole range definition.

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 produces 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 produces 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 and always produces a full view of it.

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. You can no longer update, pause, or resume the query.

Upon success, the stream id of the query is then available to 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.
- id id 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 by positions and maxLevelPerHierarchy.
  - 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 of hierarchies.
- 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. For example, 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 from namePath, whose path contains the string representation of the member, while this list contains the explicit caption.
  - properties: map of member properties.
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 highest id to the lowest, meaning from SECTIONS to SLICER. See the 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.

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 row is completely detailed, not just the fields that changed. Field values are listed in column order, as defined in the query and the full result.
removedRows: list of row indexes removed by the update.

Real-time Drillthrough notification

Message notifying 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, providing 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 of the error that occurred on the 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 the server-side. The elements in the chain are ordered from the root cause to the top-level error. For each chain item, type gives the name of the error, followed by message explaining 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 the server-side to a real object.

Query Ranges

Object defining the ranges to provide in a result. This provides the ability 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

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 highest id to the lowest, meaning from SECTIONS to SLICER.

Let's compute some ordinals using the following cellset. In this example, we have two measures on COLUMNS and two 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 the cell with 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 further 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).

As a final example, the cell with ordinal 4 is Count for (USD, Paris).

ActivePivot

5.9.1

ActivePivot /pivot

ContentServer /register

General format

Payloads

MDX queries

Client payload

Query registration

Query update

Query range update

Query pause

Query resume

Query refresh

Query stop

Server payload

Query result

Cellset result

Cell update

Real-time Cellset notification

Drillthrough result

Drillthrough update

Real-time Drillthrough notification

Query error

Common elements

Error message

Error details

Query definition

Query Ranges

List of axis ids

Miscellaneous

Computing cell ordinals