Mondrian

Hi Julian,=20

=20

I'm doing some virtual cube testing and all I find is three unit tests
in Mondrian.rolap.VirtualCubeTest.java?  Is that all we have or I am
looking in the wrong place?

=20

Also, Mondrian virtual cubes do not support calculated members, correct?
We need to do simple ratios between metrics that come from different
stars. =20

=20

Thanks

Benny

We have many use cases to support measure definitions based on SQL
expressions.  For example, you need to do pre-aggregation filtering:

=20

(CASE WHEN OPTY_PRIM_COMP_KEY =3D 0 THEN 0 ELSE 1 END)
COMPETITIVE_OPTY_COUNT,

=20

Or filter on a dimensional attribute:

=20

CASE WHEN VLE_STATUS.STATUS_CODE =3D 'CLOSED' THEN 0=20

ELSE VLE_OPPORTUNITY.OPTY_CLOSE_DK - OPTY_START_DK END AS DAYS_TO_CLOSE

=20

I'd like to implement this in Mondrian and contribute it back.  There
was some discussion on this earlier:

=20

http://sourceforge.net/forum/message.php?msg_id=3D3715134

=20

=20

It might look something like this:

=20

<measure name=3D"Competitive Opportunity Count" aggregator=3D"sum">

            <measureExpression>

                        <SQL dialect=3D"my_favorite_db">

                                    CASE WHEN OPTY_PRIM_COMP_KEY =3D 0
THEN 0 ELSE 1 END

                        </SQL>

            </measureExpression>

</measure>

=20

In order to support dimensional filtering, I think it would be too
difficult to get Mondrian to implicitly join in the dimension so instead
it would be written as an inline select that returns a scalar value.
The DB should be able to flatten this into a join:

=20

Ex.

=20

<measure name=3D"Days to Close" aggregator=3D"sum">

            <measureExpression>

                        <SQL dialect=3D"my_favorite_db">

                                    SELECT CASE WHEN
VLE_STATUS.STATUS_CODE =3D 'CLOSED' THEN 0=20

ELSE <ColumnRef alias=3D"FACT_TABLE" name=3D"OPTY_CLOSE_DK" /> - =
<ColumnRef
alias=3D"FACT_TABLE" name=3D"OPTY_START_DK" /> END

FROM VLE_STATUS VLE_STATUS

WHERE VLE_STATUS.ID =3D <ColumnRef alias=3D"FACT_TABLE" =
name=3D"STATUS_ID" />

                        </SQL>

            </measureExpression>

</measure>

=20

This also assumes we build support for:
http://sourceforge.net/forum/forum.php?thread_id=3D1503987&forum_id=3D111=
376

=20

=20

Any guidance or suggestions on where to start this in the code base?

=20

Thanks

Benny

=20

=20

OK, I've fixed the page.
=20
Julian


  _____ =20

From: Dewerse Nicolas [mailto:ndewerse@...
Sent: Thursday, June 15, 2006 06:15
To: julian@...
Cc: mondrian-devel@...
Subject: Re : Mondrian install page


I had already removed this semicolon, it didn't work with it :)


----- Message d'origine ----
De : Julian Hyde <julian@...>
=C0 : Dewerse Nicolas <ndewerse@...>
Cc : mondrian-devel@...
Envoy=E9 le : Jeudi, 8 Juin 2006, 9h20mn 28s
Objet : RE: Mondrian install page


You have a semicolon just before 'catalogUri':
=20

   <jp:mondrianQuery id=3D"query01" jdbcDriver=3D"com.mysql.jdbc.Driver"
 =
jdbcUrl=3D"jdbc:mysql://localhost/foodmart?user=3Dfoodmart&#38;password=3D=
foodmart"; catalogUri=3D"/WEB-INF/queries/FoodMart.xml">
=20
Try removing it. Does the following work?
=20

   <jp:mondrianQuery id=3D"query01" jdbcDriver=3D"com.mysql.jdbc.Driver"
 =
jdbcUrl=3D"jdbc:mysql://localhost/foodmart?user=3Dfoodmart&#38;password=3D=
foodmart" catalogUri=3D"/WEB-INF/queries/FoodMart.xml">
=20
Julian


  _____ =20

From: Dewerse Nicolas [mailto:ndewerse@...
Sent: Thursday, June 08, 2006 09:51
To: julian@...
Cc: mondrian-devel@...
Subject: Re : Mondrian install page


Julian,

I just retry with '&#38;' and it didn't work,
and it works with '&'

Nicolas


----- Message d'origine ----
De : Julian Hyde <julian@...>
=C0 : Dewerse Nicolas <ndewerse@...>
Cc : mondrian-devel@...
Envoy=E9 le : Samedi, 3 Juin 2006, 12h15mn 22s
Objet : RE: Mondrian install page


Nicholas,
=20
I think that the '&#38;' is necessary. That's because Jsp is a subset of =
XML, and XML expands '&variable;' whenever it sees it. The
Jsp parser should expand '&#38;' to '&', so the will have the desired =
value
'jdbc:mysql://localhost/foodmart?user=3Dfoodmart&;password=3Dfoodmart' =
by the time it gets to the tag library.
=20
But I might be wrong. Cc:ing mondrian-devel -- anyone else have =
experience with this?
=20
Julian



> -----Original Message-----
> From: Dewerse Nicolas [mailto:ndewerse@...]
> Sent: Wednesday, May 31, 2006 05:01
> To: jhyde@...
> Subject: Mondrian install page
>
>   Hello,
>  =20
>   In the install page of Mondrian, there is :
>  =20
>   "
>       *  Modify the fourheir, mondrian, colors and arrows
> JSPs in <path to tomcat>\webapps\mondrian\WEB-INF\queries as follows:
>  =20
>   Modify the line:
>  =20
>       <jp:mondrianQuery id=3D"query01"
> jdbcDriver=3D"sun.jdbc.odbc.JdbcOdbcDriver"
> jdbcUrl=3D"jdbc:odbc:MondrianFoodMart"
> catalogUri=3D"/WEB-INF/queries/FoodMart.xml">
>  =20
>   to the same database parameters for the FoodMart database
> you installed as per the above instructions. ie. for MySQL,
> something like:
>  =20
>       <jp:mondrianQuery id=3D"query01"
> jdbcDriver=3D"com.mysql.jdbc.Driver"
> jdbcUrl=3D"jdbc:mysql://localhost/foodmart?user=3Dfoodmart&#38;pas
> sword=3Dfoodmart"; catalogUri=3D"/WEB-INF/queries/FoodMart.xml">
>   "
>  =20
>   I think
> =20
> =
"jdbc:mysql://localhost/foodmart?user=3Dfoodmart&#38;password=3Dfoodmart"=

>   should be replaced by
>             =20
> "jdbc:mysql://localhost/foodmart?user=3Dfoodmart&password=3Dfoodmart"
>
>
>
> Thank you for your great work,
>
> Nicolas
>
> =20
>
>=20