MDX Drillthrough

DRILLTHROUGH
SELECT FROM (
    SELECT Filter(
        [Time].[HistoricalDates].[AsOfDate].Members, 
        IsDate([Time].[HistoricalDates].CurrentMember.MemberValue) 
        AND DateDiff("d",[Time].[HistoricalDates].CurrentMember.MemberValue, Now()) = 0) 
    ON COLUMNS FROM (
        SELECT {[Underlyings].[Products].[ALL].[AllMember].[LISTED].[OPTION], 
            [Underlyings].[Products].[ALL].[AllMember].[OTC].[BARRIER OPTION]} 
        ON COLUMNS FROM (
            SELECT TopCount(Filter([Bookings].[Desk].Levels(1).Members, 
                NOT IsEmpty([Measures].[delta.SUM])), 1, [Measures].[delta.SUM]) 
            ON COLUMNS FROM [EquityDerivativesCube])))

DRILLTHROUGH 
SELECT FROM [EquityDerivativesCube] WHERE [Bookings].[Desk].[ALL].[AllMember].[DeskA]
RETURN [Date], [Desk], [pnl]

DRILLTHROUGH 
MAXROWS 1000000 //Max number of rows
FIRSTROW 0 //the index of the first row to retrieve
SELECT FROM [EquityDerivativesCube] WHERE [Bookings].[Desk].[ALL].[AllMember].[DeskA] //Regular MDX Statement

DRILLTHROUGH
MAXROWS 1000000 //Max number of rows
FIRSTROW 1000000 //the index of the first row to retrieve
SELECT FROM [EquityDerivativesCube] WHERE [Bookings].[Desk].[ALL].[AllMember].[DeskA] //Regular MDX Statement

@QuartetExtendedPluginValue(
    intf = ICalculatedDrillthroughColumn.class,
    key = DoubleAdderColumn.PLUGIN_KEY)
public class DoubleAdderColumn extends ACalculatedDrillthroughColumn {
  private static final long serialVersionUID = 1L;
  public static final String PLUGIN_KEY = "DoubleAdder";
  public DoubleAdderColumn(String name, String fields, Properties properties) {
    super(name, fields, properties);
  }
  @Override
  public Object evaluate(Object[] underlyingFields) {
    final Double v1 = (Double) underlyingFields[0];
    final Double v2 = (Double) underlyingFields[1];
    // Return the sum
    if (v1 == null) {
      return v2;
    } else if (v2 == null) {
      return v1;
    } else {
      return v1 + v2;
    }
  }
  @Override
  public String getType() {
    return PLUGIN_KEY;
  }
}

/**
 * A drillthrough column set that is able to generate "on the fly"; that is to say when a
 * drillthrough query is launched (in real time or not), a set of new (calculated) drillthrough
 * columns. In this example, it creates six columns to display pnl values for each currency defined
 * in the sandbox.
 *
 * <p>There are two important things to notice. The first is the use of {@link
 * IDrillthroughQueryContext context} to generate new columns into which different attributes
 * deduced from the context are injected. The second is the fact that conversion rates are computed
 * when the columns are generated (when the query is launched). It means that in real time, the rate
 * applied to the pnl values are the ones computed when the query was launched. <b>The rate is and
 * must be the same as long as the continuous query lives.</b> This is mainly because calculated
 * columns can't have an associated streams and handlers like post processors.
 *
 * @see IDrillthroughQueryContext
 * @see PnlCurrencyColumn
 * @author ActiveViam
 */
@QuartetExtendedPluginValue(
    intf = ICalculatedDrillthroughColumnSet.class,
    key = PnlCurrencyColumnSet.PLUGIN_KEY)
