Skip to main content
Pentaho Documentation

Implement Plug-in Extensions

Sometimes Analyzer for MongoDB's schema language is not flexible enough, or the MDX language is not powerful enough, to solve the problem at hand. What you want to do is add a little of your own Java code into the application, and a plug-in is a way to do this.

Each of these extensions is technically a Service Provider Interface (SPI); in short, a Java interface which you write code to implement, and which will be called at runtime. You also need to register an extension—usually somewhere in your schema.xml file—and ensure that it appears on the classpath.

Plug-ins include user-defined functions; cell, member and property formatters; and dynamic schema processors. Other extensions include Dynamic datasource XMLA servlet.

Some plug-ins, such as user-defined function, member formatter, property formatter, and cell formatter, can be implemented in a scripting language such as JavaScript. In this case, you do not need to write a Java class; you just enclose the script code within a Script element in the schema file. Extensions implemented in scripting languages do not generally perform as well as extensions implemented in Java, but they are much more convenient because you do not need to compile any code. Just modify the script code in the schema file and reload the schema. The shorter code-debug-fix cycle allows you to develop your application much faster. Once you have implemented the plug-in in script, if performance is still a concern, you can translate your plug-in into Java.

User-defined Function

A user-defined function must have a public constructor and implement the mondrian.spi.UserDefinedFunction interface.


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;
    }
} 
 

Declare it in your schema.


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

Then, use it in 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] 

If a user-defined function has a public constructor with one string argument, Analyzer for MongoDB will pass in the function's name. Why? This allows you to define two or more user-defined functions using the same class.


package com.example;

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

/**
 * A user-defined function which either adds one to or 
 * subtracts one from its argument.
 */
public class PlusOrMinusOneUdf implements UserDefinedFunction {
    private final name;
    private final isPlus;

    // public constructor with one argument
    public PlusOneUdf(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;
    }

    public String getDescription() {
        return "Returns its argument plus or minus 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) {
            if (isPlus) {
                return new Double(((Number) argValue).doubleValue() + 1);
            } else {
                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;
    }
} 

Register the two functions in your schema.

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

If you are tired of writing duplicate user-defined function declarations in schema files, you can pack your user-defined function implementation classes into a jar file with a embedded META-INF/services/mondrian.spi.UserDefinedFunction resource file. This resource file contains class names of implementations of interface mondrian.spi.UserDefinedFunction, one name per line.

For more details, you may look into src/main/META-INF/services/mondrian.spi.UserDefinedFunction in the source distribution and the Service Provider section of the specification of JAR files. User-defined functions declared by this means are available to all schemas in the JVM.

Caution: you cannot define more than one user-defined function implementations in one class when you declare user-defined functions in this way. One function will be loaded for each class, and given the name that the getName() method returns.

User-defined functions can also be implemented in a scripting language, such as JavaScript. These functions may not perform quite as well as Java UDFs or built-in functions, but they are a lot more convenient to implement.

To define a UDF in script, use the Script element and include within it the following functions.

  • getName()—optional; defaults to the name attribute on the UserDefinedFunction element
  • getDescription()—optional; defaults to the name
  • getReservedWords()—optional; returns the empty list
  • getSyntax()—optional; defaults to mondrian.olap.Syntax.Function
  • getParameterTypes()—similar to the meanings in the UserDefinedFunction SPI
  • getReturnType(parameterTypes)—optional; returns list of parameterTypes
  • execute(evaluator, arguments)—similar to the meanings in the UserDefinedFunction SPI
 

Here is an example of the factorial function as a JavaScript UDF.


<UserDefinedFunction name="Factorial">
<Script language="JavaScript"><![CDATA[
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 modifies the behavior of Cell.getFormattedValue(). The class must implement the mondrian.spi.CellFormatter interface, and is specified like this.


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

You can specify a formatter in a scripting language such as JavaScript, using the Script element.


<Measure name="name">
<CellFormatter>
<Script language="JavaScript"><![CDATA[
]]></Script>
</CellFormatter>
</Measure>

The script has available a value variable, corresponding to the parameter of the mondrian.spi.CellFormatter.formatCell(Object value) method. The code fragment can have multiple statements, but must end in a return statement.

For a calculated member that belongs to a cube, you can also use the CellFormatter element.


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

You can also define a formatter by setting the CELL_FORMATTER property of the member to the name of the formatter class.


<CalculatedMember name="name" formatter="com.example.MyCellFormatter">
<CalculatedMemberProperty name="CELL_FORMATTER" value="com.example.MyCellFormatter"/>
</CalculatedMember>

For a calculated measure defined in the WITH MEMBER clause of an MDX query, you can set the same property in the MDX to achieve the same effect.


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]

To define a scripted formatter, use the CELL_FORMATTER_SCRIPT and CELL_FORMATTER_SCRIPT_LANGUAGE properties.


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]

The cell formatter property is ignored if a member does not belong to the [Measures] dimension.

Member Formatter

A member formatter modifies the behavior of Member.getCaption(). The class must implement the mondrian.spi.MemberFormatter interface, and is specified like this.


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

You can specify a formatter in a scripting language such as JavaScript, using the Script element.


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

The script has available a member variable, corresponding to the parameter of the mondrian.spi.MemberFormatter.formatMember(Member member) method. The code fragment can have multiple statements, but must end in a return statement.

Property Formatter

A property formatter modifies the behavior of Property.getPropertyFormattedValue(). The class must implement the mondrian.spi.PropertyFormatter interface, and is specified like this.


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

You can specify a formatter in a scripting language such as JavaScript, using the Script element.


<Level name="name" column="column">
<Property name="MyProp" column="PropColumn">
<PropertyFormatter>
<Script language="JavaScript">
return member.getName().toUpperCase();
</Script>
</PropertyFormatter>
</Property>
</Level>

The script has available member, propertyName and propertyValue variables, corresponding to the parameters of the mondrian.spi.PropertyFormatter.formatProperty(Member member, String propertyName, Object propertyValue) method. The code fragment can have multiple statements, but must end in a return statement.

Dynamic Schema Processor

A dynamic schema processor implements the mondrian.spi.DynamicSchemaProcessor interface. It is specified as part of the connection string.


Jdbc=jdbc:odbc:MondrianFoodMart; JdbcUser=ziggy; 
JdbcPassword=stardust; 
DynamicSchemaProcessor=com.example.MySchemaProcessor 

The effect is that when reading the contents of the schema from a URL, Analyzer for MongoDB turns to the schema processor rather than Java's default URL handler. This gives the schema reader the opportunity to run a schema through a filter, or even generate an entire schema on the fly.

When DynamicSchemaProcessor is specified, schema would be processed and reloaded on every ROLAP connection request. Property UseContentChecksum should be used along with a schema processor to enable caching of the schema.


DataSource=java:/jdbc/MyWarehouse; 
DynamicSchemaProcessor=com.example.MySchemaProcessor; 
UseContentChecksum=true

In this case, once loaded, schema would be cached until it changes. If schema content changes, it is be reloaded. The newly loaded schema is regarded as a different schema, and will start with empty caches.

Dynamic schemas are a very powerful construct. As you shall see, an important application for them is internationalization.