Tr : [Mondrian] Question on non-aggregable measures
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.
----- 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.
From: michael bienstein
[mailto:mbienstein at yahoo.fr]
Sent: Thursday, November 22, 2007 1:21
To: jhyde at pentaho.org
Subject: Re : [Mondrian] Question
on non-aggregable measures
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.
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
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 property to the RolapStar to indicate that no rollups are allowed.
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).
Modify SegmentLoader.createExecuteSql() to check if the sql String returned
from generateSql() is null and if so return a null SqlStatement instead of
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
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
I think that should do it. Am I right? (If so I
will be implementing it in January).
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
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
So, it isn't there, but it could be fairly easily
implemented by generalizing the distinct-count
From: mondrian-bounces at pentaho.org
[mailto:mondrian-bounces at pentaho.org] On Behalf Of michael
Sent: Wednesday, November 21, 2007 11:16
To: Mondrian developer mailing list
[Mondrian] Question on non-aggregable measures
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
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!
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Mondrian