Extensions: functions and formatters

Mondrian gives schema authors several extension points to tailor how MDX functions work, how cell values are displayed, and how member captions appear across languages. This page covers the extension points you are most likely to use day-to-day.

User-defined functions

A user-defined function (UDF) lets you call a custom Java (or scripted) function from any MDX expression. Mondrian calls it during query evaluation exactly as it would call a built-in function.

Writing a Java UDF

Your class must have a public no-argument constructor and implement the mondrian.spi.UserDefinedFunction interface. The following example adds one to its numeric argument:

package com.example;

import mondrian.olap.*;
import mondrian.olap.type.*;
import mondrian.spi.UserDefinedFunction;

/**
 * A simple user-defined function which adds one to its argument.
 */
public class PlusOneUdf implements UserDefinedFunction {

    // public constructor
    public PlusOneUdf() {
    }

    public String getName() {
        return "PlusOne";
    }

    public String getDescription() {
        return "Returns its argument plus one";
    }

    public Syntax getSyntax() {
        return Syntax.Function;
    }

    public Type getReturnType(Type[] parameterTypes) {
        return new NumericType();
    }

    public Type[] getParameterTypes() {
        return new Type[] { new NumericType() };
    }

    public Object execute(Evaluator evaluator, Exp[] arguments) {
        final Object argValue = arguments[0].evaluateScalar(evaluator);
        if (argValue instanceof Number) {
            return new Double(((Number) argValue).doubleValue() + 1);
        } else {
            // Argument might be a RuntimeException indicating that
            // the cache does not yet have the required cell value.
            // The function will be called again when the cache is loaded.
            return null;
        }
    }

    public String[] getReservedWords() {
        return null;
    }
}

Registering a UDF in your schema

Declare the UDF inside your <Schema> element, after your cubes:

<Schema ...>
  ...
  <UserDefinedFunction name="PlusOne" className="com.example.PlusOneUdf"/>
</Schema>

Using the UDF in MDX

Once registered, call the function from any MDX statement:

WITH MEMBER [Measures].[Unit Sales Plus One]
    AS 'PlusOne([Measures].[Unit Sales])'
SELECT
    {[Measures].[Unit Sales]} ON COLUMNS,
    {[Gender].MEMBERS} ON ROWS
FROM [Sales]

Sharing a class across multiple function names

If you give your class a public constructor that accepts a single String argument, Mondrian passes the function name to that constructor. This lets one class back multiple registered UDFs:

public class PlusOrMinusOneUdf implements UserDefinedFunction {
    private final String name;
    private final boolean isPlus;

    public PlusOrMinusOneUdf(String name) {
        this.name = name;
        if (name.equals("PlusOne")) {
            isPlus = true;
        } else if (name.equals("MinusOne")) {
            isPlus = false;
        } else {
            throw new IllegalArgumentException("Unexpected name " + name);
        }
    }

    public String getName() { return name; }
    // ... getDescription, getSyntax, getReturnType, getParameterTypes as before ...

    public Object execute(Evaluator evaluator, Exp[] arguments) {
        final Object argValue = arguments[0].evaluateScalar(evaluator);
        if (argValue instanceof Number) {
            if (isPlus) {
                return new Double(((Number) argValue).doubleValue() + 1);
            } else {
                return new Double(((Number) argValue).doubleValue() - 1);
            }
        } else {
            return null;
        }
    }

    public String[] getReservedWords() { return null; }
}

Register both names pointing at the same class:

<Schema ...>
  ...
  <UserDefinedFunction name="PlusOne"  className="com.example.PlusOrMinusOneUdf"/>
  <UserDefinedFunction name="MinusOne" className="com.example.PlusOrMinusOneUdf"/>
</Schema>

Auto-discovery via the service-provider mechanism

You can package your UDF implementations in a JAR and include a file at META-INF/services/mondrian.spi.UserDefinedFunction listing one fully-qualified class name per line. Mondrian discovers these automatically and makes them available to every schema loaded in that JVM — no per-schema declaration required.

Caution

Each class in a service-provider file backs exactly one function. If you need two function names from one class, use the schema-declaration approach with a named-argument constructor instead.

Scripted UDFs (JavaScript)

For simple logic you prefer not to compile, embed the implementation inside a <Script> child of <UserDefinedFunction>. The following functions must be present in the script:

• getParameterTypes()
• getReturnType(parameterTypes)
• execute(evaluator, arguments)

getName(), getDescription(), getReservedWords(), and getSyntax() are optional and default to sensible values based on the name attribute.

Here is a factorial function written in JavaScript:

<UserDefinedFunction name="Factorial">
  <Script language="JavaScript">
    function getParameterTypes() {
      return new Array(new mondrian.olap.type.NumericType());
    }
    function getReturnType(parameterTypes) {
      return new mondrian.olap.type.NumericType();
    }
    function execute(evaluator, arguments) {
      var n = arguments[0].evaluateScalar(evaluator);
      return factorial(n);
    }
    function factorial(n) {
      return n <= 1 ? 1 : n * factorial(n - 1);
    }
  </Script>
