Location-Based Queries

An ActivePivot cube can be queried in multiple ways, including - most commonly - via the MDX query language.

MDX is suitable for most business use cases, but behind the scene the ActivePivot MDX engine interprets MDX queries and decomposes them into more "primitive" queries that are defined in terms of cube locations.

This article describes the low-level API that supports the location-based queries implemented in ActivePivot.

Throughout this article, we assume that a cube instance is accessible through a variable named pivot (we don't discuss the type of cube instance we are dealing with or how to get it, as this is not the focus of this article). It is also assumed that the cube includes four hierarchies: Currency (AllMember enabled), Date (AllMember disabled), Trader (AllMember enabled) and Maturity (AllMember enabled).

GetAggregatesQuery

The goal of the GetAggregatesQuery (GAQ) is to retrieve all the aggregates that are defined by a set of locations and aggregated measures.

The following code block illustrates a GAQ querying all the locations with "EUR" as currency, and requesting the aggregated measures "Quantity.SUM" and "PositionValue.SUM". The queryResults contains the locations with the aggregated measures (together with their values) for each location.

//measures we want to focus on
Collection<String> measures= Arrays.asList("Quantity.SUM", "PositionValue.SUM");
//create the location to query all the locations content with EUR as currency
List<ILocation> locations = new ArrayList<ILocation>();
Object[][] arrayLocation =  new Object[][]{{ILevel.ALLMEMBER,"EUR"},{null},{ILevel.ALLMEMBER},{ILevel.ALLMEMBER}};
ILocation location = new Location(arrayLocation);
locations.add(location);
//execute the query
IGetAggregatesQuery aggregatesQuery = Registry.create(IGetAggregatesQuery.class, locations, measures);
ICellSet queryResults =  pivot.execute(aggregatesQuery);

DrillthroughQuery

A drillthrough query returns all the facts that have contributed to a range of selected locations.

In the example below, we query all the projections that contributed to all "EUR" locations.

//create the location to query the content of all the locations with EUR as currency
List<ILocation> locations = new ArrayList<ILocation>();
Object[][] arrayLocation =  new Object[][]{{ILevel.ALLMEMBER,"EUR"},{null},{ILevel.ALLMEMBER},{ILevel.ALLMEMBER}};
ILocation location = new Location(arrayLocation);
locations.add(location);
//execute the query
IDrillthroughQuery drillthroughQuery = Registry.create(IDrillthroughQuery.class, locations);
List<IDrillthroughRow> queryResults =  pivot.execute(drillthroughQuery);

Local Registration of a Location-Based Continuous Query

A GetAggregatesQuery or a DrillthroughQuery can be registered as a continuous query:

pivot.registerContinuousQuery("myQueryName", aggregatesQuery, null, Collections.singleton(listener));

The listener(s) specified on registration must be compatible with the type of query. Namely, IGetAggregatesContinuousQueryListener (shortcut for IContinuousQueryListener<ICellSet, IVersionedContinuousQueryUpdate<ICellSet>>) for a GetAggregatesQuery, or IContinuousQueryListener<List<IDrillthroughRow>, IContinuousQueryUpdate<List<IDrillthroughRow>>> for a DrillthroughQuery.

Here is an example of a simple printing listener for a GetAggregatesQuery:

final IGetAggregatesContinuousQueryListener listener = new IGetAggregatesContinuousQueryListener() {
    @Override
    public void viewUpdated(final IVersionedContinuousQueryUpdate<ICellSet> update) {
        System.out.println(
                "Received an update for version " + update.getVersion().getEpochId() +
                        " caused by the event " + update.getEvent() +
                        " for the query " + update.getContinuousQuery().getQuery());
        if (update.getRefreshed() != null) {
            // Print the new complete cellset
            System.out.println("Full refresh of the cellset: " + update.getRefreshed());
            return;
        }
        // Print the updated cells
        final ICellSet updatedAggregates = update.getAdded();
        if (updatedAggregates != null) {
            System.out.println("Updates aggregates: " + updatedAggregates);
        }
        // Print the removed aggregates (i.e. the values that disappeared from the cube)
        if (update.getRemoved() != null) {
            System.out.println("Removed aggregates: " + update.getRemoved());
        }
    }
    @Override
    public void registration(IContinuousQuery<? extends ICellSet, ? extends IVersionedContinuousQueryUpdate<ICellSet>> continuousQuery) {}
    @Override
    public void deregistration(IContinuousQuery<? extends ICellSet, ? extends IVersionedContinuousQueryUpdate<ICellSet>> continuousQuery) {}
};

The query name specified at registration time can be used to unregister the continuous query.

pivot.unregisterContinuousQuery("myQueryName");