[Mondrian] Except function performance

Hilario Fernandes hilario.fernandes at cortex-intelligence.com
Tue Jun 16 10:55:12 EDT 2015


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


More information about the Mondrian mailing list