MDX++ Meets Tabular: Next-Gen Aggregations

The multi-dimensional model, exposed through MDX, gives end users a very convenient way to query data. Once the model is built, users can simply drag and drop fields and let the engine take care of the calculations.

The idea is that you don’t need to worry about the underlying technology anymore, “just analyze.” That’s the usual pitch—and while it’s more or less true, the reality is more nuanced.

Where the Model Shows Its Cracks

As powerful as the dimensional model is, it has shortcomings. A trivial but common one is the calculation of a weighted average.

• MDX technically supports weighted averages, but only based on dimension members, not at the row level.
• Performance degrades when averaging hundreds of thousands (or even millions) of items.
• Out of the box, MDX handles sums, counts, and averages—but not row-based weighted averages.

The usual workaround:

• Add a calculated column at the row level (value × weight).
• When the model aggregates, divide summed products by total weight.

This works, but it’s clunky, error-prone, and bloats the model.

And that’s just the easy case. Real-world requirements often need logic that doesn’t play nicely with the OLAP “aggregate first, calculate later” philosophy.

More Complex Scenarios

1. Foreign Exchange (FX) Conversions

Financial transactions recorded in multiple currencies require row-level conversion before aggregation.

• If you sum EUR transactions and then apply an “average” FX rate at query time, results are wrong.
• Accuracy requires applying the correct daily FX rate per row first, then aggregating.

2. Time-Dependent Adjustments

Values often must be adjusted by factors that change over time:

• Regulatory coefficients
• Inflation factors
• Product-specific multipliers

If applied at the aggregate level, results are distorted. Each row must be multiplied by its factor before rollup.

3. Property Management (Customer Case)

Another time-dependent scenario is when values are valid for all dates between a given start date and end date.

A real-world customer stored property rental data in a delta-based model: only changes (rent increase, heating cost change, etc.) were saved.

But in reporting, monthly amounts are required. Current workarounds:

• Pre-calculate full values for every month (huge memory cost).
• Custom Java MDX function with icCube vectors (messy, tricky null handling).

Both are compromises. What’s needed is row-level aggregation directly in the model.

The Solution: MDX++ Meets Tabular

Version 9 introduces a feature that allows users to define an object that aggregates data row by row using the columns defined in the fact table of a measure group.

• Aggregations are defined in Java code.
• Compiled and optimized just like icCube’s internal engine.
• No more bloated models or preprocessing.

Now you can define row-level logic inside the tabular model itself. The engine does the aggregation automatically.

Practical Outcomes

• Weighted Averages → Engine manages value × weight + weight internally → correct ratio every time.
• FX Conversions → Each row’s amount × daily FX rate applied at row level → accurate totals.
• Time-Based Adjustments → Multipliers applied per row → consistent results across time.
• Property Management → Delta-based storage becomes monthly values without memory bloat.

Adding a Table Aggregator

This feature is still alpha, so you’ll need a little Java code.

Step 1: Create Weighted Average Class

public class WeightedAverage implements IOlapFactTableAggregatorFactory {
   @Override
   public IOlapFactTableAggregator newInstance() {
       return new IOlapFactTableAggregator() {
           int weightIdx;
           int valueIdx;
           double weightedSum = 0;
           double weight = 0;

           @Override
           public String getName() {
               return "WeightedAverage";
           }

           @Override
           public void init(IFactTableContext namesToArrayPosition, String measureName, String factColumnName) {
               weightedSum = weight = 0;
               weightIdx = namesToArrayPosition.getFromColumnName("weight");
               valueIdx = namesToArrayPosition.getFromColumnName("amount");
           }

           @Override
           public boolean onRow(int rowNumber, IOlapFactsTableRow row) {
               final double w = row.getAsDouble(weightIdx);
               weight += w;
               weightedSum += w * row.getAsDouble(valueIdx);
               return true;
           }

           @Override
           public OlapScalarEntity asScalarEntity() {
               return weight == 0
                   ? null
                   : new OlapNumericEntity(weightedSum / weight);
           }
       };
   }
}


Step 2: Add Class to plugin.xml

<tableAggregator name="WeightedAverage" className="com.myamazing.WeightedAverage"/>


Step 3: Overwrite Aggregation in MDX

CREATE SET_TABLE_AGGREGATOR [Measures].[Weighted Amount] ‘WeightedAverage’


Result

Analysts still get drag-and-drop simplicity—but with calculations that respect real-world data complexity.

• No more “good enough” shortcuts.
• Just precise numbers, every time.

The Future

• Add the possibility to define the class directly in the schema.
• Extend language to allow syntax like:

MEASURE [Weighted Amount] AS    
   AGGREGATE ROWS SUM([amount] * [weight]) / SUM([weight])


‍‍

This blog was written with the help of an AI. No AI was harmed in the process (just a few CPUs overheated).

‍