</UserDefinedFunction>

---

Cell formatter

A cell formatter overrides how Mondrian formats the raw value of a measure cell for display. It controls what Cell.getFormattedValue() returns. Your class must implement mondrian.spi.CellFormatter.

Attaching a formatter to a measure

<Measure name="Revenue">
  <CellFormatter className="com.example.MyCellFormatter"/>
</Measure>

Scripted cell formatter

<Measure name="Revenue">
  <CellFormatter>
    <Script language="JavaScript">
      var s = value.toString();
      while (s.length() &lt; 20) {
        s = "0" + s;
      }
      return s;
    </Script>
  </CellFormatter>
</Measure>

The script receives a value variable corresponding to the raw cell value. The fragment can have multiple statements but must end with a return statement.

Formatter on a calculated member

<CellFormatter> also works on <CalculatedMember> elements:

YAML

calculated_members:
- name: "Double Sales"
  dimension: "Measures"
  formula: "[Measures].[Unit Sales] * 2"
  cell_formatter:
    script:
      body: "var s = value.toString();\nwhile (s.length() < 20) { s = \"0\" +\
        \ s; }\nreturn s;"

XML

<CalculatedMember name="Double Sales" dimension="Measures">
  <Formula>[Measures].[Unit Sales] * 2</Formula>
  <CellFormatter>
    <Script language="JavaScript">
      var s = value.toString();
      while (s.length() &lt; 20) { s = "0" + s; }
      return s;
    </Script>
  </CellFormatter>
</CalculatedMember>

Formatter via CELL_FORMATTER property (MDX)

For calculated measures defined in the WITH MEMBER clause of an MDX query, set the CELL_FORMATTER property:

WITH MEMBER [Measures].[Foo]
  AS '[Measures].[Unit Sales] * 2',
  CELL_FORMATTER='com.example.MyCellFormatter'
SELECT {[Measures].[Unit Sales], [Measures].[Foo]} ON COLUMNS,
       {[Store].Children} ON ROWS
FROM [Sales]

For a scripted formatter inline in MDX, use CELL_FORMATTER_SCRIPT and CELL_FORMATTER_SCRIPT_LANGUAGE:

WITH MEMBER [Measures].[Foo]
  AS '[Measures].[Unit Sales] * 2',
  CELL_FORMATTER_SCRIPT_LANGUAGE='JavaScript',
  CELL_FORMATTER_SCRIPT='var s = value.toString(); while (s.length() < 20) s = "0" + s; return s;'
SELECT {[Measures].[Unit Sales], [Measures].[Foo]} ON COLUMNS,
       {[Store].Children} ON ROWS
FROM [Sales]

Note

The cell formatter property is ignored for members that do not belong to the [Measures] dimension.

---

Member formatter

A member formatter overrides how Mondrian formats the caption of a member. It controls what Member.getCaption() returns. Your class must implement mondrian.spi.MemberFormatter.

Attaching a formatter to a level

<Level name="Store Name" column="store_name">
  <MemberFormatter className="com.example.MyMemberFormatter"/>
</Level>

Scripted member formatter

<Level name="Store Name" column="store_name">
  <MemberFormatter>
    <Script language="JavaScript">
      return member.getName().toUpperCase();
    </Script>
  </MemberFormatter>
</Level>

The script receives a member variable — the Member being formatted. The fragment must end with a return statement.

---

Property formatter

A property formatter overrides how Mondrian formats the value of a member property. It controls what Property.getPropertyFormattedValue() returns. Your class must implement mondrian.spi.PropertyFormatter.

Attaching a formatter to a property

In Mondrian 4 the <Property> element lives inside an <Attribute> definition:

<Attribute name="My Attribute" keyColumn="attributeColumn" uniqueMembers="true">
  <Property name="My Property" column="propColumn">
    <PropertyFormatter className="com.example.MyPropertyFormatter"/>
  </Property>
</Attribute>

Scripted property formatter

<Attribute name="Store Name" keyColumn="store_name">
  <Property name="Store Type" column="store_type">
    <PropertyFormatter>
      <Script language="JavaScript">
        return member.getName().toUpperCase();
      </Script>
    </PropertyFormatter>
  </Property>
</Attribute>

The script receives member, propertyName, and propertyValue variables. The fragment must end with a return statement.

---

Localizing your schema

Mondrian lets you expose your schema in multiple languages without maintaining separate schema files. The approach combines two things: locale-specific caption and description values stored as <Annotation> elements, and the LocalizingDynamicSchemaProcessor class that rewrites the schema XML on the fly for each locale.

Using <Annotation> for locale-specific metadata

Every schema object (<Schema>, <Cube>, <Dimension>, <Hierarchy>, <Level>, <Measure>, named set, and so on) supports an <Annotations> child block. The Mondrian convention is to use dot-qualified annotation names for locale metadata:

YAML

cubes:
  Sales:
    caption: "Sales"
    annotations:
      caption.de_DE: "Verkaufen"
      caption.fr_FR: "Ventes"
      description.fr_FR: "Cube des ventes"
      description.de_AT: "Cube den Verkaufen"

