Operation description

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

ActivePivot WS API only offers running MDX queries, either SELECT or DRILLTHROUGH queries.
It is not possible to emit operational queries through this API, such as creating a new calculated member or KPI.

List of operations

MDX queries

Users 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 through the network.
Real-time queries can be paused to have time to analyze data. Real-time can be resumed after that.
At any time, the query can be updated, even before receiving the result for the previous query.

Start and stop a query

A new query is started by sending 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. The query is stopped by sending a stop request. Currently, no acknowledgment is sent if the query successfully stops.

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 will be 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:

1. Obtain a result for the query in its version 2 but not send it yet
2. Update the query to version 3
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 query or a DRILLTHROUGH query.

Along with the query, it is possible to define context values to apply for this specific queries. These context values will complete or override the standard context defined for the connected user.
Context values are resolved only once and will not dynamically change if their definition change. One must update the query to resolve once again the context values. Particularly, 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 will 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 will receive 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, a full cellset is received.
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 are made of the list of new or updated rows, with all values, or the ids of the rows to remove.

Real-time queries

At creation, users 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 may receive 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 twice a query or resuming twice a query will produce 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, one may not want to send the entire result through the network. To ask only for a part of the cellset, it is possible to select a range of cells to view, along each axis of the query.

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

It is possible to set ranges for only part of the query axes. Unspecified ranges default to "virtual" ranges covering all cells.

Notifications for changes

It is possible to ask the server to send notifications every time a real-time update occurs. This allows to explore a current result without running the risk of seeing it change and at the same time know when the current view is not up-to-date anymore.

This can be activated by setting the flag pushData to false in the registration request. It is not possible to edit it after. One must stop the query and restart it.

For SELECT queries configured for notifications, the client will first receive a full cellset of the results at the time of the query. After that, empty cellsets are received.

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

Errors

Errors that occurred during the execution of the query are sent to the client like any result. Errors in the query do not affect the query registration. It is possible to update the query to fix the error, pause/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. And it is not possible to manipulate it afterward.

See the error payload for more details.