Which web services should we use to send MDX queries to ActivePivot?

Question

In our company, users already query activepivot through standard front-end (Excel). But we would like to build a custom MDX application in Java to query ActivePivot. The goal is to be able to subscribe to several specific views and to retrieve aggregated data updates in real-time in order to feed several of our internal systems.

I know ActivePivot implements XMLA standard and has custom web services. But I was wondering what would be the best choice to send MDX queries and why ? does XMLA standard supports real time updates ?

score 2 · Answer 1 · answered Sep 20 '12 at 13:26

ActivePivot is indeed primarily queried using the MDX language. As you noted, there are a few different ways to send MDX queries to ActivePivot and get an answer.

The first and most standard way is to query ActivePivot with the XMLA standard. This is what happens, for instance, when you use Excel, Tableau or most GUIs that support MDX to query ActivePivot. This method, however, is only valid for ad-hoc queries. You cannot use ActivePivot's continuous queries feature with XMLA.

In order to register continuous queries, you need to use the existing web services that are shipped with ActivePivot, notably:

the IdGenerator service
the Streaming service
the LongPolling service

The general workflow is the following:

Create a listener thread that will register itself to the LongPolling service and listen to a specific domain name (say myDomain). This thread will, in a loop, listen for events and process them asynchronously.
Register the MDX query to the Streaming service. The stream property should tell it to publish it events to the myDomain domain.

At this point, the listener thread will receive the current result set for the registered MDX query, and will afterwards receive only the changed cells as their value changes with new transactions.

Here is a sample code for the query registration:

// Create my MDX query
final String mdx =
  "SELECT NON EMPTY {DrilldownLevel({[Bookings].[ALL].[AllMember]})} ON ROWS " + 
  "FROM [EquityDerivativesCube] " +
  "WHERE ([Measures].[contributors.COUNT])";
final IMDXQuery mdxQuery = new MDXQuery(mdx);

// Retrieve your various webservices
final IStreamingService streamingService = ...;
final ILongPollingService longPollingService = ...;
final IIdGenerator idGenerator = ...;

// Initiate a (long polling based) communication channel
final String listenerId = idGenerator.generateListenerIds(1)[0];
longPollingService.addListener(PUBLICATION_DOMAIN, listenerId);
new Thread(new Listener(longPollingService, listenerId)).start();

// Subscribe a continuous mdx query, events will be received
// through the communication channel.
final String mdxStreamId = idGenerator.generateListenerIds(1)[0];
final IStreamProperties mdxStreamProperties = new StreamProperties(
        mdxStreamId,
        PUBLICATION_DOMAIN,
        InitialState.STARTED,
        true);
streamingService.createStream(mdxQuery, mdxStreamProperties);

// From now on the listener will receive the real-time events

And here is a simple listener code:

public class Listener implements Runnable {

  final ILongPollingService service;
  final String listenerId;

  Listener(ILongPollingService service, String listenerId) {
    this.service = service;
    this.listenerId = listenerId;
  }

  @Override
  public void run() {
    for(int iteration = 0; iteration < 100; iteration++) {
      IBulkedStreamEvents events = service.listen(listenerId);
      if(events != null) {
        logger.log(Level.INFO, "Received events:");
        for(final IDomainStreamEvent domainEvent: events.getDomainEvents()) {
          for(final IStreamEvent event : domainEvent.getEvents())
            logger.log(Level.INFO, event.toString());
        }
      } else {
        logger.log(Level.INFO, "No events received.");
      }
    }
  }
}

Which web services should we use to send MDX queries to ActivePivot?

1 Answers1