[Mondrian] Except function performance

Hilario Fernandes hilario.fernandes at cortex-intelligence.com
Mon Jun 15 12:40:58 EDT 2015


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


More information about the Mondrian mailing list