public class PnlCurrencyColumnSet extends ACalculatedDrillthroughColumnSet {
  /** Serialization. */
  private static final long serialVersionUID = 1L;
  /** The logger. */
  private static Logger logger = Logger.getLogger(PnlCurrencyColumnSet.class.getName());
  /** Calculated column set plugin key. */
  public static final String PLUGIN_KEY = "PnlCurrencyColumnSet";
  protected static final String SEPARATOR = ",";
  /**
   * Key to retrieve from the {@link Properties} the prefix to apply to each column's name that will
   * be generated.
   */
  public static final String PREFIX_KEY = "prefix";
  /** Key to retrieve from the {@link Properties} the existing currencies. */
  public static final String CURRENCIES_KEY = "currencies";
  /** List of available currencies. */
  protected final String[] underlierCurrencies;
  /** Store the prefix retrieve from the {@link Properties}. */
  public final String baseName;
  /**
   * Constructor.
   *
   * @param properties properties associated to be given to this {@link
   *     ICalculatedDrillthroughColumnSet} to parameterize the implementation class.
   */
  public PnlCurrencyColumnSet(Properties properties) {
    super(properties);
    this.baseName = (String) properties.get(PREFIX_KEY);
    this.underlierCurrencies = ((String) properties.get(CURRENCIES_KEY)).split(SEPARATOR);
  }
  @Override
  public Collection<ICalculatedDrillthroughColumnDescription> generate(
      IDrillthroughQueryContext queryContext) {
    // Retrieve the IQueryCache from the context. It is used to get
    // access to the current database version.
    final IQueryCache queryCache = queryContext.getContext().get(IQueryCache.class);
    if (queryCache == null) {
      logger.log(Level.FINE, "The query cache is null, cannot retrieve the database version");
      return null;
    }
    // Get rates from the database for this version
    final Map<String, TObjectDoubleMap<String>> currencies =
        getAllRatesByCurrency(queryCache.getDatabaseVersion());
    // underlying field of the columns to generate
    final String underlyingFields =
        ACalculatedDrillthroughColumn.buildFieldProperty(RISK__PNL, "Currency");
    // For each currency, it creates a new column responsible for
    // giving the pnl in a given currency.
    final List<ICalculatedDrillthroughColumnDescription> descriptions = new ArrayList<>();
    for (final String underlierCurrency : underlierCurrencies) {
      // The rates for the given currency is added into properties
      // The calculated column generated will introspect it to
      // extract the conversion rate and convert the pnl value
      final Properties properties = new Properties();
      properties.put(PnlCurrencyColumn.RATE_KEY, currencies.get(underlierCurrency));
      // Use a different name for each column. Here the name depends on the currency.
      descriptions.add(
          new CalculatedDrillthroughColumnDescription(
              PnlCurrencyColumn.PLUGIN_KEY,
              baseName + " " + underlierCurrency,
              underlyingFields,
              properties));
    }
    return descriptions;
  }
  /**
   * Retrieve the all rates between each currency.
   *
   * @return a map containing the rates between each currency indexed by currency.
   */
  protected Map<String, TObjectDoubleMap<String>> getAllRatesByCurrency(
      final IDatabaseVersion databaseVersion) {
    final var query =
        databaseVersion
            .getQueryManager()
            .listQuery()
            .forTable(FOREX_STORE_NAME)
            .withoutCondition()
            .selectingAllTableFields()
            .toQuery();
    final ICursor cursor = databaseVersion.getQueryRunner().listQuery(query).runCurrentThread();
    final Map<String, TObjectDoubleMap<String>> currencies = new HashMap<>();
    for (final IRecordReader reader : cursor) {
      final String cur1 = (String) reader.read(FOREX_CURRENCY);
      final String cur2 = (String) reader.read(FOREX_TARGET_CURRENCY);
      final double rate = (double) reader.read(FOREX_RATE);
      if (!currencies.containsKey(cur1)) {
        currencies.put(cur1, new TObjectDoubleHashMap<>());
        currencies.get(cur1).put(cur1, 1.0d); // 1 for the currency itself
      }
      currencies.get(cur1).put(cur2, rate);
    }
    return currencies;
  }
  @Override
  public String getType() {
    return PnlCurrencyColumnSet.PLUGIN_KEY;
  }
}

/**
 * This {@link ILocationInterpreter} is able to create a {@link ICondition} that follows the
 * business logic of {@link TimeBucketHierarchy}. The condition will be used while performing a
 * drillthrough taking into account the location coordinates and subcubes on the {@link
 * TimeBucketHierarchy analysis hierarchy}.
 *
 * <p>Only trades whose date belong to the selected buckets (this selection can be deduced from
 * locations and subcubes) will appear in the drillthrough result.
 *
 * <p>Pay attention that this <b>object is shared</b> among queries so it must support concurrency
 * calls.
 *
 * @author ActiveViam
 */