XML

<Cube name="Sales" caption="Sales">
  <Annotations>
    <Annotation name="caption.de_DE">Verkaufen</Annotation>
    <Annotation name="caption.fr_FR">Ventes</Annotation>
    <Annotation name="description.fr_FR">Cube des ventes</Annotation>
    <Annotation name="description.de_AT">Cube den Verkaufen</Annotation>
  </Annotations>
  ...
</Cube>

Client tools that understand this convention can then display captions in the user’s locale. The YAML schema format supports these annotations directly under the annotations key of any element — see YAML schema reference for the full syntax.

Saiku-specific annotations

The same <Annotation> mechanism carries the saiku.semantic.* vocabulary that drives the AI Ask layer and PII protection — descriptions, synonyms, units, cardinality, grain, and the saiku.semantic.pii=true marker. See Saiku semantic annotations for the full key set and enforcement points.

Property-file substitution with LocalizingDynamicSchemaProcessor

For full server-side locale substitution, write your schema using %{key} placeholders in caption, description, allMemberCaption, and measuresCaption attributes:

<Schema name="FoodMart" measuresCaption="%{foodmart.measures.caption}">
  <Dimension name="Store" caption="%{foodmart.dimension.store.caption}"
             description="%{foodmart.dimension.store.description}">
    <Hierarchy hasAll="true"
               allMemberName="All Stores"
               allMemberCaption="%{foodmart.dimension.store.allmember.caption}"
               primaryKey="store_id">
      <Table name="store"/>
      <Level name="Store Country" column="store_country" uniqueMembers="true"
             caption="%{foodmart.dimension.store.country.caption}"
             description="%{foodmart.dimension.store.country.description}"/>
      <Level name="Store State" column="store_state" uniqueMembers="true"
             caption="%{foodmart.dimension.store.state.caption}"/>
      <Level name="Store City" column="store_city" uniqueMembers="false"
             caption="%{foodmart.dimension.store.city.caption}"/>
    </Hierarchy>
  </Dimension>

  <Cube name="Sales" caption="%{foodmart.cube.sales.caption}"
        description="%{foodmart.cube.sales.description}">
    ...
    <MeasureGroup table="sales_fact_1997">
      <Measures>
        <Measure name="Unit Sales" column="unit_sales"
                 caption="%{foodmart.cube.sales.measure.unitsales.caption}"
                 description="%{foodmart.cube.sales.measure.unitsales.description}"/>
      </Measures>
    </MeasureGroup>
  </Cube>
</Schema>

Provide a default properties file (locale.properties) and one per supported locale (locale_fr.properties, locale_de.properties, and so on). Each file maps the placeholder keys to translated strings:

# locale.properties — default (English)
foodmart.measures.caption=Measures
foodmart.dimension.store.caption=Store
foodmart.dimension.store.country.caption=Store Country
foodmart.dimension.store.state.caption=Store State
foodmart.dimension.store.city.caption=Store City
foodmart.dimension.store.allmember.caption=All Stores
foodmart.cube.sales.caption=Sales
foodmart.cube.sales.measure.unitsales.caption=Unit Sales

# locale_hu.properties — Hungarian
foodmart.measures.caption=Hungarian Measures
foodmart.dimension.store.caption=Áruház
foodmart.dimension.store.country.caption=Ország
foodmart.dimension.store.state.caption=Állam/Megye
foodmart.dimension.store.city.caption=Város
foodmart.dimension.store.allmember.caption=Minden Áruház
foodmart.cube.sales.caption=Forgalom
foodmart.cube.sales.measure.unitsales.caption=Eladott db

The LocalizingDynamicSchemaProcessor is enabled through the Mondrian connection string. On Saiku Cloud this is configured at the data-source level by your administrator. If you need schema-level locale substitution, contact support.

Note

When no caption attribute is set, a schema object’s display name defaults to its name attribute value. A hierarchy without its own caption inherits the caption of its parent dimension.

---

Advanced: self-hosted extension points

The three extension points below apply to self-hosted or embedded Mondrian deployments only. They require deployment-level configuration (connection strings, web.xml servlet declarations) and are not typically needed when using Saiku Cloud.

• Dynamic schema processor (mondrian.spi.DynamicSchemaProcessor): Intercepts schema loading to filter or generate the schema XML on every ROLAP connection. Used to inject locale or tenant-specific content at load time.
• Data source change listener (mondrian.spi.DataSourceChangeListener): Plugs into Mondrian’s cache invalidation path. When called, it signals whether underlying dimension or fact data has changed, triggering a cache flush.
• Dynamic datasource XMLA servlet (mondrian.xmla.impl.DynamicDatasourceXmlaServlet): An alternative XMLA servlet that reloads datasources.xml on every client request and selectively clears catalog caches for changed entries.

If you are running Mondrian embedded and need to implement any of these SPIs, refer to the Mondrian Javadoc for mondrian.spi.

---

Adapted from the Mondrian project schema guide (EPL v1.0).