ActivePivot Websocket API

ActivePivot provides a Websocket API for querying the cube.

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

ActivePivot WS API only offers MDX queries - either SELECT or DRILLTHROUGH.
You cannot run operational queries through this API, such as creating a new calculated member or KPI.

List of operations

MDX queries

You can run any MDX query using this API. The query can be started in real-time or one-time mode. MDX SELECT queries can define ranges of the result to look at, avoiding sending a massive result across the network.
To have time to analyze data, you can pause real-time queries, then resume them afterwards.
At any time, the query can be updated, even before receiving the result for the previous query.

Start and stop a query

To start a new query, send a registration request. In return, the client receives at least one full view. Then, if configured for real-time, the client will receive updates. At any time, the query can be updated by sending an update request. To stop the query, send a stop request. Currently, no acknowledgment is sent if the query stops successfully.

The following paragraphs describe the key features of queries. Explore the detailed payload for a list of all options.

Query identifiers

In the request, the client defines a streamId and a queryId. The streamId identifies the query throughout the updates while the queryId identifies a unique version of the query.

For example, the initial request defines the pair (streamId, queryId) to (abc, 1). The query is updated once and identified as (abc, 2) and a second time with (abc, 3).
The streamId is used by the server to update the appropriate query without recreating it. The queryId is used both by the server and the client to filter results from previous query versions. Due to the asynchronous nature of WebSocket, it is possible to:

Obtain a result for the query in its version 2 but not send it yet
Update the query to version 3
Send the result for version 2

Using the queryId, it is possible for clients to ignore updates marked for version 2.

Query definition

The query must be an MDX query. It can either be a SELECT or a DRILLTHROUGH query.

Along with the query, you can define context values to apply to this specific query. These context values complete or override the standard context defined for the connected user.

Context values are resolved only once and do not dynamically change if their definition changes. You must update the query to resolve the context values once again.

Specifically, MDX calculated measures are part of the context value MdxContext. Any change to the definition of those MDX measures will not affect the query until it is updated or restarted.

The results received from the query depend on the type of the MDX query. See Query Results for more information.

Query results

For SELECT queries, on registration success, whatever the query mode, the client receives an initial view, consisting of a cellset. If ranges are defined, the result contains only the requested selection.
By default, updates consist of the list of cells that changed. When new members appear or disappear, the client receives a full cellset.
When configured to produce notifications, updates consist only of notification objects.

For DRILLTHROUGH queries, the client receives a full drillthrough result, that is, an object with the list of drillthrough headers and the rows.
Updates comprise the list of new or updated rows, with all values, or the ids of the rows to remove.

Real-time queries

At creation, you can start the query in real-time mode - initialMode = STARTED - or in one-time mode - initialMode = PAUSED.

When paused, a query does not listen to changes to the underlying data. It can be refreshed manually by sending a refresh request.

Real-time queries are sensitive to real-time updates of the underlying data. Depending on the update, the client receives a cellset, a list of updated cells or simple notifications.

At any time, it is possible to pause a real-time query by sending a pause request or activate real-time for a paused query by sending a resume request. Pausing or resuming a query more than once produces an error.

Loading partial views of the result

This is restricted to SELECT queries only. For DRILLTHROUGH queries, as a workaround, use the query attributes FIRSTROW and MAXROWS.

When computing potentially large cellsets, you may not want to send the entire result across the network. To only request a portion of the cellset, you can select a range of cells to view, along each axis of the query.

Cellsets only contain the members belonging to the defined ranges. Note that the cell ordinals are still computed for the whole cellset. Ranges only affect the size of the content transferred to the listeners.

If needed, you can set ranges for only part of the query axes. Unspecified ranges default to "virtual" ranges covering all cells.

Notifications for changes

You can request the server to send notifications every time a real-time update occurs. This provides the ability to explore a current result without running the risk of seeing it change and at the same time know when the current view is no longer up-to-date.

To activate this, set the flag pushData to false in the registration request. You can no longer edit it afterwards. Instead you must stop the query and restart it.

For SELECT queries configured for notifications, the client first receives a full cellset of the results at the time of the query, then empty cellsets after that.

For DRILLTHROUGH queries configured for notifications, the client first receives a full list of headers and rows, then empty updates after that.

Errors

Errors that occurred during the execution of the query are sent to the client just like any result. Errors in the query do not affect the query registration. You can update the query to fix the error, pause or resume the query, refresh the query and ultimately stop it.

Errors when sending a request - existing streamId, bad queryId, invalid option - have no effect on the initial state of the query. Of course, a bad registration request does not create any query.

See the error payload for more details.

Payload Definition

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

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

When using pagination, the payload of the expected response includes an additional range element that includes the axisLength property indicating the total size of the axis.

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],
                "range": {
                    "from":0,
                    "to":99,
                    "axisLength":1007,
                    "entireAxis":false
                }
            }
        ],
        ...
    }
}

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

MDX queries​

Start and stop a query​

Query identifiers​

Query definition​

Query results​

Real-time queries​

Loading partial views of the result​

Notifications for changes​

Errors​

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​

MDX queries

Start and stop a query

Query identifiers

Query definition

Query results

Real-time queries

Loading partial views of the result

Notifications for changes

Errors

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