[Mondrian] Multiple sets in where clause

Pedro Alves pmgalves at gmail.com
Mon May 7 17:41:13 EDT 2012


Benny, can you give two examples / templates of queries that use one mode
and the other?



On Saturday, May 5, 2012, Benny Chow wrote:

> Analyzer used to use calculated members too and now I recently added the
> support to apply the same filter via named sets.  I think there are two
> benefits:
>
> If you use a named set, then applying the slicer context will take O(n)
> where n is the number of tuples in the set.  Whereas if you use calculated
> members, then the complexity would O(x*y) where x is number of members in
> the first calculated member and y is number in second.  O(n) could be
> significantly less than O(x*y) if n was a nonempty cross join of x and y.
>
> The other benefit is that there will more cases where drill though on
> cells is possible.  This is because Mondrian cannot produce drillthrough
> sql when the slicer contains calculated members but it can when the slicer
> is based on a set.
>
> If you decide to switch between the two, be careful on how the slicer
> context affects native evaluation in named set expressions.
>
> See: http://www.luciddb.org/wiki/MondrianQueryEvaluationContext
>
>
>
> -----Original Message-----
> From: mondrian-bounces at pentaho.org <javascript:;> [mailto:
> mondrian-bounces at pentaho.org <javascript:;>] On Behalf Of
> mondrian-request at pentaho.org <javascript:;>
> Sent: Saturday, May 05, 2012 12:00 AM
> To: mondrian at pentaho.org <javascript:;>
> Subject: Mondrian Digest, Vol 66, Issue 1
>
> Send Mondrian mailing list submissions to
>        mondrian at pentaho.org <javascript:;>
>
> To subscribe or unsubscribe via the World Wide Web, visit
>        http://lists.pentaho.org/mailman/listinfo/mondrian
> or, via email, send a message with subject or body 'help' to
>        mondrian-request at pentaho.org <javascript:;>
>
> You can reach the person managing the list at
>        mondrian-owner at pentaho.org <javascript:;>
>
> When replying, please edit your Subject line so it is more specific than
> "Re: Contents of Mondrian digest..."
>
>
> Today's Topics:
>
>   1. Multiple sets in where clause (Pedro Alves)
>   2. Re: Multiple sets in where clause (Pedro Alves)
>   3. Re: Multiple sets in where clause (Paul Stoellberger)
>   4. Re: Multiple sets in where clause (Julian Hyde)
>   5. Re: Multiple sets in where clause (Pedro Alves)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 3 May 2012 17:27:34 +0100
> From: Pedro Alves <pmgalves at gmail.com <javascript:;>>
> Subject: [Mondrian] Multiple sets in where clause
> To: Mondrian developer mailing list <mondrian at pentaho.org <javascript:;>>
> Message-ID: <4FA2B1F6.9060904 at gmail.com <javascript:;>>
> Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
>
>
>
> Is it supported without ussing calculated (aggregated) members?
>
>
> Where <set> works.
>
> where (<member>,<member>) works
>
> where ( <set> * <set> works but this isn't a very optimal approach.
>
>
>
> I feel I'm missing something very very obvious
>
>
>
> -pedro
>
>
> ------------------------------
>
> Message: 2
> Date: Thu, 3 May 2012 17:30:14 +0100
> From: Pedro Alves <pmgalves at gmail.com <javascript:;>>
> Subject: Re: [Mondrian] Multiple sets in where clause
> To: Mondrian developer mailing list <mondrian at pentaho.org <javascript:;>>
> Message-ID: <4FA2B296.6070402 at gmail.com <javascript:;>>
> Content-Type: text/plain; charset="UTF-8"; format=flowed
>
> Bah - from http://jira.pentaho.com/browse/MONDRIAN-555 I take it it's a
> no? :S
>
> On Thu 03 May 2012 05:27:34 PM WEST, Pedro Alves wrote:
> >
> >
> > Is it supported without ussing calculated (aggregated) members?
> >
> >
> > Where <set> works.
> >
> > where (<member>,<member>) works
> >
> > where ( <set> * <set> works but this isn't a very optimal approach.
> >
> >
> >
> > I feel I'm missing something very very obvious
> >
> >
> >
> > -pedro
>
>
> ------------------------------
>
> Message: 3
> Date: Thu, 3 May 2012 21:07:25 +0200
> From: Paul Stoellberger <p.stoellberger at gmail.com <javascript:;>>
> Subject: Re: [Mondrian] Multiple sets in where clause
> To: Mondrian developer mailing list <mondrian at pentaho.org <javascript:;>>
> Cc: Mondrian developer mailing list <mondrian at pentaho.org <javascript:;>>
> Message-ID: <CA581489-AFD4-4AC6-9ACA-FB6CB0282C39 at gmail.com <javascript:;>
> >
> Content-Type: text/plain; charset="us-ascii"
>
> i always use aggregated members with (member,member) or union(
> (member,member), (member, member))
>
> working with sets in the where clause appears to be a bit unstable too. i
> noticed some wrong results using them (cant recall the details) and
> aggregated members always worked fine for me
>
>
>
>
> Am 03.05.2012 um 18:30 schrieb Pedro Alves <pmgalves at gmail.com<javascript:;>
> >:
>
> > Bah - from http://jira.pentaho.com/browse/MONDRIAN-555 I take it it's
> > a no? :S
> >
> > On Thu 03 May 2012 05:27:34 PM WEST, Pedro Alves wrote:
> >>
> >>
> >> Is it supported without ussing calculated (aggregated) members?
> >>
> >>
> >> Where <set> works.
> >>
> >> where (<member>,<member>) works
> >>
> >> where ( <set> * <set> works but this isn't a very optimal approach.
> >>
> >>
> >>
> >> I feel I'm missing something very very obvious
> >>
> >>
> >>
> >> -pedro
> > _______________________________________________
> > Mondrian mailing list
> > Mondrian at pentaho.org <javascript:;>
> > http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
> ------------------------------
>
> Message: 4
> Date: Thu, 3 May 2012 23:37:47 -0500
> From: Julian Hyde <jhyde at pentaho.com <javascript:;>>
> Subject: Re: [Mondrian] Multiple sets in where clause
> To: Mondrian developer mailing list <mondrian at pentaho.org <javascript:;>>
> Message-ID: <4EE9DE5B-CD73-4234-B515-0933FF89C2C0 at pentaho.com<javascript:;>
> >
> Content-Type: text/plain; charset="us-ascii"
>
> On Thu 03 May 2012 05:27:34 PM WEST, Pedro Alves wrote:
> Is it supported without ussing calculated (aggregated) members?
>
> Where <set> works.
>
> where (<member>,<member>) works
>
> where ( <set> * <set> works but this isn't a very optimal approach.
>
> I feel I'm missing something very very obvious
>
> Pedro you may be missing something obvious but I'm not sure what it is.
>
> Remember that in MDX:
>
>  *    ( ... ) is the syntax for a tuple. Members must be from different
> hierarchies.
>  *   { ... } is the syntax for a set. Elements in the set must be members
> (or tuples) with the same dimensionality.
>
> You can write
>
> WHERE {h1.m1, h1.m2} * {h2.m1}
>
> or
>
> WHERE CROSSJOIN({h1.m1, h1.m2},  {h2.m1})
>
> or
>
> WHERE {(h1.m1, h2.,1), (h1.m2, m2.m2)}
>
> and they are all valid and equivalent.
>
>
> Am 03.05.2012 um 18:30 schrieb Pedro Alves <pmgalves at gmail.com<javascript:;>
> <mailto:pmgalves at gmail.com <javascript:;>>>:
>
> Bah - from http://jira.pentaho.com/browse/MONDRIAN-555 I take it it's a
> no? :S
>
> No, that bug only relates if you have ancestor and descendant members in
> the same set. It will affect totals to a value that is arguably wrong, but
> the slicer should still work.
>
> Julian
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.pentaho.org/pipermail/mondrian/attachments/20120503/d209a909/attachment-0001.html
>
> ------------------------------
>
> Message: 5
> Date: Fri, 4 May 2012 10:06:31 +0100
> From: Pedro Alves <pmgalves at gmail.com <javascript:;>>
> Subject: Re: [Mondrian] Multiple sets in where clause
> To: Mondrian developer mailing list <mondrian at pentaho.org <javascript:;>>
> Message-ID: <4FA39C17.2000909 at gmail.com <javascript:;>>
> Content-Type: text/plain; charset="UTF-8"; format=flowed
>
>
> Crossjoin works just as I want. Initially I'd think it would be
> inefficient but works great for what I'm working on. Will keep you posted
>
> On Fri 04 May 2012 05:37:47 AM WEST, Julian Hyde wrote:
> >>> On Thu 03 May 2012 05:27:34 PM WEST, Pedro Alves wrote:
> >>>> Is it supported without ussing calculated (aggregated) members?
> >>>>
> >>>> Where <set> works.
> >>>>
> >>>> where (<member>,<member>) works
> >>>>
> >>>> where ( <set> * <set> works but this isn't a very optimal approach.
> >>>>
> >>>> I feel I'm missing something very very obvious
> >
> > Pedro you may be missing something obvious but I'm not sure what it is.
> >
> > Remember that in MDX:
> >
> >   *  ( ... ) is the syntax for a tuple. Members must be from different
> >     hierarchies.
> >   * { ... } is the syntax for a set. Elements in the set must be
> >     members (or tuples) with the same dimensionality.
> >
> >
> > You can write
> >
> > WHERE {h1.m1, h1.m2} * {h2.m1}
> >
> > or
> >
> > WHERE CROSSJOIN({h1.m1, h1.m2},  {h2.m1})
> >
> > or
> >
> > WHERE {(h1.m1, h2.,1), (h1.m2, m2.m2)}
> >
> > and they are all valid and equivalent.
> >
> >
> >> Am 03.05.2012 um 18:30 schrieb Pedro Alves <pmgalves at gmail.com<javascript:;>
> >> <mailto:pmgalves at gmail.com <javascript:;>>>:
> >>
> >>> Bah - from http://jira.pentaho.com/browse/MONDRIAN-555 I take it
> >>> it's a no? :S
> >
> > No, that bug only relates if you have ancestor and descendant members
> > in the same set. It will affect totals to a value that is arguably
> > wrong, but the slicer should still work.
> >
> > Julian
> >
> >
> >
> > _______________________________________________
> > Mondrian mailing list
> > Mondrian at pentaho.org <javascript:;>
> > http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
> ------------------------------
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <javascript:;>
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
> End of Mondrian Digest, Vol 66, Issue 1
> ***************************************
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <javascript:;>
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120507/0e1e97ef/attachment-0001.html 


More information about the Mondrian mailing list