[Mondrian] Adding Grouping Set support for Distinct Count measures

John V. Sichi jsichi at gmail.com
Thu Jan 31 05:21:13 EST 2008


John V. Sichi wrote:
> As Ajit points out, this means we have to be careful with pushing down 
> constraints for non-distinct aggs to SQL.  Rushan and I have been 
> thinking about how to build on the distinct-count optimization she put 
> in, with the next step being optimization and caching for non-visual 
> totals via calculated members.

Oops, I meant for visual totals.  Below is an example MDX query which 
demonstrates an extreme case (computing the visual grand total for some 
combination of large arbitrary subsets of three dimensions).

With
Set [*BASE_MEMBERS_Customer] as [Customers].[Name].members
Set [*BASE_MEMBERS_Store] as [Store].[Store Name].members
Set [*BASE_MEMBERS_Product] as [Product].[All Products].[Food].children
Member [Customers].[*SUBTOTAL] As Aggregate([*BASE_MEMBERS_Customer])
Member [Product].[*SUBTOTAL] As Aggregate([*BASE_MEMBERS_Product])
Member [Store].[*SUBTOTAL] As Aggregate([*BASE_MEMBERS_Store])
Select
{[Measures].[Unit Sales]} on columns,
([Store].[*SUBTOTAL],[Customers].[*SUBTOTAL],[Product].[*SUBTOTAL])
on rows
 From [Sales]
where ([Time].[1997]);

With latest Mondrian, on my laptop against Derby, it takes about 180 
seconds.  And if I repeat it, the time does not go down, since the total 
cannot be cached.

However, if I edit line 79 of AggregateFunDef.java to

             if ((aggregator == RolapAggregator.DistinctCount)
                 || (aggregator == RolapAggregator.Sum)) {

then the time goes down to 105s for the first execution, and 40s for 
subsequent executions.  The speedup comes from pushing the sum 
computation down to SQL instead of letting Mondrian iterate over the big 
sparse 3D space.

Derby is quite slow for the join/agg; if I run it on LucidDB, the 
improvement for the first MDX execution (where the SQL execution 
matters) is quite a bit larger, taking it down to 83s.  (Subsequent runs 
are 40s as with Derby since the result is already cached, so SQL 
performance doesn't come into play.)

I was wondering why the cached runs still took 40s, so I probed the 
stack and it was spending most of the time in 
AggregateFunDef.removeOverlappingTupleEntries.  I commented this out, 
and the LucidDB time went down to 15s for the first execution, and 8s 
for the cached execution.

To try this, you also have to set mondrian.rolap.maxConstraints=100000, 
because the IN list generated is huge.  (If your DBMS doesn't like large 
IN lists, this won't be an attractive approach.)  This implies cache 
bloat issues, since those make up the cache key.  And the overall 
approach raises optimization issues, since for many MDX queries, pushing 
down to SQL like this would be overkill.

JVS




More information about the Mondrian mailing list