[Mondrian] Except function performance

Luc Boudreau lucboudreau at gmail.com
Tue Jun 16 11:00:43 EDT 2015


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


More information about the Mondrian mailing list