[Mondrian] Question on non-aggregable measures
jhyde at pentaho.org
Fri Nov 23 06:34:20 EST 2007
You're made a simplifying assumption that the star is flagged non-rollup. No
question that it simplifies things. But it seems much more useful to
implement the more general requirement that you can flag an individual
measure as non-rollup.
Can you investigate what it would take to implement that?
michael bienstein wrote:
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
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
I think that should do it. Am I right? (If so I will be implementing it in
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Mondrian