[Mondrian] Except function performance

Luc Boudreau lucboudreau at gmail.com
Tue Jun 16 16:30:25 EDT 2015


The full docs about Cache() is here.

http://mondrian.pentaho.com/documentation/performance.php#Optimizing_Calculations_with_the_Expression_Cache

Not sure why it wouldn't work. It's a trivial function implementation.

On Tue, Jun 16, 2015 at 4:08 PM, Hilario Fernandes <
hilario.fernandes at cortex-intelligence.com> wrote:

> Unfortunately using the Cache() around the except in the FILTERED_D set
> seems no make no difference... Any reason why it shouldn't?
>
>
>
> On Tue, Jun 16, 2015 at 1:47 PM, Brandon Jackson <usbrandon at gmail.com>
> wrote:
>
>> Luc!  Thanks for that golden nugget.
>>
>> Sent from my iPhone
>>
>> On Jun 16, 2015, at 10:00 AM, Luc Boudreau <lucboudreau at gmail.com> wrote:
>>
>> If you know that a given mdx expression will always return the same
>> value, you can wrap it in a Cache() function.
>> On Jun 16, 2015 10:55, "Hilario Fernandes" <
>> hilario.fernandes at cortex-intelligence.com> wrote:
>>
>>> The set returned by the Except() is small, 3 members. If we replace the
>>> except with those 3 members, the performance is closer to expected.
>>>
>>> Is there some way we can avoid the except from being recalculated on
>>> each iteration?
>>>
>>> On Mon, Jun 15, 2015 at 2:44 PM, Matt Campbell <mcampbell at pentaho.com>
>>> wrote:
>>>
>>>>  NonEmptyCrossJoin and Crossjoin on a NON EMPTY axis are mapped to the
>>>> same native evaluator, so there should be no difference.
>>>>
>>>>
>>>>
>>>> How big is the set returned by Except?  That Aggregate() needs to be
>>>> evaluated for each tuple on the rows, which can be time consuming for
>>>> larger sets.
>>>>
>>>> Just out of curiosity, if you replace the named set containing the
>>>> Except with the same enumerated members the except() would return, do you
>>>> see much difference?
>>>>
>>>>
>>>>
>>>> *From:* mondrian-bounces at pentaho.org [mailto:
>>>> mondrian-bounces at pentaho.org] *On Behalf Of *Hilario Fernandes
>>>> *Sent:* Monday, June 15, 2015 1:36 PM
>>>> *To:* Mondrian developer mailing list
>>>>
>>>> *Subject:* Re: [Mondrian] Except function performance
>>>>
>>>>
>>>>
>>>> Thank you for your reply Matt!
>>>>
>>>>
>>>>
>>>> We are using the CrossJoin() function in our mdx, i just used the '*'
>>>> operator for readability. Will NonEmptyCrossjoin() be more efficient than
>>>> CrossJoin() considering that we are using NonEmpty on the axis?
>>>>
>>>>
>>>>
>>>> Unfortunately we already have the ExpandNonNative property set to true.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Mon, Jun 15, 2015 at 2:26 PM, Matt Campbell <mcampbell at pentaho.com>
>>>> wrote:
>>>>
>>>>  Except() is not handled natively.  You can try setting
>>>> “mondrian.native.ExpandNonNative=true”, which will evaluate the except
>>>> non-natively and attempt to include the resulting tuples in a native
>>>> context.  That property is enabled by default in Pentaho biserver, but
>>>> false by default in Mondrian.
>>>>
>>>>
>>>>
>>>> Another thing that caught my eye- the ‘*’ crossjoin operator in your
>>>> query is not mapped to native crossjoin (MONDRIAN-2284).  So that could
>>>> also be a factor.  Try replacing the crossjoins with nested
>>>> NonEmptyCrossJoin().
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> *From:* mondrian-bounces at pentaho.org [mailto:
>>>> mondrian-bounces at pentaho.org] *On Behalf Of *Hilario Fernandes
>>>> *Sent:* Monday, June 15, 2015 12:41 PM
>>>> *To:* Mondrian mailing list
>>>> *Subject:* Re: [Mondrian] Except function performance
>>>>
>>>>
>>>>
>>>> Hello!
>>>>
>>>>
>>>>
>>>> I've sent this question a while ago without any replay, bringing it up
>>>> again.
>>>>
>>>>
>>>>
>>>> As I could see, using something like except in that crossjoin, it
>>>> cannot use a native evaluation and that slows things really a lot. This is
>>>> as far as i've gotten, maybe someone that knows how things work more in
>>>> dept can throw some comments in?
>>>>
>>>>
>>>>
>>>> Thanks
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Wed, Mar 4, 2015 at 2:15 PM, Hilario Fernandes <
>>>> hilario.fernandes at cortex-intelligence.com> wrote:
>>>>
>>>>  Hi!
>>>>
>>>>
>>>>
>>>> I'm having somewhat of a performance problem when trying execute the
>>>> following query:
>>>>
>>>>
>>>>
>>>> WITH
>>>>
>>>> MEMBER [Measures].[TotalA] AS
>>>>
>>>> IIF((NOT IsEmpty([Measures].[M1])), Aggregate({[A].CurrentMember} *
>>>> {[B].[Total B]} * {[C].[Total C]} * {[FILTERED_D]} * {[E].[Total E]},
>>>> [Measures].[M1]), NULL)
>>>>
>>>>
>>>>
>>>> SET [FILTERED_D] AS
>>>>
>>>> Except({[D].[D].Members},{[D].[D].[member1], [D].[D].[member2]})})
>>>>
>>>>
>>>>
>>>> SELECT
>>>>
>>>> NON EMPTY ({[Measures].[M1], [Measures].[TotalA]}) ON COLUMNS,
>>>>
>>>> NON EMPTY [A].[A].Members * [B].[B].Members * [C].[C].Members
>>>> * [FILTERED_D] * [E].[E].Members ON ROWS
>>>>
>>>> FROM [C]
>>>>
>>>>
>>>>
>>>> The point of the TotalA measure is to return the total of the measure
>>>> for every A member, repeating it for the rest of the other dimension
>>>> members on the axis. The problem is this measure must respect the filters
>>>> in that axis, so the except is placed both on the rows and used on the
>>>> aggregate.
>>>>
>>>>
>>>>
>>>> Thing is, when the except is used in the TotalA calculation its makes
>>>> the evaluation a lot slower. This only happens with except, if something
>>>> like Filter(members, measure >100) is used then there is no major
>>>> difference.
>>>>
>>>>
>>>>
>>>> Can anyone shed some light on why this happens? And maybe some ideas to
>>>> work around it with some other way to achieve the same result.
>>>>
>>>>
>>>>
>>>> Thanks
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> Hilario Fernandes
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> Hilario Fernandes
>>>>
>>>>
>>>> _______________________________________________
>>>> Mondrian mailing list
>>>> Mondrian at pentaho.org
>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> Hilario Fernandes
>>>>
>>>> _______________________________________________
>>>> Mondrian mailing list
>>>> Mondrian at pentaho.org
>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>
>>>>
>>>
>>>
>>> --
>>> Hilario Fernandes
>>>
>>> _______________________________________________
>>> 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
>>
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>
>
> --
> Hilario Fernandes
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20150616/87c664c2/attachment-0001.html 


More information about the Mondrian mailing list