[Mondrian] Multiple sets in where clause

Benny Chow bchow at pentaho.com
Fri May 4 21:37:42 EDT 2012 

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 [mailto:mondrian-bounces at pentaho.org] On Behalf Of mondrian-request at pentaho.org
Sent: Saturday, May 05, 2012 12:00 AM
To: mondrian at pentaho.org
Subject: Mondrian Digest, Vol 66, Issue 1

Send Mondrian mailing list submissions to
	mondrian at pentaho.org

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

You can reach the person managing the list at
	mondrian-owner at pentaho.org

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>
Subject: [Mondrian] Multiple sets in where clause
To: Mondrian developer mailing list <mondrian at pentaho.org>
Message-ID: <4FA2B1F6.9060904 at gmail.com>
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>
Subject: Re: [Mondrian] Multiple sets in where clause
To: Mondrian developer mailing list <mondrian at pentaho.org>
Message-ID: <4FA2B296.6070402 at gmail.com>
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>
Subject: Re: [Mondrian] Multiple sets in where clause
To: Mondrian developer mailing list <mondrian at pentaho.org>
Cc: Mondrian developer mailing list <mondrian at pentaho.org>
Message-ID: <CA581489-AFD4-4AC6-9ACA-FB6CB0282C39 at gmail.com>
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>:

> 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
> 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>
Subject: Re: [Mondrian] Multiple sets in where clause
To: Mondrian developer mailing list <mondrian at pentaho.org>
Message-ID: <4EE9DE5B-CD73-4234-B515-0933FF89C2C0 at pentaho.com>
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<mailto:pmgalves at gmail.com>>:

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>
Subject: Re: [Mondrian] Multiple sets in where clause
To: Mondrian developer mailing list <mondrian at pentaho.org>
Message-ID: <4FA39C17.2000909 at gmail.com>
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
>> <mailto:pmgalves at gmail.com>>:
>>
>>> 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
> http://lists.pentaho.org/mailman/listinfo/mondrian


------------------------------

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian


End of Mondrian Digest, Vol 66, Issue 1
***************************************

More information about the Mondrian mailing list