User Defined Aggregation Functions

DirectQuery delegates some aggregation work to the remote database. The core Atoti Server aggregation (such as SUM, AVG, MAX…) are natively converted to the remote database equivalent aggregations. However, if you write some custom aggregation functions (UDAF) you will also need to define how this aggregation is done in your remote database.

Sum product example

This example is based on a ClickHouse database and SQL but can easily be ported to other databases. For instance let’s consider a simple sum product on scalar values, which in SQL would be SUM(quantity * price). The measure is defined in the cube with a Copper UDAF, whether the cube runs on an in-memory database or on DirectQuery. Here is a simple implementation:

Copper.userDefinedAgg("QUANTITY", "UNIT_PRICE")
    .aggregationBuffer(Types.TYPE_DOUBLE)
    .pluginKey("MY_SUM_PRODUCT")
    .contribute((reader, agg) -> agg.addDouble(0, reader.readDouble(0) * reader.readDouble(1)))
    .merge((in, out) -> out.addDouble(0, in.readDouble(0)))
    .outputFromBuffer(0)
    .as("My Sum Product")
    .publish(context);

For this UDAF to be usable with DirectQuery, you must provide a SQL translation of it: a DirectQuery UDAF. DirectQuery uses the DirectQuery UDAF whenever it pushes the aggregation of the Copper UDAF to the external database. To build this DirectQuery UDAF you need:

the plugin key of the Copper UDAF, set with pluginKey(...). DirectQuery finds the DirectQuery UDAF of a measure through this key, so both UDAFs must declare the same one
the number of inputs of the SQL expressions (i.e. the number of SQL columns to read)
the SQL expression(s) resolving the aggregation function results(s)
the aggregation function(s) results types
the re-aggregation function, to query an aggregate table at a coarser granularity than the one it stores

You must provide as much aggregation results as needed in your registered Copper terminate function.

final UserDefinedSqlAggregationFunction udaf =
    UserDefinedSqlAggregationFunction.builder()
        .pluginKey("MY_SUM_PRODUCT")
        .inputsNumber(2)
        .sqlExpressionProviders(
            List.of(
                sqlColumnNames ->
                    "SUM(" + sqlColumnNames.getFirst() + " * " + sqlColumnNames.get(1) + ")"))
        .outputTypes(List.of(StandardTypes.DOUBLE))
        .reaggregable(IAggregationFunction.SUM_FUNCTION_PLUGIN_KEY)
        .build();

Finally, you can register the DirectQuery UDAF in the DirectQuery application.

final Application application =
    Application.builder(connector)
        .schema(schema)
        .managerDescription(managerDescription)
        .userDefinedSqlAggregationFunctions(List.of(udaf))
        .build();

This new Aggregation can now be used in a DirectQuery project and the computation will be pushed to the remote database.

final ICellSet result =
    IActivePivotQueryRunner.create()
        .withWildcardCoordinate(LevelIdentifier.simple("DATE"))
        .forMeasures("My Sum Product")
        .run(pivot);
GetAggregatesResultCellset.check(pivot, result).getHumanRepresentationOfCellSet();

DATE	My Sum Product
2022-02-03	580.0
2022-01-31	798.0
2022-02-04	258.0
2022-02-01	1030.0
2022-02-02	604.0

Re-aggregable UDAF

A DirectQuery UDAF must be re-aggregable for an aggregate table to serve queries at a coarser granularity than the one it stores. At the granularity of the aggregate table, DirectQuery reads its rows as they are and does not need a re-aggregation function. An aggregation function is re-aggregable if a function, capable of merging two aggregation results into one, exists. For instance, the sum product aggregation can be re-aggregated by the SUM aggregation function in the example above. DirectQuery applies the re-aggregation function in the external database, to the aggregate table column storing the UDAF. It is not used anywhere else: in memory, Atoti merges aggregates with the merge function of the Copper UDAF.

final UserDefinedSqlAggregationFunction udaf =
    UserDefinedSqlAggregationFunction.builder()
        .pluginKey("MY_SUM_PRODUCT")
        .inputsNumber(2)
        .sqlExpressionProviders(
            List.of(
                sqlColumnNames ->
                    "SUM(" + sqlColumnNames.getFirst() + " * " + sqlColumnNames.get(1) + ")"))
        .outputTypes(List.of(StandardTypes.DOUBLE))
        .reaggregable(IAggregationFunction.SUM_FUNCTION_PLUGIN_KEY)
        .build();

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

User Defined Aggregation Functions

Sum product example

Re-aggregable UDAF

​Sum product example

​

​Re-aggregable UDAF

Sum product example

Re-aggregable UDAF