Tr : [Mondrian] Question on non-aggregable measures

michael bienstein mbienstein at yahoo.fr
Fri Nov 23 01:35:28 EST 2007


Sorry about mailing to your personal address.  I hit respond thinking the mail list system would take care of it.  I was wondering why they didn't appear actually.

Michael

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



 



Can you re-post this to the developer's list, please. I 
will reply to it there.

 

Julian



  
  
  From: michael bienstein 
  [mailto:mbienstein at yahoo.fr] 
Sent: Thursday, November 22, 2007 1:21 
  PM
To: jhyde at pentaho.org
Subject: Re : [Mondrian] Question 
  on non-aggregable measures



  

  
  I've had 
  a look through the code.  There's a lot of new and quite complex code 
  around the Grouping Sets in there and the distinct count stuff seems a bit all 
  over the place and I can't easily follow it.  I do however have an idea 
  to make this simple and would appreciate your opinion(s).

The sort of 
  measure I'm referring to would have to be in a separate star/aggregate-schema 
  because the SQL generated would never have GROUP BY.  It would be 
  impossible to have normal measures in the same star and do queries that 
  recover normal measures and this sort of measure.  So the easiest way to 
  do it is to use Virtual Cubes between normal measures from one sort of cube 
  and another type of cube for the special, non-aggregable measures.  (The 
  other way I can think of is to launch separate SQLs against the same set of 
  aggregate tables but I don't think this is a good fix).

In this case, 
  it's essentially a property of a RolaStar.  In examining the code I see 
  the following points:
1) AggregationManager.generateSql().  This code 
  generates a String of SQL to execute. I want to run through the logic of the 
  aggregation table matching found here.  If there's no match I DON'T want 
  to fall through to aggregating off the fact table.  However I can't put a 
  check in there because we expect to get a non-null string of valid SQL and in 
  my case there simply won't be any.  So I want to see if I can return null 
  and deal with that case.
2) 
  SegmentLoader.createExecuteSql(GroupingSetsList) is the only place I see that 
  calls AggregationManager.generateSql().  It currently takes the valid, 
  non-null SQL and executes it in JDBC returning the wrapped ResultSet in a 
  SqlStatement.  One option would be to check for a null return (then 
  assert on the type of RolapStar) and if null return a fake SqlStatement object 
  that returns valid meta-data but no lines.  At least I THINK that that 
  could work.  We may expect lines for known members ... I don't know 
  yet.  In any case, I would have trouble faking a ResultSet I think.  
  The JDBC spec changes so much I don't think its a good idea even if we now 
  have WrappedResultSet and the like.  Much better would be to know that it 
  is fake.
3) SegmentLoader.load(List<GroupingSets>, 
  RolapAggregationManager.PinSet) is the only place that createExecuteSql is 
  called.  It calls processData() to get a list of rows from the 
  SqlStatement.  The only other thing it does is catch SQLExceptions with 
  it.  If we just return an empty list from processData() or skip 
  processData if the SqlStatement doesn't wrap a JDBC ResultSet (has a null 
  instead) then this should work.

So the proposed changes are:
a) Add 
  a property to the RolapStar to indicate that no rollups are allowed.
b) 
  Include this in the XML DTD.
c) Modify SegmentLoader.load() on line 54 to 
  check if the SqlStatement returned from createExecuteSql() is null or 
  not.  If null, skip the call to processData, set sparse to true ...  
  (maybe modify the catch clause for SQLException but not necessary).
d) 
  Modify SegmentLoader.createExecuteSql() to check if the sql String returned 
  from generateSql() is null and if so return a null SqlStatement instead of 
  calling executeQuery().
e) Add a check at line 221 of 
  AggregationManager.generateSql() to avoid dealing with the Fact table if the 
  rolap star has the new property set to true.  In this case it returns a 
  null String.
f) So as to trick the aggregate table matching we need to have 
  aggStar.getDistinctMeasureBitKey() return a bitkey with all the measures in 
  this star on.  This is because we don't ever want to rollup from 
  aggregate tables (just like distinct count measures at the moment).  This 
  is simple: modify AggregationManager.findAgg() line 278.  isDistinct = 
  star.isRollupAllowed() ? /*Current condition*/ : true; //That's the new 
  property.

I think that should do it.  Am I right?  (If so I 
  will be implementing it in January).

Michael
3) 
  

SegmentLoader.loadData() assumes we get a genuine 


  ----- 
  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/83d0af86/attachment.html 


More information about the Mondrian mailing list