Cube Filters

Introduction

In ActivePivot, it is possible to restrict the view of a cube by granting access to a specific set of members. This is achieved using :

• ActivePivot entitlements: They provide a way to configure ISubCubeProperties context values characterizing the resources an authenticated user can access (see ActivePivot Security section for more details).
• Cube Filters: They represent filters that can hide members of an ActivePivot cube for a given query.

In this article, we explain the use case behind cube filters and detail how these objects can be manipulated to reach the wanted result.

Query filters generation is closely tied to the original MDX. Nevertheless, for the sake of clarity this topic is out of this article's scope. More details can be found in the MDX Filtering page.

Post-Processors and CubeFilters

Editing filters can be very handy, especially when working in an environment relying on automated MDX generation through a UI or using a third-party component that may not offer the possibility of editing the final MDX. An MDX generated filter may result in hiding the very members you want to do calculations on, thus producing confusing results.

ActivePivot allows post-processors to manipulate ICubeFilters to remove such member restrictions. This can be done using the Prefetcher API of the post-processors. PrefetchRequests are requests emitted by post-processors to notify the core product to prefetch the given aggregates on a given location with a defined scope expressed using an ICubeFilter. Those requests are typically created by the IPrefetcher of a post-processor. IPrefetchRequests also hold a context that can be used to store computations made in the prefetcher, so that they can be used within the post-processor's compute() method.

Note : When building a Prefetch Request, the ILocation corresponding to the required data for the post-processor computation can be different from the one from the query. One should make sure to provide all the required locations to the IPrefetchRequest.

In a post-processors chain, filters are propagated along the chain. This mean that each post-processor may edit the cube filter it received and pass it along to the underlying post-processors.

Use Case

A classic example would be the calculation of day-to-day differences. The following query calculates the differences of contributors.COUNT between each day and the day before.

WITH
 Member Measures.YesterdayName AS [Time].[HistoricalDates].CurrentMember.NextMember.Name
 Member Measures.Diff AS [Time].[HistoricalDates].CurrentMember - [Time].[HistoricalDates].CurrentMember.NextMember
SELECT
  NON EMPTY {
    [Measures].[contributors.COUNT],
    [Measures].[Diff],
    [Measures].[YesterdayName]
  } ON COLUMNS,
  NON EMPTY {
    [Time].[HistoricalDates].[AsOfDate].Members
  } ON ROWS
  FROM [EquityDerivativesCube]

The above query yields the following result:

Now let us change the initial MDX by selecting only the 24th and 25th of September.

WITH
 Member Measures.YesterdayName AS [Time].[HistoricalDates].CurrentMember.NextMember.Name
 Member Measures.Diff AS [Time].[HistoricalDates].CurrentMember - [Time].[HistoricalDates].CurrentMember.NextMember
SELECT
  NON EMPTY {
    [Measures].[contributors.COUNT],
    [Measures].[Diff],
    [Measures].[YesterdayName]
  } ON COLUMNS,
  NON EMPTY {
    [Time].[HistoricalDates].[AsOfDate].Members
  } ON ROWS
  FROM (
    SELECT
    {
      [Time].[HistoricalDates].[AsOfDate].[Mon Sep 25 02:00:00 CEST 2017],
      [Time].[HistoricalDates].[AsOfDate].[Sun Sep 24 02:00:00 CEST 2017]
    } ON ROWS
    FROM [EquityDerivativesCube]
  )

The result becomes:

Filtering introduced a result change for the 24th of September. This is because the previous day has been hidden by the Subselect, and so is not accessible through the top SELECT.

While it is possible to rewrite the MDX query without filtering to get the expected result, it can't be done by pure UI interactions. Even worse, the user may be completely stuck if the UI does not provide a way to edit the underlying MDX query (Excel, for example).

For such use cases, the ICubeFilter API provides the capability to directly edit the resulting Cube Filters, ignoring the query filter when needed and allowing more granular control over the queried space.

Building CubeFilters

ICubeFilters are immutable objects that once built cannot be modified. An ICubeFilter can only be obtained by calling the ICubeFilterBuilder.build() method.

There are two approaches for building a Cube Filter:

• From scratch using CubeFilter.builder(). This call will provide an ICubeFilterBuilder with no access restrictions by default.
• From an existing ICubeFilter using ICubeFilter.edit(). This will provide a builder including a copy of the permissions of the input ICubeFilter.

The fluent ICubeFilterBuilder API allows you to:

• Specify access to some measure with ICubeFilterBuilder.includeMeasure(String measureName).

