<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY><SPAN class=279570307-06082007><FONT color=#000080>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>I just checked in the last feature on the roadmap for
mondrian 2.4, </SPAN></FONT><FONT color=#000080><SPAN
class=279570307-06082007>in change 9710: <A
href="http://p4web.eigenbase.org/@md=d&c=6PU@//9710?ac=10"><U><FONT
color=#0000ff>http://p4web.eigenbase.org/@md=d&c=6PU@//9710?ac=10</U></FONT></A></SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana size=2><SPAN
class=279570307-06082007></SPAN></FONT> </DIV>
<DIV><FONT face=Verdana size=2>I<SPAN class=279570307-06082007>t was a
</SPAN>big check-in to mondrian for what seems (on the face of it) a small
feature: the ability to roll up distinct-count measures to calculated members
which represent a span of time or a collection of
customers.</FONT></FONT></SPAN></DIV>
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080 size=2>A
simple example: you know you had 1000 distinct customers in Q1 and 800 distinct
customers in Q2. How many distinct customers did you have in Q1 and Q2 combined?
In MDX, we represent Q1 and Q2 combined as a member calculated using the
Aggregate function:</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080
size=2>WITH MEMBER [Time].[1997 H1]</FONT></SPAN></DIV>
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080
size=2> AS 'Aggregate({[Time].[1997].[Q1],
[Time].[1997].[Q2]})'</FONT></SPAN></DIV>
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080
size=2>SELECT {[Measures].[Customer Count]} ON COLUMNS,</FONT></SPAN></DIV>
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080
size=2> {[Time].[1997].[Q1], [Time].[1997].[Q2], [Time].[1997 H1]} ON
ROWS</FONT></SPAN></DIV>
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080
size=2>FROM [Sales]</FONT></SPAN></DIV></BLOCKQUOTE>
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080 size=2>We
know the answer is somewhere between 1000 (if all of the Q2 customers were from
Q1) and 1800 (if all of the Q2 customers were new), but finding that answer is a
</FONT></SPAN><SPAN class=279570307-06082007><FONT face=Verdana color=#000080
size=2>difficult one for mondrian. Norm</FONT></SPAN><SPAN
class=279570307-06082007><FONT face=Verdana color=#000080 size=2>ally the MDX
layer asks for a cell from the cell cache, using a set of (column, value) pairs
as the coordinates of the cell, but the compound member [Time].[1997 H1] doesn't
fit into that coordinate scheme. In fact the answer cannot be computed from
cells in cache.</FONT></SPAN></DIV>
<DIV><SPAN class=279570307-06082007><FONT face=Verdana color=#000080
size=2></FONT></SPAN> </DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>To answer queries like this, I needed to extend the
data structure which mondrian uses to record cache requests, and translate each
cache request into a single SQL statement. The SQL turns out to be quite
complex, especially if there are multiple compound
members.</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>For example, the
query</SPAN></FONT></FONT></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>WITH </SPAN></FONT></FONT></FONT><FONT
face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>MEMBER [Store].[CA plus USA]
AS</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>
'AGGREGATE({[Store].[USA].[CA],
[Store].[USA]})', solve_order=1</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007> MEMBER [Time].[Q1 plus July]
AS</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>
'AGGREGATE({[Time].[1997].[Q1], [Time].[1997].[Q3].[7]})',
solve_order=2</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>SELECT {[Measures].[Customer Count], [Measures].[Unit
Sales]} ON COLUMNS,<BR> {[Store].[CA plus USA]} *
{[Time].[Q1 plus July]} ON ROWS<BR>FROM
Sales</SPAN></FONT></FONT></FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>generates</SPAN></FONT></FONT></FONT><FONT
face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></SPAN></FONT></FONT></FONT><FONT face=Verdana><FONT size=2><FONT
color=#000080><SPAN class=279570307-06082007>select count(`c`) as `c0`<BR>from
(<BR> select distinct `sales_fact_1997`.`customer_id` as
`c`<BR> from `time_by_day` as
`time_by_day`,<BR> `sales_fact_1997` as
`sales_fact_1997`,<BR> `store` as
`store`<BR> where `sales_fact_1997`.`time_id` =
`time_by_day`.`time_id`<BR> and
`time_by_day`.`the_year` = 1997<BR> and
`sales_fact_1997`.`store_id` =
`store`.`store_id`<BR> and (`store`.`store_state` =
'CA'<BR> or
`store`.`store_country` = 'USA')<BR> and
((`time_by_day`.`quarter` = 'Q1' and `time_by_day`.`the_year` =
1997)<BR> or (`time_by_day`.`month_of_year` = 7 and
`time_by_day`.`quarter` = 'Q3' and `time_by_day`.`the_year` =
1997))</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>) as
`dummyname`</SPAN></FONT></FONT></FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>Besides representing these requests and generating this
baroque SQL, I also had to ensure that these unusual, more general requests did
not compromise the design simplicity and performance of ordinary dimensional
requests. I did this by storing the non-relational coordinates in a new field
RolapEvaluator.aggregationLists, and ensuring that most of the dimensional
expression process doesn't look at that field. To make things a bit simpler, I
issue a one SQL query per compound cell.</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007>Please give this new feature a try, and run your
application's regression suite over the latest code base. Next stop is
mondrian-2.4. Now this feature is complete, I will make release candidate 1 in
the next day or so, and one RC per week until we have the quality required for a
release.</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Verdana><FONT size=2><FONT color=#000080><SPAN
class=279570307-06082007></SPAN></FONT></FONT></FONT><FONT face=Verdana><FONT
size=2><FONT color=#000080><SPAN
class=279570307-06082007>Julian</SPAN></FONT></FONT></FONT></DIV></BODY></HTML>