Custom Member Properties

Introduction

Members have standard properties, such as names or captions (which are defined in the MDX standard). They are also allowed to have non-standard properties. These non-standard properties are called custom member properties. For example, in a hierarchy of the employees of a company, the employees (who are the members of the hierarchy) could have their phone numbers as properties. Custom member properties can be displayed on a pivot table delivered by Excel or Atoti UI. They can also be used to filter the result.

Defining a Custom Member Property

A custom member property is an extended plugin implementing the ICustomProperty interface. Here is an example of a custom member property that retrieves the date of the earliest trade for a given desk (additional snippet on the sandbox project):

@AtotiExtendedPluginValue(intf = ICustomProperty.class, key = DeskNextTradeDateProperty.PLUGIN_KEY)
public class DeskNextTradeDateProperty extends ADatabaseVersionAwareProperty {
  /** For serialization. */
  @Serial private static final long serialVersionUID = 6_01_14L;
  /** Plugin type. */
  public static final String PLUGIN_KEY = "DESK_NEXT_TRADE_DATE";
  /**
   * Constructs the plugin.
   *
   * @param name the name of the plugin
   */
  public DeskNextTradeDateProperty(final String name) {
    super(name);
  }
  public DeskNextTradeDateProperty(final String name, final String expression) {
    super(name, expression);
  }
  @Override
  protected Object getValue(final Object desk, final IDatabaseVersion databaseVersion) {
    // Warning: doing complex computations in this function will
    // hurt performance, especially when doing big cross-joins.
    //////////////////////////////////////////////////////////////
    // Retrieve all the trade dates corresponding to this desk,
    // using the condition query API.
    final var query =
        databaseVersion
            .getQueryManager()
            .listQuery()
            .forTable("Trade")
            .withCondition(BaseConditions.equal(FieldPath.of("Desk"), desk))
            .withAliasedFields(List.of(AliasedField.fromFieldName("Date")))
            .toQuery();
    Temporal nextDate;
    try (final ICursor results = databaseVersion.getQueryRunner().listQuery(query).run()) {
      //////////////////////////////////////////////////////////////
      // Iterate over the result to find the next trade date
      nextDate = null;
      while (results.next()) {
        // Read the first and only value, which is the date
        final Object result = results.getRecord().read(0);
        // Result might also be the N/A String
        if (result instanceof final Temporal date) {
          final ChronoUnit unit;
          if (date.isSupported(ChronoUnit.MILLIS)) {
            unit = ChronoUnit.MILLIS;
          } else {
            unit = ChronoUnit.DAYS;
          }
          if (nextDate == null || date.until(nextDate, unit) > 0) {
            nextDate = date;
          }
        } else {
          // Verifies that it was actually the N/A string
          assert result instanceof String && result.equals("N/A")
              : "Expected N/A String, but was: " + result;
        }
      }
    }
    return nextDate;
  }
  @Override
  public String getType() {
    return PLUGIN_KEY;
  }
  @Override
  public DeskNextTradeDateProperty copy() {
    return new DeskNextTradeDateProperty(this.name, this.expression);
  }
}

That custom member property can then be set for the desk in the cube definition:

StartBuilding.cube("DeskNextTradeDatePropertyCube")
    .withDimension("Booking")
    .withHierarchy("Desk")
    .withLevel("Desk")
    .withMemberProperty()
    .withNameAndExpression("nextTradeDate")
    .withPluginKey(DeskNextTradeDateProperty.PLUGIN_KEY)
    .build();

Accessing a Custom Member Property via MDX

There are two ways to access custom member properties via MDX:

Using the PROPERTIES keyword:

DIMENSION PROPERTIES [Booking].[Desk].[nextTradeDate]

Using the Properties function:

[Booking].[Desk].[ALL].[AllMember].[DeskA].Properties('nextTradeDate')

You can also filter a query based on the custom member property:

WITH
Member Measures.NextTrade AS [Bookings].CurrentMember.Properties(
  "nextTradeDate"
)
SELECT {
  [Measures].[contributors.COUNT]
} ON COLUMNS,
Filter(
  [Bookings].[Desk].Members,
  [Measures].[NextTrade] = "2021-01-02"
) ON ROWS
FROM [DeskNextTradeDatePropertyCube]

Viewing a Member Property in Excel

When connecting to Atoti Server from Excel, you can view member properties by hovering over the members. Alternatively, you can add a column to your table and display the member properties as a column. As of Excel Version 2101, a simple right-click on the pivot-table where a member displays will open a pop-up menu that contains the “Show Properties in Report” option. If the level corresponding to the member you right-clicked has defined custom properties, those properties will display and you can choose which member property to add to your report as a column.

Viewing a Member Property in Atoti UI

By default, member properties do not display in the Atoti UI PivotTable. If you add a member property via the PROPERTIES keyword to the MDX query, that member property will display when you hover over a cell that contains a member with that property:

SELECT NON EMPTY Hierarchize(
  DrilldownLevel(
    [Booking].[Desk].[ALL].[AllMember]
  )
) PROPERTIES [Booking].[Desk].[Desk].[nextTradeDate] ON ROWS
FROM [DeskNextTradeDatePropertyCube]

Releases and upgrades

Getting started

Concepts

Step 1: Bring your data into Atoti

Step 2: Design your Cube

Step 3: Build an Atoti application

Step 4: Query an Atoti application

Configure Atoti

Secure Atoti

Operate Atoti

Reference

Custom Member Properties

Introduction

Defining a Custom Member Property

Accessing a Custom Member Property via MDX

Viewing a Member Property in Excel

Viewing a Member Property in Atoti UI

​Introduction

​Defining a Custom Member Property

​Accessing a Custom Member Property via MDX

​Viewing a Member Property in Excel

​Viewing a Member Property in Atoti UI

Introduction

Defining a Custom Member Property

Accessing a Custom Member Property via MDX

Viewing a Member Property in Excel

Viewing a Member Property in Atoti UI