• Grant or deny access to some members with various ICubeFilterBuilder.includeMembers(...) methods or ICubeFilterBuilder.excludeMembers(...) methods, respectively. The input of these methods specify the dimension, hierarchy, and the member path of the required members. Alternatively, ICubeFilterBuilder.includeMembersWithConditions(...) methods and ICubeFilterBuilder.excludeMembersWithConditions(...) support, on top of the member path specification, ICondition conditions to express more complex filtering rules.

• Intersect a given filter with another pre-existing filter with ICubeFilterBuilder.intersect(ICubeFilter filter).

• Clear restrictions on a given hierarchy using ICubeFilter.clear(...) methods.

The final ICubeFilter instance can be obtained by calling the ICubeFilterBuilder.build() method.

If the path to the members to grant access to is incomplete, then all members below are granted.

Examples :

CubeFilter.builder().includeMembers("Booking", "Desk", "AllMember", "DeskA", "Book3").build();

Creates a filter that has access to measures with a member path "AllMember/DeskA/Book3".

CubeFilter.builder()
    .excludeMembersWithConditions(
        "Booking", "Desk", new NotCondition(new EqualCondition("DeskA")))
    .build();

Creates a filter that has access to measures of members that do not correspond to "DeskA".

TrendPostProcessor Example

The following example illustrates the usage of the CubeFilter API in a Trend post-processor.

The CubeFilter built when defining the IPrefetcher relative to the post-processor (see init() and createPrefetchers()) will dynamically allow access to the correct dates through the filter created in computeFilter(). The computeLocation() method will include the location of all the data needed for performing the post-processor's computations.

/* (C) ActiveViam 2020
 * ALL RIGHTS RESERVED. This material is the CONFIDENTIAL and PROPRIETARY
 * property of ActiveViam Ltd. Any unauthorized use,
 * reproduction or transfer of this material is strictly prohibited
 */
package com.activeviam.biz.pivot.filtering.impl;
import com.activeviam.fwk.ActiveViamException;
import com.activeviam.pivot.postprocessing.impl.AAdvancedPostProcessor;
import com.qfs.literal.ILiteralType;
import com.qfs.util.impl.QfsArrays;
import com.quartetfs.biz.pivot.ILocation;
import com.quartetfs.biz.pivot.cellset.IAggregatesRetrievalResult;
import com.quartetfs.biz.pivot.context.subcube.ICubeFilter;
import com.quartetfs.biz.pivot.cube.hierarchy.ILevel;
import com.quartetfs.biz.pivot.cube.hierarchy.ILevelInfo;
import com.quartetfs.biz.pivot.cube.hierarchy.axis.IAxisHierarchy;
import com.quartetfs.biz.pivot.cube.hierarchy.axis.IAxisMember;
import com.quartetfs.biz.pivot.cube.hierarchy.impl.HierarchiesUtil;
import com.quartetfs.biz.pivot.cube.hierarchy.measures.IMeasureMember;
import com.quartetfs.biz.pivot.cube.hierarchy.measures.IPostProcessorCreationContext;
import com.quartetfs.biz.pivot.impl.LocationUtil;
import com.quartetfs.biz.pivot.postprocessing.IPostProcessor;
import com.quartetfs.biz.pivot.postprocessing.IPrefetcher;
import com.quartetfs.biz.pivot.query.aggregates.IAdvancedAggregatesRetriever;
import com.quartetfs.biz.pivot.query.aggregates.impl.PrefetchRequest;
import com.quartetfs.fwk.QuartetExtendedPluginValue;
import com.quartetfs.fwk.filtering.impl.InCondition;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import java.util.stream.Collectors;
/**
 * <b>TrendPostProcessor</b><br>
 * TrendPostProcessor does the difference between today and the day before if no measure retrieved
 * from the day before then return null.
 *
 * @author ActiveViam
 */