public class TimeBucketLocationInterpreter extends DefaultLocationInterpreter {
  /** Bucket hierarchy property. */
  public static final String BUCKET_HIERARCHY_PROPERTY =
      ABucketerPostProcessor.BUCKET_HIERARCHY_PROPERTY;
  /** Bucketed Level property. */
  public static final String BUCKETED_LEVEL_PROPERTY =
      ABucketerPostProcessor.BUCKETED_LEVEL_PROPERTY;
  /** The ordinal of the bucket level. */
  public static final int BUCKET_LEVEL_ORDINAL = ABucketerPostProcessor.BUCKET_LEVEL_ORDINAL;
  /** Name of the analysis hierachy. */
  protected final String bucketHiearchyName;
  /** Property of the bucketed level. */
  protected final String bucketedLevel;
  /** The time bucketer used. */
  protected DefaultTimeBucketer timeBucketer;
  /** The current time in milliseconds. */
  protected final long now;
  /** The bucket level info. */
  protected ILevelInfo bucketLevelInfo;
  /** The bucketed level info. */
  protected ILevelInfo bucketedLevelInfo;
  /** The bucket hierarchy. */
  protected final IHierarchy bucketHierarchy;
  /**
   * Default constructor.
   *
   * @param properties some additional {@link Properties properties}
   */
  public TimeBucketLocationInterpreter(
      final IActivePivotVersion pivot,
      final ISelection underlyingView,
      final Properties properties) {
    super(underlyingView, properties);
    // It will create the bucket map.
    this.timeBucketer = new DefaultTimeBucketer();
    this.bucketedLevel = properties.getProperty(BUCKETED_LEVEL_PROPERTY);
    this.bucketHiearchyName = properties.getProperty(BUCKET_HIERARCHY_PROPERTY);
    this.now = System.currentTimeMillis();
    // Initialization
    this.bucketHierarchy = init(pivot);
  }
  /**
   * Initialization of the location interpreter. It is called each time a condition is created (i.e
   * per query).
   *
   * @param pivot the current cube
   * @return the analysis hierarchy (TimeBucketDynamic in this case)
   */
  protected IHierarchy init(IActivePivot pivot) {
    // Retrieve the bucket hierarchy.
    final IHierarchy bucketHierarchy = HierarchiesUtil.getHierarchy(pivot, bucketHiearchyName);
    if (bucketHierarchy.getLevels().size() != BUCKET_LEVEL_ORDINAL + 1) {
      throw new ActiveViamRuntimeException(
          "The bucket hierarchy must have exactly one level: "
              + bucketHierarchy.getName()
              + ", "
              + bucketHierarchy.getLevels());
    }
    // Extract the level info
    bucketLevelInfo = bucketHierarchy.getLevels().get(BUCKET_LEVEL_ORDINAL).getLevelInfo();
    // Retrieve the bucketed level info.
    bucketedLevelInfo = HierarchiesUtil.getLevel(pivot, bucketedLevel).getLevelInfo();
    return bucketHierarchy;
  }
  @Override
  public ICondition buildCondition(
      IActivePivot pivot, Collection<ILocation> locations, Collection<String> measures) {
    // The conditions built from the locations and taking into account the analysis hierarchy.
    final ICondition locationCondition = super.buildCondition(pivot, locations, measures);
    // The condition deduced from the subcubes.
    final var subCubesCondition = getSubCubeCondition(pivot.getContext(), bucketHierarchy);
    if (locationCondition == null && subCubesCondition == null) {
      return null;
    } else if (locationCondition != null && subCubesCondition != null) {
      return BaseConditions.and(locationCondition, subCubesCondition);
    } else if (locationCondition != null && subCubesCondition == null) {
      return locationCondition;
    } else return subCubesCondition; // Only the subcubes conditions is not null
  }
  @Override
  protected List<ICondition> locationConditions(
      List<? extends IHierarchy> hierarchies, ILocation location) {
    // First, compute the condition using the default behavior,
    // that is to say the one ignoring the analysis hierarchies.
    final List<ICondition> originalConditions = super.locationConditions(hierarchies, location);
    // Introspect the location
    if (LocationUtil.isAtOrBelowLevel(location, bucketLevelInfo)) {
      final Object[] values =
          LocationUtil.extractValues(location, new ILevelInfo[] {bucketLevelInfo});
      if (values != null) {
        // The condition deduced from the location.
        final var locationCondition = createBucketCondition(values);
        if (locationCondition != null) {
          originalConditions.add(locationCondition);
        }
      }
    }
    return originalConditions;
  }
  /**
   * Extract a {@link ICondition condition} from the subcubes.
   *
   * @param context the current pivot context
   * @param bucketHierarchy the bucket hierarchy (TimeBucketDynamic)
   * @return the created condition. It can be null.
   */
  protected ICondition getSubCubeCondition(
      IActivePivotContext context, IHierarchy bucketHierarchy) {
    // The condition deduced from the subcubes.
    ICondition subCubesCondition = null;
    // Introspect the sub cubes
    final ISubCubeProperties subCubes = context.get(ISubCubeProperties.class);
    if (subCubes != null) {
      final ISubCubeTree subCubeTree =
          subCubes.getSubCubeTree(
              bucketHierarchy.getHierarchyInfo().getDimensionInfo().getName(),
              bucketHierarchy.getName());
      if (subCubeTree != null) {
        if (subCubeTree == AllExcludedSubCubeTree.getInstance()) {
          subCubesCondition = BaseConditions.FALSE;
        }
        final int depth = subCubeTree.getRestrictionDepth();
        if (depth >= BUCKET_LEVEL_ORDINAL) {
          final Set<IAxisMember> axisMembers =
              subCubeTree.retrieveAxisMembers(
                  BUCKET_LEVEL_ORDINAL + 1, (IAxisHierarchy) bucketHierarchy);
          if (axisMembers != null) {
            final Object[] grantedBuckets = new Object[axisMembers.size()];
            int i = 0;
            for (final IAxisMember member : axisMembers) {
              grantedBuckets[i++] = member.getObjectPath()[depth];
            }
            subCubesCondition = createBucketCondition(grantedBuckets);
          }
        }
      }
    }
    return subCubesCondition;
  }
  /**
   * Create a {@link ICondition} from input buckets and relatively to the current time.
   *
   * @param buckets the bucket from which a {@link ICondition} will be created.
   * @return the deduced {@link ICondition}. It can be returned null.
   */
  protected ICondition createBucketCondition(Object[] buckets) {
    final NavigableSet<Object> elements = new TreeSet<>(bucketLevelInfo.getComparator());
    for (final Object value : buckets) {
      if (value != null) {
        elements.add(value);
      }
    }
    final Object lastBucket = elements.last();
    if (lastBucket != null) {
      final String fieldName = bucketedLevelInfo.getSelectionField();
      final FieldPath fieldExpression = underlyingView.findFieldByAlias(fieldName).getFieldPath();
      final Long lastDateElement = timeBucketer.getBoundary(this.now, lastBucket);
      final Calendar cal = Calendar.getInstance();
      cal.setTimeInMillis(lastDateElement);
      return BaseConditions.lessOrEqual(fieldExpression, cal.getTime());
    }
    return null;
  }
}

/**
 * A drillthrough executor that uses the {@link TimeBucketLocationInterpreter} as {@link
 * ILocationInterpreter}.
 *
 * @author ActiveViam
 */
@QuartetExtendedPluginValue(
    intf = IDrillthroughExecutor.class,
    key = TimeBucketDrillthroughExecutor.PLUGIN_KEY)
public class TimeBucketDrillthroughExecutor extends DrillthroughExecutor {
  private static final long serialVersionUID = 5_11_0L;
  /** The plugin key. */
  @SuppressWarnings("hiding")
  public static final String PLUGIN_KEY = "TIME_BUCKET_DT_EXECUTOR";
  /**
   * Default constructor.
   *
   * @param context the context needed for the execution of a drillthrough query
   * @param properties some additional {@link Properties properties}
   */
  public TimeBucketDrillthroughExecutor(DrillthroughSession context, Properties properties) {
    super(context, properties);
  }
  @Override
  public String getType() {
    return PLUGIN_KEY;
  }
  @Override
  protected ILocationInterpreter createLocationInterpreter(final Properties properties) {
    return new TimeBucketLocationInterpreter(
        this.context.getActivePivotVersion(), this.context.getCubeSelection(), properties);
  }
}