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

@AtotiExtendedPluginValue(
    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(final String name, final String fields, final Properties properties) {
    super(name, fields, properties);
  }
  @Override
  public Object evaluate(final 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
 */
@AtotiExtendedPluginValue(
    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(final 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(
      final 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 the bucketing. The condition will be used while performing a drillthrough
 * taking into account the location coordinates and subcubes on the Copper bucketing 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 {
  /** Time buckets indexed by their name. */
  public static final Map<Object, TimeBucket> bucketMap = new LinkedHashMap<>();
  static {
    bucketMap.put("1D", new TimeBucket(ChronoUnit.DAYS, 1));
    bucketMap.put("2D", new TimeBucket(ChronoUnit.DAYS, 2));
    bucketMap.put("3D", new TimeBucket(ChronoUnit.DAYS, 3));
    bucketMap.put("1W", new TimeBucket(ChronoUnit.WEEKS, 1));
    bucketMap.put("2W", new TimeBucket(ChronoUnit.WEEKS, 2));
    bucketMap.put("1M", new TimeBucket(ChronoUnit.MONTHS, 1));
    bucketMap.put("2M", new TimeBucket(ChronoUnit.MONTHS, 2));
    bucketMap.put("3M", new TimeBucket(ChronoUnit.MONTHS, 3));
    bucketMap.put("6M", new TimeBucket(ChronoUnit.MONTHS, 6));
    bucketMap.put("1Y", new TimeBucket(ChronoUnit.YEARS, 1));
    bucketMap.put("2Y", new TimeBucket(ChronoUnit.YEARS, 2));
    bucketMap.put("5Y", new TimeBucket(ChronoUnit.YEARS, 5));
    bucketMap.put("10Y", new TimeBucket(ChronoUnit.YEARS, 10));
    bucketMap.put("20Y", new TimeBucket(ChronoUnit.YEARS, 20));
    bucketMap.put("30Y", new TimeBucket(ChronoUnit.YEARS, 30));
    bucketMap.put("50Y", new TimeBucket(ChronoUnit.YEARS, 50));
    bucketMap.put("<30D", new TimeBucket(ChronoUnit.DAYS, 30));
  }
  public static NavigableMap<Long, Object> createBucketMap(final long timeReference) {
    final NavigableMap<Long, Object> result = new TreeMap<>();
    bucketMap.forEach(
        (bucketName, timeBucket) -> result.put(timeBucket.getBoundary(timeReference), bucketName));
    return result;
  }
  /**
   * Relative time bucket.
   *
   * @param unit The unit.
   * @param count The upper boundary of this bucket, in the given.
   */
  public record TimeBucket(ChronoUnit unit, int count) {
    /**
     * Gives the smaller instant that represent this bucket.
     *
     * @param reference The reference from which we should start.
     * @return The lower boundary of this bucket.
     */
    public long getBoundary(final long reference) {
      final ZonedDateTime ref = Instant.ofEpochMilli(reference).atZone(ZoneOffset.UTC);
      return ref.plus(count, unit).toInstant().toEpochMilli();
    }
    @Override
    public String toString() {
      return this.getClass().getSimpleName() + " of " + this.count + " " + this.unit;
    }
  }
  /** Bucket hierarchy property. */
  public static final String BUCKET_HIERARCHY_PROPERTY = "bucketHierarchy";
  /** Bucketed Level property. */
  public static final String BUCKETED_LEVEL_PROPERTY = "bucketLevel";
  /** The ordinal of the bucket level. */
  public static final int BUCKET_LEVEL_ORDINAL = 1;
  /** Name of the analysis hierachy. */
  protected final String bucketHiearchyName;
  /** Property of the bucketed level. */
  protected final String bucketedLevel;
  /** 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.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(final 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);
    // Retrieve the bucketed level info.
    bucketedLevelInfo = HierarchiesUtil.getLevel(pivot, bucketedLevel);
    return bucketHierarchy;
  }
  @Override
  protected List<ICondition> locationConditions(
      final List<? extends IHierarchy> hierarchies, final 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;
  }
  /**
   * 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(final 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 = bucketMap.get(lastBucket).getBoundary(this.now);
      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
 */
@AtotiExtendedPluginValue(
    intf = IDrillthroughExecutor.class,
    key = TimeBucketDrillthroughExecutor.PLUGIN_KEY)
public class TimeBucketDrillthroughExecutor extends DrillthroughExecutor {
  /** The plugin key. */
  public static final String PLUGIN_KEY = "TIME_BUCKET_DT_EXECUTOR";
  /**
   * Default constructor.
   *
   * @param context the context needed for the execution of a drillthrough query
   */
  public TimeBucketDrillthroughExecutor(final IDrillthroughSession context) {
    super(context);
  }
  @Override
  public String getType() {
    return PLUGIN_KEY;
  }
  @Override
  protected ILocationInterpreter createLocationInterpreter(
      final ISelection cubeSelection, final Properties properties) {
    return new TimeBucketLocationInterpreter(
        this.context.getActivePivotVersion(), cubeSelection, properties);
  }
}