@QuartetExtendedPluginValue(intf = IPostProcessor.class, key = TrendPostProcessor.PLUGIN_KEY)
public class TrendPostProcessor extends AAdvancedPostProcessor {
  protected static final long serialVersionUID = 2722596108421670864L;
  public static final String PLUGIN_KEY = "TREND";
  public static final String LEVEL_DESCRIPTION_PROPERTY = "LEVEL";
  public static final String HIERARCHY_DESCRIPTION_PROPERTY = "HIERARCHY";
  protected static final String INCLUDE_PREFETCHER = "INCLUDE_PREFETCHER";
  protected ILevelInfo levelInfo = null;
  protected IAxisHierarchy hierarchy = null;
  protected Set<IMeasureMember> measures;
  public TrendPostProcessor(
      final String name, final IPostProcessorCreationContext creationContext) {
    super(name, creationContext);
  }
  @Override
  public void init(final Properties properties) throws ActiveViamException {
    properties.setProperty(OUTPUT_TYPE, ILiteralType.DOUBLE);
    super.init(properties);
    final String levelDescription = properties.getProperty(LEVEL_DESCRIPTION_PROPERTY);
    final ILevel level = HierarchiesUtil.getLevel(getActivePivot(), levelDescription);
    this.levelInfo = level.getLevelInfo();
    // below required for prefetching
    this.measures =
        this.underlyingMeasures.stream()
            .map(measureName -> this.measuresProvider.getMeasure(measureName))
            .collect(Collectors.toSet());
    // get the IHierarchy needed instance
    final String hierarchyDescription = properties.getProperty(HIERARCHY_DESCRIPTION_PROPERTY);
    this.hierarchy =
        (IAxisHierarchy) HierarchiesUtil.getHierarchy(getActivePivot(), hierarchyDescription);
  }
  @Override
  protected List<IPrefetcher<?>> initializePrefetchers(Properties properties) {
    final IPrefetcher<?> prefetcher =
        (location, filter) ->
            Collections.singleton(
                new PrefetchRequest<>(
                    computeLocation(location), computeFilter(location, filter), measures));
    return QfsArrays.mutableList(IPrefetcher.name(INCLUDE_PREFETCHER, prefetcher));
  }
  protected ICubeFilter computeFilter(final ILocation location, final ICubeFilter queryFilter) {
    final Object value = LocationUtil.getCoordinate(location, levelInfo);
    ICubeFilter filter = queryFilter;
    if (value == null) {
      // we deal with a range location here, we add all the required
      // members and edit the filter
      final List<? extends IAxisMember> members =
          queryFilter.retrieveMembers(hierarchy, levelInfo.getOrdinal());
      final Set<Date> membersToInclude = new HashSet<>();
      for (final IAxisMember member : members) {
        membersToInclude.add((Date) member.getDiscriminator());
        membersToInclude.add(TrainingUtils.retrievePreviousDate((Date) member.getDiscriminator()));
      }
      filter =
          queryFilter
              .edit()
              .includeMembersWithConditions(
                  levelInfo.getHierarchyInfo(), new InCondition(membersToInclude))
              .build();
    }
    return filter;
  }
  protected ILocation computeLocation(final ILocation location) {
    final Object value = LocationUtil.getCoordinate(location, levelInfo);
    if (value instanceof Date) {
      // we deal with a point location here
      // build range location with previous date,
      // use a Set having current and previous dates
      final Set<Object> dates = new HashSet<>();
      dates.add(value);
      dates.add(TrainingUtils.retrievePreviousDate((Date) value));
      return createModifiedLocation(location, dates);
    }
    // nothing to do here as we deal with a range location
    return location;
  }
  protected ILocation createModifiedLocation(final ILocation location, final Object membersToAdd) {
    if (LocationUtil.isAtLevel(location, levelInfo)) {
      final Object[] newMembers = LocationUtil.copyPath(location, levelInfo.getHierarchyInfo());
      newMembers[levelInfo.getOrdinal()] = membersToAdd;
      return LocationUtil.createModifiedLocation(
          location, levelInfo.getHierarchyInfo(), newMembers);
    }
    return location;
  }
  @Override
  public void compute(final ILocation location, final IAdvancedAggregatesRetriever retriever) {
    final IAggregatesRetrievalResult results = retriever.retrieveAggregates(INCLUDE_PREFETCHER);
    final int measureForResults = results.getMeasureId(underlyingMeasures.get(0));
    results.forEachPoint(
        (pointReader, rowId) -> {
          final int rowIdPrevious = results.getRow(retrievePreviousDateLocation(pointReader));
          final Double currentValue;
          final Double previousValue;
          if (rowIdPrevious != -1) { // -1 if the point does not exist
            currentValue = (Double) results.read(rowId, measureForResults);
            previousValue = (Double) results.read(rowIdPrevious, measureForResults);
            if (currentValue != null && previousValue != null) {
              retriever.write(retriever.addPoint(pointReader), currentValue - previousValue);
            }
          }
          return true;
        });
  }
  protected ILocation retrievePreviousDateLocation(final ILocation currentLocation) {
    final Date currentDate = (Date) LocationUtil.getCoordinate(currentLocation, levelInfo);
    return createModifiedLocation(currentLocation, TrainingUtils.retrievePreviousDate(currentDate));
  }
  @Override
  public String getType() {
    return PLUGIN_KEY;
  }
}