[Mondrian] Tracker 1811228: Cartesian join in getMemberChildren SQL w/ aggregate tables

Robin Tharappel rtharappel at gmail.com
Thu Oct 25 19:39:03 EDT 2007


Hello,

In some cases Mondrian is generating a SQL with a Cartesian join (for
retrieving non empty dimension members) when it is configured to use
aggregate tables and to evaluate nonempty sets natively. This
particular problem appears to occur when aggregate table used has the
dimension levels specified.  Below is an example MDX used with
FoodMart:

WITH SET [#DataSet#] AS
'NonEmptyCrossjoin({Descendants([Customers].[All Customers], 2)},
{Descendants([Store].[All Stores], 2)})'
SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} on columns,
Hierarchize({[#DataSet#]}) on rows FROM [Sales]

The following SQL is generated by SqlMemberSource.getMemberChildren:

select "customer"."country" as "c0"
from "customer" "customer","agg_lc_06_sales_fact_1997"
"agg_lc_06_sales_fact_1997"
group by "customer"."country" order by "customer"."country" ASC

Notice that there is not a join specified between the
agg_lc_06_sales_fact_1997 and the customer table.
Since the aggregate table already contains the customer dimension at the
country level the follow query should work:

select "agg_lc_06_sales_fact_1997"."country" as "c0"
from "agg_lc_06_sales_fact_1997" "agg_lc_06_sales_fact_1997"
group by "agg_lc_06_sales_fact_1997"."country"
order by"agg_lc_06_sales_fact_1997"."country" ASC

One solution would be to have the SqlMemberSource.makeChildMemberSql()
method identify if the aggregate table has the dimension levels and
use the dimension level column in the aggregate table instead of
joining with the dimension table.  In addition a change to
SqlContextConstraint.getMemberChildern was required to prevent a
situation where the same constraint was added twice (for example
"customer"."country" = 'USA' could show up in the query more than
once).

Attached is a zip file containing the source code changes (based on
2.4.2). II am still in the process of validating theses changes with
the unit tests. There is some test case coverage with the unit tests
(specificially mondrian.rolap.NonEmptyTest).  There is probably a
better way to implement this change. Please feel free to provide any
feedback.

Thanks,

Robin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: tracker1811228.zip
Type: application/zip
Size: 12262 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20071025/07d65638/attachment.zip 


More information about the Mondrian mailing list