[Mondrian] Except function performance

Hilario Fernandes hilario.fernandes at cortex-intelligence.com
Mon Jun 15 13:35:51 EDT 2015


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


More information about the Mondrian mailing list