# [Mondrian] Question on non-aggregable measures

Julian Hyde jhyde at pentaho.org
Thu Nov 22 19:00:43 EST 2007

```This thread should have stayed on the developers' list. (I don't usually
broaden personal messages to public lists, because I don't want to
inadvertently publish confidential information. But I took the liberty of
Cc:ing the developer's list this time.)

michael bienstein  wrote:

The requirement is that at any organisation/time crossing the budget user
can enter a number.  Even if all child organisations have numbers the parent
is NOT aggregated and could be contradictory.  Bizarre requirement that, but
that's the way it's currently done and they want the same thing only for it
to run faster so I'm proposing to change technology.

Since we're talking about developing a feature, let's see how we can broaden
the requirement to cover other common cases, without making the
specification or implementation much more complicated.

In the case of non-aggregatable measures, we could model these as an
aggregation function NONE.

I suppose if you are going to define such a measure, you need to ensure that
your fact table contains at must one row per cell. Mondrian could enforce
that by generating MAX and MIN and comparing them.

Also, I can imagine a measure which can be rolled up along some dimensions
but not others; maybe not in your application, but in others.

I experienced this with distinct-count measures. For example, the number of
distinct customers can be aggregated over the gender dimension, but not over
the time dimension. (If you have 10 distinct female customers in Q1 and 5
distinct male customers in Q1, you definitely have 15 distinct customers in
Q1. Why can you say that? Because gender is functionally dependent on
customer id.)

Also, consider semi-additive measures, the textbook example of which is
inventory-on-hand. This measure is aggregated using the LAST operator on the
time dimension, SUM operator on other dimensions (e.g. product).

Putting this all together, I see a measure being defined with a default
aggregation function, which may be SUM, AVG, FIRST, LAST, NONE; and being
able to override the aggregation function for certain dimensions:

<Cube>
...
<Measure name="Inventory on hand" aggregator="sum">
<RollupRule dimension="Time" aggregator="last"/>
</Measure>
<Measure name="Customer count" aggregator="distinct-count">
<RollupRule dimension="Gender" aggregator="sum"/>
</Measure>
<Measure name="Budget" aggregator="none"/>
</Cube>

Is this more general scheme still consistent with your requirements? It
would certainly be nice to deal with semi-additive measures properly at the
same time.

Julian

Thanks Julian, I'll have a look at that.
Michael

----- Message d'origine ----
De : Julian Hyde <jhyde at pentaho.org>
À : Mondrian developer mailing list <mondrian at pentaho.org>
Envoyé le : Jeudi, 22 Novembre 2007, 1h53mn 12s
Objet : RE: [Mondrian] Question on non-aggregable measures

We already have the mother of all non-aggregable measures, namely
distinct-count. It's slightly different in that it's computable by going
back to the fact table, whereas for what you want, if it's not in the agg
table, it ain't there at all. (Or maybe you would allow aggregation on some
dimensions but not others - I don't know your precise requirements.)

So, it isn't there, but it could be fairly easily implemented by
generalizing the distinct-count functionality.

Julian

_____

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of michael bienstein
Sent: Wednesday, November 21, 2007 11:16 PM
To: Mondrian developer mailing list
Subject: [Mondrian] Question on non-aggregable measures

Hello all,

I may very well use Mondrian in a short project soon.  One point that I
think is probably missing that is part of the requirements is a
non-aggregable measure that is read only from aggregation tables.  E.g. a
budget or objective that is filled in at certain members of an org hierarchy
but not all and certainly not aggregated from facts attached to the leaves.

I don't know if this can be handled by the existing code (it's been more
than 6 months since I last looked at the code).  Can anyone tell me please?

Thanks,

Michael

_____

Ne gardez plus qu'une seule adresse mail ! Copiez
<http://www.trueswitch.com/yahoo-fr/> vos mails vers Yahoo! Mail

_____

Ne gardez plus qu'une seule adresse mail ! Copiez vos mails
<http://www.trueswitch.com/yahoo-fr/>  vers Yahoo! Mail

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20071123/f77c010a/attachment.html
```