Tr : [Mondrian] Question on non-aggregable measures

Fri Nov 23 01:40:13 EST 2007

Once again, sorry for not mailing it to the developers list.  I just hit reply.
I've read your response.  I don't think it will work for my requirements because the data has to come from aggregate tables and not the fact table at all.  Actually there's a case of when the fact table IS the lowest level of aggregation already, but I don't think Mondrian deals with that (Count will count the rows in fact tables but sum the count measure in aggregate tables for example).  

However I think it is a good idea, if orthogonal.  The only disagreement I have is the semi-additive measure system you've got.  MDX should be used to define these sorts of things.  It's great if there is a mechanism to place these cells into the cell cache (calculated cells aren't right now I think) but don't use "last" like you have.  Once again all this is I think orthogonal to my needs.

Michael

----- Message transféré ----
De : Julian Hyde <jhyde at pentaho.org>
À : michael bienstein <mbienstein at yahoo.fr>
Cc : mondrian at pentaho.org
Envoyé le : Vendredi, 23 Novembre 2007, 1h00mn 43s
Objet : RE: [Mondrian] Question on non-aggregable measures

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 
    vos mails vers Yahoo! Mail 

  Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! 
  Mail 

      _____________________________________________________________________________ 
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20071123/f7f46897/attachment.html