Operation description

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:

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