semi-additive measures

[avix]

It makes sense to sum up a measure like "account balance" over all customers, it gives the total amount of money of all customers. It does not make sense to sum up "account balance" over all time periods (e.g. months), in the time dimension it would make sense to take the average or min/max "account balance".
Example: I have a fact table containing "account balance", a customer dimension table containing customers and a time dimension table containing months and years. Is it possible, with Mondrian, to create a query/result, that displays the measure "account balance" on columns, the customers on rows, has 1997 in the slicer and calculates the maximum account balance for every customer for that year?

[jhyde]

A few questions to help us nail down the requirements. (Sorry these are a bit terse, but I typed these once already, then something crashed...)
Do we need to control which rollup function to use, on a per-dimension basis?
Do we need to control which order dimensions are rolled up in?
I'm thinking of XML like the following. Do you agree? (The "ordinal" attribute controls rollup order. By default, dimensions have ordinal=0.)
<Measure name="Account_balance" column="account_balance" aggregator="sum">
<Rollup dimension="Time" ordinal="-2" aggregator="max"/>
<Rollup dimension="Time" ordinal="-1" aggregator="max"/>
</Measure>
Which rollup functions do we need? We currently have "sum", "count", "average". Add "min", "max", "first", "last". Any more?
Do we need custom rollup functions? If so, and assuming that these are implemented as Java classes, what interface should these classes implement?
Is the MDX "RollupChildren" function useful here?
What does the JOLAP spec have to say about custom rollups?
How does "distinct count" impact all of this?
With rollup paths, an exponential number of measures is possible. Any ideas how to manage so many measures?
Do we need to create semi-additive measures on the fly, using a WITH MEMBER clause. If so, what would the syntax be?

[avix]

Wow! I was not aware that semi-additive measures is such a complicated topic. It seems that a generic, comprehensive solution will be a big effort.
I thought it might be possible just with MDX to answer some typical questions.
For example (replace "Unit Sales" with "Account Balance" mentally):
WITH member [Measures].[Average Unit Sales]
AS 'Avg([Time].[Month].Members, [Measures].[Unit Sales])', format_string = "#.00"
SELECT {[Measures].[Average Unit Sales]} ON columns,
NON EMPTY {[Store].[Store City].Members} ON rows
FROM [Sales]
WHERE ([Time].[1997])
I think this computes the following:
- for every month and every store sum up the unit sales in all other dimensions
- then, for every store, compute the average monthly unit sales
Wouldn't this work? I understand that built-in support for semi-additive measures would make queries simpler and less error prone. But are there any practical questions that can not be answered with MDX alone?
Andreas

[jhyde]

You could definitely hand-craft an MDX query using that approach, but it would be difficult to phrase the formula so that it adapts as you drill down, etc.
Your MDX gives the wrong answer, in that it averages over all months -- 24 months in 1997 and 1998 combined -- whereas you would want to average over just the 12 months in 1997.
Let's approach this problem from both ends. You see how far you can get by writing MDX, and let me know what incremental enhancements -- e.g. new builtin and rollup functions -- might help you get there. And I will try to find the easiest way to solve the generic problem.