<br><font size=3 face="Verdana">As of now mondrian uses multiple sql queries
to get data for a MDX with &nbsp;members at different levels. We are working
on changing this behavior to combine these multiple sqls to single one.<br>
<br>
For an example this MDX generates 2 sqls, one for all gender and another
for male,female <br>
<br>
select gender.members on 0 from [sales 2] where measures.[Sales Count];<br>
<br>
sql:<br>
1. <i>select &nbsp;&quot;time_by_day&quot;.&quot;the_year&quot; as &quot;c0&quot;,
&nbsp;&quot;customer&quot;.&quot;gender&quot; as &quot;c1&quot;, &nbsp;count(&quot;sales_fact_1997&quot;.&quot;product_id&quot;)
as &quot;m0&quot; <br>
from &quot;time_by_day&quot; &quot;time_by_day&quot;, &nbsp;&quot;sales_fact_1997&quot;
&quot;sales_fact_1997&quot;, &nbsp;&quot;customer&quot; &quot;customer&quot;<br>
where <br>
 &nbsp; &nbsp;&quot;sales_fact_1997&quot;.&quot;time_id&quot; = &quot;time_by_day&quot;.&quot;time_id&quot;
and <br>
 &nbsp; &nbsp;&quot;time_by_day&quot;.&quot;the_year&quot; = 1997 and <br>
 &nbsp; &nbsp;&quot;sales_fact_1997&quot;.&quot;customer_id&quot; = &quot;customer&quot;.&quot;customer_id&quot;<br>
group by &nbsp;&quot;time_by_day&quot;.&quot;the_year&quot;, &quot;customer&quot;.&quot;gender&quot;
</i><br>
<br>
2. <i>select &quot;time_by_day&quot;.&quot;the_year&quot; as &quot;c0&quot;,
&nbsp;count(&quot;sales_fact_1997&quot;.&quot;product_id&quot;) as &quot;m0&quot;<br>
from &quot;time_by_day&quot; &quot;time_by_day&quot;, &quot;sales_fact_1997&quot;
&quot;sales_fact_1997&quot; <br>
where <br>
 &nbsp; &nbsp;&quot;sales_fact_1997&quot;.&quot;time_id&quot; = &quot;time_by_day&quot;.&quot;time_id&quot;
and <br>
 &nbsp; &nbsp;&quot;time_by_day&quot;.&quot;the_year&quot; = 1997<br>
group by <br>
 &nbsp; &nbsp;&quot;time_by_day&quot;.&quot;the_year&quot; <br>
</i><br>
<br>
with our prototype, these sqls are grouped into one (this query will only
&nbsp;work with DBs that support grouping sets):<br>
<br>
1. <i>select &quot;time_by_day&quot;.&quot;the_year&quot; as &quot;c0&quot;,
&nbsp;&quot;customer&quot;.&quot;gender&quot; as &quot;c1&quot;, &nbsp;count(&quot;sales_fact_1997&quot;.&quot;product_id&quot;)
as &quot;m0&quot; <br>
from &quot;time_by_day&quot; &quot;time_by_day&quot;, &nbsp;&quot;sales_fact_1997&quot;
&quot;sales_fact_1997&quot;, &nbsp;&quot;customer&quot; &quot;customer&quot;<br>
where <br>
 &nbsp; &nbsp;&quot;sales_fact_1997&quot;.&quot;time_id&quot; = &quot;time_by_day&quot;.&quot;time_id&quot;
and <br>
 &nbsp; &nbsp;&quot;time_by_day&quot;.&quot;the_year&quot; = 1997 and <br>
 &nbsp; &nbsp;&quot;sales_fact_1997&quot;.&quot;customer_id&quot; = &quot;customer&quot;.&quot;customer_id&quot;<br>
group by &nbsp;grouping sets((&quot;time_by_day&quot;.&quot;the_year&quot;,
&quot;customer&quot;.&quot;gender&quot;), (&quot;time_by_day&quot;.&quot;the_year&quot;))
</i><br>
<br>
Although the grouping set implementation looks fine and works in most cases,
there is a tricky problem associated with it. The problem is grouping &quot;all&quot;
(aggregate) value got from rolling up is equal to aggregate of the members
involved and not the all value <br>
<br>
to reproduce it, alter fact table and insert rows for null customers:<br>
<br>
 --modify fact table to allow nulls for customer_id</font>
<br><font size=3 face="Verdana">alter table &quot;sales_fact_1997&quot;
modify &quot;customer_id&quot; null &nbsp; &nbsp; &nbsp; &nbsp;<br>
--add sales records with no customers<br>
insert into &quot;sales_fact_1997&quot; values (337,371,null,0,2,6.3455,2.3445,4)
<br>
insert into &quot;sales_fact_1997&quot; values (337,440,null,0,2,6.3355,2.335,2)<br>
<br>
results: <br>
remains same with grouping sql:<br>
Axis #0:<br>
{[Measures].[Sales Count]}<br>
Axis #1:<br>
{[Gender].[All Gender]}<br>
{[Gender].[All Gender].[F]}<br>
{[Gender].[All Gender].[M]}<br>
Row #0: 86,837 &nbsp; &nbsp; &nbsp; <br>
Row #0: 42,831 <br>
Row #0: 44,006<br>
<br>
without grouping (mondrian's current behavior):</font>
<br><font size=3 face="Verdana">...<br>
Row #0: 86,839 &nbsp; &nbsp; &nbsp; &nbsp; //includes 2 records that don't
belong to any gender<br>
Row #0: 42,831<br>
Row #0: 44,006<br>
<br>
In summary &quot;All&quot; in mondrian is not really an aggregate of all
the members, it also includes data from rows that don't join to fact table.
This behavior is not consistent with AS 2000 (MOLAP). </font>
<br><font size=3 face="Verdana">How should we handle this issue? </font>
<br><font size=3 face="Verdana">&nbsp;One of the options that we can think
of is, to use outer joins. The problem with this is it returns addition
row for &quot;null&quot; gender.</font>
<br>
<br>
<br>
<br><font size=3 face="Verdana">Thanks,</font>
<br><font size=3 face="Verdana">Thiyagu &amp; Raghu</font>
<br>
<br>