[Mondrian] Except function performance

Hilario Fernandes hilario.fernandes at cortex-intelligence.com
Tue Jun 16 16:08:45 EDT 2015


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20150616/fda57e35/attachment-0001.html 


More information about the Mondrian mailing list